mysql 定期备份策略,MySQL--3--mysqldump备份策略
參考地址:http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/database-administration.html#backup-strategy-example
5.9 備份策略
在my.inf中啟動二進制日志,用--log-bin[=file_name]選項啟動時,mysqld寫入包含所有更新數據的SQL命令的日志文件。如果未給出file_name值, 默認名為-bin后面所跟的主機名。如果給出了文件名,但沒有包含路徑,則文件被寫入數據目錄。建議指定一個文件名,這樣在數據庫系統崩潰的情況下應該能夠使用其日志。
如果你在日志名中提供了擴展名(例如,--log-bin=file_name.extension),則擴展名被悄悄除掉并忽略。
mysqld在每個二進制日志名后面添加一個數字擴展名。每次你啟動服務器或刷新日志時該數字則增加。如果當前的日志大小達到max_binlog_size,還會自動創建新的二進制日志。如果你正使用大的事務,二進制日志還會超過max_binlog_size:事務全寫入一個二進制日志中,絕對不要寫入不同的二進制日志中。
為了能夠知道還使用了哪個不同的二進制日志文件,mysqld還創建一個二進制日志索引文件,包含所有使用的二進制日志文件的文件名。默認情況下與二進制日志文件的文件名相同,擴展名為'.index'。你可以用--log-bin-index[=file_name]選項更改二進制日志索引文件的文件名。當mysqld在運行時,不應手動編輯該文件;如果這樣做將會使mysqld變得混亂。
5.9.1 備份(以下省略了-u和-p參數)
我們都知道必須按計劃定期進行備份。可以用幾個工具完全備份(在某個時間點的數據快照)MySQL。例如,InnoDB Hot Backup為InnoDB數據文件提供在線非數據塊物理備份,mysqldump提供在線邏輯備份。這里使用mysqldump。
假定我們在星期日下午1點進行了備份,此時負荷較低。下面的命令可以完全備份所有數據庫中的所有InnoDB表:
shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql
這是在線非塊備份,不會干擾對表的讀寫。我們以前假定我們的表為InnoDB表,因此--single-transaction使用一致性地讀,并且保證mysqldump所看見的數據不會更改。(其它客戶端對InnoDB表進行的更改不會被mysqldump進程看見)。如果我們還有其它類型的表,我們必須假定在備份過程中它們不會更改。例如,對于mysql數據庫中的MyISAM表,我們必須假定在備份過程中沒有對MySQL賬戶進行管理更改。
mysqldump命令產生的.sql文件包含一系列SQL INSERT語句,可以用來重載轉儲的表。
需要進行完全備份,但有時不方便。會產生大的備份文件并需要花時間來生成。從某個角度,完全備份并不理想,因為每個成功的完全備份包括所有數據,甚至自從上一次完全備份以來沒有更改的部分。完成了初使完全備份后,進行增量備份會更有效。這樣備份文件要小得多,備份時間也較短。不利之處是,恢復時不能只重載完全備份來恢復數據。還必須要用增量備份來恢復增量更改。
要想進行增量備份,我們需要保存增量更改。應使用--log-bin選項啟動MySQL服務器,以便更新數據時將這些更改保存到文件中。該選項啟用二進制日志,因此服務器寫將每個更新數據的SQL語句寫入MySQL二進制日志。讓我們看看用--log-bin選項啟動的已經運行多日的MySQL服務器的數據目錄。我們找到以下MySQL二進制日志文件:
-rw-rw---- 1 guilhem? guilhem?? 1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem? guilhem???????? 4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem? guilhem??????? 79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem? guilhem?????? 508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem? guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem? guilhem??? 998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem? guilhem?????? 361 Nov 14 10:07 gbichot2-bin.index
每次重啟,MySQL服務器用序列中的下一個編號創建一個新的二進制日志文件。當服務器運行時,你還可以通過執行FLUSH LOGS SQL語句或mysqladmin flush-logs命令,告訴服務器關閉當前的二進制日志文件并創建一個新文件。mysqldump也有一個選項來清空日志。數據目錄中的.index文件包含該目錄下所有MySQL二進制日志的清單。該文件用于復制。
恢復時MySQL二進制日志很重要,因為它們是增量備份。如果進行完全備份時確保清空了日志,則后面創建的二進制日志文件包含了備份后的所有數據更改。讓我們稍稍修改前面的mysqldump命令,讓它在完全備份時能夠清空 MySQL二進制日志,以便轉儲文件包含包含新的當前的二進制日志:
shell> mysqldump --single-transaction --flush-logs --master-data=2?????????? --all-databases > backup_sunday_1_PM.sql
執行該命令后,數據目錄則包含新的二進制日志文件,gbichot2-bin.000007。結果.sql文件包含下列行:
-- Position to start replication or point-in-time 恢復時y from-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;
因為mysqldump命令可以執行完全備份,這些行表示兩件事情:
· .sql文件包含所有寫入gbichot2-bin.000007二進制日志文件或最新的文件之前的更改。
· 備份后所記錄的所有數據更改不出現在.sql中,但出現在gbichot2-bin.000007二進制日志文件或最新的文件中。
在星期一下午1點,我們可以清空日志開始新的二進制日志文件來創建增量備份。例如,執行mysqladmin flush-logs命令創建gbichot2-bin.000008。星期日下午1點的完全備份和星期一下午1點之間的所有更改為文件gbichot2-bin.000007。該增量備份很重要,因此最好將它復制到安全的地方。(例如,備份到磁帶或DVD上,或復制到另一臺機器上)。在星期二下午1點,執行另一個mysqladmin flush-logs命令。星期一下午1點和星期二下午1點之間的所有所有更改為文件gbichot2-bin.000008(也應復制到某個安全的地方)。
MySQL二進制日志占據硬盤空間。要想釋放空間,應隨時清空。操作方法是刪掉不再使用的二進制日志,例如進行完全備份時:
shell> mysqldump --single-transaction --flush-logs --master-data=2?????????? --all-databases --delete-master-logs > backup_sunday_1_PM.sql
注釋:如果你的服務器為復制主服務器,用mysqldump --delete-master-logs刪掉MySQL二進制日志很危險,因為從服務器可能還沒有完全處理該二進制日志的內容。
PURGE MASTER LOGS語句的描述中解釋了為什么在刪掉MySQL二進制日志之前應進行確認。參見13.6.1.1節,“PURGE MASTER LOGS語法”。 5.9.2.2. 為恢復進行備份
現在假設在星期三上午8點出現了災難性崩潰,需要使用備份文件進行恢復。恢復時,我們首先恢復最后的完全備份(從星期日下午1點開始)。完全備份文件是一系列SQL語句,因此恢復它很容易:
shell> mysql < backup_sunday_1_PM.sql
在該點,數據恢復到星期日下午1點的狀態。要想恢復從那時起的更改,我們必須使用增量備份,也就是,gbichot2-bin.000007和gbichot2-bin.000008二進制日志文件。根據需要從備份處取過這些文件,然后按下述方式處理:
shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql
另一個方法是:
shell> mysqlbinlog hostname-bin.000007 >? /tmp/statements.sql
shell> mysqlbinlog hostname-bin.000008 >> /tmp/statements.sqlshell> mysql -e "source /tmp/statements.sql"
我們現在將數據恢復到星期二下午1點的狀態,但是從該時刻到崩潰之間的數據仍然有丟失。要想恢復,我們需要MySQL服務器將MySQL二進制日志保存到安全的位置(RAID disks, SAN, ...),應為與數據文件的保存位置不同的地方,保證這些日志不在毀壞的硬盤上。(也就是,我們可以用--log-bin選項啟動服務器,指定一個其它物理設備上的與數據目錄不同的位置。這樣,即使包含該目錄的設備丟失,日志也不會丟失)。如果我們執行了這些操作,我們手頭上會有gbichot2-bin.000009文件,我們可以用它來恢復大部分最新的數據更改,而不會丟失到崩潰時的數據。 5.9.2.3. 備份策略摘要
出現操作系統崩潰或電源故障時,InnoDB自己可以完成所有數據恢復工作。但為了確保你可以睡好覺,應遵從下面的指導:
· 一定用--log-bin或甚至--log-bin=log_name選項運行MySQL服務器,其中日志文件名位于某個安全媒介上,不同于數據目錄所在驅動器。如果你有這樣的安全媒介,最好進行硬盤負載均衡(這樣能夠提高性能)。
· 定期進行完全備份,使用mysqldump命令進行在線非塊備份。
· 用FLUSH LOGS或mysqladmin flush-logs清空日志進行定期增量備份。
5.9.3. 自動恢復
如果MySQL服務器啟用了二進制日志,你可以使用mysqlbinlog工具來恢復從指定的時間點開始 (例如,從你最后一次備份)直到現在或另一個指定的時間點的數據。關于啟用二進制日志的信息,參見5.11.3節,“二進制日志”。對于mysqlbinlog的詳細信息,參見8.6節,“mysqlbinlog:用于處理二進制日志文件的實用工具”。
要想從二進制日志恢復數據,你需要知道當前二進制日志文件的路徑和文件名。一般可以從選項文件(即my.cnf or my.ini,取決于你的系統)中找到路徑。如果未包含在選項文件中,當服務器啟動時,可以在命令行中以選項的形式給出。啟用二進制日志的選項為--log-bin。要想確定當前的二進制日志文件的文件名,輸入下面的MySQL語句:
SHOW BINLOG EVENTS \G
你還可以從命令行輸入下面的內容:
mysql --user=root -pmy_pwd -e 'SHOW BINLOG EVENTS \G'
將密碼my_pwd替換為服務器的root密碼。 5.9.3.1. 指定恢復時間
對于MySQL 4.1.4,可以在mysqlbinlog語句中通過--start-date和--stop-date選項指定DATETIME格式的起止時間。舉例說明,假設在今天上午10:00(今天是2005年4月20日),執行SQL語句來刪除一個大表。要想恢復表和數據,你可以恢復前晚上的備份,并輸入:mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 \???? | mysql -u root -pmypwd
該命令將恢復截止到在--stop-date選項中以DATETIME格式給出的日期和時間的所有數據。如果你沒有檢測到幾個小時后輸入的錯誤的SQL語句,可能你想要恢復后面發生的活動。根據這些,你可以用起使日期和時間再次運行mysqlbinlog:
mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 \???? | mysql -u root -pmypwd \
在該行中,從上午10:01登錄的SQL語句將運行。組合執行前夜的轉儲文件和mysqlbinlog的兩行可以將所有數據恢復到上午10:00前一秒鐘。你應檢查日志以確保時間確切。下一節介紹如何實現。 5.9.3.2. 指定恢復位置
也可以不指定日期和時間,而使用mysqlbinlog的選項--start-position和--stop-position來指定日志位置。它們的作用與起止日選項相同,不同的是給出了從日志起的位置號。使用日志位置是更準確的恢復方法,特別是當由于破壞性SQL語句同時發生許多事務的時候。要想確定位置號,可以運行mysqlbinlog尋找執行了不期望的事務的時間范圍,但應將結果重新指向文本文件以便進行檢查。操作方法為:mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00" \????? /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
該命令將在/tmp目錄創建小的文本文件,將顯示執行了錯誤的SQL語句時的SQL語句。你可以用文本編輯器打開該文件,尋找你不要想重復的語句。如果二進制日志中的位置號用于停止和繼續恢復操作,應進行注釋。用log_pos加一個數字來標記位置。使用位置號恢復了以前的備份文件后,你應從命令行輸入下面內容:
mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \??? | mysql -u root -pmypwd mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \??? | mysql -u root -pmypwd \
上面的第1行將恢復到停止位置為止的所有事務。下一行將恢復從給定的起始位置直到二進制日志結束的所有事務。因為mysqlbinlog的輸出包括每個SQL語句記錄之前的SET TIMESTAMP語句,恢復的數據和相關MySQL日志將反應事務執行的原時間。
總結
以上是生活随笔為你收集整理的mysql 定期备份策略,MySQL--3--mysqldump备份策略的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: rust(33)-Rust and We
- 下一篇: React和Vue的Chrome扩展工具