前面寫了篇 MySQL 複寫(熱備份),這廂就要來篇備份(冷備份)。

MySQL 備份的方法很多,有專門的備份服務,例如 SqlBak、Backup Ninja,也有一些外部或內建的備份工具如下:

  • mysqldump
  • mysqlpump
  • XtraBackup
  • MyDumper

這些工具都各有支持者,而本篇採用的是本家的 mysqlpump,就本人目前的備份需求來說,足矣。

mysqlpump

它是個隨附於 MySQL 的命令列工具,裝完 MySQL 應該就有了。

只要對 MySQL 有基本的認識,使用 mysqldump 應該沒什麼障礙,下面是我的基本使用參數:

$ /usr/bin/mysqlpump \
--user=root \
--password=myrootpassword \
--compress \
--add-drop-database \
--add-drop-table \
--single-transaction \
--watch-progress \
--exclude-databases=mysql \
--result-file=/data/backups/mysql_2022-10-13.sql \

大部分的參數應該都可以望文生義,就不多解釋。

以日期命名備份檔

上面這個命令的問題是,它的備份檔日期是死的,想要用當天日期命名的話可以改成這樣:

$ /usr/bin/mysqlpump \
--user=root \
--password=myrootpassword \
--compress \
--add-drop-database \
--add-drop-table \
--single-transaction \
--watch-progress \
--exclude-databases=mysql \
--result-file=/data/backups/mysql_"$(date --iso-8601='date')".sql \

這裡用到 shell 腳本的變數($)以及 date 來組合出當天的日期當作備份檔名。

隱藏密碼

還有另外一個問題是,密碼是明碼,把這些寫成腳本令人感到不放心。

這個問題 MySQL 也有應對方案,它自帶一組密碼加密工具。

在開始前,先到 MySQL 建立一組專門的備份帳號,就叫它 bkpuser 吧!

用 MySQL 附的密碼加密工具生成加密的帳密檔:

$ mysql_config_editor set --login-path=backup --user=bkpuser --password

其中參數 login-path 只是該組帳密對的識別名稱而已,沒有功能上的意義。

執行之後,需要輸入 bkpuser 的密碼。

再確認一下:

$ mysql_config_editor print --all

應該會輸出以下結果:

[backup]
user = "bkpuser"
password = *****

其中的區塊名 backup 來自前面執行時的 login-path 參數值。

mysql_config_editor 這支程式會生成一個加密過的帳密檔案 ~/.mylogin.cnf,看一下真的是亂碼:

$ cat ~/.myloing.cnf

因為帳密對有識別名稱,所以可以視需要添加其它帳密對,MySQL 的命令列工具幾乎都支援以 login-path 的方式做身份認證。

最後回到備份話題,設好之後就可以用 login-path 替代明碼的密碼啦:

$ /usr/bin/mysqlpump \
--login-path=backup \
--compress \
--add-drop-database \
--add-drop-table \
--single-transaction \
--watch-progress \
--exclude-databases=mysql \
--result-file=/data/backups/mysql_"$(date --iso-8601='date')".sql \

日期也自動產生了,密碼也隱藏了,接著就是讓它每天自動跑啦!

定期備份

把上面的命令變成腳本:

#!/usr/bin/bash

/usr/bin/mysqlpump \
--login-path=backup \
--compress \
--add-drop-database \
--add-drop-table \
--single-transaction \
--watch-progress \
--exclude-databases=mysql \
--result-file=/data/backups/mysql_"$(date --iso-8601='date')".sql \

把腳本文存為 /usr/local/bin/backup-mysql.sh,記得加上可執行權限。

另外那放備份檔的 /data/backups/ 的寫入權限也要先開好。

設定 cron 任務:

$ crontab -e

此處我們用特定帳號的 cron,沒有為什麼。

添加 crontab 如下:

0  1 * * * /usr/local/bin/backup-mysql.sh
10 1 * * * find /data/backup/ -mtime +30 -name 'mysql_*.sql' -delete;

第一行表示每日凌晨一點跑備份腳本。

第二行是每日凌晨1:10刪除三十天以上的舊備份,主要是用 find 指令的比對和刪除功能。簡單搞定,沒有用到什麼複雜的 retention / rotation 工具。

還原

很多人做到備份就停了,但建議還是實際演練一下還原作業,真的碰到時比較不會手忙腳亂。

還原當下典型的情境應該是一台空的 MySQL,回頭看我們的還原腳本有這幾個參數:

  • --add-drop-database
  • --add-drop-table

意思是備份檔的 SQL 內有加入丟掉資料庫、丟掉資料表等指令,所以空的 MySQL 也好,已經有東西的 MySQL 也好,只要有與備份檔同名的資料庫都會被先被拋棄再重建。

還原的指令相當簡單,在 shell 環境執行:

$ mysql -uroot -p < mysql_2022-10-31.sql

如此即可還原。