利用binlog日誌恢復mysql資料

1. 環境準備：

-- 建立資料庫，建立表

create database ops;

create database ops1;

use ops;

CREATE TABLE member (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

name varchar(16) NOT NULL,

sex enum('m','w') NOT NULL DEFAULT 'm',

age tinyint(3) unsigned NOT NULL,

classid char(6) DEFAULT NULL,

PRIMARY KEY (id)) ENGINE=InnoDB;

show tables;

use ops1;

CREATE TABLE member (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

name varchar(16) NOT NULL,

sex enum('m','w') NOT NULL DEFAULT 'm',

age tinyint(3) unsigned NOT NULL,

classid char(6) DEFAULT NULL,

PRIMARY KEY (id)) ENGINE=InnoDB;

show tables;

--插入資料

use ops;

insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2');

use ops1;

insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2');

2. 場景模擬：

a. ops庫進行一次全備份，記為時間t1

mysqldump -uroot -p -B -F -R -x --master-data=2 ops ops1|gzip >/u01/backup/ops_$(date +%F).sql.gz

b. 引數說明

-B： 指定資料庫

-F： 重新整理日誌

-R： 備份儲存過程等

-x:  鎖表

--master-data: 在備份語句裡新增change master語句以及binlog檔案及位置點資訊。

待到資料庫備份完成，就不用擔心資料丟失了，因為有完全備份資料，由於上面在全備份時使用了-F選項，當資料備份操作剛開始的時候系統就會自動重新整理log，這樣就會自動產生一個新的binlog日誌，這個新的binlog日誌就會用來記錄備份之後的資料庫‘增刪改操作’。

可以用show master status;進行查詢

3.insert 資料，記為時間t2

insert into ops.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');

insert into ops1.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');

4. update資料，記為時間t3：

update ops.member set name='lisi' where id=4;

update ops1.member set name='lisi' where id=4;

update ops.member set name='xiaoer' where id=2;

update ops1.member set name='xiaoer' where id=2;

5.drop 資料庫ops,ops1,建立ops2,記為時間t4：

drop database ops;

drop database ops1;

create database ops2;

CREATE TABLE member (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

name varchar(16) NOT NULL,

sex enum('m','w') NOT NULL DEFAULT 'm',

age tinyint(3) unsigned NOT NULL,

classid char(6) DEFAULT NULL,

PRIMARY KEY (id))

ENGINE=InnoDB;

insert into ops2.member(`name`,`sex`,`age`,`classid`) values

('yiyi','w',20,'cls1'),

('xiaoer','m',22,'cls3'),

('zhangsan','w',21,'cls5'),

('lisi','m',20,'cls4'),

('wangwu','w',26,'cls6');

6. ops,ops1清除了：

先仔細檢視最後一個binlog日誌，並記錄下關鍵的pos點，到底是那個pos點的操作導致了資料庫的破壞（通常是最後幾步）；

a. 先備份最後一個binlog日誌：

b. 執行一次重新整理日誌索引的操作，重新開始新的binlog日誌記錄檔案。

flush logs;

show master status;

7. 使用mysqlbinlog 工具檢視binlog的內容，查詢資料破壞的位點：

a. 方法一： mysqlbinlog --no-defaults mysql-bin.000005

b. 方法二：登入伺服器，show binlog events in 'mysql-bin.000005';

c. 方法三： show binlog events in 'mysql-bin.000005'\G;

造成ops庫資料破壞的Pos點介於2172-2261之間，造成pos1庫破壞的Pos區間是介於2326-2418之間，恢復相應pos點之前就可以了。

8.開始做恢復，先恢復時間點t1的資料：

ops,ops1兩個庫恢復回來了。

將資料恢復到2172這個位點,此時將資料恢復到了 t3時間點。

mysqlbinlog --no-defaults --stop-position=2172  mysql-bin.000005 | mysql -uroot -p

恢復完成後檢視ops,ops1資料庫的資料，可以看到是 t3時間點的資料。

9. drop ops2資料庫，透過Binlog進行恢復：

建立ops2的pos點為2483，

mysqlbinlog --no-defaults --start-position=2483  mysql-bin.000005 | mysql -uroot -p

檢查資料是否恢復：

參考文件：

https://www.cnblogs.com/Presley-lpc/p/9619571.html

https://www.cnblogs.com/tonnytangy/p/7779164.html

https://blog.csdn.net/weixin_40524659/article/details/105902881