king

Mysql闪回工具之binlog2sql的原理及其使用

king windows 2023-02-27 1121浏览 0

 

生产上误删数据、误改数据的现象也是时常发生的现象,作为运维这时候就需要出来补锅了,最开始的做法是恢复备份,然后从中找到需要的数据再进行修复,但是这个时间太长了,对于大表少数数据的修复来讲,动作太大,成本也大。

当然还有其他的一些操作方法,我们今天有主角。

 

MySQL 闪回工具 —   binlog2sql

用途

  • 数据回滚
  • 主从切换后数据不一致的修复
  • 从 binlog 生成标准 SQL,带来的衍生功能

 

闪回原理简析

开始之前,先说说闪回。我们都知道 MySQL binlog 以 event 为单位,记录数据库的变更信息,这些信息能够帮助我们重现这之间的所有变化,也就是所谓的闪回。

binlog 有三种可选的格式:

  • statement:基于 SQL 语句的模式,binlog 数据量小,但是某些语句和函数在复制过程可能导致数据不一致甚至出错;
  • mixed:混合模式,根据语句来选用是 statement 还是 row 模式;
  • row:基于行的模式,记录的是行的完整变化。安全,但 binlog 会比其他两种模式大很多;

利用 binlog 做闪回,需要将 binlog 格式设置为 row,因为我们需要最详尽的信息来确定操作之后数据不会出错。

既然 binlog 以 event 形式记录了所有的变更信息,那么我们把需要回滚的 event,从后往前回滚回去即可。

回滚操作:

对于 delete 操作,我们从 binlog 提取出 delete 信息,反向生成 insert 回滚语句;
对于 insert 操作,反向生成 delete 回滚语句;
对于 update 操作,根据信息生成反向的 update 语句;
 

 

竟然这么厉害??

 

来实例演习下

主要测试 DML,也就是 delete、update、insert 等操作的闪回效果。

但是,DDL 语句,比如drop,truncate 在整个使用中都是无法被回滚的,这种情况,只能用最近的备份数据+二进制日志恢复

本次实验,更改一条数据,并删除一条数据,然后从解析 binlog 信息,到使用 binlog2sql 工具来生成标准和回滚 SQL,来剖析整个运行过程。

 

一、准备工作

1.确定版本信息和binlog格式

  mysql版本:5.7.12

 

   查看binlog格式的命令

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+

 

2.安装binlog2sql工具     #(随便安装在合适的服务器即可,比如我在内网15安装了一个)

   安装参考链接:http://note.youdao.com/noteshare?id=85a90269a21d877962bfce0dfa40a90b&sub=71CD3E637F534AA1AFF129563F6A064E

 

3.在mysql的主服务器上,创建闪回操作账号的权限 

mysql>   GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'glon'@'%'  identified by '123456';
mysql
> show grants for 'glon'@'%'; +--------------------------------------------------------------------------+ | Grants for glon@% | +--------------------------------------------------------------------------+ | GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'glon'@'%' | +--------------------------------------------------------------------------+ 1 row in set (0.00 sec)

 

 

4.初始化数据    

CREATE TABLE `edai_binlog2sql` (

`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
 

insert into edai_binlog2sql (name,create_time) values ('Glon Ho','2012-10-1'),('Eason Chan', '2016-05-02'),('Jacky Cheung', '2015-05-02');

 
mysql> select * from edai_binlog2sql;
+----+--------------+---------------------+
| id | name | create_time |
+----+--------------+---------------------+
| 4 | Glon Ho | 2012-10-01 00:00:00 |
| 5 | Eason Chan | 2016-05-02 00:00:00 |
| 6 | Jacky Cheung | 2015-05-02 00:00:00 |
+----+--------------+---------------------+
3 rows in set (0.00 sec)

 

 

  • 进行 update 和 delete 操作
mysql> update edai_binlog2sql set create_time = '2017-05-12' where name = 'Glon Ho';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from edai_binlog2sql;
+----+--------------+---------------------+
| id | name | create_time |
+----+--------------+---------------------+
| 4 | Glon Ho | 2017-05-12 00:00:00 |
| 5 | Eason Chan | 2016-05-02 00:00:00 |
| 6 | Jacky Cheung | 2015-05-02 00:00:00 |
+----+--------------+---------------------+
3 rows in set (0.00 sec)

mysql> delete from edai_binlog2sql where name = 'Jacky Cheung';
Query OK, 1 row affected (0.09 sec)

mysql> select * from edai_binlog2sql;
+----+------------+---------------------+
| id | name | create_time |
+----+------------+---------------------+
| 4 | Glon Ho | 2017-05-12 00:00:00 |
| 5 | Eason Chan | 2016-05-02 00:00:00 |
+----+------------+---------------------+
2 rows in set (0.00 sec)


操作时候,Glon Ho 的时间改变了,而 Jacky Cheung 也被删除了。

我们来看下mysql的binlog文件位置

mysql> show master status;

+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 6159854 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)

 

 

好的,接下来使用binlog2sql工具   #这个工具我们安装在15服务器上的,我们去15上操作

1)解析出标准的SQL

python binlog2sql.py -h192.168.1.21 -P30136 -uglon -p’123456′ -d xcrm -t edai_binlog2sql  –start-file=mysql-bin.000001 > edai_binlog2sql.sql

参数解释:
-h:数据库服务地址
-u:连接用户名 -p:密码
-P:端口
-d:数据库名 -t:表名
 --start-file: 通俗的来讲就是,要解析sql的所在的binglog文件
--flashback: 闪回,逆向解析sql语句

 

cat edai_binlog2sql.sql

Mysql闪回工具之binlog2sql的原理及其使用

 

 

可以看到,几乎完美重现了我们上面执行过的 SQL,而且生成的每个 SQL 后面都带有该语句在 binlog 中的 position 信息和该语句的执行时间

 

2)解析想要回滚的SQL

比如,我想回滚刚刚操作的,edai_binlog2sql 后面两个update和DELETE操作

找到的时间节点就是:start 6159262   end 6159823    #分析最好用pos分析,这个可以更准确的定位到想要的

 python binlog2sql.py  –flashback  -h192.168.1.21 -P30136 -uglon -p’123456′ -dxcrm -tedai_binlog2sql –start-file=mysql-bin.000001 –start-position=6159262    –stop-pos=6159823 > edai_binlog2sql-new.sql 

 

[root@soft binlog2sql]# cat edai_binlog2sql-new.sql 
INSERT INTO `xcrm`.`edai_binlog2sql`(`create_time`, `id`, `name`) VALUES ('2015-05-02 00:00:00', 6, 'Jacky Cheung'); #start 6159565 end 6159823 time 2018-11-22 15:16:30
UPDATE `xcrm`.`edai_binlog2sql` SET `create_time`='2012-10-01 00:00:00', `id`=4, `name`='Glon Ho' WHERE `create_time`='2017-05-12 00:00:00' AND `id`=4 AND `name`='Glon Ho' LIMIT 1; #start 6159262 end 6159534 time 2018-11-22 15:15:46

 

可以看到,我们刚刚的delete语句,被反转为insert语句,update 修改为原来的时间

拿到了具体的恢复语句

那我们拿去数据库执行吧

 Mysql闪回工具之binlog2sql的原理及其使用

好了,完美搞定 

 

继续浏览有关 数据库技术文章/教程 的文章
发表评论