概述

需求：模擬生產(chǎn)數(shù)據(jù)庫故障，恢復(fù)到故障前一秒

場景：有一份初始備份和后面的binlog，早上9點(diǎn)故障，然后直接拿所有的備份和binlog到另外一臺服務(wù)器做恢復(fù)，按備份文件和備份文件記錄的位置+最后8.59分的時(shí)間之間的binlog恢復(fù)。

下面使用MySQLdump+binlog來測試備份與恢復(fù)。

一、環(huán)境準(zhǔn)備

1、備份數(shù)據(jù)庫(數(shù)據(jù)庫實(shí)例為test)

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

說明：

當(dāng)master_data和 single_transaction 同時(shí)使用時(shí)，先加全局讀鎖，然后設(shè)置事務(wù)一致性和使用一致性快照開始事務(wù)，然后馬上就取消鎖，然后執(zhí)行導(dǎo)出。過程如下

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;

如果不想阻塞同時(shí)表是innodb引擎可使用 single_transaction 取得一致性快照(取出的數(shù)據(jù)是導(dǎo)出開始時(shí)刻事務(wù)點(diǎn)的狀態(tài))

如果表不支持多版本特性，則只能使用 lock-all-tables 阻塞方式來保證一致性的導(dǎo)出數(shù)據(jù)。

2、向表中插入數(shù)據(jù)

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

二、模擬故障

1、繼續(xù)插入數(shù)據(jù)，在有備份的情況下刪除數(shù)據(jù)庫，模擬誤操作

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

此時(shí)查看數(shù)據(jù)庫發(fā)現(xiàn)test庫就沒有了。

三、恢復(fù)前準(zhǔn)備

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，查看數(shù)據(jù)庫binlog內(nèi)容時(shí)候就看不到增刪改查的具體語句，都是經(jīng)過64位編碼轉(zhuǎn)換后的內(nèi)容，所以需要加參數(shù)--base64-output=decode-rows轉(zhuǎn)換。

2、導(dǎo)出故障前的binlog日志并輸出為sql文件

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

也可以用初始位置+最后時(shí)間來恢復(fù)：

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

四、開始恢復(fù)

導(dǎo)入之前的所有備份文件及binlog文件

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

五、驗(yàn)證

到此數(shù)據(jù)成功全部恢復(fù)！