centos7定时备份mysql并上传到其它服务器异地备份

服务器需要安装sshpass

yum install -y sshpass

shell脚本

#!/bin/bash


MYSQLDB=mcms
BAK_DIR=/backup/mysql8019/$MYSQLDB

if [ $UID -ne 0 ];then
        echo "Must to be use root for exec shell."
fi

if [ ! -d $BAK_DIR ];then
        mkdir -p $BAK_DIR
        echo -e "\033[32mTHE $BAK_DIR Create successfully!\033[0m"
else
        echo "This $BAK_DIR is exists... "
fi

####### Mysql Backup command ####### 

#删除7天前的备份文件
/usr/bin/find $BAK_DIR -mtime +7 | xargs rm -rf
#备份数据库为sql文件
/usr/bin/mysqldump $MYSQLDB > $BAK_DIR/`date +%Y%m%d`.sql
if [ $? -eq 0 ];then
        echo -e "\033mThe Mysql Backup $MYSQLDB Succeesfully!\033[0m"
else
        echo -e "\033mThe Mysql Backup $MYSQLDB Failed,please check!\033[0m"
fi
#判断目的服务器是否存在目标文件夹，不存在则创建
/usr/bin/sshpass -p123456 /usr/bin/ssh -p 22 admin@www.imwork.net <<EOF
	if [ ! -d $BAK_DIR ];then
		mkdir -p $BAK_DIR
	fi
EOF
#上传sql文件到目的服务器
/usr/bin/sshpass -p123456 /usr/bin/scp -P 22 $BAK_DIR/`date +%Y%m%d`.sql admin@www.imwork.net:$BAK_DIR

if [ $? -eq 0 ];then
        echo -e "\033mThe Mysql upload $MYSQLDB Succeesfully!\033[0m"
else
        echo -e "\033mThe Mysql upload $MYSQLDB Failed,please check!\033[0m"
fi

配置每天晚上2点执行定时任务

crontab -e 
0 2 * * * /usr/bin/bash bakcup_mysql.sh &> /backup/mysql8019/backup_$(date +\%F_\%T).log
:wq

如果crontab没启动，请启动

systemctl start crond.service