mysqlpump 是 MySQL 官方提供的指令工具,用来把 MySQL 内保存的数据输出成 SQL 格式的文字档;mysql 是 MySQL 官方提供的 SQL Shell 客户端,可以用来运行 SQL 叙述。将 mysqlpump 和 mysql 搭配使用就可以备份与还原 MySQL。

安装 mysqlpump 和 mysql

基于 Debian 的 Linux 发行版可以使用以下指令来安装:

sudo apt install mysql-client-core-8.0

红帽系的 Linux 发行版可以使用以下指令来安装:

sudo dnf install mysql

Windows 可以下载 MySQL Community Server 来取得 mysqlpump.exe 和 mysql.exe:

备份 MySQL

以下的 mysqlpump 指令,可以将整个 MySQL 备份为单一个 SQL 文件:

mysqlpump [-h<HOST> [-P<PORT>]] -u<USERNAME> -p<PASSWORD> -C --add-drop-database --add-drop-user > $(date +%F_%T).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 这个 MySQL 预设的数据库,还原的时候将会出问题。

mysqlpump 指令备份特定几个 MySQL 数据库的指令格式如下:

mysqlpump [-h<HOST> [-P<PORT>]] -u<USERNAME> -p<PASSWORD> -C --add-drop-database --add-drop-user -B <DB_NAME1> <DB_NAME2> > $(date +%F_%T).sql

所以我们想要备份除了 MySQL 预设的数据库之外的所有数据库,就只能一个一个填进去吗?不,我们可以利用 SHOW databases; 这个 SQL 叙述来查找 MySQL 用户有权限读取的所有数据库名称,然后再排除掉 MySQL 预设的那些数据库的名称即可。所以在使用 mysqlpump 指令时,可以搭配 mysql 指令来运行 SHOW databases;,查出数据库名称,用法如下:

DATABASES="$(mysql [-h<HOST> [-P<PORT>]] -u<USERNAME> -p<PASSWORD> -C -s -e "SHOW databases;" | grep -E -v '^mysql$|^sys$|^information_schema$|^performance_schema$' | tr '\n' ' ')"

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 指令是用来将换行字符转成空格字符。

以 mysqlpump 备份 MySQL

使用 Zstd 来压缩备份档

在 Bash Shell 中可以使用管线(pipe)将 mysqlpump 指令的输出导给 zstd 指令的输入,使得输出的备份能够被 Zstd 压缩。指令用法如下:

mysqlpump [-h<HOST> [-P<PORT>]] -u<USERNAME> -p<PASSWORD> -C --add-drop-database --add-drop-user -B <DB_NAME1> <DB_NAME2> | zstd > $(date +%F_%T).sql.zst

基于 Debian 的 Linux 发行版可以使用以下指令来安装 Zstd:

sudo apt install zstd
使用 RAR 来压缩备份档

在 Bash Shell 中可以使用管线将 mysqlpump 指令的输出导给 rar 指令的输入,使得输出的备份能够被 RAR 压缩,也能够制作修复 RAR 压缩档的还原记录,降低硬盘出问题而使得备份档损坏的概率。指令用法如下:

mysqlpump [-h<HOST> [-P<PORT>]] -u<USERNAME> -p<PASSWORD> -C --add-drop-database --add-drop-user -B <DB_NAME1> <DB_NAME2> | rar a [-m5] -si -rr1 $(date +%F_%T).sql.rar

rar 指令的 -m5 参数可以让 RAR 使用最大压缩,压缩时长并不会像 Zstd 这样显著增长,推荐使用。-si 参数可以让 RAR 从标准输入(stdin)读取数据,也可以接上一个路径来设置这份数据在 RAR 内要存放在哪,但没有必要。-rr1 参数可以设置 1% 的复原记录,如果想要安全一点也可以设为更高的数值。

RAR 的安装方法可以参考这篇文章:

自动化备份脚本

搭配 SSH Tunnel,我们可以在任意一台能够 SSH 登录进 MySQL 所在的主机的电脑上进行自动化备份。如果您不熟悉 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).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}"

上面的脚本可以透过设置环境变量而适应不同的需求,列表如下:

  1. SSH_SERVER:MySQL 所在的 SSH 服务器,或是其它可以连接得到 MySQL 的 SSH 服务器。您应该要自行改掉脚本内的默认值。
  2. BACKUP_NAME:此备份的识别名称,用来分类备份档。默认值是 mysql-backup。每个备份应使用不同的名称。
  3. DB_NAMES:要备份的数据库名称。如果不设置的话就是除了 MySQL 预设的数据库之外的所有的数据库。
  4. BASIC_BACKUP_DIRECTORY:备份档存放的基础目录。预设是放在 /var/backups/magic,如果运行这个脚本的用户不是 root 的话,则可能会没有权限创建出这个目录,此时可以先手动建好这个目录,并将拥有者和群组改为要运行这个脚本的用户和群组。
  5. BACKUP_DIRECTORY:此备份要存放备份档的目录。预设是放在 ${BASIC_BACKUP_DIRECTORY}/${BACKUP_NAME}。不太需要去设置这个。
  6. SSH_SOCKET_PATH:SSH control master socket 文件的位置。预设的路径放在 /tmp/ssh-tunnel-${BACKUP_NAME}。不太需要去设置这个。
  7. OLD_BACKUP_AGE:设置备份成功后要删除几天之前的备份档。预设是 15。
  8. CLIENT_SERVICE_IP:设置 SSH Tunnel 的客户端 IP。预设是 127.0.0.1。不太需要去设置这个。
  9. CLIENT_SERVICE_PORT:设置 SSH Tunnel 的客户端连接端口。预设是 3306。建议替每个备份设置不同的连接端口,避免重叠。
  10. SERVER_SERVICE_IP:设置 SSH Tunnel 的服务器端 IP。预设是 127.0.0.1。
  11. SERVER_SERVICE_PORT:设置 SSH Tunnel 的服务器端连接端口。预设是 3306。
  12. MYSQL_USERNAME:MySQL 的用户帐号。预设是 root。
  13. MYSQL_PASSWORD:MySQL 的用户密码。

由于脚本会使用 Zstd 做压缩,所以脚本的运行环境要能够使用 zstd 指令才行。如果您想要用其它的压缩方式,就自行修改脚本吧!

此脚本假定运行这个脚本的用户的 SSH 公钥有被加至 SSH 服务器的 authorized_keys 文件中。如果您还没做这样的设置,请参考这篇文章来进行:

笔者习惯将这个脚本放在 Linux 操作系统的 /usr/local/bin 目录,并设置 crontab 让电脑自动去备份。如下图:

以 crontab 设置 MySQL 自动备份

还原 MySQL

以下的 mysql 指令,可以利用 mysqlpump 指令产生出来的备份档,来还原 MySQL:

mysql [-h<HOST> [-P<PORT>]] -u<USERNAME> -p<PASSWORD> -C < /path/to/backup-file

以 mysql 指令还原备份档

解压缩 Zstd 备份档

在 Bash Shell 中可以使用管线将 zstd 指令的输出导给 mysql 指令的输入,使得被 Zstd 压缩的备份能够再被 Zstd 解压缩。指令用法如下:

zstd -d < /path/to/zst-backup-file | mysql [-h<HOST> [-P<PORT>]] -u<USERNAME> -p<PASSWORD> -C
解压缩 RAR 备份档

在 Bash Shell 中可以使用管线将 rar 指令的输出导给 mysql 指令的输入,使得被 RAR 压缩的备份能够再被 RAR 解压缩。指令用法如下:

rar p /path/to/rar-backup-file | mysql [-h<HOST> [-P<PORT>]] -u<USERNAME> -p<PASSWORD> -C