MySQL中基於mysqldump和二進位日誌log-bin二進位日誌進行邏輯備份以及基於時間點的還原

標籤：ges   run   href   datetime   blank   版本   into   edit   file   

 

本文出處：http://www.cnblogs.com/wy123/p/6956464.html 

 

 

本文僅類比使用mysqldump和log-bin二進位日誌進行簡單測試，僅作為個人學習筆記，可能離實際應用還有很大差距，僅參考。

 

開啟MySQL的bin-log二進位日誌

　　類比還原是需要mysqldump出來的檔案和log-bin，因此需要開始log-bin二進位日誌。
　　mysql5.7.18在開啟二進位日誌的時候除了要設定log-bin的位置之外，另外需要設定一個server-id，MySQL之前的版本應該不需要這個設定。

　　吐槽一下開源軟體，基本上每個版本都有一些跟之前版本不一樣的地方，網上查資料，不同版本的情況下不少東西都是不一樣的，這一點需要注意。

　　

　　重啟之後，查詢log_bin相關的變數

　　

 

mysqldump備份（匯出）資料的基本使用

mysqldump命令參數相當多，簡單記錄一下常用的命令，以及利用mysqldump備份（嚴格說是匯出資料）和二進位日誌log-bin進行資料庫還原操作。

-- 備份testdb整個資料庫，-l表示給所有表上加一個讀鎖，-F(F要大寫，消協不報錯單頁無效)表示滾動產生一個新的記錄檔
mysqldump -u root -p -l -F -h localhost testdb > usr/local/mysqlbak/test20170607_data.sql

備份出來的檔案就是create table和insert into table的指令碼

-- 備份testdb資料庫中的test_table1 test_table2兩張表，加上--no-create-info就意味這個備份出來的檔案不帶create table的指令碼，僅僅是insert into table的資訊
mysqldump -uroot -p -h localhost testdb test_table1 test_table2 --no-create-info> usr/local/mysqlbak/test20170606_1.sql

-- 備份testdb資料庫中的test2表中的一部分資料，也就是備份test_table1表中符合id<1000的資料
mysqldump -uroot -p -h localhost testdb test_table1 --where "id<1000" > usr/local/mysqlbak/test20170606_2.sql

 

更多mysqldump的參數，參考：http://www.cnblogs.com/xuejie/archive/2013/01/11/2856911.html

 

利用mysqldump備份的檔案和log-bin二進位日誌進行還原

　　首先在以表中有資料的情況下進行備份

　　執行 mysqldump -u root -p -l -F -h localhost testdb --master-data=2 > usr/local/mysqlbak/test20170607_data.sql

　　

　　這裡加了一個--master-data=2的選項，目的是在備份檔案中備忘出來當前的log_bin的檔案，
　　至於為什麼要加這個命令，很多部落格上都記錄的是用mysqldump備份出來一個檔案之後，修改資料，然後類比資料庫誤刪或者宕機還原怎麼的，然後在使用mysqldump出來的檔案還原之後，接著使用log-bin還原
　　雖然都是測試類比，但是有一個明顯的問題啊，怎麼知道mysqldump之後日誌是否發生了滾動，滾動了幾次？
　　如果日誌不滾動還要，按照時間點或者位置還原log-bin檔案，如果滾動了，怎麼知道滾動了幾個記錄檔呢
　　就需要在mysqldump執行的時候，記錄下來重新整理日誌之後的新產生的log-bin檔案，在使用日誌還原的時候，才能判斷使用哪些日誌進行還原。

　　有了--master-data=2的選項，才知道mysqldump備份時候的log_file位置

　　然後繼續往表中插入10條資料

　　

　　然後類比在某個時間點誤刪資料的情況，truncate測試表，測試表此時為空白

　　

　　首先利用mysqldump出來的檔案還原資料庫，mysqldump出來的檔案備份是100行時候的資料
　　因為備份檔案中備份的時候的資料是100行，這裡還原之後是100行，沒有問題。

　　

　　然後接著利用log-bin按照還原時間點，上面說了，mysqldump出來的檔案記錄了日誌重新整理之後的log-bin的檔案名稱，
　　那麼就可以判斷日誌是否發生了滾動，如果沒有滾動，就按照最新的log-bin進行按照時間點進行還原。

　　

　　進行一次mysqldump備份還原
　　mysql -u root -p testdb < usr/local/mysqlbak/test20170607_data.sql
　　進然後再行一次基於bin-log的還原時間點
　　mysqlbinlog --stop-datetime="2017-6-7 21:45:00" /var/lib/mysql/mysql-bin.000022 | mysql -u root -p testdb
　　然後資料就回來了。

　　

 

 

　　當然這裡僅僅是類比操作，當然還有許多細節尚未確定，如果發生了日誌滾動，要做基於時間點的還原，還要追究到究竟是基於哪個記錄檔的時間點。

 

總結：

　　本文僅僅以一個簡單的樣本來模式資料庫的還原作業，
　　mysqldump備份模式比較簡單粗暴，僅僅是將資料匯出為insert指令碼，在還原較大資料時候會有效能問題，可能mysqldump就不適合了，就需要更為高效的xtrabackup來做備份還原了。

 

　　

 　　

　　

 

MySQL中基於mysqldump和二進位日誌log-bin二進位日誌進行邏輯備份以及基於時間點的還原