mysqlpump是MySQL官方提供的指令工具，用來把MySQL內儲存的資料輸出成SQL格式的文字檔；mysql是MySQL官方提供的SQL Shell客戶端，可以用來執行SQL敘述。將mysqlpump和mysql搭配使用就可以備份與還原MySQL。
安裝mysqlpump和mysql
基於Debian的Linux發行版可以使用以下指令來安裝：
紅帽系的Linux發行版可以使用以下指令來安裝：
Windows可以下載MySQL Community Server來取得mysqlpump.exe和mysql.exe：
備份 MySQL
以下的mysqlpump指令，可以將整個MySQL備份為單一個SQL檔案：
mysqlpump指令的-h參數可以接上MySQL伺服器的IP位址或是主機名稱，-P參數可以接上MySQL伺服器的連接埠。如果不使用-h參數，則預設會去連結本機上的MySQL伺服器。-u和-p參數則是要登入MySQL的使用者帳號和密碼；-C參數是用來壓縮連線，減少流量。--add-drop-database參數是在輸出CREATE DATABASE敘述之前加上DROP DATABASE敘述，這樣在還原的時後才不會有建立資料庫時發現資料庫已經存在的錯誤；--add-drop-user參數是在輸出CREATE USER敘述之前加上DROP USER敘述，這樣在還原的時後才不會有建立使用者時發現使用者已經存在的錯誤。至於$(date +%F_%T)Bash Shell的語法，可以取得當下的本地時間，例如：2022-07-07_18:00:00。
不過筆者比較建議在使用mysqlpump指令時加上-B參數來限定要備份哪些資料庫(資料庫名稱用空格隔開，不能加引號來括住所有資料庫的名稱)，不然會備份到mysql、sys、information_schema、performance_schema這幾個MySQL預設的資料庫，還原的時候將會出問題。
mysqlpump指令備份特定幾個MySQL資料庫的指令格式如下：
所以我們想要備份除了MySQL預設的資料庫之外的所有資料庫，就只能一個一個填進去嗎？不，我們可以利用SHOW databases;這個SQL敘述來查詢MySQL使用者有權限讀取的所有資料庫名稱，然後再排除掉MySQL預設的那些資料庫的名稱即可。所以在使用mysqlpump指令時，可以搭配mysql指令來執行SHOW databases;，查出資料庫名稱，用法如下：
mysqlpump [-h<HOST> [-P<PORT>]] -u<USERNAME> -p<PASSWORD> -C --add-drop-database --add-drop-user -B ${DATABASES} > $(date +%F_%T).sql
mysql指令的-s參數可以讓輸出的結果表，不用多餘的字元去繪製框線，而是單純以一行行(line)的文字，每行表示一個列(row)，且每個列用TAB字元隔開行(column)來印出表格。-e參數可以直接執行後面接的SQL敘述，不會進SQL Shell。grep指令的-E參數是要用正規表示式來查找文字，-v參數是要得到反過來的結果，即查到的不顯示，查不到的才顯示。此處的tr指令是用來將換行字元轉成空格字元。
使用Zstd來壓縮備份檔
在Bash Shell中可以使用管線(pipe)將mysqlpump指令的輸出導給zstd指令的輸入，使得輸出的備份能夠被Zstd壓縮。指令用法如下：
基於Debian的Linux發行版可以使用以下指令來安裝Zstd：
使用RAR來壓縮備份檔
在Bash Shell中可以使用管線將mysqlpump指令的輸出導給rar指令的輸入，使得輸出的備份能夠被RAR壓縮，也能夠製作修復RAR壓縮檔的還原記錄，降低硬碟出問題而使得備份檔損壞的機率。指令用法如下：
rar指令的-m5參數可以讓RAR使用最大壓縮，壓縮時長並不會像Zstd這樣顯著增長，推薦使用。-si參數可以讓RAR從標準輸入(stdin)讀取資料，也可以接上一個路徑來設定這份資料在RAR內要存放在哪，但沒有必要。-rr1參數可以設定1%的復原記錄，如果想要安全一點也可以設為更高的數值。
RAR的安裝方法可以參考這篇文章：
自動化備份腳本
搭配SSH Tunnel，我們可以在任意一台能夠SSH登入進MongoDB所在的主機的電腦上進行自動化備份。如果您不熟悉SSH Tunnel的話可以先參考這篇文章：
以下筆者撰寫的Bash腳本：
#!/bin/bash
set -o pipefail
SSH_SERVER=${SSH_SERVER:-"magiclen@192.168.56.101"}
BACKUP_NAME=${BACKUP_NAME:-"mysql-backup"}
BACKUP_NAME="$(basename "${BACKUP_NAME}")"
DB_NAMES=${DB_NAMES:-""} # separated by spaces
BASIC_BACKUP_DIRECTORY=${BASIC_BACKUP_DIRECTORY:-"/var/backups/magic"}
BASIC_BACKUP_DIRECTORY="$(realpath -m "${BASIC_BACKUP_DIRECTORY}")"
BACKUP_DIRECTORY=${BACKUP_DIRECTORY:-"${BASIC_BACKUP_DIRECTORY}/${BACKUP_NAME}"}
BACKUP_DIRECTORY="$(realpath -m "${BACKUP_DIRECTORY}")"
SSH_SOCKET_PATH=${SSH_SOCKET_PATH:-"/tmp/ssh-tunnel-${BACKUP_NAME}"}
SSH_SOCKET_PATH="$(realpath -m "${SSH_SOCKET_PATH}")"
OLD_BACKUP_AGE=${OLD_BACKUP_AGE:-"15"} # days
if [[ ! "${OLD_BACKUP_AGE}" =~ ^[0-9]+$ ]]; then
echo "OLD_BACKUP_AGE should be a positive integer." >&2
exit 1
fi
CLIENT_SERVICE_IP=${CLIENT_SERVICE_IP:-"127.0.0.1"}
CLIENT_SERVICE_PORT=${CLIENT_SERVICE_PORT:-"3306"}
SERVER_SERVICE_IP=${SERVER_SERVICE_IP:-"127.0.0.1"}
SERVER_SERVICE_PORT=${SERVER_SERVICE_PORT:-"3306"}
MYSQL_USERNAME=${MYSQL_USERNAME:-"root"}
MYSQL_PASSWORD=${MYSQL_PASSWORD:-"12345678"}
if ss -tan | tr -s ' ' | cut -d " " -f 4 | grep -m1 "${CLIENT_SERVICE_IP}":"${CLIENT_SERVICE_PORT}" > /dev/null ; then
echo "${CLIENT_SERVICE_IP}:${CLIENT_SERVICE_PORT} is used." >&2
exit 2
fi
(rm -f "${SSH_SOCKET_PATH}" && ssh -fNM -S "${SSH_SOCKET_PATH}" -L "${CLIENT_SERVICE_IP}:${CLIENT_SERVICE_PORT}:${SERVER_SERVICE_IP}:${SERVER_SERVICE_PORT}" "${SSH_SERVER}") || (echo "Unable to create the SSH tunnel!" >&2 && exit 3)
if [ ! -d "${BACKUP_DIRECTORY}" ]; then
if [ -e "${BACKUP_DIRECTORY}" ]; then
echo "${BACKUP_DIRECTORY} exists and it is not a directory!" >&2
exit 4
fi
mkdir -p "${BACKUP_DIRECTORY}" || exit 5
echo "The directory, ${BACKUP_DIRECTORY} has been created."
fi
DATABASES=
if [ -n "${DB_NAMES}" ]; then
DATABASES="${DB_NAMES}"
else
if ! DATABASES="-B $(mysql -h"${CLIENT_SERVICE_IP}" -P"${CLIENT_SERVICE_PORT}" -u"${MYSQL_USERNAME}" -p"${MYSQL_PASSWORD}" -C -s -e "SHOW databases;" | grep -E -v '^mysql$|^sys$|^information_schema$|^performance_schema$' | tr '\n' ' ')"; then
ssh -S "${SSH_SOCKET_PATH}" -O exit "${SSH_SERVER}"
exit 6
fi
fi
FILE_NAME="$(date +%F_%T)${OPLOG_FILENAME}.sql.zst"
if mysqlpump -h"${CLIENT_SERVICE_IP}" -P"${CLIENT_SERVICE_PORT}" -u"${MYSQL_USERNAME}" -p"${MYSQL_PASSWORD}" -C --add-drop-database --add-drop-user -B ${DATABASES} | zstd > "${BACKUP_DIRECTORY}/${FILE_NAME}"; then
echo "Backup successfully!"
echo "-----Delete the following old backup files-----"
find "${BACKUP_DIRECTORY}" -name "*.sql.*" -mtime "+${OLD_BACKUP_AGE}" -type f -delete -print
echo "----------"
fi
ssh -S "${SSH_SOCKET_PATH}" -O exit "${SSH_SERVER}"
上面的腳本可以透過設定環境變數而適應不同的需求，列表如下：
SSH_SERVER：MySQL所在的SSH伺服器，或是其它可以連接得到MySQL的SSH伺服器。您應該要自行改掉腳本內的預設值。BACKUP_NAME：此備份的識別名稱，用來分類備份檔。預設值是mysql-backup。每個備份應使用不同的名稱。DB_NAMES：要備份的資料庫名稱。如果不設定的話就是除了MySQL預設的資料庫之外的所有的資料庫。BASIC_BACKUP_DIRECTORY：備份檔存放的基礎目錄。預設是放在/var/backups/magic，如果執行這個腳本的使用者不是root的話，則可能會沒有權限建立出這個目錄，此時可以先手動建好這個目錄，並將擁有者和群組改為要執行這個腳本的使用者和群組。BACKUP_DIRECTORY：此備份要存放備份檔的目錄。預設是放在${BASIC_BACKUP_DIRECTORY}/${BACKUP_NAME}。不太需要去設定這個。SSH_SOCKET_PATH：SSH control master socket檔案的位置。預設的路徑放在/tmp/ssh-tunnel-${BACKUP_NAME}。不太需要去設定這個。OLD_BACKUP_AGE：設定備份成功後要刪除幾天之前的備份檔。預設是15。CLIENT_SERVICE_IP：設定SSH Tunnel的客戶端IP。預設是127.0.0.1。不太需要去設定這個。CLIENT_SERVICE_PORT：設定SSH Tunnel的客戶端連接埠。預設是3306。建議替每個備份設定不同的連接埠，避免重疊。SERVER_SERVICE_IP：設定SSH Tunnel的伺服器端IP。預設是127.0.0.1。SERVER_SERVICE_PORT：設定SSH Tunnel的伺服器端連接埠。預設是3306。MYSQL_USERNAME：MySQL的使用者帳號。預設是root。MYSQL_PASSWORD：MySQL的使用者密碼。
由於腳本會使用Zstd做壓縮，所以腳本的執行環境要能夠使用zstd指令才行。如果您想要用其它的壓縮方式，就自行修改腳本吧！
此腳本假定執行這個腳本的使用者的SSH公鑰有被加至SSH伺服器的authorized_keys檔案中。如果您還沒做這樣的設定，請參考這篇文章來進行：
筆者習慣將這個腳本放在Linux作業系統的/usr/local/bin目錄，並設定crontab讓電腦自動去備份。如下圖：
還原 MySQL
以下的mysql指令，可以利用mysqlpump指令產生出來的備份檔，來還原MongoDB：
解壓縮Zstd備份檔
在Bash Shell中可以使用管線將zstd指令的輸出導給mysql指令的輸入，使得被Zstd壓縮的備份能夠再被Zstd解壓縮。指令用法如下：
解壓縮RAR備份檔
在Bash Shell中可以使用管線將rar指令的輸出導給mysql指令的輸入，使得被RAR壓縮的備份能夠再被RAR解壓縮。指令用法如下：