利用 mysqldump 自动备份 MariaDB 数据库脚本
By 辞峡烟斜
话说现在开源系统下 WordPress 数据库都已经从 MySQL 转已经平滑移到了 MariaDB 。虽然目前阶段他们本质上并没有多少区别，基本命令都可以通用，但本文还是跟随趋势，称呼 MySQL 为 MariaDB 。年初，有一次因 VPS 磁盘空间问题导致我的 WordPress 博客 MariaDB 数据库连接出错，造成数据库损坏，N 番修复未果，无奈选择较早前手工备份的数据库版本恢复，损失了一两篇文章数据，还是通过 Google 快照才把文字内容摘取了下来。自从此次“深刻”教训后，誓要实现 MariaDB 数据库的自动备份，防患于未然。于是趁工作之余，断断续续，直到8月才形成了今天的自动备份脚本，记录一下。
MariaDB 数据库备份有逻辑备份和物理备份之分，还有热备、冷备之分等，请自行搜索解释。本文用 mysqldump 逻辑备份数据库，另文用 Xtrabackup 物理备份数据库。
本文代码主要参考：http://www.cnblogs.com/batsing/p/mysql-bak.html
一、备份思路
首先，创建专门用于数据库备份的数据库用户。再编写代码指定需要备份的数据库，用 mysqldump 命令备份压缩指定数据库为备份文件，最后设置 cron 定时任务，指定固定时间执行脚本备份。脚本里已经包含代码，用于删除超过一定天数的备份文件，防止备份文件日积月累越来越多。
二、实现步骤
（一）创建用于备份恢复的用户并赋予权限
为了安全起见，最后创建一个专门用于备份恢复数据库的用户，这里假定创建用户为 backupuser ，密码为 bei123 :
mysql> grant lock tables,reload,process,replication client,super,select,event,trigger,show view on *.* to backupuser@localhost identified by 'bei123';
（二）设置相关变量
# 数据库信息
DB_USER="backupuser"
DB_PASS="bei123"
DB_HOST="localhost"
DB_NAME=(db1 db2) #需要备份的数据库名称，这里假定为 db1 和 db2 ，注意中间用空格隔开
# 其他设置
BIN_DIR="/usr/bin" # mysqldump命令执行路径
BACK_DIR="/home/lily/MysqlDumpBkDa" #备份目录，这里设为/home/lily/MysqlDumpBkDa
DATE=`date +%Y%m%d%H%M%S` #显示备份时间，格式为20180808122556
（三）执行命令备份数据库
# 备份所有指定数据库
for eachdb in ${DB_NAME[@]}
#也可以写成for eachdb in ${DB_NAME[*]}
do
#$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $eachdb > $BCK_DIR/db_$eachdb_$DATE.sql
$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST -B ${eachdb} | gzip > $BACK_DIR/db_${eachdb}_$DATE.sql.gz
done
（四）删除超过一定天数的备份文件
这里，我是设置删除5天之前的备份文件，并保留每月1号产生的备份文件，用于手动删除
find $BACK_DIR/* -regextype "posix-extended" -not -regex ".*[0-9]{6}01[0-9]{6}\.sql\.gz$" -mtime +5 -exec rm {} \;
最后，整个代码文件内容如下，代码文件设为 mysqldump_autobak.sh 保存于/home/lily/ 下：
#!/bin/bash
# 数据库信息
DB_USER="backupuser"
DB_PASS="bei123"
DB_HOST="localhost"
DB_NAME=(db1 db2) #需要备份的数据库名称，这里假定为 db1 和 db2 ，注意中间用空格隔开
# 其他设置
BIN_DIR="/usr/bin" # mysqldump命令执行路径
BACK_DIR="/home/lily/MysqlDumpBkDa" #备份目录，这里设为/home/lily/MysqlDumpBkDa
DATE=`date +%Y%m%d%H%M%S` #显示备份时间，格式为20180808122556
# 备份所有指定数据库
for eachdb in ${DB_NAME[@]}
#也可以写成for eachdb in ${DB_NAME[*]}
do
#$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $eachdb > $BCK_DIR/db_$eachdb_$DATE.sql
$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST -B ${eachdb} | gzip > $BACK_DIR/db_${eachdb}_$DATE.sql.gz
done
# 删除5天之前的备份文件，但保留日期为1号的文件（用于手动删除）
find $BACK_DIR/* -regextype "posix-extended" -not -regex ".*[0-9]{6}01[0-9]{6}\.sql\.gz$" -mtime +5 -exec rm {} \;
三、设置定时任务
（一）编辑定时任务列表
crontab -e
（二）在最后插入定时任务
我这里设为晚上23点50分执行代码
50 23 * * * /home/lily/mysqldump_autobak.sh
（三）查看任务是否创建成功
crontab -l
四、备份效果
下面是我 vps 上实际执行结果显示：
从中可以看出，8月1日备份的文件被保留了下来。不过有点纳闷，有时分别备份的两个数据库备份文件数还不一样。
