【MySQL运维】使用备份与binlog恢复MySQL数据

不管使用逻辑备份还是物理备份，结合binlog都可以对增量数据进行恢复的效果。本文基于在从库使用Xtrabackup全量备份+实时同步的binlog作为增量备份而实现数据恢复，恢复到的数据状态为最新数据而非指定时间节点数据。由于增量数据是基于binlog进行恢复，所以为了保证备份数据完全，数据库双1参数一定要配置。恢复数据时建议先使用临时的数据库实例进行还原验证，没有问题后再进行生产还原。

一、使用Xtrabackup备份恢复全量数据

1、解压经过LZ4压缩的xtrabackup全量备份（如未压缩可跳过）

lz4_decompress /data/mysql_backup/xmglb/mysqlfull_2021-04-06_3308.xbstream /data/restore_mysql/mysqlfull_2021-04-06_3308

2、解压xtrabackup流备份

xbstream -x < /data/restore_mysql/mysqlfull_2021-04-06_3308 -C /data/restore_mysql/

3、应用日志并还原备份文件，确保每次执行后有输出"completed OK"字符串

xtrabackup --defaults-file=/etc/my3333.cnf --prepare --target-dir=/data/restore_mysql/

xtrabackup --defaults-file=/etc/my3333.cnf --move-back --target-dir=/data/restore_mysql/

4、修改权限并启动还原实例

chown -R mysql. /data/mysql3333/ 
mysqld_safe --defaults-file=/etc/my3333.cnf &

5、在恢复完全量备份后，可以在原实例上创建一些测试数据作为埋点，比如创建test_restore数据库。在增量数据恢复完成后查看埋点数据是否已经还原成功，这样可确保数据恢复的完整性。实际还原过程中对备份文件校验的大致逻辑是恢复全备+增量数据，然后作为线上主库的从服务器，确保数据能正常同步。

二、使用binlog进行增量数据还原

1、确定增量数据起始点

#这里是物理备份，所以信息存放于xtrabackup_slave_info 
cat /data/mysql3333/xtrabackup_slave_info 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000025', MASTER_LOG_POS=887027467;

2、登录还原实例清空从库状态

mysql > reset slave all;

3、导入binlog

需要注意的是在用binlog进行数据恢复时，不能用mysqlbinlog工具解析出日志然后生成SQL文件来直接执行，因为有些语句的执行结果是依赖上下文的，直接执行的结果很可能是错误的。标准做法是用mysqlbinlog工具解析后通过管道传给MySQL执行，如果存在多个二进制日志，不建议一个一个恢复，而是一起导入。如果分两次操作就是开启两个session，如果刚好用到一个临时表，一个session退出，另一个session就会出错

#导入第一个binlog时注意position信息要正确 
mysqlbinlog --start-position=887027467 mysql-bin.000025 | mysql -udba -p -S /data/mysql3333/mysql.sock
mysqlbinlog mysql-bin.000026 mysql-bin.000027 | mysql -u root -p -S /data/mysql3333/mysql.sock

4、验证数据，如果用于标识的test_restore已经恢复，基本可以代表数据已经还原到最新位置

三、使用逻辑备份+binlog恢复数据示例

1、建立一个数据库，并在其中创建一张表并插入数据

mysql > create database student;
mysql > use student;
mysql > create table student(id int,name varchar(10),age int);
mysql > insert into student values ('1','tanglu','28'),('2','zouxiaolu','28'),('3','beibei','28');

2、进行完整的备份，这样之前的数据已经有了备份

mysqldump -u root -p  --master-data=2 student > student.sql

3、再次插入三条数据

mysql> insert into student values('4','doudou','8');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values('5','mengmeng','28');      
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values('6','haha','10');
Query OK, 1 row affected (0.01 sec)

4、模拟误操作，删除之前表格中的所有数据

mysql> delete from student ;

5、重启数据库或者执行flush-logs来获得一个新的二进制日志文件，这样故障操作日志都在之前的binlog文件里

mysql > flush logs;

6、用完整备份恢复数据，这样前面3条数据就恢复成功

mysql -u root -p student < student.sql

7、通过备份文件中的change master信息可以查找出全备时刻position位点，这里假设为355。通过mysqlbinlog或者show events 命令查找出误操作SQL的位点或GTID信息（通常误操作都在最后一个binlog文件中）。假设误操作的起始点为1294，所以只需要导出1294之前的语句用于恢复即可，通常是恢复到上一个事务COMMIT之后的位置。需要注意的是由于有些语句的执行结果依赖日志上下文，在进行数据恢复时不要将mysqlbinlog工具解析出来的日志存储为SQL文件再通过数据库导入，避免数据错误。推荐做法是用mysqlbinlog工具解析后通过管道传给MySQL直接执行，如果存在多个二进制日志，不建议一个一个恢复，而是一起导入。如果分两次操作就是开启两个session，如果刚好用到一个临时表，一个session退出，另一个session就会出错。示例如下： 

mysqlbinlog --database=linuxe --start-positon=355 --stop-position=1294 mysql-binlog.000003 | mysql -uroot -p123456;