本文来自我的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/localwget http://dist.schmorp.de/libev/libev-4.22.tar.gztar xvzf libev-4.22.tar.gzcd libev-4.22./configure makemake install
2、编译安装percona-xtrabackup
此处要注意xtrabackup的版本支持mysql哪个版本，可以到xtrabackup官网查看文档，我此处的版本支持mysql5.7.11
cd /usr/localwget 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.gzcd percona-xtrabackup-2.2.13/cmake -DBUILD_CONFIG=xtrabackup_release -DWITH_MAN_PAGES=OFF && make -j4 --DINSTALL_LAYOUT=/usr/local/xtrabackupmakemake install
这样是默认装到/usr/local/xtrabackup目录下的。
3、加入环境变量
# vi ~/.bash_profileexport PATH=/usr/local/xtrabackup/bin:$PATH # source ~/.bash_profile
4、备份测试
- 在Master和Slave上安装xtrabackup：
注意：此处备份是根据主从环境来的，[参考来源]
- 在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 12015-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 12015-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 12015-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 12015-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_infoCHANGE MASTER TO MASTER_LOG_FILE='bin-log-mysqld.000222', MASTER_LOG_POS=222333[root@idc1-slave1 data]# mysql_3308 -uroot -pmysql> change master tomaster_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 status\G;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 12015-10-26_06-48-33[root@idc1-slave1 ~]# cat /mysql-backup/3308/2015-10-26_06-48-33/xtrabackup_checkpointsbackup_type = full-backupedfrom_lsn = 0to_lsn = 1631145last_lsn = 1631145compact = 0recover_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 12015-10-26_06-55-12[root@idc1-slave1 ~]# cat /mysql-backup/3308/2015-10-26_06-55-12/xtrabackup_checkpointsbackup_type = incrementalfrom_lsn = 1631145to_lsn = 1635418last_lsn = 1635418compact = 0recover_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 12015-10-26_06-59-49[root@idc1-slave1 ~]# cat /mysql-backup/3308/2015-10-26_06-59-49/xtrabackup_checkpointsbackup_type = incrementalfrom_lsn = 1635418to_lsn = 1639678last_lsn = 1639678compact = 0recover_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