1、全量备份脚本(full_mysql_backup.sh)
#!/bin/bash
BACK_DIR='/home/mysql_back/xtrabackup'
MYSQL_CONF='/etc/my.cnf'
MYSQL_SOCKET='/var/lib/mysql/mysql.sock'
PASSWORD='Runsdata@2017#7v8'
CHECK_DATE=`date +%Y%m%d`
DIR_TIME=`date +%Y%m%d-%H%M%S`
dirNew(){
mkdir -p ${BACK_DIR}/backup_now
echo "backup_now"
}
backupdb(){
dirNum=`dirNew`
oldFilePath=`tail -1 ${BACK_DIR}/${dirNum}/file_binlogs.index 2>/dev/null`
oldFile=`echo ${oldFilePath}|awk -F\/ '{print $NF}'`
newFilePath="${BACK_DIR}/${dirNum}/full_${DIR_TIME}"
binlogPath="${BACK_DIR}/${dirNum}/file_binlogs.index"
execProc="${BACK_DIR}/${dirNum}/backup_exec.proc"
## 备份目录下剩余未压缩文件
cd ${BACK_DIR}/${dirNum}/
tar -jcvf ${oldFile}.tar.bz ${oldFile} && rm -rf ${oldFile}
## 移动上星期备份文件
mv ${BACK_DIR}/backup_now ${BACK_DIR}/${CHECK_DATE} 2>/dev/null
## 创建备份目录
dirNew
## mysql8.0以下使用方法
innobackupex --defaults-file=${MYSQL_CONF} --user=root --password=${PASSWORD} -S ${MYSQL_SOCKET} --slave-info --no-timestamp ${newFilePath}
## mysql8.0的使用方法
#xtrabackup --defaults-file=${MYSQL_CONF} -uroot -p${PASSWORD} -S ${MYSQL_SOCKET} --slave-info --backup --target-dir=${newFilePath}
if [ $? -ne 0 ]
then
echo "${DIR_TIME} check: innobackupex ${newFilePath} not successful"
else
echo "${newFilePath}" >> ${binlogPath}
echo "innobackupex --defaults-file=${MYSQL_CONF} --user=root --password=${PASSWORD} -S ${MYSQL_SOCKET} --slave-info --no-timestamp ${newFilePath}" >> ${execProc}
find $BACK_DIR -maxdepth 1 -type d -mtime +10|grep -Ev "script|xtrabackup$"|xargs rm -rf
fi
}
backupdb
2、增量备份脚本(incr_mysql_backup.sh)
#!/bin/bash
BACK_DIR='/home/mysql_back/xtrabackup'
MYSQL_CONF='/etc/my.cnf'
MYSQL_SOCKET='/var/lib/mysql/mysql.sock'
PASSWORD='Runsdata@2017#7v8'
CHECK_DATE=`date +%Y%m%d`
DIR_TIME=`date +%Y%m%d-%H%M%S`
dirNew(){
echo "backup_now"
}
backupdb(){
dirNum=`dirNew`
week=`date +%w`
oldFilePath=`tail -1 ${BACK_DIR}/${dirNum}/file_binlogs.index 2>/dev/null`
oldFile=`echo ${oldFilePath}|awk -F\/ '{print $NF}'`
newFilePath="${BACK_DIR}/${dirNum}/incr_${DIR_TIME}"
newFile=`echo ${newFilePath}|awk -F\/ '{print $NF}'`
binlogPath="${BACK_DIR}/${dirNum}/file_binlogs.index"
execProc="${BACK_DIR}/${dirNum}/backup_exec.proc"
if [ ! "${oldFilePath}" ]
then
echo "${DIR_TIME} check: ${oldFilePath} dir not found or empty"
else
## mysql8.0以下使用方法
innobackupex --defaults-file=${MYSQL_CONF} --user=root --password=${PASSWORD} -S ${MYSQL_SOCKET} --slave-info --no-timestamp --incremental --incremental-basedir=${oldFilePath} ${newFilePath}
## mysql8.0的使用方法
#xtrabackup --defaults-file=${MYSQL_CONF} -uroot -p${PASSWORD} -S ${MYSQL_SOCKET} --slave-info --backup --target-dir=${newFilePath} --incremental-basedir=${oldFilePath}
if [ $? -ne 0 ]
then
echo "${DIR_TIME} check: innobackupex ${newFilePath} not successful"
else
echo "${newFilePath}" >> ${binlogPath}
echo "innobackupex --defaults-file=${MYSQL_CONF} --user=root --password=${PASSWORD} -S ${MYSQL_SOCKET} --slave-info --no-timestamp --incremental --incremental-basedir=${oldFilePath} ${newFilePath}" >> ${execProc}
cd ${BACK_DIR}/${dirNum}/
tar -jcvf ${oldFile}.tar.bz ${oldFile} && rm -rf ${oldFile}
fi
fi
}
backupdb
3、增量备份恢复教程
全量备份目录
/home/mysql_back/xtrabackup/backup_now/full_20200311-100649/
增量备份目录
/home/mysql_back/xtrabackup/backup_now/incr_20200311-102611/
/home/mysql_back/xtrabackup/backup_now/incr_20200311-120001/
3.1、第一次执行全量备份恢复
innobackupex --defaults-file= /etc/my.cnf --apply-log --redo-only /home/mysql_back/xtrabackup/backup_now/full_20200311-100649/ ## --redo-only 如果还有增量备份,需要指定该参数
执行增量备份恢复
innobackupex --defaults-file= /etc/my.cnf --apply-log --redo-only /home/mysql_back/xtrabackup/backup_now/full_20200311-100649/ --incremental-dir=/home/mysql_back/xtrabackup/backup_now/incr_20200311-102611/
3.2、最后一次增量备份恢复(不需要添加--redo-only参数)
innobackupex --defaults-file= /etc/my.cnf --apply-log /home/mysql_back/xtrabackup/backup_now/full_20200311-100649/ --incremental-dir=/home/mysql_back/xtrabackup/backup_now/incr_20200311-120001/
3.3、恢复数据到mysql数据目录
innobackupex --defaults-file=/etc/my.cnf --copy-back /home/mysql_back/xtrabackup/backup_now/full_20200311-100649/
3.4、修改目录权限
chown -R mysql.mysql /data/mysql/data
4、重启mysql数据库
systemctl start mysqld
5、恢复mysql主从关系
5.1、检查备份数据执行的binglog点
xtrabackup_binlog_info 当从库变为主库时可以使用该binlog日志文件
xtrabackup_slave_info 从库恢复时使用slave日志文件
cat /home/mysql_back/xtrabackup/backup_now/incr_20200311-120001/xtrabackup_binlog_info
5.2、在从库上执行恢复主从关系
stop slave;
reset slave;
set global gtid_purged='06e341f3-6818-11e9-b6c9-fa163e52c27c:1-194993684:195593106-270963638,7868b28b-733e-11e9-8c50-fa163efbbdeb:1-237,92acc932-2149-11ea-bbc6-fa163efbbdeb:1-8292673,e562aa0e-725e-11ea-bf71-fa163e52c27c:1-4';
CHANGE MASTER TO MASTER_HOST='192.168.123.30', MASTER_USER='root', MASTER_PASSWORD='', MASTER_PORT=3306, MASTER_AUTO_POSITION=1;
start slave;