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