mysql xtrabackup备份恢复实操案例

最新推荐文章于 2024-07-08 16:34:54 发布

蚁库

最新推荐文章于 2024-07-08 16:34:54 发布

阅读量128

点赞数

文章标签： mysql linux 数据库

本文链接：https://blog.csdn.net/weixin_52805903/article/details/126153119

版权

本文详细介绍了Percona的Xtrabackup工具，作为MySQL数据库的高效热备份解决方案。内容涵盖Xtrabackup的优势、备份原理，以及从源码安装、全量和增量备份到恢复的完整流程，还提供了自动备份脚本的编写和自动备份策略的制定。

摘要由CSDN通过智能技术生成

Xtrabackup简介

Xtrabackup是由percona开源的免费数据库热备份软件，它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）；mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合。
Xtrabackup优点
1）备份速度快，物理备份可靠

2）备份过程不会打断正在执行的事务（无需锁表）

3）能够基于压缩等功能节约磁盘空间和流量

4）自动备份校验

5）还原速度快

6）可以流传将备份传输到另外一台机器上

7）在不增加服务器负载的情况备份数据

Xtrabackup备份原理
备份开始时首先会开启一个后台检测进程，实时检测mysql redo的变化，一旦发现有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中，之后复制innodb的数据文件一系统表空间文件ibdatax，复制结束后，将执行flush tables with readlock,然后复制.frm MYI MYD等文件，最后执行unlock tables,最终停止xtrabackup_log。

一、xtrabackup源码安装

#配置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

六、编写备份脚本（自动备份：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

七、制定自动备份策略

#上传备份脚本
/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