MySQL全量备份和增量备份

MySQL目前是市面上比较流程的关系型数据库,很多人使用MySQL里的数据真的安全吗?如果发生以下问题的话:

  • MySQL数据目录所在磁盘损坏
  • MySQL服务器发生故障
  • 人为操作失误导致删除了一些数据
  • 黑客入侵导致数据被攻陷等问题

这个时候如果没有备份数据库中的数据,造成的影响将是非常严重的,但是我们备份数据不能每天手动的去全量导出,如果放假忘记了或者啥啥啥的,咋整?所以还是要让备份交给服务器去做调度。

这里推荐的备份工具是innobackupex,首先要安装它,步骤如下(centos):

官网:https://www.percona.com/downloads/Percona-XtraBackup-2.4/

1
yum -y install percona-xtrabackup

如果安装失败,那么就更改一下yum源,然后再执行安装命令

1
2
3
4
5
6
> # 备份源
> mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
>
> # 更新阿里云源
> wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
>

在安装的过程中,如果出现依赖报错,那么就安装一下:

1
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

完整的命令过程如下:

1
2
3
4
5
6
7
8
9
10
11
# 备份源
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup

# 更新阿里云源
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

# 安装依赖
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

# 安装备份工具
yum -y install percona-xtrabackup

测试是否安装成功,执行一下命令innobackupex --help,出现以下内容即为安装成功

image-20210114004925713

备份工具已经准备好了,那么我们就开始编写备份脚本(因为我们备份是一个逻辑流程,不是一条命令能解决的,这里就不特地讲解备份命令了,在下方的脚本中进行相关的解释)

  1. 全量备份

首先看一下全量备份,也就是将整个数据库的所有数据(包括库、表、视图、表数据等),脚本的主要逻辑是这样的:

  • 声明数据库账号信息和数据留存时间等基础信息
  • 动态创建备份文件临时存储目录
  • 执行备份命令开始数据备份
  • 校验备份结果是否超过,如果成功则将最新备份的文件目录写入到指定文件中(用于动态增量备份)
  • 如果失败,则给备份管理人员发送告警提醒邮件
  • 筛选数据备份存储目录中指定日期之前的备份记录,然后删除已经过期的目录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
#!/bin/bash
# set -e

# 1. 声明数据库账号信息和数据留存时间等基础信息
user=root # 数据库用户名
password=rXH?py2 # 数据库密码
LEAVE_DAY_AGO=$(date -d "-30 day" +'%s')

# 2. 动态创建备份文件临时存储目录
[ -d /app/backupdb ]|| mkdir -p /app/backupdb &>>/app/backupdb/backupdb_log.log
[ -d /data/backup/db ]|| mkdir -p /data/backup/db &>>/app/backupdb/backupdb_log.log
echo "start copying ..." &>>/app/backupdb/backupdb_log.log

# 3. 执行备份命令开始数据备份
/usr/bin/innobackupex --user="$user" --password="$password" /data/backup/db/ > /app/backupdb/backupdb_log.log 2>&1
# 4. 校验备份结果是否超过
if [ "$?" != "0" ]; then
# 4.1 备份失败,则给备份管理人员发送告警提醒邮件

# <from_email>:发送邮件
# <receiver_email>:多个邮箱以,分隔
# <smtp_server>:邮箱服务器
# <send_account>:邮件发送账户名
# <send_password>:邮件发送账户密码
/usr/local/bin/sendEmail -f <from_email> -t <receiver_email> -s <smtp_server> -u "数据库备份失败" -o message-charset=utf-8 -xu <send_account> -xp <send_password> -m "数据库备份失败,详情请查看日志文件 /app/backupdb/backupdb_log.log" -o tls=no &>>/app/backupdb/backupdb_log.log
exit 1;
else
# 4.2 备份成功,则将最新备份的文件目录写入到指定文件中(用于动态增量备份)
echo "end copied! ..." &>>/app/backupdb/backupdb_log.log
echo `ls -lt /data/backup/db/ | grep - | head -n 1 |awk '{print $9}'` > /data/backup/last_backup_dir_name
fi;

# 5. 删除${LEAVE_DAY_AGO}天前的备份目录及子目录、文件
path="/data/backup/db"
files=$(ls $path)
for filename in $files
do
file=(${filename:0:10})
if echo $file | grep -Eq "[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}" && date -d $file +%Y%m%d > /dev/null 2>&1;then
echo $filename 'format is right date' &>>/app/backupdb/backupdb_log.log
file_time=$(date -d $file +'%s')

# 校验备份记录是否已过期
if [ $file_time -lt $LEAVE_DAY_AGO ];then
rm -rf $path/$filename*
echo "删除$filename 备份目录" &>>/app/backupdb/backupdb_log.log
else
echo "$filename 为有效期内数据库备份" &>>/app/backupdb/backupdb_log.log
fi;
else
echo "日期格式不正确:" $filename &>>/app/backupdb/backupdb_log.log
fi;
done
  1. 增量备份

上面讲了全量备份,增量备份是依据于上一次全量备份的记录去做的增量,这里最重要的就是要获取到上一次备份生成的目录,也就是全量备份中4.2中保存的,具体步骤为:

  • 声明数据库账号信息和数据留存时间等基础信息
  • 动态创建备份文件临时存储目录
  • 搜索并获取上一次全量备份的目录地址
  • 执行备份命令开始数据备份
  • 校验备份结果是否超过,如果成功则将最新备份的文件目录写入到指定文件中(用于动态增量备份)
  • 如果失败,则给备份管理人员发送告警提醒邮件
  • 筛选数据备份存储目录中指定日期之前的备份记录,然后删除已经过期的目录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
#!/bin/bash
# set -e

# 1. 声明数据库账号信息和数据留存时间等基础信息
user=root # 数据库用户名
password=rXH?py2 # 数据库密码
LEAVE_DAY_AGO=$(date -d "-30 day" +'%s')

# 2. 动态创建备份文件临时存储目录
[ -d /app/backupdb ]|| mkdir -p /app/backupdb &>>/app/backupdb/backupdb_increment_log.log
[ -d /data/backup/db/increment ]|| mkdir -p /data/backup/db/increment &>>/app/backupdb/backupdb_increment_log.log
echo "increment start copying ..." &>>/app/backupdb/backupdb_increment_log.log

# 3. 搜索并获取上一次全量备份的目录地址,这里也就是上一次全量备份输出的文件中的内容
last_backup_dir_name=`echo /data/backup/last_backup_dir_name`

# 4. 执行备份命令开始数据备份
/usr/bin/innobackupex --user="$user" --password="$password" --incremental /data/backup/db/increment/ --incremental-basedir /data/backup/db/"$last_backup_dir_name" > /app/backupdb/backupdb_increment_log.log 2>&1

# 5. 校验备份结果是否超过
if [ "$?" != "0" ]; then
# 4.1 备份失败,则给备份管理人员发送告警提醒邮件

# <from_email>:发送邮件
# <receiver_email>:多个邮箱以,分隔
# <smtp_server>:邮箱服务器
# <send_account>:邮件发送账户名
# <send_password>:邮件发送账户密码
/usr/local/bin/sendEmail -f <from_email> -t <receiver_email> -s <smtp_server> -u "数据库增量备份失败" -o message-charset=utf-8 -xu <send_account> -xp <send_password> -m "数据库增量备份失败,详情请查看日志文件 /app/backupdb/backupdb_increment_log.log" -o tls=no &>>/app/backupdb/backupdb_increment_log.log
exit 1;
else
# 4.2 备份成功,则将最新备份的文件目录写入到指定文件中(用于动态增量备份)
echo "increment end copied! ..." &>>/app/backupdb/backupdb_increment_log.log
fi;

# 5. 删除30天前的备份目录及子目录、文件
path="/data/backup/db/increment"
files=$(ls $path)
for filename in $files
do
file=(${filename:0:10})
if echo $file | grep -Eq "[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}" && date -d $file +%Y%m%d > /dev/null 2>&1;then
echo $filename 'format is right date' &>>/app/backupdb/backupdb_increment_log.log
file_time=$(date -d $file +'%s')
# 校验备份记录是否已过期
if [ $file_time -lt $LEAVE_DAY_AGO ];then
rm -rf $path/$filename*
echo "删除$filename 备份目录" &>>/app/backupdb/backupdb_increment_log.log
else
echo "$filename 为有效期内数据库备份" &>>/app/backupdb/backupdb_increment_log.log
fi;
else
echo "日期格式不正确:" $filename &>>/app/backupdb/backupdb_increment_log.log
fi;
done

到目前,MySQL全量和增量备份脚本都已经编写完成了,接下来就是将脚本的执行设置为系统调度corntab中,由crontab进行调度,设置crontab的命令为:crontab -e

1
2
3
4
5
#每周一凌晨4点执行全量备份
0 4 * * 1 sh /app/backupdb/db_backup.sh >> /app/backupdb/backupdb_log.log

#每天凌晨2点执行增量备份
0 2 * * * sh /app/backupdb/db_backup_increment.sh >> /app/backupdb/backupdb_increment_log.log

既然讲了备份,那么就需要讲一下恢复,如何使用备份的数据进行全量恢复呢?下次再说