番外篇：如何利用 Binlog 還原資料庫

在MySQL世界中，有個binlog功能很重要，確實蠻有用處的。 binlog 全名是binary logging

什麼是 Binlog

Binlog 介紹

binlog本質上是二進制日誌，用來紀錄資料庫每一筆transaction細節，除了 select、view 不會變動資料庫資料的行為。 binlog可以用來：

• 資料庫主從同步

• 還原資料庫

Binlog 啟用

如果你要實現資料庫主從架構，必須啟用binlog。

[mysqld]
log-bin=mysql-bin
server-id=1

Binlog 格式

打開binlog內容如下，可以從info中得知進行了哪些資料庫行為。

+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+
| mysql-bin.000011 |   4 | Format_desc |  88955285 |         120 | Server ver: 5.6.30-log, Binlog ver: 4               |
| mysql-bin.000011 | 120 | Query       |  88955285 |         211 | create database db1                                 |
| mysql-bin.000011 | 211 | Query       |  88955285 |         310 | use `db1`; CREATE TABLE t (c CHAR(20))              |
| mysql-bin.000011 | 310 | Query       |  88955285 |         381 | BEGIN                                               |
| mysql-bin.000011 | 381 | Table_map   |  88955285 |         426 | table_id: 18 (db1.t)                                |
| mysql-bin.000011 | 310 | Query       |  88955285 |         381 | BEGIN                                               |
| mysql-bin.000011 | 426 | Write_rows  |  88955285 |         464 | table_id: 18 flags: STMT_END_F                      |
| mysql-bin.000011 | 464 | Xid         |  88955285 |         495 | COMMIT /* xid=56 */                                 |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+

GCP Cloud SQL 如何藉由binlog復原資料庫

我的環境是GCP Cloud SQL，在一開始設定資料庫時，非常建議啟用Binlog(二進位檔記錄)功能，否則下面別玩了！

在你需要透過binlog進行資料庫資料還原之前，需要知道你要還原到哪個時間點，甚至說確實點，要還原到什麼狀態之前。

找出 log position

這個步驟很重要，要與團隊確認精準的還原點，會比起進行多次還原找回資料，有效率多。

# 1.先取得 BINARY_LOG_FILE
SHOW BINARY LOGS;
# 2.查看內容，是否有您重要的 position
SHOW BINLOG EVENTS IN '<BINARY_LOG_FILE>';
# 

上面步驟2二，應該會讓你花費不少時間，建議有效率的做法是：

• 只找關鍵 transaction 語法，例如 delete 了表、庫等等

• 寫程式連接資料庫執行個體，大量、快速讀取 BINARY_LOG_FILE

當您尋找到要還原至的特定狀態之Position，務必馬上記錄Position number (顯示為 Pos) 及binlog file name。這是用來進行復原的重要參數。

找到DROP TABLE的範例，要還原資料庫至發生DROP TABLE之前 所以還原至Pos：649，其Log_name：mysql-bin.000011

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+
| mysql-bin.000011 | 495 | Query       |  88955285 |         566 | BEGIN                                               |
| mysql-bin.000011 | 566 | Table_map   |  88955285 |         611 | table_id: 18 (db1.t)                                |
| mysql-bin.000011 | 611 | Write_rows  |  88955285 |         649 | table_id: 18 flags: STMT_END_F                      |
| mysql-bin.000011 | 649 | Xid         |  88955285 |         680 | COMMIT /* xid=57 */                                 |
| mysql-bin.000011 | 680 | Query       |  88955285 |         751 | BEGIN                                               |
| mysql-bin.000011 | 751 | Table_map   |  88955285 |         796 | table_id: 18 (db1.t)                                |
| mysql-bin.000011 | 796 | Write_rows  |  88955285 |         834 | table_id: 18 flags: STMT_END_F                      |
| mysql-bin.000011 | 834 | Xid         |  88955285 |         865 | COMMIT /* xid=58 */                                 |
| mysql-bin.000011 | 865 | Query       |  88955285 |         977 | use `db1`; DROP TABLE `t` /* generated by server */ |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+

執行復原

在Cloud SQL透過binlog復原資料庫的步驟如下：

1. 前往 Google Cloud Platform 主控台的「Cloud SQL Instances」(Cloud SQL 執行個體) 頁面。

   前往 CLOUD SQL 執行個體頁面

2. 找到您要還原的執行個體，接著開啟其更多動作選單 ，然後選取 [Clone] (複製)。

3. 在「Create clone」(建立本機複本) 視窗中，視需要更新新執行個體的名稱。

4. 在「Advanced」(進階) 之下，選取 [Clone from earlier position] (從先前的位置複製)。

5. 輸入您之前在「二進位檔記錄檔案名稱」中識別的二進位檔記錄名稱。

6. 輸入您要從「復原位置」修復之事件的位置。

7. 按一下 [建立本機複本]。

接著就是等候資料庫復原完畢，復原所需時間則視您的資料庫資料多寡而定了。