往日微忆
文章目录

话说现在开源系统下 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 [email protected] 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日备份的文件被保留了下来。不过有点纳闷,有时分别备份的两个数据库备份文件数还不一样。