前幾天有個(gè)同事碰到了一個(gè)MySQL數(shù)據(jù)恢復(fù)的問題，他運(yùn)行了一條update語句，結(jié)果忘記了加where條件，結(jié)果等反應(yīng)過來已經(jīng)晚了。我簡單確認(rèn)了下，是否存在備份，沒有，是否開啟了日志，沒有。所以這個(gè)恢復(fù)無從談起。

成都創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè)、成都網(wǎng)站制作、網(wǎng)站頁面設(shè)計(jì)、網(wǎng)站優(yōu)化SEO優(yōu)化為一體的專業(yè)的建站公司，已為成都等多地近百家企業(yè)提供網(wǎng)站建設(shè)服務(wù)。追求良好的瀏覽體驗(yàn),以探求精品塑造與理念升華,設(shè)計(jì)最適合用戶的網(wǎng)站頁面。 合作只是第一步，服務(wù)才是根本,我們始終堅(jiān)持講誠信，負(fù)責(zé)任的原則，為您進(jìn)行細(xì)心、貼心、認(rèn)真的服務(wù),與眾多客戶在蓬勃發(fā)展的市場環(huán)境中,互促共生。

    當(dāng)然后來他也花了些功夫逐條數(shù)據(jù)修復(fù)，事情過去了，數(shù)據(jù)恢復(fù)的重要性，人為操作的重要性就不言而喻了，但是有些時(shí)間工作職責(zé)還是需要下移。我覺得還是需要好好總結(jié)下數(shù)據(jù)恢復(fù)的問題。我會從以下幾個(gè)方面來談。

目錄

⊙  手工恢復(fù)數(shù)據(jù)的簡單示例

⊙使用開源工具恢復(fù)數(shù)據(jù)的配置

⊙  使用開源工具恢復(fù)數(shù)據(jù)的實(shí)踐

⊙小結(jié)

首先手工恢復(fù)數(shù)據(jù)，其實(shí)有一些思路，一種就是通過全備+binlog的時(shí)間、偏移量來恢復(fù)。另外一類是通過解析binlog來恢復(fù)，前提條件是日志格式為row。我們來簡單模擬解析binlog的恢復(fù)方式。

手工恢復(fù)數(shù)據(jù)的簡單示例

先看一看binlog的情況，可以看到當(dāng)前的binlog是序號為15的日志文件。

> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000014 | 1073742219 |
| mysql-bin.000015 |  998953054 |
+------------------+------------+
2 rows in set (0.00 sec)為了方便模擬，我們可以切換一下日志，flush logs之后得到的日志情況如下：

> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000015 | 999120424 |
| mysql-bin.000016 |      6722 |
+------------------+-----------+
2 rows in set (0.00 sec)創(chuàng)建表test

create table test (id int not null  primary key,name varchar(20),memo varchar(50)) ENGINE=InnoDB auto_increment=100 default charset=utf8;插入幾條數(shù)據(jù)

> insert into test values(1,'name1','memo1'),(2,'name2','memo2'),(3,'name3','memo3'),(4,'name4','memo4'),(5,'name5','memo5');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0查看一下數(shù)據(jù)的基本情況：

> select * from test;
+----+-------+-------+
| id | name  | memo  |
+----+-------+-------+
|  1 | name1 | memo1 |
|  2 | name2 | memo2 |
|  3 | name3 | memo3 |
|  4 | name4 | memo4 |
|  5 | name5 | memo5 |
+----+-------+-------+為了測試方便，先標(biāo)記一個(gè)時(shí)間戳>  select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2017-02-06 04:14:33 |
+---------------------+我們開始模擬DML的操作。

> delete from test where id in (1,3);
Query OK, 2 rows affected (0.01 sec)

> update test set memo='new' where id in(2,4);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

> insert into test values(6,'name6','memo6');
Query OK, 1 row affected (0.00 sec)做完上面三個(gè)DML操作之后，我們標(biāo)記一下時(shí)間。

> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2017-02-06 04:15:44 |
+---------------------+下面我們來解讀一下binlog，根據(jù)時(shí)間戳得到一個(gè)基本可讀的日志，里面還有這些數(shù)據(jù)變更，但是語句和執(zhí)行的還是有一些出入，我們直接拷貝一份binlog到/tmp目錄下解析。

mysqlbinlog  --no-defaults -v --start-datetime="2017-02-06 04:14:33"   --stop-datetime="2017-02-06 04:15:44"   /tmp/mysql-bin.000016 --result-file=/tmp/result.sql生成的文件result.sql內(nèi)容如下，可以看到這些操作在binlog中都有了很詳細(xì)的標(biāo)記，數(shù)據(jù)的情況基本都是一目了然，update的部分變化前變化后的數(shù)據(jù)都一覽無余。其實(shí)DML中難度較大的就是update,而insert,delete就是一個(gè)加減法。

delete操作對應(yīng)binlog日志中的SQL

### DELETE FROM `test`.`test`
### WHERE
###   @1=1
###   @2='name1'
###   @3='memo1'
### DELETE FROM `test`.`test`
### WHERE
###   @1=3
###   @2='name3'
###   @3='memo3'
# at 998969666update操作對應(yīng)binlog日志中的SQL

### UPDATE `test`.`test`
### WHERE
###   @1=2
###   @2='name2'
###   @3='memo2'
### SET
###   @1=2
###   @2='name2'
###   @3='new'
### UPDATE `test`.`test`
### WHERE
###   @1=4
###   @2='name4'
###   @3='memo4'
### SET
###   @1=4
###   @2='name4'
###   @3='new'
# at 998971422

insert操作對應(yīng)binlog日志中的SQL

### INSERT INTO `test`.`test`
### SET
###   @1=6
###   @2='name6'
###   @3='memo6'
# at 998973859值得一提的是-v（--verbose）選項(xiàng)會將行事件重構(gòu)成被注釋掉的偽SQL語句，如果想看到更詳細(xì)的信息可以使用-vv選項(xiàng)，這樣可以包含一些數(shù)據(jù)類型和元信息的注釋內(nèi)容。

比如：

-vv的結(jié)果：

### DELETE FROM `test`.`test`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='name1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @3='memo1' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */回到數(shù)據(jù)恢復(fù)的問題，如果需要手工恢復(fù)就需要做幾件事情，一個(gè)就是根據(jù)字段標(biāo)示拼接出可運(yùn)行的SQL語句，然后按照逆向的順序執(zhí)行即可。

使用開源工具恢復(fù)數(shù)據(jù)的配置

   從上面的步驟可以看到，如果手工修復(fù)其實(shí)還是可以實(shí)現(xiàn)的，不過手工的操作不少，這個(gè)時(shí)候能夠簡化你的工作就是好工具，我試用了下binglog2sql這個(gè)開源工具，也是大眾點(diǎn)評的DBA團(tuán)隊(duì)推出的，總體還不錯(cuò)。

  這個(gè)工具是Python開發(fā)，當(dāng)然有一些依賴的庫和環(huán)境需要配置。如果你的服務(wù)器是聯(lián)網(wǎng)環(huán)境，那就省事多了。

兩個(gè)步驟即可完成。

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql   
pip install -r requirements.txt  --會安裝額外需要的插件而如果不是，那么就有一些額外的工作需要你去做。比如我這個(gè)環(huán)境連pip都沒有?？梢韵仍谄渌?wù)器上下載到對應(yīng)的腳本，部署即可。

# wget "https://pypi.python.org/packages/source/p/pip/pip-1.5.4.tar.gz#md5=834b2904f92d46aaa333267fb1c922bb" --no-check-certificate件有些環(huán)境在pip部署的時(shí)候可能有下面的錯(cuò)誤。

# python setup.py  install
Traceback (most recent call last):
  File "setup.py", line 6, in <module>
    from setuptools import setup, find_packages
ImportError: No module named setuptools看來還和一個(gè)setuptools的庫有關(guān)，我們繼續(xù)安裝。

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

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

對于插件PyMySQL可以使用如下的方式來安裝：

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

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

  使用開源工具恢復(fù)數(shù)據(jù)的實(shí)踐

完成了環(huán)境的配置，工具使用起來和mysqlbinlog還是有一些相似之處，好的地方就是多了一些輔助功能。

我們創(chuàng)建一個(gè)用戶admin來解析。

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.*  TO 'admin'@'127.0.0.1' IDENTIFIED BY  'admin';比如我們使用如下的命令來解析binlog得到指定時(shí)間戳范圍內(nèi)的SQL情況，在此我們限定數(shù)據(jù)為test

python binlog2sql/binlog2sql.py -h227.0.0.1 -P3306 -uadmin -padmin  -dtest --start-file='mysql-bin.000016' --start-datetime='2017-02-06 04:14:33' --stop-datetime='2017-02-06 04:15:44'  > /tmp/tmp.log得到的文件內(nèi)容如下：

#cat /tmp/tmp.log
DELETE FROM `test`.`test` WHERE `memo`='memo1' AND `id`=1 AND `name`='name1' LIMIT 1; #start 11127 end 11321 time 2017-02-06 04:15:23
DELETE FROM `test`.`test` WHERE `memo`='memo3' AND `id`=3 AND `name`='name3' LIMIT 1; #start 11127 end 11321 time 2017-02-06 04:15:23
UPDATE `test`.`test` SET `memo`='new', `id`=2, `name`='name2' WHERE `memo`='memo2' AND `id`=2 AND `name`='name2' LIMIT 1; #start 11400 end 11625 time 2017-02-06 04:15:29
UPDATE `test`.`test` SET `memo`='new', `id`=4, `name`='name4' WHERE `memo`='memo4' AND `id`=4 AND `name`='name4' LIMIT 1; #start 11400 end 11625 time 2017-02-06 04:15:29
INSERT INTO `test`.`test`(`memo`, `id`, `name`) VALUES ('memo6', 6, 'name6'); #start 12062 end 12239 time 2017-02-06 04:15:37其實(shí)看起來還是很省事了。

如果希望得到閃回的語句，有一個(gè)flashback的選項(xiàng)，其實(shí)就是在原來的基礎(chǔ)上進(jìn)行了解析和順序調(diào)整。

python binlog2sql/binlog2sql.py -h227.0.0.1 -P3306 -uadmin -padmin  -dtest --flashback --start-file='mysql-bin.000016' --start-datetime='2017-02-06 04:14:33' --stop-datetime='2017-02-06 04:15:44'  > /tmp/tmp.log得到的內(nèi)容如下：

#cat /tmp/tmp.log
DELETE FROM `test`.`test` WHERE `memo`='memo6' AND `id`=6 AND `name`='name6' LIMIT 1; #start 12062 end 12239 time 2017-02-06 04:15:37
UPDATE `test`.`test` SET `memo`='memo4', `id`=4, `name`='name4' WHERE `memo`='new' AND `id`=4 AND `name`='name4' LIMIT 1; #start 11400 end 11625 time 2017-02-06 04:15:29
UPDATE `test`.`test` SET `memo`='memo2', `id`=2, `name`='name2' WHERE `memo`='new' AND `id`=2 AND `name`='name2' LIMIT 1; #start 11400 end 11625 time 2017-02-06 04:15:29
INSERT INTO `test`.`test`(`memo`, `id`, `name`) VALUES ('memo3', 3, 'name3'); #start 11127 end 11321 time 2017-02-06 04:15:23
INSERT INTO `test`.`test`(`memo`, `id`, `name`) VALUES ('memo1', 1, 'name1'); #start 11127 end 11321 time 2017-02-06 04:15:23運(yùn)行了如上的語句之后，再次查看數(shù)據(jù)，數(shù)據(jù)就恢復(fù)了正常。

> select *from test;
+----+-------+-------+
| id | name  | memo  |
+----+-------+-------+
|  1 | name1 | memo1 |
|  2 | name2 | memo2 |
|  3 | name3 | memo3 |
|  4 | name4 | memo4 |
|  5 | name5 | memo5 |
+----+-------+-------+
5 rows in set (0.00 sec)

小結(jié)

    對于DML的閃回其實(shí)還是有一些技巧可以參考，對于DDL的閃回相對來說就麻煩的多了。我們后續(xù)也會跟進(jìn)這方面的工作。但是DML的閃回場景相對要多一些，我們尤其需要注意。

分享題目：MySQL誤操作數(shù)據(jù)恢復(fù)的簡單實(shí)踐（r11筆記第67天）
網(wǎng)頁URL：http://jinyejixie.com/article20/jopico.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)公司、域名注冊、搜索引擎優(yōu)化、品牌網(wǎng)站建設(shè)、手機(jī)網(wǎng)站建設(shè)、網(wǎng)站內(nèi)鏈

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)