xtrabackup备份Mysql

安装

官网地址
官网2.4版本下载列表
此篇文章使用的版本下载

主库和从库都需要安装

[root@sit-mysql1 local]# cd /usr/local
[root@sit-mysql1 local]# tar -xf percona-xtrabackup-2.4.20-Linux-x86_64.el6.tar.gz 
[root@sit-mysql1 local]# mv percona-xtrabackup-2.4.20-Linux-x86_64 xtrabackup

主从同步：关闭从库的slave
主主同步：关闭主库和从库的slave
使用命令 stop slave;

备份主库

备份脚本

#!/bin/sh
backup_dir=/home/backup/full_backup
defaults_file=/home/mysql/conf/mysql.cnf
user=root
pass=password
socket=/home/mysql/mysql.sock
backup_data_dir=`date +%F`
logfile=/home/backup/full_backup/log/full_backup_`date +%F`.log
innobackupex=/usr/local/xtrabackup/bin/innobackupex
#source /etc/profile
if [ ! -d ${backup_dir} ]; then
mkdir -p ${backup_dir}
mkdir -p ${backup_dir}/log
chown -R mysql:mysql ${backup_dir}
fi
#begin full backup
echo "`date  +"%Y-%m-%d %H:%M"` Begin innobackupex full backup" >> $logfile
${innobackupex} --defaults-file=${defaults_file} --user=${user} --password=${pass} --socket=${socket} --tmpdir=${backup_dir} --no-timestamp --slave-info ${backup_dir}/${backup_data_dir} 1>>${logfile} 2>&1
BakFlag=`tail -n 10 ${logfile} | grep "completed OK" | grep -v prints | wc -l`
if [ ${BakFlag} -eq 0 ];then
        echo "`date +"%Y-%m-%d %H:%M"` backup failed!">>${logfile}
        exit 1
elif [ ${BakFlag} -eq 1 ];then
        echo "`date +"%Y-%m-%d %H:%M"` backup success!">>${logfile}
fi

传输备份文件到从库

在从库执行命令

scp -r root@主库ip:/home/backup/full_backup /home/backup/full_backup

从库执行恢复脚本

删除从库的data目录和日志(binlog,undolog,redolog)目录里的文件

最好是备份，删除文件后要注意存储目录的权限是否属于mysql用户

执行下面的脚本文件

#!/bin/sh
backup_dir=/home/backup/full_backup
defaults_file=/opt/mysql/db_mysql/conf/mysql.cnf
user=root
pass=password
socket=/opt/mysql/db_mysql/mysql.sock
backup_data_dir=`date +%F`
logfile=/home/backup/full_backup/log/full_backup_`date +%F`.log
innobackupex=/usr/local/xtrabackup/bin/innobackupex
#source /etc/profile


#begin to recover
echo "===============================================" >> ${logfile}
echo "`date +"%Y-%m-%d %H:%M"`  Begin to Recover ..." >> ${logfile}
${innobackupex} --defaults-file=${defaults_file} --apply-log ${backup_dir}/${backup_data_dir} 1>>${logfile} 2>&1
BakFlag=`tail -n 10 ${logfile} | grep "completed OK" | grep -v prints | wc -l`
if [ ${BakFlag} -gt 0 ];then
        echo "`date +"%Y-%m-%d %H:%M"`  Recover is compeleted !" >> ${logfile}
        echo "===============================================" >> ${logfile}
		${innobackupex} --defaults-file=${defaults_file} --copy-back ${backup_dir}/${backup_data_dir}
        #begin tar backup data directory
        cd $backup_dir
        tar -zcvf ${backup_data_dir}.tar.gz ${backup_data_dir} >/dev/null 2>&1
        if [ $? -ne 0 ];then
                echo "`date +"%Y-%m-%d %H:%M"` tar backup data directory error!" >> ${logfile}
                exit 1 
        else   
                echo "`date +"%Y-%m-%d %H:%M"` tar backup data directory compeleted !" >> ${logfile}
                rm -rf ${backup_data_dir}
        fi     
else
        echo "`date +"%Y-%m-%d %H:%M"`  Recover is failed !" >> ${logfile}
        exit 1
fi

#delete expired backup for 14 days ago
cd $backup_dir
find . -mtime +14 -exec rm -rf {} \;
#delete expired log for 60 days ago
cd ${backup_dir}/log
find . -mtime +60 -exec rm -rf {} \;

这一步执行完从库上就已经有了与主库相同的数据了

主从同步

此时如果要进行主从同步

change master to master_host='主ip',master_user='复制用户',master_password='复制用户密码',master_auto_position=1;
start slave;
show slave status\G;

如果看到IO和SQL线程均为YES，说明主从配置成功
如果出错，查看是否有以下情况

• 从库存在之前的binlog文件，这种情况stop slave; reset slave; start slave;