前面寫了篇 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

如此即可還原。