mysql xtrabackup 备份恢复

2015-10-25

目录

一、源码安装

二、全量备份

三、全备恢复

四、增量备份

五、增备恢复

六、自动备份

一、源码安装

#配置yum仓库
cd /etc/yum.repos.d
wget http://mirrors.opencas.cn/epel/epel-release-latest-6.noarch.rpm 
wget http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm
rpm -ivh epel-release-latest-6.noarch.rpm
rpm -ivh rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm
yum clean all && yum makecache
#下载源码包
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/source/tarball/percona-xtrabackup-2.3.2.tar.gz
#解压源码包
tar -zxf percona-xtrabackup-2.3.2.tar.gz -C /usr/local/src/ && cd /usr/local/src/percona-xtrabackup-2.3.2/
#安装基础包
yum -y install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bison libtool ncurses-devel libgcrypt-devel libev-devel libcurl-devel
#配置环境
cmake -DBUILD_CONFIG=xtrabackup_release -DWITH_MAN_PAGES=OFF
#编译安装
make -j4 && make install 
#优化路径
vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/xtrabackup/bin
source ~/.bash_profile
或者
export PATH=/usr/local/xtrabackup/bin:$PATH

二、全量备份

#创建备份用户
mysql> grant usage,reload,lock tables,replication client,create tablespace,super on *.* to 'backup'@'localhost' identified by 'backup';
mysql> grant usage,reload,lock tables,replication client,create tablespace,super on *.* to 'backup'@'%' identified by 'backup';
#创建备份目录
mkdir -p /data/backup/{innobackup,log,mysql,scripts}
mkdir -p /data/backup/innobackup/{full,increment}
#创建测试表
mysql> create database backup_test;
mysql> use backup_test;
mysql> create table test(id int auto_increment not null primary key,name varchar(20));
mysql> insert into test(name) values('test1'),('test2'),('test3'),('test4');
#创建全量备份
innobackupex --user=backup --password=backup /data/backup/innobackup/full/

三、全备恢复

#关闭mysql服务器
service mysqld stop
#备份原始数据目录
mv /data/mysql/3306/ /data/mysql/3306.bak
#创建新数据目录
mkdir -p /data/mysql/3306
#回滚日志
innobackupex --apply-log /data/backup/innobackup/full/2015-10-25_12-36-16/
#恢复全备
innobackupex --copy-back /data/backup/innobackup/full/2015-10-25_12-36-16/
#修改目录权限
chown -R mysql.mysql /data/mysql/3306
#启动mysql服务器
service mysqld start

四、增量备份

#创建基础备份（直接使用全量备份）
innobackupex --user=backup --password=backup /data/backup/innobackup/full/
#添加增量数据
mysql> use backup_test;
mysql> insert into test(name) values('test5'),('test6'),('test7'),('test8');
#创建增量备份
innobackupex --user=backup --password=backup --incremental /data/backup/innobackup/increment/ --incremental-basedir=/data/backup/innobackup/full/2015-10-25_12-36-16/

五、增备恢复

#关闭mysql服务器
service mysqld stop
#备份原始数据目录
mv /data/mysql/3306/ /data/mysql/3306.bak1
#创建新数据目录
mkdir -p /data/mysql/3306
#回滚基础备份日志中已提交数据
innobackupex --apply-log --redo-only /data/backup/innobackup/full/2015-10-25_12-36-16/
#回滚第1个增量备份日志中已提交数据，并合并到基础备份日志
innobackupex --apply-log --redo-only /data/backup/innobackup/full/2015-10-25_12-36-16/ --incremental-dir=/data/backup/innobackup/increment/2015-10-25_14-16-00/
#回滚合并后，基础备份日志中，未提交数据
innobackupex --apply-log /data/backup/innobackup/full/2015-10-25_12-36-16/
#恢复全部备份
innobackupex --copy-back /data/backup/innobackup/full/2015-10-25_12-36-16/
#修改目录权限
chown -R mysql.mysql /data/mysql/3306
#启动mysql服务器
service mysqld start

六、自动备份

#上传备份脚本
/data/backup/scripts/inno_backup.sh
#添加计划任务
crontab -e
#backup mysql by leocen@2015-10-25
50 2 * * * /bin/bash /data/backup/scripts/inno_backup.sh >/dev/null 2>&1
#创建第1个全量备份
innobackupex --user=backup --password=backup /data/backup/innobackup/full/
#执行备份脚本
/bin/bash /data/backup/scripts/inno_backup.sh

 inno_backup.sh

#!/bin/bash
### AUTHOR: Leocen
### DATE: 2015/10/25
### REV: V0.1
source /etc/profile
source /root/.bash_profile

MYSQL_LOG=/data/backup/log/mysql_bk.log
TODAY=`date +%Y%m%d`
target_full_dir=/data/backup/innobackup/full
target_increment_dir=/data/backup/innobackup/increment
mysql_conf=/etc/my.cnf
user=backup
password=backup

# send mail configuration
DATE=`date '+%Y-%m-%d %H:%M:%S'`


echo "-------------------------------------$TODAY-----------------------">$MYSQL_LOG
WEEK_DAILY=`date +%a`

case "$WEEK_DAILY" in
        "Mon")
                export BAK_LEVEL=Increment
                ;;
        "Tue")
                export BAK_LEVEL=Increment
                ;;
        "Wed")
                export BAK_LEVEL=Increment
                ;;
        "Thu")
                export BAK_LEVEL=Increment
                ;;
        "Fri")
                export BAK_LEVEL=Increment
                ;;
        "Sat")
                export BAK_LEVEL=Increment
                ;;
        "Sun")
                export BAK_LEVEL=Full
                ;;
        "*")
                export BAK_LEVEL=error
esac

echo "Today is : $WEEK_DAILY Backup level=$BAK_LEVEL">>$MYSQL_LOG


case "$BAK_LEVEL" in
    "Increment")
                              full_db_dir=`ls $target_full_dir`
                  cd $target_increment_dir
                  rm -rf *
                              echo "Start incremental backup ........"
                              sleep 5
                        innobackupex --defaults-file=$mysql_conf --user=$user --password=$password --incremental-basedir=$target_full_dir/$full_db_dir --incremental $target_increment_dir
                              RSTAT=$?
                  echo "RSTAT=$RSTAT"
                              echo "RSTAT=$RSTAT">>$MYSQL_LOG
                  cd /data/backup/innobackup
                  tar -zcvf $HOSTNAME_"$TODAY"_Inc.tar.gz increment/ 
                mv $HOSTNAME_"$TODAY"_Inc.tar.gz /data/backup/mysql/
                  #/usr/bin/rsync --log-file=/data/backup/log/rsync.log -a /data/backup/innobackup/ivi_"$TODAY"_Inc.tar.gz 10.105.29.161::backup/ivi/ >/dev/null 2>&1
                  #if [ $? = 0 ];then
                #echo "rsync completed!"
                #rm -f /data/backup/innobackup/ivi_"$TODAY"_Inc.tar.gz
                  #else
                #echo "rsync uncompleted!"
                #RSYNCLOG="rsync error!"
                #echo "=====================$RSYNCLOG on `date`====================">>$MYSQL_LOG
                  #fi
                  ;;
        "Full")        
                  cd $target_full_dir
                  rm -rf *
                              echo "Start full backup .........."
                              sleep 5
                        innobackupex --defaults-file=$mysql_conf --user=$user --password=$password $target_full_dir
                              RSTAT=$?
                              echo "RSTAT=$RSTAT"
                              echo "RSTAT=$RSTAT" >>$MYSQL_LOG
                  cd /data/backup/innobackup
                  tar -zcvf $HOSTNAME_"$TODAY"_Full.tar.gz full/
                                mv $HOSTNAME_"$TODAY"_Full.tar.gz /data/backup/mysql/
                  #/usr/bin/rsync --log-file=/data/backup/log/rsync.log -a /data/backup/innobackup/ivi_"$TODAY"_Full.tar.gz 10.105.29.161::backup/ivi/ >/dev/null 2>&1
                  #if [ $? = 0 ];then
                   #echo "rsync completed!"    
                #rm -f /data/backup/innobackup/ivi_"$TODAY"_Full.tar.gz
                  #else
                #echo "rsync uncompleted!"
                #RSYNCLOG="rsync error!"
                #echo "=====================$RSYNCLOG on `date`====================">>$MYSQL_LOG
                  #fi
                              ;;
         "*")
                  exit 99
                              ;;
esac

if [ $RSTAT = 0 ];then
    LOGMSG="backup end successfully!"
    echo $LOGMSG

else
    LOGMSG="backup end in error."
    echo $LOGMSG
#       mailx -s "Mysql database backup was failured." -r arvinzhou@pateo.com.cn -c rich@pateo.com.cn kobezhu@pateo.com.cn,arvinzhou@pateo.com.cn < /data/backup/script/mail
fi

echo "=====================$LOGMSG on `date`====================">>$MYSQL_LOG

参考资料

[1] 唐汉明.深入浅出MySQL 数据库开发、优化与管理维护（第2版）[M].北京：人民邮电出版社，2014

[2] Schwartz.高性能MySQL（第3版）[M].北京：电子工业出版社，2013

[3] Download Percona XtraBackup

[4] Compiling and Installing from Source Code

[5] Percona XtraBackup User Manual 阅读笔记