# 如何實現 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](http://dev.mysql.com/doc/refman/5.1/en/server-logs.html)
-   [MySQL 5.1 Reference Manuel - 5.2.4. The Binary Log](http://dev.mysql.com/doc/refman/5.1/en/binary-log.html)
-   [MySQL 5.1 Reference Manuel - 4.6.7. mysqlbinlog — Utility for Processing Binary Log Files](http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html)
-   [MySQL 5.1 Reference Manuel - Chapter 6. Backup and Recovery](http://dev.mysql.com/doc/refman/5.1/en/backup-and-recovery.html)
-   [MySQL 5.1 Reference Manuel - 6.2. Example Backup and Recovery Strategy](http://dev.mysql.com/doc/refman/5.1/en/backup-strategy-example.html)
-   [MySQL 5.1 Reference Manuel - 6.2.1. Backup Policy](http://dev.mysql.com/doc/refman/5.1/en/backup-policy.html)
-   [MySQL 5.1 Reference Manuel - 6.2.2. Using Backups for Recovery](http://dev.mysql.com/doc/refman/5.1/en/recovery-from-backups.html)
-   [MySQL 5.1 Reference Manuel - 16.1.3.4. Binary Log Options and Variables](http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#option_mysqld_log-bin)
-   [MySQL 5.1 Reference Manuel - 12.6.1.1. PURGE BINARY LOGS Syntax](http://dev.mysql.com/doc/refman/5.1/en/purge-binary-logs.html)
-   [MySQL 5.1 Reference Manuel - 12.5.5.2. SHOW BINARY LOGS Syntax](http://dev.mysql.com/doc/refman/5.1/en/show-binary-logs.html)
-   [如何設定 MySQL 監控所有 AP 對資料庫的查詢](/post/2008/09/24/Monitor-All-SQL-Queries-in-MySQL "如何設定 MySQL 監控所有 AP 對資料庫的查詢")