MySQL备份

一个多表备份的脚本例子：

#/bin/bash
# 初始化变量
user=root
password=123456
backup_dir="/data/mysql_backup"
time="$(date +"%Y%m%d_%H%M%S")"
data_file="$backup_dir/meter_copy_center"_"$time.sql"
# 删除上次生成的.sql文件
rm ${backup_dir}/*.sql
# 生成表名
tables="meter_server_push meter_server_client meter_server_convertor meter_server_meter_electric meter_server_meter_water meter_server_meter_flow meter_server_ctwing_app meter_server_ctwing_product meter_server_ctwing_tenant meter_server_daily_indicator"
for i in {2022..2024}
do
  tables="${tables} meter_server_daily_freeze_${i}"
  tables="${tables} meter_server_data_electric_${i}"
  tables="${tables} meter_server_data_water_${i}"
  tables="${tables} meter_server_data_flow_${i}"
done
echo ${tables}
# 备份
mysqldump -u${user} -p${password} meter_copy_center ${tables} > $data_file
# 压缩
tar_file="$data_file".tar
tar -zcvf ${tar_file} ${data_file}
# 删除超过3天的文件
find $backup_dir -mtime +3 -type f | xargs rm -f
# 远程拷贝
user=root
password=123456
sshpass -p ${password} scp -P 22 ${tar_file} ${user}@127.0.0.1:~/mysql_backup

参考：

• MySQL备份（一）增量