MySQL :: MySQL 8.4 參考手冊 :: 9.5.2 使用事件位置的時間點復原

版本 8.4

8.0 日文

MySQL 8.4 參考手冊 / ... / 使用事件位置的時間點復原

9.5.2 使用事件位置的時間點復原

上一節「第 9.5.1 節，「使用二進位日誌的時間點復原」」說明了使用二進位日誌執行時間點復原的一般概念。該節詳細解釋了操作，並提供了一個範例。

例如，假設在 2020 年 3 月 11 日大約 20:06:00 時，執行了一個刪除資料表的 SQL 陳述式。您可以執行時間點復原，將伺服器還原到刪除資料表之前的狀態。以下是一些實現此目標的範例步驟：

還原在感興趣的時間點之前建立的最後一次完整備份（稱之為 t_p，在我們的範例中是 2020 年 3 月 11 日的 20:06:00）。完成後，記下您已還原伺服器的二進位日誌位置，以便稍後使用，然後重新啟動伺服器。

注意

雖然 InnoDB 在還原和伺服器重新啟動後也會顯示最後還原的二進位日誌位置，但這不是取得還原結束日誌位置的可靠方法，因為在顯示的位置所反映的時間之後，可能發生了 DDL 事件和非 InnoDB 變更。您的備份和還原工具應提供您還原的最後二進位日誌位置：例如，如果您正在使用 mysqlbinlog 來執行此任務，請檢查二進位日誌重播的停止位置；如果您正在使用 MySQL Enterprise Backup，則最後的二進位日誌位置已儲存在您的備份中。請參閱時間點復原。

找出與您要將資料庫還原到的時間點相對應的精確二進位日誌事件位置。在我們的範例中，假設我們知道資料表刪除發生的粗略時間（t_p），我們可以透過使用 mysqlbinlog 公用程式檢查該時間附近的日誌內容來找到日誌位置。使用 --start-datetime 和 --stop-datetime 選項來指定 t_p 周圍的短時間段，然後在輸出中尋找事件。例如：

$> mysqlbinlog --start-datetime="2020-03-11 20:05:00" \
                   --stop-datetime="2020-03-11 20:08:00" --verbose \
         /var/lib/mysql/bin.123456 | grep -C 15 "DROP TABLE"
 
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 232
#200311 20:06:20 server id 1  end_log_pos 355 CRC32 0x2fc1e5ea 	Query	thread_id=16	exec_time=0	error_code=0
SET TIMESTAMP=1583971580/*!*/;
SET @@session.pseudo_thread_id=16/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
DROP TABLE `pets`.`cats` /* generated by server */
/*!*/;
# at 355
#200311 20:07:48 server id 1  end_log_pos 434 CRC32 0x123d65df 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1583971668462467	immediate_commit_timestamp=1583971668462467	transaction_length=473
# original_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)
# immediate_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)
/*!80001 SET @@session.original_commit_timestamp=1583971668462467*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 434
#200311 20:07:48 server id 1  end_log_pos 828 CRC32 0x57fac9ac 	Query	thread_id=16	exec_time=0	error_code=0	Xid = 217
use `pets`/*!*/;
SET TIMESTAMP=1583971668/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE dogs

從 mysqlbinlog 的輸出中，可以在二進位日誌中 # at 232 和 # at 355 行之間的部分找到 DROP TABLE `pets`.`cats` 陳述式，這表示該陳述式發生在日誌位置 232 之後，並且在 DROP TABLE 陳述式之後，日誌位於位置 355。

注意

僅使用 --start-datetime 和 --stop-datetime 選項來協助您找到感興趣的實際事件位置。不建議使用這兩個選項來指定要套用的二進位日誌區段範圍：當使用這些選項時，遺失二進位日誌事件的風險較高。請改用 --start-position 和 --stop-position。

將二進位日誌檔案中的事件套用至伺服器，從您在步驟 1 中找到的日誌位置開始（假設為 155），到您在步驟 2 中找到的位置結束，該位置在您感興趣的時間點之前（即 232）。
```
$> mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456 \
         | mysql -u root -p
```
此命令會從起始位置復原直到停止位置之前的交易。由於 mysqlbinlog 的輸出在每個記錄的 SQL 陳述式之前都包含 SET TIMESTAMP 陳述式，因此還原的資料和相關的 MySQL 日誌會反映交易執行的原始時間。
您的資料庫現在已還原到感興趣的時間點 t_p，即資料表 pets.cats 被刪除之前。
除了已完成的時間點復原之外，如果您也想重新執行感興趣的時間點之後的所有陳述式，請再次使用 mysqlbinlog，將 t_p 之後的所有事件套用至伺服器。我們在步驟 2 中提到，在我們要略過的陳述式之後，日誌位於位置 355；我們可以將其用於 --start-position 選項，以便包含該位置之後的任何陳述式。
```
$> mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456 \
         | mysql -u root -p
```
您的資料庫已還原到二進位日誌檔案中記錄的最新陳述式，但略過了選定的事件。