mysql备份脚本

!/bin/bash

备份路径

db_backup_path="/data/backup"

备份的数据库

db_name=("live_net")

用户名

db_user="root"

密码

db_pass="mysql@abc.net"

gzip文件解压缩密码

gz_des_pass="fff@20210326"

压缩文件前缀

prefix=“credit"

日志路径

log_path="${db_backup_path}/${prefix}_backup.log"

系统时间

date_format="$(date +%Y%m%d%H%M)"

日志保留时间

del_days="30"

find "$db_backup_path" -name "${prefix}-20*.des3" -type f -mtime +${del_days} |xargs rm -rf
[ $? -eq 0 ] && echo "${date_format} Delete ${del_days} days ago backup file success" || echo -e "${date_format} Delete Old backup file failure"

for DB in $db_name ;do
#加密
#mysqldump -u $db_user -p$db_pass --routines --triggers --single-transaction --flush-logs --databases ${DB} | gzip - | openssl des3 -salt -k ${gz_des_pass} -out ${db_backup_path}/${prefix}-${date_format}-${DB}.sql.gz.des3
[ $? -eq 0 ] && echo "${date_format} mysqldump database ${DB} backup success" >> $log_path || echo -e "${date_format} mysqldump database ${DB} backup failure" >> $log_path

#不加密
#mysqldump -h $db_host -u $db_user -p$db_pass --column-statistics=0 --routines --triggers --single-transaction --databases ${DB} | gzip >  ${db_backup_path}/${date_format}-${DB}.sql.gz

done

8.0版本以后，需要在mysqldump 后面加上 --column-statistics=0 ，否则报错 “Unknown table 'COLUMN_STATISTICS' in information_schema (1109)”
解压缩

openssl des3 -d -salt -in credit-202103261354-live_net.sql.gz.des3 -out credit-202103261354-live_net.sql.gz

enter des-ede3-cbc decryption password: