某天工作時間，一個二貨犯暈登錯生産當測試環境了，直接drop了一個資料庫,需要緊急恢複!可利用備份的資料檔案以及增量的 binlog 檔案進行資料恢複。
具體思路歸納幾點：
1、恢複條件為 MySQL 要開啟 binlog 日志功能，并且要全備和增量的所有資料。
2、恢複時建議對外停止更新，即禁止更新資料庫。（這點很重要）
3、先恢複全量，然後把全備時刻點以後的增量日志，按順序恢複成 SQL 檔案，
4、然後把檔案中有問題的SQL語句删除(也可通過時間和位置點)，再恢複到資料庫。
具體執行個體示範：
1、首先要確定MySQL開啟了binlog日志功能，檢查如下結果
1
2
3
4
5
6
7
8
9
10
11
12
<code>mysql> show variables like </code><code>'%log_bin%'</code><code>;</code>
<code>+---------------------------------+-----------------------------+</code>
<code>| Variable_name | Value |</code>
<code>| log_bin | ON |</code>
<code>| log_bin_basename | </code><code>/mysql_data/mysql-bin</code> <code>|</code>
<code>| log_bin_index | </code><code>/mysql_data/mysql-bin</code><code>.index |</code>
<code>| log_bin_trust_function_creators | OFF |</code>
<code>| log_bin_use_v1_row_events | OFF |</code>
<code>| sql_log_bin | ON |</code>
<code>6 rows </code><code>in</code> <code>set</code> <code>(0.01 sec)</code>
2、檢視目前測試表裡面資料資訊後面好做對比
13
14
15
16
17
18
19
20
21
<code>mysql> </code><code>select</code> <code>* from Student;</code>
<code>+-----------+-----------+------+------+-------+</code>
<code>| Sno | Sname | Ssex | Sage | Sdept |</code>
<code>| 200215121 | 李勇 | 男 | 20 | CS |</code>
<code>| 200215122 | 劉晨 | 女 | 19 | CS |</code>
<code>| 200215123 | 王敏 | 女 | 18 | MA |</code>
<code>| 200215125 | 張立 | 女 | 19 | IS |</code>
<code>| 200215126 | 虎威 | 男 | 25 | CS |</code>
<code>| 200215127 | 魏大師 | 男 | 35 | IS |</code>
<code>| 200215128 | 老謝 | 男 | 33 | MA |</code>
<code>| 200215129 | 小賈 | 男 | 30 | CS |</code>
<code>| 200215130 | 會民 | 男 | 23 | CS |</code>
<code>| 200215131 | 陳興 | 男 | 33 | MA |</code>
<code>| 200215132 | 阿帆 | 男 | 36 | IS |</code>
<code>| 200215133 | 國良 | 男 | 40 | IS |</code>
<code>| 200215134 | 老宋 | 男 | 40 | IS |</code>
<code>| 200215135 | 光光 | 男 | 35 | IS |</code>
<code>| 200215136 | 王老闆 | 女 | 27 | IS |</code>
<code>15 rows </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
3、現在進行全備份
<code>mysqldump -u root -p -B -F -R -x student|</code><code>gzip</code> <code>> </code><code>/mysql_backup/student_</code><code>$(</code><code>date</code> <code>+%Y%m%d_%H%M%S).sql.gz</code>
<code>參數說明：</code>
<code>-B：指定資料庫</code>
<code>-F：重新整理日志</code>
<code>-R：備份存儲過程等</code>
<code>-x：鎖表</code>
4、再次插入新資料
<code>INSERT INTO Student VALUES (</code><code>'200215137'</code><code>,</code><code>'程程'</code><code>,</code><code>'女'</code><code>,30,</code><code>'IS'</code><code>);</code>
<code>INSERT INTO Student VALUES (</code><code>'200215138'</code><code>,</code><code>'琪琪'</code><code>,</code><code>'男'</code><code>,29,</code><code>'MA'</code><code>);</code>
<code>INSERT INTO Student VALUES (</code><code>'200215139'</code><code>,</code><code>'龍龍'</code><code>,</code><code>'男'</code><code>,27,</code><code>'IS'</code><code>);</code>
5、檢查是否插入成功，如下可以看出已經插入成功
22
23
24
<code>| 200215137 | 程程 | 女 | 30 | IS |</code>
<code>| 200215138 | 琪琪 | 男 | 29 | MA |</code>
<code>| 200215139 | 龍龍 | 男 | 27 | IS |</code>
<code>18 rows </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
6.此時誤操作，删除了student資料庫
<code>mysql> drop database student;</code>
<code>Query OK, 3 rows affected (0.11 sec)</code>
<code>mysql> show databases;</code>
<code>+--------------------+</code>
<code>| Database |</code>
<code>| information_schema |</code>
<code>| mysql |</code>
<code>| performance_schema |</code>
<code>| sys |</code>
<code>4 rows </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
7.檢視全備之後備份檔案
<code>[root@ocbsdb01 mysql_backup]</code><code># cd /mysql_backup</code>
<code>[root@ocbsdb01 mysql_backup]</code><code># gzip -d student_20170829_090319.sql.gz </code>
<code>[root@ocbsdb01 mysql_backup]</code><code># ls</code>
<code>student_20170829_090319.sql</code>
<code>[root@ocbsdb01 mysql_backup]</code><code># vim student_20170829_090319.sql</code>
8.檢查并移動binlog檔案，并導出為 SQL 檔案剔除其中的 drop 語句，檢視 MySQL 的資料存放目錄，
由下面可知是在/mysql_data下，将 binlog 檔案導出SQL檔案，并vim編輯它删除其中的 drop 語句。
/home/mysql/mysql5/bin/mysqlbinlog --no-defaults /tmp/mysql-bin.000004 > /tmp/04.sql
注意：在恢複全備資料之前必須将該 binlog檔案移出，否則恢複過程中，會繼續寫入語句到 binlog，最終導緻增量恢複資料部分變得比較混亂。
在轉換sql的時候可能會報錯，具體資訊如下：
[root@ocbsdb01 tmp]# /home/mysql/mysql5/bin/mysqlbinlog /tmp/mysql-bin.000004 > /tmp/04.sql
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
原因是mysqlbinlog這個工具無法識别binlog中的配置中的default-character-set=utf8這個指令。
兩個方法可以解決這個問題
在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改為 character-set-server = utf8，但是這需要重新開機MySQL服務，如果你的MySQL服務正在忙，那這樣的代價會比較大。
用mysqlbinlog --no-defaults mysql-bin.000004 指令打開
9、開始恢複全備資料
<code>[root@ocbsdb01 tmp]</code><code># mysql -u root -p < /mysql_backup/student_20170829_090319.sql</code>
<code>Enter password:</code>
檢視資料看看，student在不在,可以看到已經在了
<code>| student |</code>
<code>5 rows </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
11、開始恢複增量資料
使用04.sql檔案恢複全備時刻到删除資料庫之間新增的資料，編輯04bin.sql #删除裡面的drop語句
[root@ocbsdb01 tmp]# vim 04.sql
<code>将drop 操作下面的内容删除</code>
<code>drop database student</code>
<code>/*!*/;</code>
<code>SET @@SESSION.GTID_NEXT= </code><code>'AUTOMATIC'</code> <code>/* added by mysqlbinlog */ /*!*/;</code>
<code>DELIMITER ;</code>
<code>/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;</code>
<code>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;</code>
不然會報如下錯誤：
ERROR 1790 (HY000) at line 96: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID.
The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.
我的binlog檔案具體内容如下：
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
<code>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;</code>
<code>/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;</code>
<code>DELIMITER /*!*/;</code>
<code># at 4</code>
<code>#170829 9:03:22 server id 201609 end_log_pos 123 CRC32 0x669b3a18 Start: binlog v 4, server v 5.7.18-log created 170829 9:03:22</code>
<code># Warning: this binlog is either in use or was not closed properly.</code>
<code>BINLOG '</code>
<code>Wr2kWQ+JEwMAdwAAAHsAAAABAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</code>
<code>AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA</code>
<code>ARg6m2Y=</code>
<code>'/*!*/;</code>
<code># at 123</code>
<code>#170829 9:03:22 server id 201609 end_log_pos 154 CRC32 0xf909764e Previous-GTIDs</code>
<code># [empty]</code>
<code># at 154</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 219 CRC32 0x754f68f2 Anonymous_GTIDlast_committed=0sequence_number=1</code>
<code>SET @@SESSION.GTID_NEXT= </code><code>'ANONYMOUS'</code><code>/*!*/;</code>
<code># at 219</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 294 CRC32 0xdef39415 Querythread_id=7exec_time=0error_code=0</code>
<code>SET TIMESTAMP=1503968682/*!*/;</code>
<code>SET @@session.pseudo_thread_id=7/*!*/;</code>
<code>SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;</code>
<code>SET @@session.sql_mode=1344274432/*!*/;</code>
<code>SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;</code>
<code>/*!\C utf8 *</code><code>//</code><code>*!*/;</code>
<code>SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;</code>
<code>SET @@session.lc_time_names=0/*!*/;</code>
<code>SET @@session.collation_database=DEFAULT/*!*/;</code>
<code>BEGIN</code>
<code># at 294</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 359 CRC32 0xad757652 Table_map: `student`.`Student` mapped to number 225</code>
<code># at 359</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 421 CRC32 0x5da9edf3 Write_rows: table id 225 flags: STMT_END_F</code>
<code>qr2kWROJEwMAQQAAAGcBAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+</code><code>/gL</code><code>+CP4h</code><code>/jz</code><code>+</code>
<code>Bv48HlJ2da0=</code>
<code>qr2kWR6JEwMAPgAAAKUBAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM3Bueoi+eoiwPlpbMeAAJJ</code>
<code>U</code><code>/PtqV0</code><code>=</code>
<code># at 421</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 452 CRC32 0xfcd87186 Xid = 168</code>
<code>COMMIT/*!*/;</code>
<code># at 452</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 517 CRC32 0xe47a26ba Anonymous_GTIDlast_committed=1sequence_number=2</code>
<code># at 517</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 592 CRC32 0x0d3e44d1 Querythread_id=7exec_time=0error_code=0</code>
<code># at 592</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 657 CRC32 0x98d94728 Table_map: `student`.`Student` mapped to number 225</code>
<code># at 657</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 719 CRC32 0x32c7750e Write_rows: table id 225 flags: STMT_END_F</code>
<code>qr2kWROJEwMAQQAAAJECAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+</code><code>/gL</code><code>+CP4h</code><code>/jz</code><code>+</code>
<code>Bv48HihH2Zg=</code>
<code>qr2kWR6JEwMAPgAAAM8CAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM4BueQqueQqgPnlLcdAAJN</code>
<code>QQ51xzI=</code>
<code># at 719</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 750 CRC32 0x92ebbf95 Xid = 169</code>
<code># at 750</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 815 CRC32 0xfe05a22e Anonymous_GTIDlast_committed=2sequence_number=3</code>
<code># at 815</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 890 CRC32 0x551c26a4 Querythread_id=7exec_time=0error_code=0</code>
<code># at 890</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 955 CRC32 0x67c477d0 Table_map: `student`.`Student` mapped to number 225</code>
<code># at 955</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 1017 CRC32 0x5d3f2503 Write_rows: table id 225 flags: STMT_END_F</code>
<code>qr2kWROJEwMAQQAAALsDAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+</code><code>/gL</code><code>+CP4h</code><code>/jz</code><code>+</code>
<code>Bv48HtB3xGc=</code>
<code>qr2kWR6JEwMAPgAAAPkDAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM5Bum+mem+mQPnlLcbAAJJ</code>
<code>UwMlP10=</code>
<code># at 1017</code>
<code>#170829 9:04:42 server id 201609 end_log_pos 1048 CRC32 0x2d5f70ba Xid = 170</code>
<code># at 1048</code>
<code>#170829 9:06:30 server id 201609 end_log_pos 1113 CRC32 0xb8cdf9b6 Anonymous_GTIDlast_committed=3sequence_number=4</code>
<code># at 1113</code>
<code>#170829 9:06:30 server id 201609 end_log_pos 1214 CRC32 0x69d17a84 Querythread_id=7exec_time=0error_code=0</code>
<code>SET TIMESTAMP=1503968790/*!*/;</code>
調整好後開始恢複增量資料
[root@ocbsdb01 tmp]# mysql -u root -p < 04.sql
Enter password:
再次檢視資料庫，發現全備份到删除資料庫之間的那三條資料也恢複了!!
以上就是MySQL 資料庫增量資料恢複的執行個體過程!如有不足還請指正
本文轉自 yuri_cto 51CTO部落格，原文連結：http://blog.51cto.com/laobaiv1/1960846，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)