数据恢复binlog2sql
一.原理及其使用
生产上误删数据、误改数据的现象也是时常发生的现象,作为运维这时候就需要出来补锅了,最开始的做法是恢复备份,然后从中找到需要的数据再进行修复,但是这个时间太长了,对于大表少数数据的修复来讲,动作太大,成本也大。当然还有其他的一些操作方法,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 |
+---------------+-------+
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'@'%' |
+--------------------------------------------------------------------------+
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 |
+----+--------------+---------------------+
rows in set (0.00 sec)
- 进行 update 和 delete 操作
mysql>update edai_binlog2sql set create_time = '2017-05-12' where name = 'Glon Ho';
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 |
+----+--------------+---------------------+
rows in set (0.00 sec)
mysql> delete from edai_binlog2sql where name = 'Jacky Cheung';
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 |
+----+------------+---------------------+
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 | | | |
+------------------+----------+--------------+------------------+-------------------+
row in set (0.00 sec)
接下来使用binlog2sql工具
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
可以看到,几乎完美重现了我们上面执行过的 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 修改为原来的时间
拿到了具体的恢复语句,那我们拿去数据库执行吧
最新文章
- JavaWeb_day07_JSP
- %u编码
- 【转】MySQL 高可用架构在业务层面的分析研究
- Mac 安装 Tomcat
- z-index无效问题的解决方法
- linux工程管理工具make入门
- [MODX] 2. Chunks $
- Swift - 30 - 可变参数
- ubuntu系统mysql.h no such file or directory
- PHP二维数组排序函数
- linux 查看磁盘文件大小
- SQL中NVL函数
- Java大数练习
- openstack之~glance安装部署
- 搭建前端监控系统(三)NodeJs服务器部署篇
- P3489 付公主的背包
- ASP.NET Core 2.1 源码学习之 Options[3]:IOptionsMonitor
- 计蒜客 31452 - Supreme Number - [简单数学][2018ICPC沈阳网络预赛K题]
- 不写代码也能爬虫Web Scraper
- codevs 1226 倒水问题
热门文章
- vue+node+mongondb实战之mongodb登陆操作
- 如何提高C# StringBuilder的性能
- 【Microsoft Azure 的1024种玩法】二.基于Azure云平台的安全攻防靶场系统构建
- 重写(Override)与重载(Overload)区别
- [loj150]挑战多项式
- [bzoj3038]上帝造题的7分钟2
- tomcat更改端口号and设置cmd别名
- Codeforces 436E - Cardboard Box(贪心/反悔贪心/数据结构)
- Linux mount挂载磁盘报错 mount: wrong fs type, bad option, bad superblock on /dev/vdb
- R语言与医学统计图形【5】饼图、条件图