基于binlog恢複工具mysqlbinlog_flashback
簡介
mysqlbinlog_back.py 是線上讀取row格式的mysqld的binlog,然後生成反向的sql語句的工具。
一般用于資料恢複的目的。 所謂反向的sql語句就是如果是insert,則反向的sql為delete。
如果delete,反向的sql是insert,如果是update, 反向的sql還是update,但是update的值是原來的值。
這個項目需要安裝
yum update nss 或者yum update openssl
yum install python-pip(how to do it?)
pip install pymysql
官方位址
https://github.com/58daojia-dba/mysqlbinlog_flashback
使用限制
1.支援mysql版本為MySQL 5.5 and 5.6.因為底層使用的是python-mysql-replication包。
2.資料庫必須是row格式的。
3.反向生成的表必須有主鍵。
4.日志必須在主庫存在
5.反向生成的mysql資料類型列出在下面。沒有列出的類型沒有經過嚴格的測試,也許有問題
6.支援的類型
允許解析的字段類型,不在裡面的會報錯
ALLOW_TYPE={ "varchar":True, "char":True, "datetime":True, "date":True, "time":True, "timestamp":True, "bigint":True, "mediumint":True, "smallint":True, "tinyint":True, "int":True, "smallint":True, "decimal":True, "float":True, "double":True, "longtext":True, "tinytext":True, "text":True, "mediumtext":True }
使用:
環境: 5.5.24
database:scheduler
table:user
表結構:
mysql> show create table user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '使用者唯一标志符 UID',
`username` varchar(64) DEFAULT NULL COMMENT '使用者名,不區分大小寫',
`email` varchar(128) DEFAULT NULL COMMENT '注冊郵箱,不區分大小寫',
`cell_phone` bigint(11) DEFAULT NULL COMMENT '手機号碼',
`password` char(32) NOT NULL COMMENT '密碼hash值',
`school_code` bigint(20) unsigned DEFAULT NULL COMMENT '學校代碼',
`register_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '注冊時間',
`usertype` int(5) NOT NULL DEFAULT '1' COMMENT '1為微信關注使用者,2為微信登入app的使用者,3為APP端微信登入的微信使用者',
`state` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1<0: UID是否有效 1<1: 是否設定使用者名密碼 1<2: 是否認證郵箱 1<3: 是否認證手機号碼',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
今天客服回報有個使用者使用手機号登陸不了,檢視是由于程式BUG導緻在使用者登陸表裡面的手機号是錯誤的值,
拿到使用者真實的手機号之後,我們的初級DBA小明上線手一滑執行了
mysql> update user set cell_phone=15811111111;
Query OK, 10000 rows affected (0.08 sec)
Rows matched: 10000 Changed: 10000 Warnings: 0
當發現的時候已經來不及了,資料瞬間就被更改了
mysql> select * from user limit 10;
+-----+----------+-------+-------------+----------------------------------+-------------+---------------------+----------+-------+
| id | username | email | cell_phone | password | school_code | register_time | usertype | state |
+-----+----------+-------+-------------+----------------------------------+-------------+---------------------+----------+-------+
| 1 | NULL | NULL | 15811111111 | bc41014a6785b133a3c4fe29c1fc181e | 1 | 2014-02-26 18:09:44 | 3 | 1 |
| 664 | NULL | NULL | 15811111111 | 2b7dc70e9ef4aec3b36d9ed01f6c3917 | 1 | 2014-02-26 23:08:14 | 3 | 1 |
| 666 | NULL | NULL | 15811111111 | 19c0f7f7008765f55cfd64af4b7b7009 | 1 | 2014-02-26 23:08:15 | 3 | 1 |
| 670 | NULL | NULL | 15811111111 | 86b28ea44b6e4bc0b7d5c5010d288f1c | 1 | 2014-02-26 23:08:17 | 3 | 1 |
| 681 | NULL | NULL | 15811111111 | db79ffb99fafbf059d525c93e7c1e38c | 1 | 2014-02-26 23:08:26 | 1 | 1 |
| 682 | NULL | NULL | 15811111111 | aa237deac77a9b5c652e14b888aefccf | 1 | 2014-02-26 23:08:28 | 1 | 1 |
| 685 | NULL | NULL | 15811111111 | dae5acb6bd5de68011dd55f1b4d31303 | 1 | 2014-02-26 23:08:29 | 1 | 1 |
| 687 | NULL | NULL | 15811111111 | 05319ba2ec8767c80e68408eaeccd773 | 1 | 2014-02-26 23:08:31 | 1 | 1 |
| 689 | NULL | NULL | 15811111111 | 1dfda975919441990949918ceb25d7b6 | 1 | 2014-02-26 23:08:32 | 1 | 1 |
| 695 | NULL | NULL | 15811111111 | 8f13c73164f32794a2efb6f5b43194e8 | 1 | 2014-02-26 23:08:36 | 1 | 1 |
+-----+----------+-------+-------------+----------------------------------+-------------+---------------------+----------+-------+
這個時候記得趕緊加一個全局讀鎖,防止有新的寫入發生 flush tables with read lock;
使用mysqlbinlog_back.py工具進行復原
python mysqlbinlog_back.py --help 檢視下參數使用說明
mysqlbinlog -v -v --base64-output=decode-rows mysql-bin.000006 >6.sql、
# at 700692
#170221 15:26:27 server id 1003306 end_log_pos 700765 Query thread_id=83 exec_time=0 error_code=0
SET TIMESTAMP=1487661987;
BEGIN
### UPDATE scheduler.user
### WHERE
### @1=1
### @2=NULL
### @3=NULL
### @4=13930226699
### @5='bc41014a6785b133a3c4fe29c1fc181e'
### @6=1
### @7=1393409384
### @8=3
### @9=1
### SET
### @1=1
### @2=NULL
### @3=NULL
### @4=15811111111
### @5='bc41014a6785b133a3c4fe29c1fc181e'
### @6=1
### @7=1393409384
### @8=3
### @9=1
在binlog裡面找到剛剛的update資料,開始進行復原
python mysqlbinlog_back.py --host="127.0.0.1" --port=3306 --username="root" --password="[email protected]" --schema=scheduler --tables="user" -S "mysql-bin.000006" -L "700692"
在log目錄下生産反向sql檔案flashback_scheduler_20170221_154300.sql
#end_log_pos 701812 2017-02-21T15:26:27 1487661987 mysql-bin.000006;
update `user` set`username`=null,`cell_phone`=13930226699,`register_time`='2014-02-26 18:09:44',`school_code`=1,`email`=null,`usertype`=3,`state`=1,`password`='bc41014a6785b133a3c4fe29c1fc181e',`id`=1 where `id`=1;
update `user` set`username`=null,`cell_phone`=18818272661,`register_time`='2014-02-26 23:08:14',`school_code`=1,`email`=null,`usertype`=3,`state`=1,`password`='2b7dc70e9ef4aec3b36d9ed01f6c3917',`id`=664 where `id`=664;
update `user` set`username`=null,`cell_phone`=15821823213,`register_time`='2014-02-26 23:08:15',`school_code`=1,`email`=null,`usertype`=3,`state`=1,`password`='19c0f7f7008765f55cfd64af4b7b7009',`id`=666 where `id`=666;
update `user` set`username`=null,`cell_phone`=18817817795,`register_time`='2014-02-26 23:08:17',`school_code`=1,`email`=null,`usertype`=3,`state`=1,`password`='86b28ea44b6e4bc0b7d5c5010d288f1c',`id`=670 where `id`=670;
update `user` set`username`=null,`cell_phone`=13482311832,`register_time`='2014-02-26 23:08:26',`school_code`=1,`email`=null,`usertype`=1,`state`=1,`password`='db79ffb99fafbf059d525c93e7c1e38c',`id`=681 where `id`=681;
update `user` set`username`=null,`cell_phone`=15026616052,`register_time`='2014-02-26 23:08:28',`school_code`=1,`email`=null,`usertype`=1,`state`=1,`password`='aa237deac77a9b5c652e14b888aefccf',`id`=682 where `id`=682;
update `user` set`username`=null,`cell_phone`=15121110056,`register_time`='2014-02-26 23:08:29',`school_code`=1,`email`=null,`usertype`=1,`state`=1,`password`='dae5acb6bd5de68011dd55f1b4d31303',`id`=685 where `id`=685;
将資料導入mysql恢複
mysql -uroot -p scheduler
最後驗證下資料,釋放全局表鎖
心得:這個復原工具在誤操作急救的時候比較好用,相對于使用全備加binlog恢複的方式更加便捷有效
---------------------
作者:隔壁master王
來源:CSDN
原文:https://blog.csdn.net/jerry____wang/article/details/56285859
版權聲明:本文為部落客原創文章,轉載請附上博文連結!