mysql binlog 恢复 表_truncate 数据恢复【使用mysqlbinlog rewrite-db】

环境

CentOS Linux release 7.6.1810

MySQL 5.7.27-log row模式

这个方法只是执行指定时间段内的binlog恢复出单表或多表，若binlog完整未被删除过则可追回所有，若被删除过，则只可获得已有binlog内的数据

实际是将binlog重执行一次，将结果重新写入到指定的新库中去存在create database 是否会被重新执行【 执行会报错 Can't create database 】

rewrite-db

mysqlbinlog --skip-gtids --rewrite-db='ytest->re_ytest' --start-position=2538 --stop-position=3112 bin.000004|mysql re_ytest

rewrite-db 会将指定的binlog执行到另一个指定的库中去

示例

创建测试数据

-- 测试表与数据

create table tb_yq(id int auto_increment primary key ,va varchar(10),vb varchar(10));

create table tb_mc(id int auto_increment primary key ,va varchar(10),vb varchar(10));

create table tb_ps(id int auto_increment primary key ,va varchar(10),vb varchar(10));

insert into tb_yq (va,vb)values ('1','1'),('2','2'),('3','3'),('4','4'),('5','5'),('6','6');

insert into tb_mc (va,vb)values ('11','11'),('22','22'),('33','33'),('44','44'),('55','55'),('66','66');

insert into tb_ps (va,vb)values ('111','111'),('222','222'),('333','333'),('444','444'),('555','555'),('666','666');

update tb_yq set vb = '啦啦啦' where id = 5;

update tb_yq set vb = '隔壁老苗' where id = 8;

update tb_mc set vb = '隔壁老苗' where id = 6;

-- 当前表 tb_yq 数据为

mysql> select * from tb_yq;

+----+------+-----------+

| id | va | vb |

+----+------+-----------+

| 1 | 1 | 1 |

| 2 | 2 | 2 |

| 3 | 3 | 3 |

| 4 | 4 | 4 |

| 5 | 5 | 啦啦啦 |

| 6 | 6 | 6 |

+----+------+-----------+

6 rows in set (0.00 sec)

mysql> select * from tb_mc;

+----+------+--------------+

| id | va | vb |

+----+------+--------------+

| 1 | 11 | 11 |

| 2 | 22 | 22 |

| 3 | 33 | 33 |

| 4 | 44 | 44 |

| 5 | 55 | 55 |

| 6 | 66 | 隔壁老苗 |

+----+------+--------------+

6 rows in set (0.33 sec)

模拟手抖truncate了

假设手抖truncate了

truncate table tb_yq;

truncate table tb_mc;

模拟业务继续写入数据，注意这里由于表是设置的主键自增，truncate后写入的数据重新从1开始了，若主键与业务相关的话得注意后续的修正操作

insert into tb_yq (va,vb)values ('7','7'),('8','8');

insert into tb_mc (va,vb)values ('77','77'),('88','88');

update tb_yq set vb = '隔壁老王' where id = 2;

update tb_yq set vb = '隔壁老潘' where id = 7;

mysql> select * from tb_yq;

+----+------+--------------+

| id | va | vb |

+----+------+--------------+

| 1 | 7 | 7 |

| 2 | 8 | 隔壁老王 |

+----+------+--------------+

2 rows in set (0.00 sec)

mysql> select * from tb_mc;

+----+------+------+

| id | va | vb |

+----+------+------+

| 1 | 77 | 77 |

| 2 | 88 | 88 |

+----+------+------+

2 rows in set (0.00 sec)注意:主键从1开始了

找到truncate发生的日志位置

当前最新的是 bin.000007 ，执行以下 flush logs 刷新下日志文件

mysql> show binary logs;

+------------+-----------+

| Log_name | File_size |

+------------+-----------+

| bin.000003 | 4075 |

| bin.000004 | 3554 |

| bin.000005 | 1468 |

| bin.000006 | 4507 |

| bin.000007 | 5910 |

+------------+-----------+

5 rows in set (0.00 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.00 sec)

查看truncate的语句pos点，可得知truncate操作在 4746 与 4937 时准备开始执行，这个binlog初始的pos是4，那我们要恢复执行的可以从 4-4746的范围

mysql> show binlog events in 'bin.000007';

bin.000007 | 4 | Format_desc | 10 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 |

| bin.000007 | 123 | Previous_gtids | 10 | 194 | a7776f71-c8be-11e9-838f-0050563bb195:1-51

...略

| bin.000007 | 4389 | Gtid | 10 | 4454 | SET @@SESSION.GTID_NEXT= 'a7776f71-c8be-11e9-838f-0050563bb195:67' |

| bin.000007 | 4454 | Query | 10 | 4527 | BEGIN |

| bin.000007 | 4527 | Table_map | 10 | 4582 | table_id: 373 (ytest.tb_mc) |

| bin.000007 | 4582 | Update_rows | 10 | 4650 | table_id: 373 flags: STMT_END_F |

| bin.000007 | 4650 | Xid | 10 | 4681 | COMMIT /* xid=8543 */ |

| bin.000007 | 4681 | Gtid | 10 | 4746 | SET @@SESSION.GTID_NEXT= 'a7776f71-c8be-11e9-838f-0050563bb195:68' |

| bin.000007 | 4746 | Query | 10 | 4872 | use `ytest`; /* ApplicationName=DataGrip 2020.1 */ truncate table tb_yq |

| bin.000007 | 4872 | Gtid | 10 | 4937 | SET @@SESSION.GTID_NEXT= 'a7776f71-c8be-11e9-838f-0050563bb195:69' |

| bin.000007 | 4937 | Query | 10 | 5063 | use `ytest`; /* ApplicationName=DataGrip 2020.1 */ truncate table tb_mc |

| bin.000007 | 5063 | Gtid | 10 | 5128 | SET @@SESSION.GTID_NEXT= 'a7776f71-c8be-11e9-838f-0050563bb195:70' |

| bin.000007 | 5128 | Query | 10 | 5201 | BEGIN |

| bin.000007 | 5201 | Table_map | 10 | 5256 | table_id: 376 (ytest.tb_yq) |

| bin.000007 | 5256 | Write_rows | 10 | 5309 | table_id: 376 flags: STMT_END_F |

| bin.000007 | 5309 | Xid | 10 | 5340 | COMMIT /* xid=9118 */

...略

有时binlog太大，看起来太累，可以使用如下语句查看

[root@initnode101 ~]# mysqlbinlog -v --base64-output=decode-rows --start-datetime='2020-10-12 09:00:00' --stop-datetime='2020-10-12 10:00:00' /data/mysql_data/bin.000007|grep -i -B 10 truncate

### @3='隔壁老苗'

# at 4650

#201012 9:46:08 server id 10 end_log_pos 4681 CRC32 0x0c5becf4 Xid = 8543

COMMIT/*!*/;

# at 4681

#201012 9:46:23 server id 10 end_log_pos 4746 CRC32 0xd141756e GTID last_committed=16 sequence_number=17 rbr_only=no

SET @@SESSION.GTID_NEXT= 'a7776f71-c8be-11e9-838f-0050563bb195:68'/*!*/;

# at 4746

#201012 9:46:23 server id 10 end_log_pos 4872 CRC32 0xe21acbb5 Query thread_id=79 exec_time=0 error_code=0

SET TIMESTAMP=1602467183/*!*/;

/* ApplicationName=DataGrip 2020.1 */ truncate table tb_yq

/*!*/;

# at 4872

#201012 9:46:23 server id 10 end_log_pos 4937 CRC32 0xce0048c8 GTID last_committed=17 sequence_number=18 rbr_only=no

SET @@SESSION.GTID_NEXT= 'a7776f71-c8be-11e9-838f-0050563bb195:69'/*!*/;

# at 4937

#201012 9:46:23 server id 10 end_log_pos 5063 CRC32 0xc70a75d6 Query thread_id=79 exec_time=0 error_code=0

SET TIMESTAMP=1602467183/*!*/;

/* ApplicationName=DataGrip 2020.1 */ truncate table tb_mc

数据恢复

创建对应的库表

create database re_ytest;

use re_ytest

-- 由于我这已知在binlog中存在建表语句，因此就不创建了

create table re_ytest.tb_yq like ytest.tb_yq;

create table re_ytest.tb_mc like ytest.tb_mc;

执行恢复操作

mysqlbinlog --skip-gtids --rewrite-db='ytest->re_ytest' --start-position=4 --stop-position=4746 /data/mysql_data/bin.000007|mysql re_ytest

查看恢复结果

数据会被恢复到 re_ytest 库中去，binlog恢复时，表结构不存在数据不会被恢复进来，binlog内有新建表语句的数据会被恢复出来

mysql> use re_ytest

mysql> show tables;

+--------------------+

| Tables_in_re_ytest |

+--------------------+

| tb_mc |

| tb_ps |

| tb_yq |

+--------------------+

5 rows in set (0.00 sec)

mysql> select * from tb_yq;

+----+------+-----------+

| id | va | vb |

+----+------+-----------+

| 1 | 1 | 1 |

| 2 | 2 | 2 |

| 3 | 3 | 3 |

| 4 | 4 | 4 |

| 5 | 5 | 啦啦啦 |

| 6 | 6 | 6 |

+----+------+-----------+

6 rows in set (0.00 sec)

mysql> select * from tb_mc;

+----+------+--------------+

| id | va | vb |

+----+------+--------------+

| 1 | 11 | 11 |

| 2 | 22 | 22 |

| 3 | 33 | 33 |

| 4 | 44 | 44 |

| 5 | 55 | 55 |

| 6 | 66 | 隔壁老苗 |

+----+------+--------------+

6 rows in set (0.00 sec)

修正数据

将源库中新增的数据补到re_ytest中来，id问题就解决了

mysql> insert into re_ytest.tb_yq(va,vb) select va,vb from ytest.tb_yq;

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> rename table ytest.tb_yq to ytest.tb_yq_bak;

Query OK, 0 rows affected (0.00 sec)

mysql> rename table re_ytest.tb_yq to ytest.tb_yq;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from ytest.tb_yq;

+----+------+--------------+

| id | va | vb |

+----+------+--------------+

| 1 | 1 | 1 |

| 2 | 2 | 2 |

| 3 | 3 | 3 |

| 4 | 4 | 4 |

| 5 | 5 | 啦啦啦 |

| 6 | 6 | 6 |

| 7 | 7 | 7 |

| 8 | 8 | 隔壁老王 |

+----+------+--------------+

8 rows in set (0.00 sec)