?
生產上誤刪數據、誤改數據的現象也是時常發生的現象，作為運維這時候就需要出來補鍋了，最開始的做法是恢復備份，然后從中找到需要的數據再進行修復，但是這個時間太長了，對于大表少數數據的修復來講，動作太大，成本也大。
當然還有其他的一些操作方法，我們今天有主角。
?
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=utf8insert 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: 0mysql> 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
?
?
可以看到，幾乎完美重現了我們上面執行過的 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 修改為原來的時間
拿到了具體的恢復語句
那我們拿去數據庫執行吧
?
好了，完美搞定?
?
)
