centos环境下使用percona-xtrabackup对mysql5.6数据库innodb和myisam进行快速备份及恢复
centos环境下使用percona-xtrabackup对mysql5.6数据库innodb和myisam进行快速备份及恢复
有时候我们会碰到这样的业务场景：
1.将大的数据库恢复到本地进行业务测试
2.数据库性能碰到了瓶颈需要做基于主从复制的读写分离
这样我们就需要对源数据库进行备份，并恢复到目标服务器中
如果源数据库全部是myisam数据库那我们可以直接通过拷贝的方式进行数据恢复，这种情况速度取决于磁盘的IO能力，是比较快速的一种方式
如果源数据库是innodb引擎或者myisam都有，此时
主要的思路主要有两个：
1.通过mysql自带的工具mysqldump进行逻辑备份和恢复，这样可以节省磁盘空间，但是速度很慢(如果数据量很大，这样执行sql语句的方式恢复将不可接受)
2.通过第三方工具percona-xtrabackup进行恢复
Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品。 Xtrabackup有两个主要的工具：xtrabackup、innobackupex xtrabackup只能备份InnoDB和XtraDB两种数据表，支持在线热备份，不会锁表 innobackupex则封装了xtrabackup，同时可以备份MyISAM数据表，如果你的数据库里有innodb和myisam存储引擎，只能使用innobackupex备份
准备好服务器
1.在原服务器和目标服务器上都需要安装percona-xtrabackup(建议mysql通过源码安装否则percona依赖的mysql-lib版本可能和高版本mysql冲突)
下载好rpm包：
percona-toolkit-2.2.4-1.noarch.rpm
percona-xtrabackup-2.1.8-733.rhel6.x86_64.rpm
# yum安装，自动解决依赖关系
yum localinstall percona-*.rpm
2.完整备份制定数据库
备份指定库yunva
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password=root --databases="yunva" /data/ >> /data/backup.log 2>&1
3.执行数据库事物一致性
innobackupex --apply-log /data/2016-11-11_16-03-44
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
如果执行正确，其最后输出的几行信息通常如下：
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
161110 15:39:34 InnoDB: Starting shutdown...
161110 15:39:38 InnoDB: Shutdown completed; log sequence number 7308344844
161110 15:39:38 innobackupex: completed OK!
4.恢复yunva数据库：
停用目标机器3.17的mysql服务并删除ibdata1、ib_logfile0、ib_logfile1，使用备份替换
service mysqld stop
# rm -f /data/mydata/ibdata1
# rm -f /data/mydata/ib_logfile0
# rm -f /data/mydata/ib_logfile1
将数据拷贝到要恢复的服务器3.17上：
chown -R mysql.mysql /data/mydata
重新启动mysql服务器，测试验证数据发现数目对得上
select count(1) from tbl_user_app_20160427
8693860
二、全量恢复实验：
1.全量备份192.168.3.16数据库，生成了2016-11-10_15-14-22目录
innobackupex --user=root --password=root /data
2.将事物提交
innobackupex --apply-log 2016-11-10_15-14-22
3.拷贝到3.17中
# scp -r 2016-11-10_15-14-22 192.168.3.17:/data
4.在3.17中修改mysql的权限
chown -R mysq.mysql /data/2016-11-10_15-14-22
并且修改3.17的my.cnf文件
datadir = /data/2016-11-10_15-14-22
重新启动mysql服务(如果先前没有及时关闭，需要kill -9 pid强行结束mysql服务)
测试是ok的，和3.16的数据库结构完全一致
说明复制了一份完全一样的mysql到目标服务器(包括mysql的user/权限等数据表)
查询了3.16源数据库和3.17目标数据库yunva中的tbl_user_app_20160427表，数据条目一致
mysql> select count(1) from tbl_user_app_20160427;
+----------+
| count(1) |
+----------+
| 8693860 |
+----------+
1 row in set (8 min 48.85 sec)
-------------------------------------------------------------------------------
以下是摘抄的其他网文，通过脚本自动备份和恢复的方式，供参考：
安装配置Xtrabackup
先看看如何安装Xtrabackup,最简单的安装方式是使用RPM包，不过想使用源代码方式安装的话，其安装方式有点古怪，因为它采用的在MySQL源代码上打补丁构建的方式安装的。这里使用二进制包的安装方式，相对比较灵活。
Shell> mkdir /usr/local/xtrabackup
Shell> tar -zxvf xtrabackup-1.6.tar.gz –C /usr/local/xtrabackup
Shell>cd /usr/local/xtrabackup/bin
Shell>ln –s innobackupex-1.5.1 innobackupex
配置环境变量：
Shell>export PATH=$PATH:/usr/local/xtrabackup/bin
希望永久生效的话，可以加到
echo "export PATH=$PATH:/usr/local/xtrabackup/bin" >> /etc/profile
source /etc/profile
修改mysql配置文件:
Shell> vi /etc/my.cnf
添加或修改：datadir =/usr/local/mysql/var（数据库目录）
特别注意：default_table_type = InnoDB（必须改，否则进行增量备份的时候不成功）
经过几天的测试，文档终于可以与大家分享了。使用Xtrabackup能够非常快速地备份与恢复mysql数据库，是mysql dba的首选。以下是线上用的简单脚本
#!/bin/bash
USER=root
PASSWORD=123456
date > /data/scripts/backup.log
echo "begin backup-------------------------------" >> /data/scripts/backup.log
find /data/mysql/backups -mtime +7 |xargs rm -rf
/usr/bin/innobackupex --defaults-file=/opt/mysql5/my.cnf --user=$USER --password=$PASSWORD --databases="test1 test2" /data/mysql/backups >> /data/scripts/backup.log 2>&1
echo "end backup-------------------------------" >> /data/scripts/backup.log
date >> /data/scripts/backup.log
mail -s "backup Passport report" "localhost@localhost" < /data/scripts/backup.log
exit 0
以下是恢复脚本：
#!/bin/bash
USER=root
PASSWORD=123456
/etc/init.d/mysql stop
/usr/bin/innobackupex-1.5.1 --apply-log --defaults-file=/opt/mysql5/my.cnf --user=$USER --password=$PASSWORD /data/mysql/backups/passportdb/`date +%Y-%m-%d`
rm -rf /opt/mysql5/var/test1
rm -rf /opt/mysql5/var/test2
rm -f /opt/mysql5/var/ibdata1
rm -f /opt/mysql5/var/ib_logfile0
rm -f /opt/mysql5/var/ib_logfile1
cd /data/mysql/backups
tar zcvf `date +%Y-%m-%d`.tgz `date +%Y-%m-%d`
rm -rf `date -d -30day +%Y-%m-%d`.tgz
cp -r /data/mysql/backups/`date +%Y-%m-%d`/ib* /opt/mysql5/var/
cp -r /data/mysql/backups/`date +%Y-%m-%d`/test1 /opt/mysql5/var/
cp -r /data/mysql/backups/`date +%Y-%m-%d`/test2 /opt/mysql5/var/
rm -rf /data/mysql/backups/`date +%Y-%m-%d`/*
chown -R mysql.mysql /opt/mysql5/var/test1
chown -R mysql.mysql /opt/mysql5/var/test2
chown mysql.mysql /opt/mysql5/var/ib*
/etc/init.d/mysql start
恢复的时候执行完/usr/bin/innobackupex-1.5.1 --apply-log后，直接拷贝就可以了，不用使用/usr/bin/innobackupex-1.5.1 --copy-back命令。如果使用/usr/bin/innobackupex-1.5.1 --copy-back命令后，会报Original data directory is not empty! at /usr/local/xtrabackup/bin/innobackupex line 538.恢复的目录必须为空。经查官网，这是xtrabackup的一个BUG。
innobackupex did not check that MySQL datadir was empty before –copy-back was run. With this bug fix, innobackupex will now error out of the –copy-back operation if the destination is not empty, avoiding potential data loss or a strang combination of a restored backup and previous data. Bug Fixed: #737569 (Valentine Gostev)
********************************************************************
# innobackupex --copy-back /data/2016-11-10_15-14-22
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
innobackupex: Error: Original data directory '/data/mydata' is not empty! at /usr/bin/innobackupex line 2113.
********************************************************************
网上有好多文章恢复使用
/usr/bin/innobackupex-1.5.1 --apply-log 然后
/usr/bin/innobackupex-1.5.1 --copy-back，如果目录非空则会报错
有时候我们会碰到这样的业务场景：
1.将大的数据库恢复到本地进行业务测试
2.数据库性能碰到了瓶颈需要做基于主从复制的读写分离
这样我们就需要对源数据库进行备份，并恢复到目标服务器中
如果源数据库全部是myisam数据库那我们可以直接通过拷贝的方式进行数据恢复，这种情况速度取决于磁盘的IO能力，是比较快速的一种方式
如果源数据库是innodb引擎或者myisam都有，此时
主要的思路主要有两个：
1.通过mysql自带的工具mysqldump进行逻辑备份和恢复，这样可以节省磁盘空间，但是速度很慢(如果数据量很大，这样执行sql语句的方式恢复将不可接受)
2.通过第三方工具percona-xtrabackup进行恢复
Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品。 Xtrabackup有两个主要的工具：xtrabackup、innobackupex xtrabackup只能备份InnoDB和XtraDB两种数据表，支持在线热备份，不会锁表 innobackupex则封装了xtrabackup，同时可以备份MyISAM数据表，如果你的数据库里有innodb和myisam存储引擎，只能使用innobackupex备份
准备好服务器
1.在原服务器和目标服务器上都需要安装percona-xtrabackup(建议mysql通过源码安装否则percona依赖的mysql-lib版本可能和高版本mysql冲突)
下载好rpm包：
percona-toolkit-2.2.4-1.noarch.rpm
percona-xtrabackup-2.1.8-733.rhel6.x86_64.rpm
# yum安装，自动解决依赖关系
yum localinstall percona-*.rpm
2.完整备份制定数据库
备份指定库yunva
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password=root --databases="yunva" /data/ >> /data/backup.log 2>&1
3.执行数据库事物一致性
innobackupex --apply-log /data/2016-11-11_16-03-44
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
如果执行正确，其最后输出的几行信息通常如下：
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
161110 15:39:34 InnoDB: Starting shutdown...
161110 15:39:38 InnoDB: Shutdown completed; log sequence number 7308344844
161110 15:39:38 innobackupex: completed OK!
4.恢复yunva数据库：
停用目标机器3.17的mysql服务并删除ibdata1、ib_logfile0、ib_logfile1，使用备份替换
service mysqld stop
# rm -f /data/mydata/ibdata1
# rm -f /data/mydata/ib_logfile0
# rm -f /data/mydata/ib_logfile1
将数据拷贝到要恢复的服务器3.17上：
scp -r /data/2016-11-11_16-03-44 192.168.3.17:/data/
mv /data/2016-11-11_16-03-44/ibdata1 /data/mydata/
mv /data/2016-11-11_16-03-44/ib_logfile0 /data/mydata/
mv /data/2016-11-11_16-03-44/ib_logfile1 /data/mydata
mv /data/2016-11-11_16-03-44/yunva /data/mydata
chown -R mysql.mysql /data/mydata
重新启动mysql服务器，测试验证数据发现数目对得上
select count(1) from tbl_user_app_20160427
8693860
二、全量恢复实验：
1.全量备份192.168.3.16数据库，生成了2016-11-10_15-14-22目录
innobackupex --user=root --password=root /data
2.将事物提交
innobackupex --apply-log 2016-11-10_15-14-22
3.拷贝到3.17中
# scp -r 2016-11-10_15-14-22 192.168.3.17:/data
4.在3.17中修改mysql的权限
chown -R mysq.mysql /data/2016-11-10_15-14-22
并且修改3.17的my.cnf文件
datadir = /data/2016-11-10_15-14-22
重新启动mysql服务(如果先前没有及时关闭，需要kill -9 pid强行结束mysql服务)
测试是ok的，和3.16的数据库结构完全一致
说明复制了一份完全一样的mysql到目标服务器(包括mysql的user/权限等数据表)
查询了3.16源数据库和3.17目标数据库yunva中的tbl_user_app_20160427表，数据条目一致
mysql> select count(1) from tbl_user_app_20160427;
+----------+
| count(1) |
+----------+
| 8693860 |
+----------+
1 row in set (8 min 48.85 sec)
-------------------------------------------------------------------------------
以下是摘抄的其他网文，通过脚本自动备份和恢复的方式，供参考：
安装配置Xtrabackup
先看看如何安装Xtrabackup,最简单的安装方式是使用RPM包，不过想使用源代码方式安装的话，其安装方式有点古怪，因为它采用的在MySQL源代码上打补丁构建的方式安装的。这里使用二进制包的安装方式，相对比较灵活。
Shell> mkdir /usr/local/xtrabackup
Shell> tar -zxvf xtrabackup-1.6.tar.gz –C /usr/local/xtrabackup
Shell>cd /usr/local/xtrabackup/bin
Shell>ln –s innobackupex-1.5.1 innobackupex
配置环境变量：
Shell>export PATH=$PATH:/usr/local/xtrabackup/bin
希望永久生效的话，可以加到
echo "export PATH=$PATH:/usr/local/xtrabackup/bin" >> /etc/profile
source /etc/profile
修改mysql配置文件:
Shell> vi /etc/my.cnf
添加或修改：datadir =/usr/local/mysql/var（数据库目录）
特别注意：default_table_type = InnoDB（必须改，否则进行增量备份的时候不成功）
经过几天的测试，文档终于可以与大家分享了。使用Xtrabackup能够非常快速地备份与恢复mysql数据库，是mysql dba的首选。以下是线上用的简单脚本
#!/bin/bash
USER=root
PASSWORD=123456
date > /data/scripts/backup.log
echo "begin backup-------------------------------" >> /data/scripts/backup.log
find /data/mysql/backups -mtime +7 |xargs rm -rf
/usr/bin/innobackupex --defaults-file=/opt/mysql5/my.cnf --user=$USER --password=$PASSWORD --databases="test1 test2" /data/mysql/backups >> /data/scripts/backup.log 2>&1
echo "end backup-------------------------------" >> /data/scripts/backup.log
date >> /data/scripts/backup.log
mail -s "backup Passport report" "localhost@localhost" < /data/scripts/backup.log
exit 0
以下是恢复脚本：
#!/bin/bash
USER=root
PASSWORD=123456
/etc/init.d/mysql stop
/usr/bin/innobackupex-1.5.1 --apply-log --defaults-file=/opt/mysql5/my.cnf --user=$USER --password=$PASSWORD /data/mysql/backups/passportdb/`date +%Y-%m-%d`
rm -rf /opt/mysql5/var/test1
rm -rf /opt/mysql5/var/test2
rm -f /opt/mysql5/var/ibdata1
rm -f /opt/mysql5/var/ib_logfile0
rm -f /opt/mysql5/var/ib_logfile1
cd /data/mysql/backups
tar zcvf `date +%Y-%m-%d`.tgz `date +%Y-%m-%d`
rm -rf `date -d -30day +%Y-%m-%d`.tgz
cp -r /data/mysql/backups/`date +%Y-%m-%d`/ib* /opt/mysql5/var/
cp -r /data/mysql/backups/`date +%Y-%m-%d`/test1 /opt/mysql5/var/
cp -r /data/mysql/backups/`date +%Y-%m-%d`/test2 /opt/mysql5/var/
rm -rf /data/mysql/backups/`date +%Y-%m-%d`/*
chown -R mysql.mysql /opt/mysql5/var/test1
chown -R mysql.mysql /opt/mysql5/var/test2
chown mysql.mysql /opt/mysql5/var/ib*
/etc/init.d/mysql start
恢复的时候执行完/usr/bin/innobackupex-1.5.1 --apply-log后，直接拷贝就可以了，不用使用/usr/bin/innobackupex-1.5.1 --copy-back命令。如果使用/usr/bin/innobackupex-1.5.1 --copy-back命令后，会报Original data directory is not empty! at /usr/local/xtrabackup/bin/innobackupex line 538.恢复的目录必须为空。经查官网，这是xtrabackup的一个BUG。
innobackupex did not check that MySQL datadir was empty before –copy-back was run. With this bug fix, innobackupex will now error out of the –copy-back operation if the destination is not empty, avoiding potential data loss or a strang combination of a restored backup and previous data. Bug Fixed: #737569 (Valentine Gostev)
********************************************************************
# innobackupex --copy-back /data/2016-11-10_15-14-22
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
innobackupex: Error: Original data directory '/data/mydata' is not empty! at /usr/bin/innobackupex line 2113.
********************************************************************
网上有好多文章恢复使用
/usr/bin/innobackupex-1.5.1 --apply-log 然后
/usr/bin/innobackupex-1.5.1 --copy-back，如果目录非空则会报错
