相信后端研發(fā)的同學在開發(fā)過程經(jīng)常會遇到產(chǎn)品臨時修改線上數(shù)據(jù)的需求雌团，如果手法很穩(wěn)那么很慶幸可以很快完成任務萍恕，很不幸某一天突然手一抖把表里的數(shù)據(jù)修改錯誤或者誤刪了厅须，這個時候你會發(fā)現(xiàn)各種問題反饋接踵而來椿猎。如果身邊有BDA或者有這方面經(jīng)驗的同事那么可以很快解決這個問題硼被，如果沒有那么希望這篇文章可以幫到你示损。
binglog介紹
首先第一步保證mysql已經(jīng)開啟binlog，查看命令：
show variables like '%log_bin%'
mysql binlog分三種格式 :
Statement : 會在binlog中記錄每一條執(zhí)行修改數(shù)據(jù)的sql語句的相關信息嚷硫，優(yōu)點是不需要記錄每一行的變化检访，減少了binlog日志量，節(jié)約了IO
Row : 會在binlog中記錄每一修改語句的詳細信息仔掸，包括數(shù)據(jù)在修改之前和修改之后的數(shù)據(jù)的具體信息脆贵，好處是會清晰記錄每一條修改的詳細信息，不好的地方是會產(chǎn)生大量日志
Mixed ：這種格式實際上就是Statement和Row的結(jié)合體起暮，如果遇到表結(jié)構(gòu)變更就會以Statement來記錄丹禀，如果涉及語句修改那么就以Row格式記錄
這里的binlog格式推薦row，my.cnf 的配置可參考 :
server_id = 1001
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
我們來模擬一些數(shù)據(jù):
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(125) NOT NULL DEFAULT '' COMMENT '名稱',
`age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年齡',
`sex` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '性別',
`deleted` tinyint(4) unsigned DEFAULT '0',
`created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表測試';
INSERT INTO `user` (`id`, `name`, `age`, `sex`, `deleted`, `created`)
VALUES
(1, '小王', 21, 1),
(2, '小張', 22, 1),
(3, '小紅', 22, 0),
(4, '小楠', 23, 0),
(5, '小柱', 25, 1);
然后我們把數(shù)據(jù)全部刪掉
delete from `user`
數(shù)據(jù)恢復方法一 :
使用開源框架binlog2sql : https://github.com/danfengcao/binlog2sql
好處是成熟鞋怀，穩(wěn)定，上手難度比較低且可直接生成可執(zhí)行sql持搜，示例 ：
python /binlog2sql/binlog2sql.py --flashback -h127.0.0.1 -P3306 -uroot -p'123456' -dlocal -tuser --start-file='mysql-bin.000038' --sql-type=DELETE --start-datetime='2017-12-17 19:39:33' --stop-datetime='2017-12-17 19:40:01' >/**/data6.sql
解析后的結(jié)果大概是這樣
INSERT INTO `local`.`user`(`name`, `created`, `deleted`, `age`, `sex`, `id`) VALUES ('小柱', '2017-12-18 13:21:52', 0, 25, 1, 5); #start 1890 end 2244 time 2017-12-19 09:20:26
INSERT INTO `local`.`user`(`name`, `created`, `deleted`, `age`, `sex`, `id`) VALUES ('小楠', '2017-12-18 13:21:52', 0, 23, 0, 4); #start 1890 end 2244 time 2017-12-19 09:20:26
INSERT INTO `local`.`user`(`name`, `created`, `deleted`, `age`, `sex`, `id`) VALUES ('小紅', '2017-12-18 13:21:52', 0, 22, 0, 3); #start 1890 end 2244 time 2017-12-19 09:20:26
INSERT INTO `local`.`user`(`name`, `created`, `deleted`, `age`, `sex`, `id`) VALUES ('小張', '2017-12-18 13:21:52', 0, 28, 1, 2); #start 1890 end 2244 time 2017-12-19 09:20:26
INSERT INTO `local`.`user`(`name`, `created`, `deleted`, `age`, `sex`, `id`) VALUES ('小王', '2017-12-18 13:21:52', 0, 21, 1, 1); #start 1890 end 2244 time 2017-12-19 09:20:26
參數(shù)--sql-type建議加上密似，因為可能會有其他類型語句生成干擾了執(zhí)行結(jié)果
如果是線上阿里云或者其他產(chǎn)品建議先去管理后臺找到事發(fā)時間的binlog日志下載下來，先在測試環(huán)境驗證數(shù)據(jù)回滾結(jié)果.
數(shù)據(jù)恢復方法二:
當線上數(shù)據(jù)出現(xiàn)錯誤的時候首先可以詢問具體操作人記錄時間點葫盼，這個時候可以借助mysql自帶的binlog解析工具mysqlbinlog残腌，具體位置在mysql安裝目錄**/mysql/bin/下，示例：
mysqlbinlog --base64-output=decode-rows -v --start-datetime="2017-12-15 17:48:49" --stop-datetime="2017-12-16 23:59:49" /usr/local/mysql/mysql-bin.000038 >/**/data.sql
如果是阿里云rds或者其他產(chǎn)品可通過遠程方式解析
mysqlbinlog --no-defaults -u賬號 -p密碼 -h ***.rds.aliyuncs.com --read-from-remote-server mysql-bin.000180 --base64-output=decode-rows -v > /data.sql
這里因為binlog文件默認是通過base64編碼過的贫导，所以需要加上--base64-output=decode-rows -v
解析后的格式大概是這樣的 :
### DELETE FROM `local`.`user`
### WHERE
### @1=1
### @2='小王'
### @3=21
### @4=1
### @5=0
### @6='2017-12-18 13:21:52'
### DELETE FROM `local`.`user`
### WHERE
### @1=2
### @2='小張'
### @3=28
### @4=1
### @5=0
### @6='2017-12-18 13:21:52'
....
仔細查看這種格式文件抛猫，發(fā)現(xiàn)這種格式文件并不能直接執(zhí)行，但是在where條件后面記錄了被刪除之前的原始數(shù)據(jù)孩灯，需要借助sed闺金、awk把SQL文本轉(zhuǎn)換成真正的SQL》宓担或者當你在遇到開源框架解決不了的情況下败匹，可以根據(jù)具體場景嘗試手動把這種格式的文件解析成可執(zhí)行的sql語句寨昙。
