CENTOS6.6下mysql5.7.11的percona-xtrabackup安装与备份

本文来自我的github pages博客http://galengao.github.io/ 即www.gaohuirong.cn

Xtrabackup有两个主要的工具：xtrabackup、innobackupex
（1）xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表
（2）innobackupex-1.5.1则封装了xtrabackup，是一个脚本封装，所以能同时备份处理innodb和myisam，但在处理myisam时需要加一个读锁

安装percona-xtrabackup

1、安装依赖包

yum -y  install cmake gcc gcc-c++ libaio libaio-devel automake autoconf  bison libtool ncurses-devel libgcrypt-devel libev-devel libcurl*  
yum -y install perl-Time-HiRes  
yum -y install perl-DBI  
yum -y install perl-DBD-MySQL  
yum install perl-IO-Socket-SSL  

cd /usr/local
wget http://dist.schmorp.de/libev/libev-4.22.tar.gz
tar xvzf libev-4.22.tar.gz
cd libev-4.22
./configure 
make
make install

2、编译安装percona-xtrabackup

此处要注意xtrabackup的版本支持mysql哪个版本，可以到xtrabackup官网查看文档，我此处的版本支持mysql5.7.11

cd /usr/local
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.2/source/tarball/percona-xtrabackup-2.4.2.tar.gz  
tar -zxvf percona-xtrabackup-2.2.13.tar.gz
cd percona-xtrabackup-2.2.13/
cmake -DBUILD_CONFIG=xtrabackup_release -DWITH_MAN_PAGES=OFF && make -j4 --DINSTALL_LAYOUT=/usr/local/xtrabackup
make
make install  

这样是默认装到/usr/local/xtrabackup目录下的。

3、加入环境变量

# vi  ~/.bash_profile
export PATH=/usr/local/xtrabackup/bin:$PATH  
# source ~/.bash_profile

4、备份测试

在Master和Slave上安装xtrabackup：

注意：此处备份是根据主从环境来的，[参考来源]http://heylinux.com/archives/3777.html

在Master和Slave上创建一个用于备份的用户backup-user：

mysql> CREATE USER 'backup-user'@'localhost' IDENTIFIED BY 'backup-pass';
mysql> GRANT SUPER, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup-user'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

在Master上备份

# 常规方式
[root@idc1-master1 ~]# innobackupex --defaults-file=/etc/my_3308.cnf --user=backup-user --password=backup-pass --no-lock --use-memory=4G /mysql-backup/3308

[root@idc1-master1 ~]# ls -rt1 /mysql-backup/3308/ | tail -n 1
2015-10-26_03-00-10

# 压缩打包方式  
[root@idc1-master1 ~]# innobackupex --defaults-file=/etc/my_3308.cnf --user=backup-user --password=backup-pass --no-lock --use-memory=4G --compress --compress-threads=8 --stream=xbstream --parallel=4 /mysql-backup/3308 > /mysql-backup/3308/$(date +%Y-%m-%d_%H-%M-%S).xbstream

[root@idc1-master1 ~]# ls -rt1 /mysql-backup/3308/ | tail -n 1
2015-10-26_03-05-05.xbstream

在Slave上备份

# 常规方式  
[root@idc1-slave1 ~]# innobackupex --defaults-file=/etc/my_3308.cnf --user=backup-user --password=backup-pass --no-lock --use-memory=4G --slave-info --safe-slave-backup /mysql-backup/3308
    
[root@idc1-slave1 ~]# ls -rt1 /mysql-backup/3308/ | tail -n 1
2015-10-26_03-11-03
    
# 压缩打包方式  
[root@idc1-slave1 ~]# innobackupex --defaults-file=/etc/my_3308.cnf --user=backup-user --password=backup-pass --no-lock --use-memory=4G --slave-info --safe-slave-backup --compress --compress-threads=8 --stream=xbstream --parallel=4 /mysql-backup/3308 > /mysql-backup/3308/$(date +%Y-%m-%d_%H-%M-%S).xbstream
    
[root@idc1-slave1 ~]# ls -rt1 /mysql-backup/3308/ | tail -n 1
2015-10-26_03-15-03.xbstream

在Master上恢复

[root@idc1-master1 ~]# /etc/init.d/mysql_3308 stop
    
[root@idc1-master1 ~]# mv /opt/mysql_3308/data /opt/mysql_3308/data_broken
[root@idc1-master1 ~]# mkdir /opt/mysql_3308/data
    
# 常规方式
[root@idc1-master1 ~]# innobackupex --apply-log --use-memory=4G /mysql-backup/3308/2015-10-26_03-00-10
[root@idc1-master1 ~]# innobackupex --copy-back --use-memory=4G /mysql-backup/3308/2015-10-26_03-00-10 --defaults-file=/etc/my_3308.cnf
    
# 压缩打包方式
[root@idc1-master1 ~]# mkdir -p /mysql-backup/3308/2015-10-26_03-05-05
[root@idc1-master1 ~]# xbstream -x < /mysql-backup/3308/2015-10-26_03-05-05.xbstream -C /mysql-backup/3308/2015-10-26_03-05-05
[root@idc1-master1 ~]# innobackupex --decompress --parallel=4 /mysql-backup/3308/2015-10-26_03-05-05
[root@idc1-master1 ~]# find /mysql-backup/3308/2015-10-26_03-05-05 -name "*.qp" -delete
[root@idc1-master1 ~]# innobackupex --apply-log --use-memory=4G /mysql-backup/3308/2015-10-26_03-05-05
[root@idc1-master1 ~]# innobackupex --copy-back --use-memory=4G /mysql-backup/3308/2015-10-26_03-05-05 --defaults-file=/etc/my_3308.cnf
    
[root@idc1-master1 ~]# chown -R mysql:mysql /opt/mysql_3308/data
    
[root@idc1-master1 ~]# /etc/init.d/mysql_3308 start

在Slave上恢复

[root@idc1-slave1 ~]# /etc/init.d/mysql_3308 stop

[root@idc1-slave1 ~]# mv /opt/mysql_3308/data /opt/mysql_3308/data_broken
[root@idc1-slave1 ~]# mkdir /opt/mysql_3308/data

# 常规方式
[root@idc1-slave1 ~]# innobackupex --apply-log --use-memory=4G /mysql-backup/3308/2015-10-26_03-11-03
[root@idc1-slave1 ~]# innobackupex --copy-back --use-memory=4G /mysql-backup/3308/2015-10-26_03-11-03 --defaults-file=/etc/my_3308.cnf

# 压缩打包方式
[root@idc1-slave1 ~]# mkdir -p /mysql-backup/3308/2015-10-26_03-15-03
[root@idc1-slave1 ~]# xbstream -x < /mysql-backup/3308/2015-10-26_03-15-03.xbstream -C /mysql-backup/3308/2015-10-26_03-15-03
[root@idc1-slave1 ~]# innobackupex --decompress --parallel=4 /mysql-backup/3308/2015-10-26_03-15-03
[root@idc1-slave1 ~]# find /mysql-backup/3308/2015-10-26_03-15-03 -name "*.qp" -delete
[root@idc1-slave1 ~]# innobackupex --apply-log --use-memory=4G /mysql-backup/3308/2015-10-26_03-15-03
[root@idc1-slave1 ~]# innobackupex --copy-back --use-memory=4G /mysql-backup/3308/2015-10-26_03-15-03 --defaults-file=/etc/my_3308.cnf

[root@idc1-slave1 ~]# chown -R mysql:mysql /opt/mysql_3308/data

[root@idc1-slave1 ~]# /etc/init.d/mysql_3308 start

[root@idc1-slave1 ~]# cd /opt/mysql_3308/data
# 从Master的备份中恢复时查看 xtrabackup_slave_info
[root@idc1-slave1 data]# cat xtrabackup_binlog_pos_innodb
./bin-log-mysqld.000222  222333

# 从Slave的备份中恢复时查看 xtrabackup_slave_info
[root@idc1-slave1 data]# cat xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='bin-log-mysqld.000222', MASTER_LOG_POS=222333

[root@idc1-slave1 data]# mysql_3308 -uroot -p
mysql> change master to
master_host='idc1-master1',
master_port=3308,
master_user='backup-user',
master_password='backup-pass',
master_log_file='bin-log-mysqld.000222',
master_log_pos=222333;

mysql> start slave;
mysql> show slave statusG;
mysql> exit;

增量备份与恢复

增量备份的原理是，基于一个现有的完整备份，针对InnoDB-based表仅备份增量的部分，针对MyISAM表则仍然保持全量备份。
备份环境：
在Slave服务器上进行
备份策略：
每天1次完整备份 + 每天2次增量备份
具体步骤：

7.1 增量备份
7.1.1 准备完整备份（压缩但不打包方式）：

[root@idc1-slave1 ~]# innobackupex --defaults-file=/etc/my_3308.cnf --user=backup-user --password=backup-pass --no-lock --use-memory=4G --slave-info --safe-slave-backup --compress --compress-threads=8 /mysql-backup/3308

[root@idc1-slave1 ~]# ls -rt1 /mysql-backup/3308/ | tail -n 1
2015-10-26_06-48-33

[root@idc1-slave1 ~]# cat /mysql-backup/3308/2015-10-26_06-48-33/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1631145
last_lsn = 1631145
compact = 0
recover_binlog_info = 0

7.1.2 进行第1次增量备份（压缩但不打包方式）：

[root@idc1-slave1 ~]# innobackupex --defaults-file=/etc/my_3308.cnf --user=backup-user --password=backup-pass --no-lock --use-memory=4G --slave-info --safe-slave-backup --compress --compress-threads=8 --incremental /mysql-backup/3308 --incremental-basedir=/mysql-backup/3308/2015-10-26_06-48-33

[root@idc1-slave1 ~]# ls -rt1 /mysql-backup/3308/ | tail -n 1
2015-10-26_06-55-12

[root@idc1-slave1 ~]# cat /mysql-backup/3308/2015-10-26_06-55-12/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1631145
to_lsn = 1635418
last_lsn = 1635418
compact = 0
recover_binlog_info = 0

7.1.3 进行第2次增量备份（压缩但不打包方式）：

[root@idc1-slave1 ~]# innobackupex --defaults-file=/etc/my_3308.cnf --user=backup-user --password=backup-pass --no-lock --use-memory=4G --slave-info --safe-slave-backup --compress --compress-threads=8 --incremental /mysql-backup/3308 --incremental-basedir=/mysql-backup/3308/2015-10-26_06-55-12

[root@idc1-slave1 ~]# ls -rt1 /mysql-backup/3308/ | tail -n 1
2015-10-26_06-59-49

[root@idc1-slave1 ~]# cat /mysql-backup/3308/2015-10-26_06-59-49/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1635418
to_lsn = 1639678
last_lsn = 1639678
compact = 0
recover_binlog_info = 0

7.2 增量恢复：
7.2.1 取回完整备份（必须加参数 –redo-only）

[root@idc1-slave1 ~]# innobackupex --decompress --parallel=4 /mysql-backup/3308/2015-10-26_06-48-33
[root@idc1-slave1 ~]# find /mysql-backup/3308/2015-10-26_06-48-33 -name "*.qp" -delete
[root@idc1-slave1 ~]# innobackupex --apply-log --redo-only --use-memory=4G /mysql-backup/3308/2015-10-26_06-48-33

7.2.2 合并第1个增量（必须加参数 –redo-only）

[root@idc1-slave1 ~]# innobackupex --decompress --parallel=4 /mysql-backup/3308/2015-10-26_06-55-12
[root@idc1-slave1 ~]# find /mysql-backup/3308/2015-10-26_06-55-12 -name "*.qp" -delete
[root@idc1-slave1 ~]# innobackupex --apply-log --redo-only --use-memory=4G /mysql-backup/3308/2015-10-26_06-48-33 --incremental-dir=/mysql-backup/3308/2015-10-26_06-55-12

7.2.3 合并第2个增量（合并最后一个增量备份时不加 –redo-only）

[root@idc1-slave1 ~]# innobackupex --decompress --parallel=4 /mysql-backup/3308/2015-10-26_06-59-49
[root@idc1-slave1 ~]# find /mysql-backup/3308/2015-10-26_06-59-49 -name "*.qp" -delete
[root@idc1-slave1 ~]# innobackupex --apply-log --use-memory=4G /mysql-backup/3308/2015-10-26_06-48-33 --incremental-dir=/mysql-backup/3308/2015-10-26_06-59-49

7.2.4 准备完整备份（定稿完整备份时不加 –redo-only）

[root@idc1-slave1 ~]# innobackupex --apply-log --use-memory=4G /mysql-backup/3308/2015-10-26_06-48-33