问题描述：
由于随着生产环境的运行，MySQL中存储的数据越来越多，备份的时间也是越来越长，极大的影响了客户的体验，在客户以及同事的要求下，决定更改MySQL的备份方式，从之前的每天全量备份改为周末进行一次全量备份，工作日为增量备份。
问题的解决：
数据库中主流的增量备份工具有mysqldump和xtrabackup，与mysqldumop相比较，xtrabackup属于物理备份，备份数据快，备份过程不会打断正在执行的事物，能够实现自动备份校验，而且备份的时候不会增加服务器的负载。综合考虑采用xtrabackup来实现增量备份
一）xtrabackup的安装
1）执行
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
安装yum仓库
2）执行
yum list | grep percona
查看仓库是否成功安装
3）执行
percona-release enable-only tools release
使仓库中的工具生效
4）执行
yum install percona-xtrabackup-24
安装percona xtrabackup
5）创建备份的Mysql用户，并完成授权
mysql> CREATE USER 'xtraback'@'%' IDENTIFIED BY '<YOUR PASSWD>';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'xtraback'@'%';
mysql> FLUSH PRIVILEGES;
二）xtrabackup备份的相关命令
innobackupex --user=<User> --password=<Password> --port <Port> /data/apps_data/mysql-backup/
全量备份
innobackupex --user=<User> --password=<Password> --incremental <Incremental Dir> --incremental-basedir=<Full Backup Dir>
增量备份
innobackupex --apply-log --redo-only <Full Backup Dir>
在恢复增量备份时，需要先对主库执行该操作，然后再将增量备份的拷贝到主库里面去
innobackupex --apply-log --redo-only <Full Back Dic> --incremental-dir=<Incremental Dic>
增量备份加入到全量备份中（不能对最后一个增量备份使用）
innobackupex --apply-log <Full Backup Dir> --incremental-dir=<Incremental Dir>
增量备份加入到全量备份中（只能够对最后一个增量备份使用）
xtrabackup --copy-back --target-dir=<Full Backup Dir>
备份恢复（数据库需要停止，且数据目录需要清空）
三）脚本实现
#!/bin/bash
weekday=`date +%w` # 0 is sunday, 1-6 mon-sat
backup_day=`date +%m-%d`
backup_time=`date +%m-%d-%H-%M-%S`
backup_month=`date +%Y-%m`
backup_dir=/data/apps_data/mysql-backups
user=<Your user>
password=<Your password>
port=3307
log_dir=/data/apps_data/mysql-backups/logs
log_file=`date +%F`.log
index_dic=/data/apps_data/mysql-backups/.index
index_file=/data/apps_data/mysql-backups/.index/index.txt
test ! -d $backup_dir && mkdir -p $backup_dir;
test ! -d $index_dic && mkdir -p $index_dic;
test ! -d $log_dir && mkdir -p $log_dir;
function full_backup()
{
test ! -d $backup_dir/$backup_month/${backup_day}_full && mkdir -p $backup_dir/$backup_month/${backup_day}_full;
echo "start full backup"
# echo "$backup_dir/$backup_month/$backup_day_full" > $index_file
innobackupex --user=$user --password=$password --port $port --no-timestamp $backup_dir/$backup_month/${backup_day}_full >> $log_dir/$log_file 2>&1
if [ $? == 0 ];
then
grep "innodb_to_lsn" $backup_dir/$backup_month/${backup_day}_full/xtrabackup_info|awk -F "=" {'print $2'}|cut -d " " -f 2 > $index_file
cd /data/apps_data/mysql-backups/$backup_month && gzip ${backup_day}_full
else
echo "full backup fail" >> $log_dir/$log_file 2>&1
rm -rf $backup_dir/$backup_month/${backup_day}_full
exit
fi
}
function incremental_backup()
{
# test ! -r $index_file && echo "Cant't Scan Full Backup, Now Start Full Backup" > $log_dir/$log_file 2>&1 && full_backup && exit;
if [ ! -r $index_file ];
then
echo "Cant't Scan Full Backup, Now Start Full Backup"
echo "Cant't Scan Full Backup, Now Start Full Backup" >> $log_dir/$log_file 2>&1
full_backup
exit
fi
# incremental_lsn=`cat $index_file`
if [ -s "$index_file" ]
then
incremental_lsn=`cat $index_file`
else
echo "index file is none, now start full backup"
full_backup
exit
fi
test ! -d $backup_dir/$backup_month/$backup_day/inc_$backup_time && mkdir -p $backup_dir/$backup_month/$backup_day/inc_$backup_time
innobackupex --user=$user --password=$password --port $port --incremental $backup_dir/$backup_month/$backup_day/inc_$backup_time --no-timestamp --incremental-lsn=$incremental_lsn >> $log_dir/$log_file 2>&1
if [ $? == 0 ];
then
grep "innodb_to_lsn" $backup_dir/$backup_month/$backup_day/inc_$backup_time/xtrabackup_info|awk -F "=" {'print $2'}|cut -d " " -f 2 > $index_file
cd $backup_dir/$backup_month/$backup_day && gzip inc_$backup_time
# echo "$backup_dir/$backup_month/$backup_day_full" > $index_file
else
echo "incremental backup fail" >> $log_dir/$log_file 2>&1
rm -rf $backup_dir/$backup_month/$backup_day/inc_$backup_time
exit
fi
# echo "$backup_dir/$backup_month/$backup_day/inc_$backup_time" > $index_file
}
function get_backup_type()
{
if [ "$weekday" == 0 ];
then
full_backup
else
echo "start incremental backup"
incremental_backup
fi
}
function main()
{
get_backup_type
}
main
注意：
1）在上线的时候可以加上--host来指定备份某一台主机的数据库
2）如果调用该脚本的用户是普通用户，例如centos，会导致不能备份，因为centos用户没有访问mysql文件的权限，执行usermod -a -G mysql centos将centos用户加入到mysql组中，即可解决
3）当该脚本以增量备份模式运行的时候，需要加载上一次备份的index文件，所以不支持集群部署，在需要集群部署时，需要在另外两台编写格外的脚本去增量备份的主机执行增量备份的脚本
