使用mysqldump备份指定数据库并异地存储
2020-05-25 本文已影响0人
静美书斋
环境
- CentOS8.0.1905
- MySQL 5.7.29
- 备份数据库：192.168.101.231（使用root备份）
- 异地存储主机：192.168.101.230
1、备份服务器上安装expect服务
yum install expect -y
如果yum无法安装下载，则手动下载 tcl-8.6.8-2.el8.x86_64.rpm与expect-5.45.4-5.el8.x86_64.rpm安装即可。
2、备份数据库配置文件
/etc/my.cnf文件内容如下
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
pid-file=/usr/local/mysql/data/mysql.pid
log-error=/usr/local/mysql/data/error.log
character_set_server=utf8
user=mysql
max_connections=1500
symbolic-links=0
default-time-zone = '+8:00'
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_allowed_packet=1024M
lower_case_table_names=1
log-bin=mysql-bin
server-id=1
!includedir /etc/my.cnf.d
其中
log-bin与server-id必须要设置
3、备份脚本
功能：支持多数据库备份；支持异地存储备份；删除指定日期之前的数据库备份文件。
#!/bin/bash
#
# mysql params
mysql_user="root"
mysql_password="123456"
mysql_host="localhost"
mysql_port="3306"
mysql_databases="databases_name" # e.g DB1 DB2
mysql_database="database_name" # e.g DB1_DB2
#mysql_charset="utf8mb4"
mysqldump=$(which mysqldump 2>/dev/null)
# remote backup ip
remote1_ip="192.168.101.230" # mkdir -p /root/mysql_backup
remote1_password="123456"
#remote2_ip="10.10.0.1" # mkdir -p /root/mysql_backup
#remote2_password="123456"
# backup location
backup_location=/root/mysql_backup/
# delete files
expire_backup_delete="ON" # OFF | ON，默认开启删除
expire_days=7
backup_time=`date +%Y%m%d%H%M%S`
backup_dir=$backup_location
welcome_msg="Welcome to use MySQL backup tools!"
# check mysql
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
start_time=`date '+%Y-%m-%d %H:%M:%S'`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
echo "ERROR:MySQL is not running! backup stop!"
echo [${start_time}] "ERROR:MySQL is not running! backup stop!" >>${backup_location}/backup_log.txt
exit
else
echo $welcome_msg
echo [${start_time}] backup start >> ${backup_location}/backup_log.txt
fi
# do sql dump
${mysqldump} -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password --opt --single-transaction --master-data=2 -R -B $mysql_databases > $backup_dir/${mysql_database}-backup-$backup_time.sql
flag=`echo $?`
end_time=`date '+%Y-%m-%d %H:%M:%S'`
if [ $flag == "0" ];then
echo [${end_time}] "backup success" >>${backup_location}/backup_log.txt
else
echo [${end_time}] "backup fail!" >>${backup_location}/backup_log.txt
exit
fi
#zip
echo [${end_time}] start zip...${mysql_database}-backup-$backup_time.sql >> ${backup_location}/backup_log.txt
gzip $backup_dir/${mysql_database}-backup-${backup_time}.sql
flag=`echo $?`
end_time=`date '+%Y-%m-%d %H:%M:%S'`
if [ $flag == "0" ];then
echo [${end_time}] "zip success" >>${backup_location}/backup_log.txt
else
echo [${end_time}] "zip failed!" >>${backup_location}/backup_log.txt
exit
fi
# copy to remote
if [ -n "$remote1_ip" ]; then
echo [${end_time}] "copy to ${remote1_ip}...start" >> ${backup_location}/backup_log.txt
/usr/bin/expect <<-EOF
set timeout 5
spawn scp ${backup_dir}/${mysql_database}-backup-$backup_time.sql.gz root@${remote1_ip}:${backup_location}
expect "*password:" {set timeout 300; send "${remote1_password}\r"}
expect "*#"
EOF
sleep 1s
end_time=`date '+%Y-%m-%d %H:%M:%S'`
echo [${end_time}] "copy to ${remote1_ip} success" >> ${backup_location}/backup_log.txt
fi
if [ -n "$remote2_ip" ]; then
echo [${end_time}] "copy to ${remote2_ip}...start" >> ${backup_location}/backup_log.txt
/usr/bin/expect <<-EOF
set timeout 5
spawn scp ${backup_dir}/${mysql_database}-backup-$backup_time.sql.gz root@${remote2_ip}:${backup_location}
expect "*password:" {set timeout 300; send "${remote2_password}\r"}
expect "*#"
EOF
sleep 1s
end_time=`date '+%Y-%m-%d %H:%M:%S'`
echo [${end_time}] "copy to ${remote2_ip} success" >> ${backup_location}/backup_log.txt
fi
# do delete file
if [ "$expire_backup_delete" == "ON" -a "$backup_location" != "" ];then
`find $backup_location/ -type f -mtime +$expire_days | xargs rm -rf`
echo "Expired backup data delete complete!"
fi
备注
- 上述脚本保存为backup-db.sh，保存至/root/mysql_backup目录下
- 在同步备份至另一台异地存储主机时，必须执行一次
ssh root@192.168.101.230，并在此主机上执行命令：mkdir -p ${backup_location}（backup_location替换为真实的路径） - 其余参数按真实数替换
4、设置定时任务
crontab -e
# 每日凌晨1:30执行
30 01 * * * /root/mysql_backup/backup-db.sh
5、恢复
在需要恢复的数据库服务器端执行以下命令你（这里以mysql数据的root用户，密码为123456举例）
gunzip < backup.sql.gz | mysql -uroot -p123456
