该脚本用于数据备份,同步到另外的库,使用场景包括生产数据库同步到测试,开发库。
#!/bin/sh
#auther jack date:20200827
backhost= #备份数据库的地址
backport= #备份数据库的端口
backpasswd= #备份数据库的密码
backac= #备份数据库的用户
imhost= #同步数据库的地址
import= #同步数据库的端口
impasswd= #同步数据库的密码
imac=root #同步数据库的账户
backdatabase1=" " #备份数据库的数据库名1
backdatabase2=" " #备份数据库的数据库名2
backfile1="$backdatabase1"_"$backdate" #备份文件名1
backfile2="$backdatabase2"_"$backdate" 备份文件名2
imdatabase1=" " #导入数据库名1
imdatabase2=" " #导入数据库名2
ignoretable1=base_ddct #忽略表1
ignoretable2=sys_user #忽略表2
ignoretable3=biz_msg_stg_inf #忽略表3
ignoretable4=biz_msg_config #忽略表4
ignoretable5=biz_cst_bsc_inf #忽略表5
ignoretable6=base_stm_parm #忽略表6
#数据库备份
mysqldump -u$backac -h$backhost -P$backport -p$backpasswd --databases $backdatabase1 --set-gtid-purged=off --ignore-table=$backdatabase1.$ignoretable1 --ignore-table=$backdatabase1.$ignoretable2 --ignore-table=$backdatabase1.$ignoretable3 --ignore-table=$backdatabase1.$ignoretable4 --ignore-table=$backdatabase1.$ignoretable5 --ignore-table=$backdatabase1.$ignoretable6 > /root/sh/datash/"$backdatabase1"_"$(date +%Y_%m_%d_%H)".sql
if [ $? -ne 0 ]; then
echo "$(date "+%Y-%m-%d %H:%M:%S") The fc's $backdatabase1 back up failed"
else
echo "$(date "+%Y-%m-%d %H:%M:%S") The fc's $backdatabase1 back up succeed"
fi
mysqldump -u$backac -h$backhost -P$backport -p$backpasswd --databases $backdatabase2 --set-gtid-purged=off > /root/sh/datash/"$backdatabase2"_"$(date +%Y_%m_%d_%H)".sql
if [ $? -ne 0 ]; then
echo "$(date "+%Y-%m-%d %H:%M:%S") The fc's $backdatabase2 back up failed"
else
echo "$(date "+%Y-%m-%d %H:%M:%S") The fc's $backdatabase2 back up succeed"
#SQL库名替换
sed -i "s/$backdatabase1/$imdatabase1/g" /root/sh/datash/"$backdatabase1"_"$(date +%Y_%m_%d_%H)".sql
sed -i "s/$backdatabase2/$imdatabase2/g" /root/sh/datash/"$backdatabase2"_"$(date +%Y_%m_%d_%H)".sql
fi
#数据库导入
mysql -u $imac -h$imhost -P$import -p$impasswd $imdatabase1 < /root/sh/datash/"$backdatabase1"_"$(date +%Y_%m_%d_%H)".sql
if [ $? -ne 0 ]; then
echo "$(date "+%Y-%m-%d %H:%M:%S") The fc's $imdatabase1 Import failed"
else
echo "$(date "+%Y-%m-%d %H:%M:%S") The fc's $imdatabase1 Import succeed"
fi
mysql -u $imac -h$imhost -P$import -p$impasswd $imdatabase2 < /root/sh/datash/"$backdatabase2"_"$(date +%Y_%m_%d_%H)".sql
if [ $? -ne 0 ]; then
echo "$(date "+%Y-%m-%d %H:%M:%S") The fc's $imdatabase2 Import failed"
else
echo "$(date "+%Y-%m-%d %H:%M:%S") The fc's $imdatabase2 Import succeed"
fi
#备份清理
find /root/sh/datash -mtime +30 -name "*.sql" -exec rm -rf {} \; #清理30天之前的备份
if [ $? -ne 0 ]; then
echo "$(date "+%Y-%m-%d %H:%M:%S") The file has been deleted successfully"
else
echo "$(date "+%Y-%m-%d %H:%M:%S") No file to delete"
fi
定时任务配置:
运行定时任务命令:crontab -e
添加如下字段
0 2 * * * /root/sh/datash/fc_data.sh>>/root/sh/datash/logs/log.txt #每日凌晨两点执行一次
效果如下: