使用mysqldump备份指定数据库并异地存储

环境

  • 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.rpmexpect-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-binserver-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

参考