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