使用mysqlbinlog工具進行基於位置或時間點的數據恢復

使用mysqlbinlog工具進行基於位置或時間點的數據恢復

使用mysqlbinlog工具進行基於位置或時間點的恢復

MySQL備份一般采取全備份加日志備份的方式，比如每天執行一次全備份，每小時執行一次二進制日志備份。這樣在MySQL Server故障後可以使用全備份和日志備份將數據恢復到最後一個二進制日志備份前的任意位置或時間。用來進行全備和日志備的工具各種各樣，各有其特色，在這裡不做描述。本文主要講解一下在回復完全備份後，如何應用備份的二進制日志來將數據恢復到指定的位置或時間點。

這裡有個十分重要的工具——mysqlbinlog,專門用來查看二進制日志。我們以一些列子來說明問題：

先看看如何在MySQL Server中直接查看有哪些二進制日志文件及文件中包含哪些事件。

先清空MySQL Server上的所有二進制日志
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

查看MySQL Server上的二進制日志
mysql> show binary logs;
+---------------------+-----------+
| Log_name | File_size |
+---------------------+-----------+
| VMS00781-bin.000001 | 120 |
+---------------------+-----------+

查看二進制日志中的事件

mysql>show binlog events;
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
| VMS00781-bin.000001 | 4 | Format_desc | 36 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+

執行一些DML操作

mysql> delete from ab limit 2;
Query OK, 2 rows affected (1.01 sec)

重新開始一個新的日志文件

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

執行一些DML操作

mysql> delete from ab limit 1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from ab limit 2;
Query OK, 2 rows affected (0.01 sec)

查看MySQL Server上的二進制日志
mysql> show binary logs;
+---------------------+-----------+
| Log_name | File_size |
+---------------------+-----------+
| VMS00781-bin.000001 | 372 |
| VMS00781-bin.000002 | 515 |
+---------------------+-----------+

查看二進制日志中的事件
mysql> show binlog events;
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
| VMS00781-bin.000001 | 4 | Format_desc | 36 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |
| VMS00781-bin.000001 | 120 | Query | 36 | 192 | BEGIN |
| VMS00781-bin.000001 | 192 | Table_map | 36 | 238 | table_id: 204 (test.ab) |
| VMS00781-bin.000001 | 238 | Delete_rows | 36 | 291 | table_id: 204 flags: STMT_END_F |
| VMS00781-bin.000001 | 291 | Xid | 36 | 322 | COMMIT /* xid=289981 */ |
| VMS00781-bin.000001 | 322 | Rotate | 36 | 372 | VMS00781-bin.000002;pos=4 |
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
默認顯示可找到的第一個二進制日志文件中的事件，包含了事件的開始位置、結束位置、事件類型、信息等內容。可以看到，第一個事件為格式描述事件；第二個為查詢事件，事務開始；第三個為表映射事件，第四個為我們執行的刪除操作，第五個為Xid時間是自動提交事務的動作，第六個為日志輪換事件，是我們執行flush logs開啟新日志文件引起的。

查看指定的二進制日志中的事件
mysql> show binlog events in 'VMS00781-bin.000002';
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
| VMS00781-bin.000002 | 4 | Format_desc | 36 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |
| VMS00781-bin.000002 | 120 | Query | 36 | 192 | BEGIN |
| VMS00781-bin.000002 | 192 | Table_map | 36 | 238 | table_id: 204 (test.ab) |
| VMS00781-bin.000002 | 238 | Delete_rows | 36 | 282 | table_id: 204 flags: STMT_END_F |
| VMS00781-bin.000002 | 282 | Xid | 36 | 313 | COMMIT /* xid=290004 */ |
| VMS00781-bin.000002 | 313 | Query | 36 | 385 | BEGIN |
| VMS00781-bin.000002 | 385 | Table_map | 36 | 431 | table_id: 204 (test.ab) |
| VMS00781-bin.000002 | 431 | Delete_rows | 36 | 484 | table_id: 204 flags: STMT_END_F |
| VMS00781-bin.000002 | 484 | Xid | 36 | 515 | COMMIT /* xid=290005 */ |
| VMS00781-bin.000002 | 515 | Query | 36 | 593 | flush slow logs |
| VMS00781-bin.000002 | 593 | Query | 36 | 671 | flush slow logs |
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
該命令還包含其他選項以便靈活查看
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
mysql> show binlog events in 'VMS00781-bin.000002' from 120 limit 2,3;
+---------------------+-----+-------------+-----------+-------------+---------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+-----+-------------+-----------+-------------+---------------------------------+
| VMS00781-bin.000002 | 238 | Delete_rows | 36 | 282 | table_id: 204 flags: STMT_END_F |
| VMS00781-bin.000002 | 282 | Xid | 36 | 313 | COMMIT /* xid=290004 */ |
| VMS00781-bin.000002 | 313 | Query | 36 | 385 | BEGIN |
+---------------------+-----+-------------+-----------+-------------+---------------------------------+

SHOW BINARY LOGS 等價於 SHOW MASTER LOGS
PURGE BINARY LOGS用於裡二進制日志，如：
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

RESET MASTER 與 RESET SLAVE
前者清空index文件中列出的所有二進制日志，重置index文件為空，並創建一個新的二進制日志文件，一般用於MASTER首次啟動時。後者使SLAVE忘記其在MASTER二進制日志文件中的復制位置，它會刪除master.info、relay-log.info 和所有中繼日志文件並開始一個新的中繼日志文件，以便於開始一個干淨的復制。在使用RESET SLAVE前需先關閉 SLAVE復制線程。

上述方式可以查看到服務器上存在的二進制日志文件及文件中的事件，但是想查看到文件中具體的內容並應於恢復場景還得借助mysqlbinlog這個工具。
查看：
shell> mysqlbinlog [options] log_file ...
比如：
mysqlbinlog [options] VMS00781-bin.000001
輸出內容會因日志文件的格式以及mysqlbinlog工具使用的選項不同而略不同。二進制日志文件中具體內容的含義以及mysqlbinlog的可用選項可參考相關手冊。這裡就一些需要特別注意的情況進行說明。

二進制日志文件的格式包含行模式、語句模式和混合模式（也即有服務器決定在什麼情況下記錄什麼類型的日志），基於語句的日志中事件信息包含執行的語句等，基於行的日志中事件信息包含的是行的變化信息等。混合模式的日志中兩種類型的事件信息都會記錄。為了便於查看記錄了行變化信息的事件在當時具體執行了什麼樣的SQL語句可以使用mysqlbinlog工具的-v（--verbose）選項，該選項會將行事件重構成被注釋掉的偽SQL語句，如果想看到更詳細的信息可

以將該選項給兩次如-vv，這樣可以包含一些數據類型和元信息的注釋內容，如

mysqlbinlog -v VMS00781-bin.000001
mysqlbinlog -vv VMS00781-bin.000001

另外mysqlbinlog和可以通過--read-from-remote-server選項從遠程服務器讀取二進制日志文件，這時需要一些而外的連接參數，如--host，--password ，--port，--user，--socket，--protocol等，這些參數僅在指定了--read-from-remote-server後有效。

無論是本地二進制日志文件還是遠程服務器上的二進制日志文件，無論是行模式、語句模式還是混合模式的二進制日志文件，被mysqlbinlog工具解析後都可直接應用與MySQL Server進行基於時間點、位置或數據庫的恢復。
比如：
mysqlbinlog VMS00781-bin.000001 | mysql -uusername -p

或者先將二進制日志寫到.sql文件中，然後在mysql客戶端執行這些文件，比如：
mysqlbinlog VMS00781-bin.000001 > /tmp/VMS00781-bin.000001.sql
mysql>source /tmp/VMS00781-bin.000001.sql

這裡有幾個比較關鍵的參數:
--database=db_name, -d db_name
該參數使mysqlbinlog僅從本地二進制日志中輸出指定的db_name被use命令選作默認數據庫時產生的日志事件。行為類似於mysqld的--binlog-do-db命令。若該參數指定了多次那麼只有最後一次指定的內容有效。參數具體的影響依賴於二進制日志格式，只有在使用行模式的日志格式時該參數才能保證一致性。基於語句或混合模式的二進制日志格式中因為可能存在跨庫的更新導致--database參數表現不同的行為，從而不能保證數據一致性。
mysqlbinlog VMS00781-bin.000001 -d testDB | mysql -uusername -p

--force-read, -f
使用了該參數後mysqlbinlog工具在讀取到不能識別的日志事件時會打印出warning，忽略事件並繼續執行，沒有此參數的情況下mysqlbinlog會停止。
mysqlbinlog VMS00781-bin.000001 -d testDB -f | mysql -uusername -p

--no-defaults
阻止mysqlbinlog工具從任何配置文件讀取參數， .mylogin.cnf除外（以便於安全的保存密碼）
mysqlbinlog VMS00781-bin.000001 -d testDB -f --no-defaults| mysql -uusername -p

--start-datetime=datetime
--stop-datetime=datetime
上邊一組參數用於指定恢復開始時間點和結束時間點，可以一起或單獨給出，也可與--start-position，--stop-position混用
mysqlbinlog VMS00781-bin.000001 -d testDB -f --no-defaults --start-datetime=datetime --stop-position=NNNNNN | mysql -uusername -p

--start-position=N, -j N
--stop-position=N
上邊一組參數用於指定恢復開始位置和結束位置，可以一起或單獨給出也可與--start-datetime，--stop-datetime混用
mysqlbinlog VMS00781-bin.000001 -d testDB -f --no-defaults --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

需要還原的二進制日志文件通常不止一個，那麼要是有多個二進制日志文件需要還原呢，該注意些什麼？
首先，可以選擇上述直接重定向到mysql客戶端的方法或先導入到.sql文件然後執行.sql文件的方式逐個應用二進制日志文件。但是這裡存在一個隱患，及，如果二進制日志中記錄有使用臨時表的情況，那麼當上一個日志應用完，在新連接中應用下一個二進制日志時臨時表就會丟失，引起錯誤。所以，安全的方式是多個二進制文件同時執行。
如：
mysqlbinlog VMS00781-bin.000001 VMS00781-bin.000002 VMS00781-bin.000003 --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

或mysqlbinlog VMS00781-bin.00000[1-3] --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

當多個二進制日志文件同時執行時，--start-position和--stop-position分別只應用於第一個列出的二進制日志文件和最後一個列出的二進制日志文件

當然也可以先將多個二進制日志文件的輸出導到同一個.sql文件最後在執行該.sql文件（適用於日志量不多的情況）