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: