一文看懂mysql数据库备份恢复

2019-09-20

概述

需求：模拟生产数据库故障，恢复到故障前一秒

场景：有一份初始备份和后面的binlog，早上9点故障，然后直接拿所有的备份和binlog到另外一台服务器做恢复，按备份文件和备份文件记录的位置+最后8.59分的时间之间的binlog恢复。

下面使用MySQLdump+binlog来测试备份与恢复。

一、环境准备

1、备份数据库(数据库实例为test)

mysqldump -u root -p test --single_transaction --flush-logs --master-data=2 > /backup/test-`date +"%Y%m%d-%H%M%S"`.sql;

说明：

当master_data和 single_transaction 同时使用时，先加全局读锁，然后设置事务一致性和使用一致性快照开始事务，然后马上就取消锁，然后执行导出。过程如下

FLUSH TABLES WITH READ LOCK
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
SHOW MASTER STATUS -- 这一步就是取出 binlog index and position
UNLOCK TABLES
...dump...

如果需要binlog信息则使用 master_data;

如果不想阻塞同时表是innodb引擎可使用 single_transaction 取得一致性快照(取出的数据是导出开始时刻事务点的状态)

如果表不支持多版本特性，则只能使用 lock-all-tables 阻塞方式来保证一致性的导出数据。

2、向表中插入数据

show master statusG;
use test;
create table tb1(id int);
insert into tb1 values (10),(20),(30);
select * from tb1;

二、模拟故障

1、继续插入数据，在有备份的情况下删除数据库，模拟误操作

use test;
insert into tb1 values (40),(50);
drop database test;
show databases;

此时查看数据库发现test库就没有了。

三、恢复前准备

1、查看binlog

获取备份文件和故障前最新的binlog

mysqlbinlog --base64-output=decode-rows -v --start-datetime="2019-09-11 15:00:00" --stop-datetime="2019-09-11 16:00:00" mysql-bin.000005

说明：配置文件使用了binlog_format= row，查看数据库binlog内容时候就看不到增删改查的具体语句，都是经过64位编码转换后的内容，所以需要加参数--base64-output=decode-rows转换。

2、导出故障前的binlog日志并输出为sql文件

mysqlbinlog --start-position=154 --stop-position=10189 -d test mysql-bin.000005 > /backup/binlog-`date +"%Y%m%d-%H%M%S"`.sql

也可以用初始位置+最后时间来恢复：

mysqlbinlog --start-position=154 --stop-datetime="xxx" -d test mysql-bin.000005 > /backup/binlog-`date +"%Y%m%d-%H%M%S"`.sql

四、开始恢复

导入之前的所有备份文件及binlog文件

mysql -uroot -p test < /backup/test-20190911-153754.sql 
mysql -uroot -p test < /backup/binlog-20190911-171045.sql

五、验证

到此数据成功全部恢复！