mysql备份方案的选择

概述

• 对于DBA来说，备份和刷新简历是最重要的两项工作，如果发生故障后发现备份也不可用，那么刷新简历的重要性就显现出来。
• 考虑到备份还原的耗时和繁杂程度，针对数据库的大小，推荐不同的备份方式，大致如下：

实测：data 目录约 588G，执行 xtrabackup 备份耗时 74 分钟、备份文件 182G (ECS规格: 8核-64G内存)；目标机器至少需要 1552G 空间 (data 目录 588G、xtra_bak 目录 782G、备份文件 182G)

mysqldump

• 若未启用binlog，则去掉 --master-data=2
• 若开启gtid，添加 --set-gtid-purged=off 可避免将gtid信息导出
• 添加 --triggers --events --routines 可避免备份过程丢失 trigger/event/proc/function
• 添加 --master-data 将binlog的位置和文件名追加到输出文件中
• 添加 --single-transaction 导出之前提交一个BEGIN语句，BEGIN不会阻塞任何程序且能保证数据库的一致性，仅InnoDB
• 添加 --skip-tz-utc 可解决current_timestamp字段导出后偏差8小时
• 若报错 Can't connect to MySQL server x.x.x.x when trying to dump tablespaces，可添加 --all-tablespaces
• 由于华为云RDS提供的root账号其实没有super权限，在导入.sql备份文件的时候，function/view/procedure/trigger/event 会报错，将备份文件中的 DEFINER='root'@'%' 去掉之后就可以成功导入

只备库结构：mysqldump --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc -h 127.0.0.1 -uroot -p -d --databases db1 > db1.2023.sql
只备库数据：mysqldump --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc -h 127.0.0.1 -uroot -p -t --databases db1 > db2.2023.sql
备份单个库：mysqldump --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc -h 127.0.0.1 -uroot -p --databases db1 > db3.2023.sql
备份多个库：mysqldump --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc -h 127.0.0.1 -uroot -p --databases db1 db2 > db4.2023.sql
备份所有库：mysqldump --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc -h 127.0.0.1 -uroot -p -A > db5.2023.sql

导出多张表（结构+数据)：mysqldump --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc -h 127.0.0.1 -uroot -p db1 table1 table2 > table1.2023.sql
导出多张表（仅备结构）：mysqldump --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc -h 127.0.0.1 -uroot -p -d db1 table1 table2 > table2.2023.sql
导出多张表（仅备数据）：mysqldump --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc -h 127.0.0.1 -uroot -p -t db1 table1 table2 > table3.2023.sql

导出查询结果：mysql -h 127.0.0.1 -uroot -p -e "select id from testdb.tb01;" > result1.sql

如何在18G的mysqldump文件中恢复指定表

• 如何在18GB的实例备份文件中，快速恢复 xxx库.yyy表 这一张表呢？

[root@localhost]# cat -n mysqldump.sql | grep xxx
 99433  -- Current Database: `xxx`
 99436  CREATE DATABASE /*!32312 IF NOT EXISTS*/ `xxx` /*!40100 DEFAULT CHARACTER SET utf8 */;
 99438  USE `xxx`;
101149  -- Dumping events for database 'xxx'
101153  -- Dumping routines for database 'xxx'

[root@localhost]# sed -n 99436,101153p mysqldump.sql > sed.log

[root@localhost]# cat sed.log | grep INSERT | grep VALUES | grep "\`yyy\`" > yyy.sql
[root@localhost]# du -sh *
18G     mysqldump.sql
313M    sed.log
6.2M    yyy.sql

xtrabackup

• 推荐rpm安装 xtrabackup，注意根据mysql版本安装对应的xtrabackup版本及对应的依赖

#.针对ubuntu安装xtrabackup-2.4
wget -c https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
percona-release enable-only tools
apt update
apt install -y qpress percona-xtrabackup-24

#.mysql-5.7 推荐安装 xtrabackup-2.4.28，依赖perl-Digest-MD5及qpress
# yum install -y epel-release
# yum install -y libev perl-DBD-MySQL perl-DBI perl-Digest-MD5 libaio rsync perl-Time-HiRes
rpm -ivh http://iso.sqlfans.cn/mysql/perl-Digest-MD5-2.52-3.el7.x86_64.rpm
rpm -ivh http://iso.sqlfans.cn/mysql/percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm
rpm -ivh http://iso.sqlfans.cn/mysql/qpress-11-1.el7.x86_64.rpm
xtrabackup --version

#.mysql-8.0.34 推荐安装 xtrabackup-80-8.0.34，依赖zstd及perl-Digest-MD5及qpress
rpm -ivh http://iso.sqlfans.cn/mysql/zstd-1.5.5-1.el7.x86_64.rpm
rpm -ivh http://iso.sqlfans.cn/mysql/perl-Digest-MD5-2.52-3.el7.x86_64.rpm
rpm -ivh http://iso.sqlfans.cn/mysql/percona-xtrabackup-80-8.0.34-29.1.el7.x86_64.rpm
rpm -ivh http://iso.sqlfans.cn/mysql/qpress-11-1.el7.x86_64.rpm
xtrabackup --version

• 下面演示一下，从源示例（比如10.30.3.231）执行xtrabackup，并恢复到目标示例（比如10.30.3.232）的全过程，假设root密码为 Admin_147

注：若 /data 空间不足可以将 /data/mysql_3306/xtra_bak 改成别的挂载点

#.1.源实例：执行xtrabackup备份，--parallel=3开启3个线程
xtrabackup --defaults-file=/data/mysql_3306/my_3306.cnf --user=root --password=Admin_147 --socket=/tmp/mysql_3306.sock --compress --history --kill-long-queries-timeout=40 --parallel=3 --kill-long-query-type=all --tmpdir=/tmp/ --backup --stream=xbstream --slave-info --safe-slave-backup > /opt/xtrabackup_2023.xbstream

#.2.源实例：将备份文件到目标示例（比如10.30.3.232）
scp /opt/xtrabackup_2023.xbstream 10.30.3.232:/opt/

#.3.目标实例：解压备份文件
rm -r -f /data/mysql_3306/xtra_bak
mkdir -p /data/mysql_3306/xtra_bak
xbstream -x < /opt/xtrabackup_2023.xbstream -C /data/mysql_3306/xtra_bak

#.4.目标实例：执行prepare
xtrabackup --decompress --target-dir=/data/mysql_3306/xtra_bak
xtrabackup --prepare --target-dir=/data/mysql_3306/xtra_bak

#.5.目标实例：先关掉mysql实例，再改名datadir目录
mysqladmin -uroot -pAdmin_147 -S /tmp/mysql_3306.sock shutdown
mv /data/mysql_3306/iblog /data/mysql_3306/iblog_$(date +%Y%m%d%H%M%S)
mv /data/mysql_3306/var /data/mysql_3306/var_$(date +%Y%m%d%H%M%S)

#.6.目标实例：执行copy-back
xtrabackup --defaults-file=/data/mysql_3306/my_3306.cnf --copy-back --target-dir=/data/mysql_3306/xtra_bak

#.7.目标示例：启动mysql服务
chown -R mysql.mysql /data/mysql_3306
/opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf &
/opt/mysql/bin/mysql -uroot -pAdmin_147 -S /tmp/mysql_3306.sock -e "show databases;"

• [可选]：找到 Position 位置以便搭建主从复制

注：2026.08.17.遇到 xtrabackup_slave_info 中的 binlog 早已不存在，改从 xtrabackup_binlog_info 读取

# cat /data/mysql_3306/xtra_bak/xtrabackup_slave_info
cat /data/mysql_3306/xtra_bak/xtrabackup_binlog_info

mydumper

• 推荐rpm安装 mydumper-0.15.2-6

rpm -ivh http://iso.sqlfans.cn/mysql/mydumper-0.15.2-6.el7.x86_64.rpm
mydumper --version

• 下面演示一下，从源示例（比如10.30.3.231）执行mydumper备份，并通过myloader恢复到目标示例（比如10.30.3.232）的全过程，假设root密码为 Admin_147

#.1.源实例：准备测试数据
mysql -uroot -pAdmin_147 -S /tmp/mysql_3306.sock -e"create database if not exists db1;"
mysql -uroot -pAdmin_147 -S /tmp/mysql_3306.sock -e"create database if not exists db2;"
mysql -uroot -pAdmin_147 -S /tmp/mysql_3306.sock -e"create table if not exists db1.tb1(id int);"
mysql -uroot -pAdmin_147 -S /tmp/mysql_3306.sock -e"create table if not exists db2.tb2(id int);"
mysql -uroot -pAdmin_147 -S /tmp/mysql_3306.sock -e"insert into db1.tb1 values(1);"
mysql -uroot -pAdmin_147 -S /tmp/mysql_3306.sock -e"insert into db2.tb2 values(2);"

#.2.源实例：备份本地实例（比如10.30.3.231）到本地目录
mydumper -u root -p Admin_147 -S /tmp/mysql_3306.sock -o /data/mysql_3306/20221206

#.2.目标示例：备份远程实例（比如10.30.3.231）到本地目录，这里只备份了2个测试库 db1和db2
mydumper -h 10.30.3.231 -P 3306 -u root -p Admin_147 -R -E -G --trx-consistency-only --regex '^(db1|db2)' -o /data/mysql_3306/20221206

#.3.目标示例：恢复数据
myloader -u root -p Admin_147 -S /tmp/mysql_3306.sock -e -t 10 -d /data/mysql_3306/20221206

#.4.目标示例：确认数据
mysql -uroot -pAdmin_147 -S /tmp/mysql_3306.sock -e"show databases;"

#.查看binlog文件及pos,可用于slave搭建
cat /data/mysql_3306/20221206/metadata

部署定时备份

• 方案1：mysqldump 逻辑备份

#!/bin/sh

dt=$(date +%Y%m%d%H%M)
ip=$1
port=$2
user=dba_admin
pwd=cf_rB1NKCzbaQuPH
path=/data/mysql_$port/backup

echo "Begin dump..."
mkdir -p $path/today
mkdir -p $path/history
mv $path/*.tar.gz $path/history/
rm -f $path/today/*.sql
find $path/history/ -name "*.tar.gz" -mtime +3 -exec rm -f {} \;
dblist=`mysql -u$user -p$pwd -P$port -h$ip -e "show databases;" | grep -Ev "information_schema|mysql|sys|performance_schema" | grep -v "Database" 2>/dev/null`
for db in $dblist 
do 
  mysqldump --single-transaction --master-data=2 --set-gtid-purged=off --flush-logs --triggers --events --routines --skip-tz-utc -u$user -p$pwd -P$port -h$ip --databases $db > $path/today/${db}_${dt}.sql 2>/dev/null
done
tar zcvf $path/$dt.tar.gz $path/today
rm -f $path/today/*.sql

• 方案2：xtrabackup 物理备份 + 异地保存（备份到一个 windows 共享）

#!/bin/sh

dt=$(date +%Y%m%d%H%M)
ip=$1
port=$2
user=dba_admin
pwd=cf_rB1NKCzbaQuPH
path=/mnt/192.168.31.168
mycnf=/data/mysql_$port/my_$port.cnf

echo "Begin dump..."
mount -t cifs -o username=dba_copy,password="mgLg0Z_JHaqTwyf" //192.168.31.168/mysql_backup $path
mkdir -p $path/tmp
find $path/ -name "*.xbstream" -mtime +3 -exec rm -f {} \;
xtrabackup --defaults-file=$mycnf --user=$user --password=$pwd --compress --history --kill-long-queries-timeout=40 --kill-long-query-type=all --tmpdir=$path/tmp/ --parallel=3 --backup --stream=xbstream --slave-info --safe-slave-backup > $path/xtrbackup_full_$port_$dt.xbstream
umount $path

• 根据需要选择任一种备份方案，将备份脚本保存为 mysqldump_daily_backup.sh 然后配置为定时任务，示例每天 02:01 进行备份

#.配置任务
mkdir -p /scripts/crontab
touch /scripts/crontab/mysqldump_daily_backup.sh
chmod +x /scripts/crontab/mysqldump_daily_backup.sh
echo "1 2 * * * /scripts/crontab/mysqldump_daily_backup.sh 192.168.31.101 3306 > /dev/null 2>&1" >> /var/spool/cron/`whoami`
crontab -l | grep mysqldump_daily_backup

#.验证一次
/scripts/crontab/mysqldump_daily_backup.sh 192.168.31.101 3306

• 附：windows 共享的创建与取消

:: 创建共享
net user dba_copy mgLg0Z_JHaqTwyf /add /Y /expires:never
mkdir c:\share\mysql_backup
net share mysql_backup /del /y
net share mysql_backup=c:\share\mysql_backup /grant:dba_copy,change /remark:"mysql_backup"
net share

:: 取消共享
net share mysql_backup /del /y
rd /s /q c:\share\mysql_backup
net user dba_copy /del