MySql增量恢復(fù)的幾種實現(xiàn)方法
一、 使用二進(jìn)制日志的時間點恢復(fù)
注意
本節(jié)和下一節(jié)中的許多示例都使用mysql客戶端來處理mysqlbinlog生成的二進(jìn)制日志輸出。如果您的二進(jìn)制日志包含\0(null)字符,那么mysql將無法解析該輸出,除非您使用--binary模式選項調(diào)用它。
時間點恢復(fù)的信息源是在完全備份操作之后生成的一組二進(jìn)制日志文件。因此,為了允許服務(wù)器恢復(fù)到某個時間點,必須在其上啟用二進(jìn)制日志記錄,這是MySQL 8.0的默認(rèn)設(shè)置
要從二進(jìn)制日志中恢復(fù)數(shù)據(jù),您必須知道當(dāng)前二進(jìn)制日志文件的名稱和位置。默認(rèn)情況下,服務(wù)器在數(shù)據(jù)目錄中創(chuàng)建二進(jìn)制日志文件,但可以使用--log-bin選項指定路徑名,以將文件放置在不同的位置。要查看所有二進(jìn)制日志文件的列表,請使用以下語句:
mysql> SHOW BINARY LOGS;
要確定當(dāng)前二進(jìn)制日志文件的名稱,請發(fā)出 以下聲明:
mysql> SHOW MASTER STATUS;
mysqlbinlog實用程序?qū)⒍M(jìn)制日志文件中的事件從二進(jìn)制格式轉(zhuǎn)換為文本,以便可以查看或應(yīng)用它們。mysqlbinlog具有根據(jù)事件時間或事件在日志中的位置選擇二進(jìn)制日志部分的選項。
應(yīng)用二進(jìn)制日志中的事件會導(dǎo)致重新執(zhí)行它們所表示的數(shù)據(jù)修改。這樣可以在給定的時間段內(nèi)恢復(fù)數(shù)據(jù)更改。要應(yīng)用二進(jìn)制日志中的事件,請使用mysql客戶端處理mysqlbinlog輸出:
$> mysqlbinlog binlog_files | mysql -u root -p
如果二進(jìn)制日志文件已經(jīng)加密(可以從MySQL 8.0.14開始),則mysqlbinlog不能像上面的示例那樣直接讀取它們,而是可以使用--read from remote server(-R)選項從服務(wù)器讀取它們。例如:
$> mysqlbinlog --read-from-remote-server --host=host_name --port=3306 --user=root --password --ssl-mode=required binlog_files | mysql -u root -p
這里,選項--ssl mode=required用于確保二進(jìn)制日志文件中的數(shù)據(jù)在傳輸過程中受到保護(hù),因為它是以未加密的格式發(fā)送到mysqlbinlog的。
重要
對于SSL模式,VERIFY_CA和VERIFY_IDENTITY是比REQUIRED更好的選擇,因為它們有助于防止中間人攻擊。若要實現(xiàn)其中一個設(shè)置,必須首先確保服務(wù)器的CA證書對環(huán)境中使用它的所有客戶端都可靠可用,否則將導(dǎo)致可用性問題。
當(dāng)您需要確定事件時間或位置以在執(zhí)行事件之前選擇部分日志內(nèi)容時,查看日志內(nèi)容非常有用。要查看日志中的事件,請將mysqlbinlog輸出發(fā)送到一個分頁程序中:
$> mysqlbinlog binlog_files | more
或者,將輸出保存在文件中,然后在 文本編輯器:
$> mysqlbinlog binlog_files > tmpfile $> ... edit tmpfile ...
編輯文件后,按如下方式應(yīng)用內(nèi)容:
$> mysql -u root -p < tmpfile
如果在MySQL服務(wù)器上有多個二進(jìn)制日志要應(yīng)用,請使用單個連接來應(yīng)用要處理的所有二進(jìn)制日志文件的內(nèi)容。以下是一種方法:
$> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
另一種方法是將整個日志寫入單個文件,然后處理該文件:
$> mysqlbinlog binlog.000001 > /tmp/statements.sql $> mysqlbinlog binlog.000002 >> /tmp/statements.sql $> mysql -u root -p -e "source /tmp/statements.sql"
二、使用事件位置的時間點恢復(fù)
例如,假設(shè)在2020年3月11日20:06:00左右,執(zhí)行了一條SQL語句,刪除了一個表。您可以執(zhí)行時間點恢復(fù),將服務(wù)器恢復(fù)到刪除表之前的狀態(tài)。以下是實現(xiàn)這一目標(biāo)的一些示例步驟:
- 恢復(fù)在感興趣的時間點之前創(chuàng)建的最后一個完整備份(稱為tp,在我們的示例中為2020年3月11日20:06:00)。完成后,請記下已將服務(wù)器還原到的二進(jìn)制日志位置,以便以后使用,然后重新啟動服務(wù)器。
注意
雖然恢復(fù)的最后一個二進(jìn)制日志位置也會在恢復(fù)和服務(wù)器重啟后由InnoDB顯示,但這不是獲取恢復(fù)的結(jié)束日志位置的可靠方法,因為在顯示位置反映的時間之后可能會發(fā)生DDL事件和非InnoDB更改。您的備份和恢復(fù)工具應(yīng)該為您的恢復(fù)提供最后一個二進(jìn)制日志位置:例如,如果您正在使用mysqlbinlog執(zhí)行任務(wù),請檢查二進(jìn)制日志回放的停止位置;如果您使用的是MySQL Enterprise Backup,則最后一個二進(jìn)制日志位置已保存在備份中。
- 查找與要恢復(fù)數(shù)據(jù)庫的時間點對應(yīng)的精確二進(jìn)制日志事件位置。在我們的例子中,假設(shè)我們知道表刪除發(fā)生的大致時間(tp),我們可以通過使用mysqlbinlog實用程序檢查該時間前后的日志內(nèi)容來找到日志位置。使用--start-datetime和--stop-datetime選項指定tp附近的短時間段,然后在輸出中查找事件。例如:
$> 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的輸出中,DROP TABLE為“pets”`cats語句可以在二進(jìn)制日志的232行#和355行#之間的段中找到,這意味著該語句發(fā)生在日志位置232之后,并且該日志位于DROP TABLE語句之后的位置355。
注意
僅使用 --開始日期時間和 --停止日期時間選項來幫助您找到 利息。使用這兩個選項指定 不建議應(yīng)用二進(jìn)制日志段:有一個 使用 選項。改用 --start-position 和 --stop-position。
- 將二進(jìn)制日志文件中的事件應(yīng)用于服務(wù)器,從您在步驟1中找到的日志位置開始(假設(shè)為155),到您在步驟2中找到的位于感興趣時間點之前的位置結(jié)束(即232):
$> mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456 \ | mysql -u root -p
該命令將所有事務(wù)從起始位置恢復(fù)到停止位置之前。由于mysqlbinlog的輸出在記錄每條SQL語句之前都包含SET TIMESTAMP語句,因此恢復(fù)的數(shù)據(jù)和相關(guān)的MySQL日志反映了事務(wù)執(zhí)行的原始時間。
您的數(shù)據(jù)庫現(xiàn)在已經(jīng)恢復(fù)到感興趣的時間點tp,就在表pets.cats被刪除之前。
- 除了已經(jīng)完成的時間點恢復(fù)之外,如果您還想在感興趣的時間點之后重新執(zhí)行所有語句,請再次使用mysqlbinlog將tp之后的所有事件應(yīng)用到服務(wù)器。我們在步驟2中注意到,在我們想要跳過的語句之后,日志位于355位置;我們可以將其用于--start-position選項,以便包含該位置之后的任何語句:
?$> mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456 \ | mysql -u root -p
您的數(shù)據(jù)庫已還原為二進(jìn)制日志文件中記錄的最新語句,但跳過了所選事件。
到此這篇關(guān)于MySql增量恢復(fù)的幾種實現(xiàn)方法的文章就介紹到這了,更多相關(guān)MySql增量恢復(fù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
安裝MySQl報Initializing?database?(may?take?a?long?time)錯誤
這篇文章主要給大家介紹了關(guān)于安裝MySQl報Initializing?database?(may?take?a?long?time)錯誤的解決辦法,文中通過圖文將解決的辦法介紹的非常詳細(xì),對大家學(xué)習(xí)或者使用mysql具有一定的參考借鑒價值,需要的朋友可以參考下2024-03-03MySQL數(shù)據(jù)庫之字符集?character
這篇文章主要介紹了MySQL數(shù)據(jù)庫之字符集?character,文章基于MySQL的的相關(guān)資料展開詳細(xì)介紹,具有一定的參考價值需要的小伙伴可以參考一下2022-05-05MySQL操作數(shù)據(jù)庫和表的常用命令新手教程
這篇文章主要介紹了MySQL操作數(shù)據(jù)庫和表的常用命令新手教程,本文總結(jié)的命令都是控制mysql必須掌握的、常用的命令,需要的朋友可以參考下2014-09-09簡單了解MYSQL數(shù)據(jù)庫優(yōu)化階段
這篇文章主要介紹了簡單了解MYSQL數(shù)據(jù)庫優(yōu)化階段,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-04-04mysql忘記root密碼的解決辦法(針對不同mysql版本)
這篇文章主要介紹了mysql忘記root密碼的解決辦法(針對不同mysql版本),文章通過代碼示例和圖文結(jié)合的方式給大家講解的非常詳細(xì),對大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-06-06