mysql誤刪除數據怎么恢復

這篇文章主要介紹“誤刪除數據怎么恢復”，在日常操作中，相信很多人在mysql誤刪除數據怎么恢復問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對大家解答”mysql誤刪除數據怎么恢復”的疑惑有所幫助！接下來(lái)，請跟著(zhù)小編一起來(lái)學(xué)習吧！

誤刪除數據恢復：

有兩張表被誤刪除了，t_a  3萬(wàn)多數據

鎖定刪除數據時(shí)binlog的文件名

將binlog解析成能夠看懂的內容查找刪除數據的開(kāi)始的binlog position和結束的binlog position

mysqlbinlog  mysql-bin.000819   --base64-output=DECODE-ROWS -v   --start-datetime="2017-08-10 16:04:26" --stop-datetime="2017-08-10 16:04:30"  >/tmp/819.sql

解決過(guò)程：

根據找到的pos點(diǎn)，生成反轉的sql 文件：-h 是誤刪除數據的庫的ip   -d  所屬數據庫  -t 所屬表

python binlog2sql.py --flashback -h2.1.1.1.  -P3306 -ubinlog  -p'123456' -doms  -tt_a --start-file='mysql-bin.000819' --start-position=13736449 --stop-position=47327695  >/tmp/roll.sql

將解析好的sql導入到測試庫 看看數據是否正確：

source  roll.sql

當數據正確的時(shí)候：導入到誤刪除數據的庫中。

binlog2sql使用：

安裝：

shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

shell> pip install -r requirements.txt

git與pip的安裝問(wèn)題請自行搜索解決。

使用

MySQL server必須設置以下參數:

[mysqld]

server_id = 1

log_bin = /var/log/mysql/mysql-bin.log

max_binlog_size = 1G

binlog_format = row

binlog_row_image = full

user需要的最小權限集合：

select, super/replication client, replication slave

建議授權

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 

權限說(shuō)明

select：需要讀取server端information_schema.COLUMNS表，獲取表結構的元信息，拼接成可視化的sql語(yǔ)句

super/replication client：兩個(gè)權限都可以，需要執行'SHOW MASTER STATUS', 獲取server端的binlog列表

replication slave：通過(guò)BINLOG_DUMP協(xié)議獲取binlog內容的權限

基本用法

解析出標準SQL

shell> python binlog2sql.py -h227.0.0.1 -P3306 -uadmin -p'admin' -dtest -t test3 test4 --start-file='mysql-bin.000002'

輸出：

INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); #start 570 end 736

UPDATE `test`.`test3` SET `addtime`='2016-12-10 12:00:00', `data`='中文', `id`=3 WHERE `addtime`='2016-12-10 13:03:22' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954

DELETE FROM `test`.`test3` WHERE `addtime`='2016-12-10 13:03:38' AND `data`='english' AND `id`=4 LIMIT 1; #start 981 end 1147

解析出回滾SQL

shell> python binlog2sql.py --flashback -h227.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147

輸出：

INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); #start 981 end 1147

UPDATE `test`.`test3` SET `addtime`='2016-12-10 13:03:22', `data`='中文', `id`=3 WHERE `addtime`='2016-12-10 12:00:00' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954

選項

mysql連接配置

-h host; -P port; -u user; -p password

解析模式

--stop-never 持續同步binlog?？蛇x。不加則同步至執行命令時(shí)最新的binlog位置。

-K, --no-primary-key 對INSERT語(yǔ)句去除主鍵?？蛇x。

-B, --flashback 生成回滾語(yǔ)句，可解析大文件，不受內存限制，每打印一千行加一句SLEEP SELECT(1)?？蛇x。與stop-never或no-primary-key不能同時(shí)添加。

解析范圍控制

--start-file 起始解析文件。必須。

--start-position/--start-pos start-file的起始解析位置?？蛇x。默認為start-file的起始位置。

--stop-file/--end-file 末尾解析文件?？蛇x。默認為start-file同一個(gè)文件。若解析模式為stop-never，此選項失效。

--stop-position/--end-pos stop-file的末尾解析位置?？蛇x。默認為stop-file的最末位置；若解析模式為stop-never，此選項失效。

--start-datetime 從哪個(gè)時(shí)間點(diǎn)的binlog開(kāi)始解析，格式必須為datetime，如'2016-11-11 11:11:11'?？蛇x。默認不過(guò)濾。

--stop-datetime 到哪個(gè)時(shí)間點(diǎn)的binlog停止解析，格式必須為datetime，如'2016-11-11 11:11:11'?？蛇x。默認不過(guò)濾。

對象過(guò)濾

-d, --databases 只輸出目標db的sql?？蛇x。默認為空。

-t, --tables 只輸出目標tables的sql?？蛇x。默認為空。

應用案例

誤刪整張表數據，需要緊急回滾

閃回詳細介紹可參見(jiàn)example目錄下《閃回原理與實(shí)戰》example/mysql-flashback-priciple-and-practice.md

test庫tbl表原有數據

mysql> select * from tbl;

+----+--------+---------------------+

| id | name   | addtime             |

+----+--------+---------------------+

|  1 | 小趙   | 2016-12-10 00:04:33 |

|  2 | 小錢(qián)   | 2016-12-10 00:04:48 |

|  3 | 小孫   | 2016-12-13 20:25:00 |

|  4 | 小李   | 2016-12-12 00:00:00 |

+----+--------+---------------------+

4 rows in set (0.00 sec)

mysql> delete from tbl;

Query OK, 4 rows affected (0.00 sec)

20:28時(shí)，tbl表誤操作被清空

mysql> select * from tbl;

Empty set (0.00 sec)

恢復數據步驟：

登錄mysql，查看目前的binlog文件

mysql> show master status;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000051 |       967 |

| mysql-bin.000052 |       965 |

+------------------+-----------+

最新的binlog文件是mysql-bin.000052，我們再定位誤操作SQL的binlog位置。誤操作人只能知道大致的誤操作時(shí)間，我們根據大致時(shí)間過(guò)濾數據。

shell> python binlog2sql/binlog2sql.py -h227.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-datetime='2016-12-13 20:25:00' --stop-datetime='2016-12-13 20:30:00'

輸出：

INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:26:00', 4, '小李'); #start 317 end 487 time 2016-12-13 20:26:26

UPDATE `test`.`tbl` SET `addtime`='2016-12-12 00:00:00', `id`=4, `name`='小李' WHERE `addtime`='2016-12-13 20:26:00' AND `id`=4 AND `name`='小李' LIMIT 1; #start 514 end 701 time 2016-12-13 20:27:07

DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='小趙' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='小錢(qián)' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-13 20:25:00' AND `id`=3 AND `name`='小孫' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-12 00:00:00' AND `id`=4 AND `name`='小李' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

我們得到了誤操作sql的準確位置在728-938之間，再根據位置進(jìn)一步過(guò)濾，使用flashback模式生成回滾sql，檢查回滾sql是否正確(注：真實(shí)環(huán)境下，此步經(jīng)常會(huì )進(jìn)一步篩選出需要的sql。結合grep、編輯器等)

shell> python binlog2sql/binlog2sql.py -h227.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-position=3346 --stop-position=3556 -B > rollback.sql | cat

輸出：

INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-12 00:00:00', 4, '小李'); #start 728 end 938 time 2016-12-13 20:28:05

INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孫'); #start 728 end 938 time 2016-12-13 20:28:05

INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小錢(qián)'); #start 728 end 938 time 2016-12-13 20:28:05

INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, '小趙'); #start 728 end 938 time 2016-12-13 20:28:05

確認回滾sql正確，執行回滾語(yǔ)句。登錄mysql確認，數據回滾成功。

shell> mysql -h227.0.0.1 -P3306 -uadmin -p'admin' < rollback.sql

mysql> select * from tbl;

+----+--------+---------------------+

| id | name   | addtime             |

+----+--------+---------------------+

|  1 | 小趙   | 2016-12-10 00:04:33 |

|  2 | 小錢(qián)   | 2016-12-10 00:04:48 |

|  3 | 小孫   | 2016-12-13 20:25:00 |

|  4 | 小李   | 2016-12-12 00:00:00 |

+----+--------+---------------------+

限制（對比mysqlbinlog）

mysql server必須開(kāi)啟，離線(xiàn)模式下不能解析

參數 binlog_row_image 必須為FULL，暫不支持MINIMAL

解析速度不如mysqlbinlog

優(yōu)點(diǎn)（對比mysqlbinlog）

純Python開(kāi)發(fā)，安裝與使用都很簡(jiǎn)單

自帶flashback、no-primary-key解析模式，無(wú)需再裝補丁

flashback模式下，更適合閃回實(shí)戰

解析為標準SQL，方便理解、調試

代碼容易改造，可以支持更多個(gè)性化解析

問(wèn)題：

在安裝的時(shí)候出現了一些問(wèn)題：

(Run "ez_setup.py -U setuptools" to reinstall or upgrade.)

[root@ces3 binlog2sql]# python binlog2sql.py 

Traceback (most recent call last):

  File "binlog2sql.py", line 5, in

    import pymysql

ImportError: No module named pymysq

# python setup.py  install

Traceback (most recent call last):

  File “setup.py”, line 6, in

    from setuptools import setup, find_packages

ImportError: No module named setuptools看來(lái)還和一個(gè)setuptools的庫有關(guān)，我們繼續安裝。

# wget https://bootstrap.pypa.io/ez_setup.py –no-check-certificate然后使用 python ez_setup.py install 即可編譯成功setuptools

再次嘗試python setup.py install即可完成pip的安裝。

對于插件PyMySQL可以使用如下的方式來(lái)安裝：

git clone https://github.com/PyMySQL/PyMySQL對于插件mysql replication可以使用如下的方式：

git clone https://github.com/noplay/python-mysql-replication就這樣前期的工作就做好了。