如何實現 MySQL 資料庫的增量備份以減少備份空間耗用

如何實現 MySQL 資料庫的增量備份以減少備份空間耗用

我們有幾個 MySQL 資料庫非常大，資料庫都將近 1GB 之多，所以每次在做完整備份時都非常耗時也非常耗費硬碟空間，因此研究出 MySQL 進行增量備份的方法，以下為設定過程的心得筆記。

首先必須設定 my.cnf 設定檔

Linux 平台

/etc/mysql/my.cnf

Windows 平台

C:\Program Files\MySQL\MySQL Server 5.0\my.ini

開啟後要找到 [mysqld] 區段，並加上 log_bin 參數指定 Binary Log 的儲存位置 ( 類似 "交易記錄檔" )

Linux 平台

[mysqld]



log_bin = /var/log/mysql/mysql-bin.log

Windows 平台

[mysqld]

log_bin="E:/MySQL/log/mysql-bin.log"

設定完成後將 MySQL 重新啟動，接著在指定的目錄下就會開始紀錄所有資料庫的交易紀錄，檔名如下：

mysql-bin.000001

接著建議立即做一次完整備份，需注意以下指令的參數跟一般做完整備份時不太一樣：

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases -r MySQL.Full.sql

備份完成後，你會在 Binary Log 目錄下發現多出一個 Binary Log 檔，如下：

mysql-bin.000001
mysql-bin.000002

這時的 mysql-bin.000002 檔案就是你完整備份過後的「增量備份」記錄檔。

若日後資料庫需要還原時，就可以透過以下指令依序還原資料庫：

mysql < MySQL.Full.sql
mysqlbinlog mysql-bin.000002 | mysql
mysqlbinlog mysql-bin.000003 | mysql
mysqlbinlog mysql-bin.000004 | mysql
mysqlbinlog mysql-bin.000005 | mysql

如果想要一天產生一個「增量備份」檔，可以透過以下指令。執行以下指令後，會讓現有的交易記錄停止紀錄，並產生新的交易記錄檔(Binary Log)：

mysql -e "FLUSH LOGS"

若要刪除舊的交易記錄檔，可透過以下指令指定特定時間前的交易記錄檔自動刪除：

mysql -e "PURGE BINARY LOGS BEFORE '2009-07-24 00:00:00';"

注意：建議不要手動刪除交易記錄檔，透過指令刪除是比較保險的方式。

熟悉這些概念後就可以寫程式自動化所有備份作業，建議可以透過批次檔或 Powershell 進行自動化。

相關的原理與說明請參考 [相關連結] 的參考文件。

相關連結

* MySQL 5.1 Reference Manuel - 5.2. MySQL Server Logs
* MySQL 5.1 Reference Manuel - 5.2.4. The Binary Log
* MySQL 5.1 Reference Manuel - 4.6.7. mysqlbinlog — Utility for Processing Binary Log Files
* MySQL 5.1 Reference Manuel - Chapter 6. Backup and Recovery
* MySQL 5.1 Reference Manuel - 6.2. Example Backup and Recovery Strategy
* MySQL 5.1 Reference Manuel - 6.2.1. Backup Policy
* MySQL 5.1 Reference Manuel - 6.2.2. Using Backups for Recovery
* MySQL 5.1 Reference Manuel - 16.1.3.4. Binary Log Options and Variables
* MySQL 5.1 Reference Manuel - 12.6.1.1. PURGE BINARY LOGS Syntax
* MySQL 5.1 Reference Manuel - 12.5.5.2. SHOW BINARY LOGS Syntax
* 如何設定 MySQL 監控所有 AP 對資料庫的查詢