mysql备份方案的选择

[TOC]

概述

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

mysqldump

• 若未启用binlog，请取消 --master-data=2
• 若缺少 --triggers --events --routines 则备份过程会丢失 trigger/event/proc/function
• 若开启gtid，添加 --set-gtid-purged=off 可避免将gtid信息导出
• 添加 --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版本及对应的依赖

#.mysql-5.7 推荐安装 xtrabackup-2.4.28，只依赖perl-Digest-MD5
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
xtrabackup --version

#.mysql-8.0.34 推荐安装 xtrabackup-80-8.0.34，依赖zstd及perl-Digest-MD5
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
xtrabackup --version

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

#.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;"

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