1、背景
我们有一台业务数据库一直都只做了主从,虽然一定程度上解决了读写性能问题,但是这个是有风险的,比如某同学删除主库数据,从库也会跟着删除,所以及时的备份还是很有必要的。计划是每天全量备份两次,为什么不增量备份呢?两个原因:
(1)增量备份在多库多表的场景下备份策略变得复杂,而且不易验证业务正确性
(2)增量备份在恢复的时候不能做到快速恢复,这在线上场景下是致命的,业务恢复分秒必争
2、技术选型
业界常用的备份工具有两个,一个是 MySQL 自带的管理工具 mysqldump,它能完成一般的数据库管理备份恢复工作,但是性能和功能比较弱,专业性欠缺。另外一个是 MySQL咨询公司Percona提供的 innobackupex,功能强大,性能优秀,是专业级的数据库备份恢复工具,基本算是业界应用最广泛的备份恢复工具了。
Percona XtraBackup(简称PXB)是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具,支持 MySQl(Oracle)、Percona Server 和 MariaDB,并且开源。
Xtrabackup有两个主要的工具:xtrabackup、innobackupex,现在xtrabackup版本升级到了2.4.4,相比之前的2.1有了比较大的变化:innobackupex
功能全部集成到 xtrabackup
里面,只有一个 binary,另外为了使用上的兼容考虑,innobackupex
作为 xtrabackup
的一个软链,即xtrabackup现在支持非Innodb表备份,并且Innobackupex在下一版本中移除,建议通过xtrabackup替换innobackupex。
(1)xtrabackup是C/C++编译的二进制文件,只能备份InnoDB和XtraDB两种数据表,而不能备份MyISAM数据表;
(2)innobackupex则封装了xtrabackup,是一个perl脚本封装,所以能同时备份处理innodb和myisam,但在处理myisam时需要加一个读锁;
整个备份过程如下图:
3、常用参数
--defaults-file:指明服务器的配置文件,此参数必须作为innobackupex的第一个参数,否则报错
--host:指明连接数据库的主机
--user:指明执行数据库备份的用户名
--password:指明执行备份的密码
--backup:指明为备份,此参数可以忽略
--apply-log:重做日志
--copy-back:执行数据恢复
--slave-info:备份从库的show slave status信息,仅用于在备份从库时使用
--no-lock:不锁表,仅适用于存储引擎为innodb,并且不在乎备份位置点时使用
4、自动化备份恢复脚本
4.1 安装:
sudo yum -y install perl perl-devel libaio libaio-devel
sudo yum -y install perl-DBI perl-DBD-MySQL perl-TermReadKey perl-devel perl-Time-HiRes
sudo yum -y install libev.x86_64
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm
4.2 备份:
#全量备份 MySQL
set -x
echo "===========backup start========="`date -Iseconds`
IP=`/usr/sbin/ss |head |awk '{print $4}'|grep -vE '127.0.0.1|0.0.0.0'|awk -F: '/\./{print $1;exit}'`
bak_date=`date +"%Y-%m-%d_%H"`
bak_date_short=`date +"%Y-%m-%d"`
mysql_datadir=`cat /etc/my.cnf|grep datadir|awk -F= '{print $2}'`
#bak_base_dir=`echo $mysql_datadir|awk -F/ 'NF=NF-1{OFS="/";print $0}'`
bak_base_dir=/opt/data/bak_mysql
bak_dir=$bak_base_dir/${bak_date}__${IP}
FINISH_FLAG_FILE=$bak_dir/Finish.flag
## 全量备份
/usr/bin/innobackupex --defaults-file=/etc/my.cnf \
--backup \
--datadir=$mysql_datadir \
--user=$username \
--password=${passwords[$IP]} \
--host=127.0.0.1 \
--port=3306 \
--kill-long-queries-timeout=30 \
--kill-long-query-type=select \
--lock-wait-timeout=60 \
--lock-wait-query-type=update \
--no-timestamp \
$bak_dir
[[ $? -eq 0 ]] || { echo "Backup ERROR!"; sendSMS $telList "$IP Backup MySQL Fail!"; exit 110; }
bak_rsync(){
## TODO:
# 可以改成自动创建目录
#传输备份到远端机器:灾备
rsync -avz $bak_dir root@$remote_bak_ip:$bak_base_dir
#传输结束标记文件,对端以此检测文件传输完成
touch $FINISH_FLAG_FILE
rsync -avz $FINISH_FLAG_FILE root@$remote_bak_ip:$bak_dir
}
##(1) kinit 免密登录任意 kerberos 授权机器
kinit user <<<$kerberos_passwd
##(2)备份数据库
#backup_mysql_full
##(3)传输备份到远程机器
bak_rsync
echo "===========backup finish========="`date -Iseconds`
4.3 恢复:
# 全量恢复 MySQL
set -x
echo "===========recovery start========="`date -Iseconds`
IP=`/usr/sbin/ss |head |awk '{print $4}'|grep -vE '127.0.0.1|0.0.0.0'|awk -F: '/\./{print $1;exit}'`
username=root
bak_date=`date +"%Y-%m-%d_%H"`
bak_date_short=`date +"%Y-%m-%d"`
mysql_datadir=/opt/data/mysql
bak_base_dir=/opt/data/bak_mysql
bak_dir=$bak_base_dir/${bak_date}__${recovery_server_ip}
FINISH_FLAG_FILE=$bak_dir/Finish.flag
# 超时 N 秒
TIMEOUT=7200
kinit user <<<$kerberos_passwd
bak_recovery(){
# TODO:
# 此处应该先备份,但是磁盘不够,先不做,直接删除
rm -rf ${mysql_datadir:?var is empty will exit}
mkdir -p $mysql_datadir
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log $bak_dir
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --move-back $bak_dir
chown -R mysql.mysql $mysql_datadir
}
execute_recovery(){
service mysqld stop
bak_recovery
service mysqld start
result=`mysql -uusername -ppasswd -NB -e 'select 1'`
[[ $result -eq 1 ]] && rm -rf ${bak_dir:?var is empty will exit} || { echo "Recovery ERROR!"; sendSMS $telList "$IP Recovery MySQL Fail!"; }
}
while [[ $c -lt $TIMEOUT ]]
do
((c++))
mkdir -p $bak_dir
rsync -avz root@$bak_server_ip:$FINISH_FLAG_FILE $FINISH_FLAG_FILE 2>/dev/null
[[ $? -ne 0 ]] && continue
rsync -avz root@$bak_server_ip:$bak_dir $bak_base_dir
[[ $? -eq 0 ]] && execute_recovery && echo "SUCCESS!" && break
sleep 1
done
# 如果文件在 TIMEOUT 时间内还没有恢复成功,那就发个告警吧~
[[ $c -ge $TIMEOUT ]] && echo `date +'%F %T'`"Recovery Timeout ..." && sendSMS $telList "$IP Recovery retry $c times, and it's Exception!"
echo "===========recovery finish========="`date -Iseconds`
Refer:
[1] xtrabackup 使用说明(续)
http://www.cnblogs.com/zhoujinyi/p/5893333.html
[2] 【mysql】使用xtrabackup在线增量备份及恢复数据库
https://www.cnblogs.com/chenpingzhao/p/4905310.html
[3] MySQL之——基于mysqldump全量备份还原
https://blog.csdn.net/l1028386804/article/details/78118980
[4] pt-online-schema-change utf8mb4 错误解决方法
http://www.ttlsa.com/mysql/pt-online-schema-change-utf8mb4-error-solution/
[5] Download Percona XtraBackup
https://www.percona.com/downloads/XtraBackup/LATEST/