前言
在日常工作或者學習中，操作數據庫時候難免會因為“大意”而誤操作，需要快速恢復的話通過備份來恢復是不太可能的，下面這篇文章主要給大家介紹關于Mysql誤操作后利用binlog2sql快速回滾的方法，話不多說，來一起看看詳細的介紹：
一、總體解釋：
DML（data manipulation language）：
它們是SELECT、UPDATE、INSERT、DELETE，就象它的名字一樣，這4條命令是用來對數據庫里的數據進行操作的語言
DDL（data definition language）：
DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定義或改變表（TABLE）的結構，數據類型，表之間的鏈接和約束等初始化工作上，他們大多在建立表時使用
DCL（Data Control Language）：
是數據庫控制功能。是用來設置或更改數據庫用戶或角色權限的語句，包括（grant,deny,revoke等）語句。在默認狀態下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人員才有權力執行DCL
二、binlog2sql安裝
從mysql binlog解析出你要的sql。根據不同選項，你可以得到原始sql、回滾sql、去除主鍵的insert sql等。
2.1、用途
- ?數據快速回滾(閃回)
- ?主從切換后數據不一致的修復
- ?從binlog生成標準SQL，帶來的衍生功能
2.2、安裝
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
# cd /usr/local# git clone https://github.com/danfengcao/binlog2sql.git# ls binlog2sql games java lib64 mariadb sbin src # cd binlog2sql# pip install -r requirements.txt-bash: pip: command not found -------------安裝pip工具-------------# wget https://bootstrap.pypa.io/get-pip.py # python get-pip.py# pip -V #查看pip版本pip 9.0.1 from /usr/lib/python2.7/site-packages (python 2.7)# pip install -r requirements.txtRequirement already satisfied: PyMySQL==0.7.8 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 1))Requirement already satisfied: wheel==0.24.0 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 2))Requirement already satisfied: mysql-replication==0.9 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 3)) |
2.3、user需要的最小權限集合：
select, super/replication client, replication slave權限建議授權
|
1
2
|
mysql > GRANT SELECT,REPLICATION SLAVE,REPLICATION CLIENT ON *.* to flashback@'localhost' identified by 'flashback';mysql > GRANT SELECT,REPLICATION SLAVE,REPLICATION CLIENT ON *.* to flashback@'127.0.0.1' identified by 'flashback'; |
2.4、基本用法
解析出標準SQL
|
1
|
shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -ddatabase -t table1 table2 --start-file='mysql-bin.000002' --start-datetime='2017-01-12 18:00:00' --stop-datetime='2017-01-12 18:30:00' --start-pos=1240 |
解析出回滾SQL
|
1
|
shell> python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147 |
三、測試：
3.1、新建表users
|
1
2
3
|
create table cope_users like info_users; # 新建表insert into cope_users select * from info_users limit 500; # 插入500行數據delete from cope_users where id<20; # 刪除20行數據 |
3.2、解析標準sql
|
1
2
3
|
# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-datetime='2017-07-11 15:10:00' --stop-datetime='2017-07-11 15:12:00'DELETE FROM `ttt`.`users` WHERE `uid`='0e8e2609c748bbb052d7' AND `ip`='172.16.208.32' AND `sex`=0 AND `app_ver`='5.2.3' AND `device_type`=2 AND `guides`='' AND `last_login_time`=1481602129 AND `id`=1 AND `latitude`='' AND `add_time`=1481602080 AND `recharge_time`=0 AND `token_change_time`=1481602129 AND `expire_time`=0 AND `nickname`='阿超' AND `device_id`='cc0e154d9b5dd703eccc7d8a0dbc0f67d64b79e8' AND `push_key`='' AND `level`=0 AND `mobile`='18810895535' AND `settings`='' AND `longitude`='' AND `signature`='' AND `os_ver`='' LIMIT 1; #start 79078 end 83053 time 2017-07-11 15:11:50DELETE FROM `ttt`.`users` WHERE `uid`='b5cfbdb4205b56703a97' AND `ip`='172.16.208.48' AND `sex`=0 AND `app_ver`='5.2.2' AND `device_type`=2 AND `guides`='' AND `last_login_time`=1481602096 AND `id`=2 AND `latitude`='' AND `add_time`=1481602096 AND `recharge_time`=0 AND `token_change_time`=1481602096 AND `expire_time`=0 AND `nickname`='家長091410' AND `device_id`='fedea666076a7906be53523acc7a8b32811354fe' AND `push_key`='7759d6772c9851a2bfc13835a3d7e7da' AND `level`=0 AND `mobile`='13629470521' AND `settings`='' AND `longitude`='' AND `signature`='' AND `os_ver`='' LIMIT 1; #start 79078 end 83053 time 2017-07-11 15:11:50 |
3.3、解析出回滾SQL
|
1
|
# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py --flashback -h127.0.0.1 -P3306 -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-position=79078 --stop-position=83053 |
查看解析出的sql，如無誤，可打印到sql文件中/data/backup/rollback.sql
|
1
2
3
4
|
# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py --flashback -uflashback -pflashback -dttt -tusers --start-file='mysql-bin.000034' --start-position=79078 --stop-position=83053> /data/backup/rollback.sql# cat /data/backup/rollback.sql `id`, `latitude`, `add_time`, `recharge_time`, `token_change_time`, `expire_time`, `nickname`, `device_id`, `push_key`, `level`, `mobile`, `settings`, `longitude`, `signature`, `os_ver`) VALUES ('24667530f4b16a446b3e', '172.16.218.75', 0, '5.2.93', 3, '{\"2103\":1,\"2100\":1,\"2101\":1,\"2102\":1,\"2104\":1,\"2105\":1}', 1490239125, 19, '', 1481610680, 0, 1490239125, 0, 'zf', 'da75b093-bd22-48f6-bbb1-d3296e29e9b5', 'be05183f80a96e788e0b0a99d1275392', 0, '15101538925', '', '', '', ''); #start 79078 end 83053 time 2017-07-11 15:11:50INSERT INTO `ttt`.`users`(`uid`, `ip`, `sex`, `app_ver`, `device_type`, `guides`, `last_login_time`, `id`, `latitude`, `add_time`, `recharge_time`, `token_change_time`, `expire_time`, `nickname`, `device_id`, `push_key`, `level`, `mobile`, `settings`, `longitude`, `signature`, `os_ver`) VALUES ('77e50b4910a9389057ed', '172.16.218.37', 0, '5.2.1.14', 3, '', 1488787835, 18, '39.978212', 1481610517, 0, 1488787835, 0, '陳俊宇', 'ed0a273d-74de-4173-92c6-55d92597bc79', '', 0, '18612482272', '', '116.306826', '', ''); #start 79078 end 83053 time 2017-07-11 15:11:50 |
mysql連接配置
|
1
|
-h host; -P port; -u user; -p password |
解析模式
- --realtime 持續同步binlog?？蛇x。不加則同步至執行命令時最新的binlog位置。
- --popPk 對INSERT語句去除主鍵。可選。
- -B, --flashback 生成回滾語句。可選。與realtime或popPk不能同時添加。
解析范圍控制
- --start-file 起始解析文件。必須。
- --start-pos start-file的起始解析位置?？蛇x。默認為start-file的起始位置；
- --end-file 末尾解析文件?？蛇x。默認為start-file同一個文件。若解析模式為realtime，此選項失效。
- --end-pos end-file的末尾解析位置。可選。默認為end-file的最末位置；若解析模式為realtime，此選項失效。
對象過濾
- -d, --databases 只輸出目標db的sql?？蛇x。默認為空。
- -t, --tables 只輸出目標tables的sql。可選。默認為空。
3.4、開始回滾
|
1
|
# mysql -uroot -p000000 < /data/backup/rollback.sql |
3.5、登陸數據庫驗證
四、注意事項
4.1、在配置文件中設置了以下參數：
|
1
2
3
4
5
|
server_id = 1log_bin = /data/mysql/mysql-bin.logmax_binlog_size = 1Gbinlog_format = rowbinlog_row_image = full # 默認 |
4.2、在閃回的時候必須啟動 MySQL 服務
因為它是通過 BINLOG_DUMP 協議來獲取 binlog 內容，需要讀取server端 information_schema.COLUMNS 表，來獲取表結構的元信息，才能拼接成 SQL 語句。因此需要給用戶提供的最小權限如下：
|
1
|
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'%'; |
源碼中，主要是使用 python-mysql-replication 作為實時解析 MySQL binlog 來獲取各個 EVENT。 python-mysql-replication 實現了 MySQL 復制協議，客戶端偽裝成 slave 來獲取主的 binlog 和 EVENT。
4.3、insert、update、delete大部分時候可以解析出來標準sql和回滾sql
一種情況例外:insert、updete、delete操作之后，drop/truncate table。 此時雖然在binlog中記錄了所有的event，但是使用binlog2sql生成標準sql、回滾sql的時候已經找不到了dml操作的相應的表
4.4、DDL無法使用binlog2sql閃回數據。
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對服務器之家的支持。
原文鏈接：https://segmentfault.com/a/1190000010141754
