运维实战:Xtrabackup备份脚本
生产需求
- 如果是星期天进行完全备份;
- 如果是周一执行第一次增量,参考 完全;
- 如果是周二到周六执行第1+N次增量,参考上一次增量;
需要考虑的问题
- 备份文件理应不能与数据文件放在同一块磁盘,备份之后可通过rsync至远端备份服务器;
- 什么时候是业务低谷期;
- 保留多长时间;
- 备份失败记录日志;
脚本思路
- 需要实现自动备份;
- 需要有日志功能;
- 需要能实现主动备份;
备份路径规划
作用 | 路径 |
全量备份数据 | /home/backups/xtra-full |
增量备份数据 | /home/backups/xtra-increment |
错误日志 | /home/backups/mysql-bak.log |
输出日志 | /home/backups/xtra.tmp |
图1. 备份路径规划表
Shell实现
#!/bin/bash
i_home="/home/backups"
i_full="${i_home}/xtra-full"
i_incre="${i_home}/xtra-increment"
err_log="${i_home}/mysql-bak.log"
tmpfile="${i_home}/xtra.tmp"
i_week=`date +%u`
log_time=`date +%F`
bkp_user="root"
myfile="/etc/my.cnf"
bkp_stm="innobackupex --user=$bkp_user"
bkp_pwd=''
[[ ! -d "$i_full" ]]&&mkdir -p "$i_full"
[[ ! -d "$i_incre" ]]&&mkdir -p "$i_incre"
usage(){
echo "USAGE:$0 (all|add|info)"
exit 1
}
use_info(){
cat << EOF
使 用 手 册
all: 全量备份 usage: script_name all
add: 增量备份 usage: script_name add
EOF
}
result(){
local err_time=`date +%F-%H:%m:%S`
res=`tail -1 $tmpfile|grep "completed OK"|wc -l`
if [ $res -eq 0 ];then
echo "**** 备份出错 **** 请查看日志" > "$eror_log"
else
echo "Complete OK!" >>"$err_log"
fi
}
all_backup(){
echo "[完全备份开始] `date` ===================" >> $err_log
${bkp_stm} $i_full 2> $tmpfile
result
cp $myfile "${i_full}"/my-"${log_time}".cnf
}
add_backup(){
basebak=`ls -ltr $i_full|grep "^d.*20[1-9][1-9].*[0-9]$"|tail -1|awk '{print $NF}'`
echo "[增量备份开始] `date` ===================" >> $err_log
${bkp_stm} --incremental $i_incre --incremental-basedir=$i_full/$basebak 2> $tmpfile
result
}
main(){
if [ $# -eq 0 ];then
case $i_week in
7)
all_backup;;
*)
add_backup
esac
else
case "$1" in
--all)
all_backup
;;
--add)
add_backup
;;
--info)
use_info
;;
*)
usage
esac
fi
}
main $1
该脚本并不是特别完美,是之前写的,有几个地方还可以在专业一些。譬如,将所有的变量放置再一个config文件中,通过 source 或 . 进行读取;写入SHOW LOG功能,当等于1的时候,会将结果输出至屏幕;还有一个就是LOG功能,可以将执行的命令追加至日志中;这几点都是方便排查的,想要更详细的输出可以自行加。
执行过程
创建测试数据
MariaDB [(none)]> CREATE DATABASE db1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> USE db1;
Database changed
MariaDB [db1]> CREATE TABLE tb1(id int(10))ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| tb1 |
+---------------+
1 row in set (0.00 sec)
MariaDB [db1]> INSERT INTO tb1 VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [db1]> SELECT * FROM tb1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
执行全备
# 执行全备
[root@www ~]# /server/scripts/Xtrabkp.sh --all
# 查看备份状态
[root@www backups]# cat /home/backups/xtra-full/2019-11-14_16-42-45/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1601525
last_lsn = 1601525
compact = 0
recover_binlog_info = 0
flushed_lsn = 1601525
执行增量
# 有意增加几条数据
MariaDB [db1]> INSERT INTO tb1 VALUES(8),(9),(10);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [db1]> SELECT id FROM tb1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 8 |
| 9 |
| 10 |
+------+
6 rows in set (0.00 sec)
# 执行增量操作
[root@www backups]# /server/scripts/Xtrabkp.sh --add
# 检查增量备份状态
[root@www backups]# cat /home/backups/xtra-increment/2019-11-14_16-47-36/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1601525
to_lsn = 1602669
last_lsn = 1602669
compact = 0
recover_binlog_info = 0
flushed_lsn = 1602669
模拟故障
mysql会在启动的时候会将未提交的事务自动做回滚。再者如果增量后还有数据写入可通过binlog进行恢复;
systemctl stop mariadb
rm -fr /var/lib/mysql/*
# 整理全备
innobackupex --user=root --apply-log --redo-only /home/backups/xtra-full/2019-11-14_16-42-45/
# 将增量还原至主干
innobackupex --user=root --apply-log --redo-only /home/backups/xtra-full/2019-11-14_16-42-45/ --incremental-dir=/home/backups/xtra-increment/2019-11-14_16-47-36/
# 还原数据
innobackupex --user=root --copy-back /home/backups/xtra-full/2019-11-14_16-42-45/
chown -R mysql. /var/lib/mysql/
systemctl start mariadb
# 检测数据
MariaDB [(none)]> USE db1;
Database changed
MariaDB [db1]> SELECT id FROM tb1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 8 |
| 9 |
| 10 |
+------+
6 rows in set (0.00 sec)