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

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

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

--------------------------------------分割線 --------------------------------------

用mysqldump和mysqlbinlog的MySQL資料恢複實驗

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

這裡有個十分重要的工具——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的可用選項可參考相關手冊。這裡就一些需要特別注意的情況進行說明。

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁