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

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

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

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
```
您的資料庫已還原到二進位日誌檔案中記錄的最新陳述式，但跳過了選定的事件。