pt-tools系列：xtrabackup 最佳实践 | Focus on MySQL,Focus on Life

Contents

1. 安装xtrabackup
2. 安装qpress
3. 一、生产环境默认参数
4. 二、各种场景实战
5. 三、已知的限制和不足
6. 四、频繁问到的问题

安装xtrabackup

下载2.4版本即可
运行报错

/data/Keithlan/pt_backup/bin/innobackupex: /usr/lib64/libstdc++.so.6: version `GLIBCXX_3.4.15' not found (required by /data/Keithlan/pt_backup/bin/innobackupex)
解决问题：
strings /usr/lib64/libstdc++.so.6 | grep GLIBCXX 
GLIBCXX_3.4
GLIBCXX_3.4.1
GLIBCXX_3.4.2
GLIBCXX_3.4.3
GLIBCXX_3.4.4
GLIBCXX_3.4.5
GLIBCXX_3.4.6
GLIBCXX_3.4.7
GLIBCXX_3.4.8
GLIBCXX_3.4.9
GLIBCXX_3.4.10
GLIBCXX_3.4.11
GLIBCXX_3.4.12
GLIBCXX_3.4.13
GLIBCXX_FORCE_NEW
GLIBCXX_DEBUG_MESSAGE_LENGTH
发现的确没有GLIBCXX_3.4.15  
那么需要下载相关文件并替代
http://ftp.de.debian.org/debian/pool/main/g/gcc-4.7/libstdc++6_4.7.2-5_amd64.deb  去这里下载
然后：
ar -x libstdc++6_4.7.2-5_amd64.deb  && tar xvf data.tar.gz
cp -p  libstdc++.so.6.0.17 /usr/lib64
cd /usr/lib64/
rm libstdc++.so.6
ln -s libstdc++.so.6.0.17 libstdc++.so.6
此问题解决

安装qpress

1. 这是解压*.qp的工具，如果你使用了--compress 压缩备份的话  
2. 官方地址：http://www.quicklz.com/
3. tar xf qpress-11-linux-x64.tar

一、生产环境默认参数

常用参数

* 基本option： 
	--defaults-file=[MY.CNF]:
		指定配置文件，一定要放在最参数最前面  
		
	--user=$backupUser 
	--password=$backupPwd 
	--socket=$socket
    --ibbackup=$ibbackup
		主要用于指定xtrabackup二进制文件的，在某些场景特别有用：比如多个xtrabackup文件，比如xtrabackup没有在环境变量中 等等  
	--stream=xbstream
		流式备份的格式，目前常用的是xbstream  
	--rsync
		主要是用来优化本地文件传输的  
		使用rsync代替cp，用来传输所有non-innodb文件，可以更加快速的传输文件  
	--kill-long-queries-timeout=30 
		在FLUSH TABLES WITH READ LOCK 开始之前，xtrabackup等待指定秒数进行kill掉其他query  
		主要目的就是要保证FLUSH TABLES WITH READ LOCK能够执行成功，所以xtrabackup用户需要process和super权限  
	--kill-long-query-type=all 
		什么类型的query会被kill掉，配合上面的参数一起使用  
	--parallel=8 
		备份文件的时候并行多少个进程，用来加快备份速度,如果你的ibd文件越多，作用越大。如果你就一个大表，那么不起作用    
	--compress：
		创建一个压缩的备份  
	--compress-threads=#:
		派生出几个工作线程用于压缩 
	--no-timestamp 
		指定该参数后，备份目录BACKUP-ROOT-DIR不会有时间戳  
	--slave-info
		这个参数非常有用，尤其是在slave进行备份  
		将binlog位置和master的binlog写到xtrabackup_slave_info，作为change master的命令  
				
	--tmpdir=$tmpDIR
		临时文件存放的目录
		redo日志就是先存放在这个临时目录，然后在拷贝到remote host的。
	--copy-back
		拷贝所有备份的文件到original目录  
		它不会覆盖已经存在的文件，除非你指定了 --force-non-empty-directories  
	--move-back
		跟copy back类型，但是它会删掉老的文件 , 使用前请谨慎    
	传输速度:  61440 KB/S
	/usr/bin/rsync -auvP --bwlimit=$bwlimit $dataDir  主要适用于针对local备份，然后rsync到remote server用的  
* 其他option    	
	--apply-log：
		将redo日志（xtrabackup_logfile）apply到备份中，并且根据backup-my.cnf重新创建redo日志  
		innobackupex –apply-log 使用的是backup-my.cnf，或者你显示指定了–defaults-file ，它主要是初始化innodb_page_size，innodb_log_block_size等等，所以不要随意添加配置文件  
	--backup-locks：
		备份锁是在percona server独有的，如果MySQL Server不支持back lock，那么会忽略掉，使用原生态的FLUSH TABLES WITH READ LOCK  
	--compact：
		对所有溢出的二级索引页创建一个紧凑的（compact）格式，一般情况下有碎片和稀疏    
	
	--rebuild-indexes:
		这个参数用于--apply-log阶段，在每次apply完日志后，会重建所有二级索引
		所以这个参数主要是用来跟上面--compact对应的，用于创建compact backups
	--rebuild-threads=NUMBER-OF-THREADS:
		重建二级索引的线程数  
	--decompress:
		解压缩--compress的.qp扩展的文件  
		默认xtrabackup不会自动删除*.qp文件，所以如果你需要clean up这些文件，需要自己手动接入进来  	
	
	--export:
		主要用于export独立的表（而不是整个备份），用于恢复到另外的server上去  		
		
	--sshopt=SSH-OPTION:
		主要用于传输相关ssh的参数，尤其是指定了 --remost-host

xtrabackup备份相关的常用文件

* backup-my.cnf
	跟原始my.cnf不一样，这是xtrabackup创建出来的my.cnf 
	只包含了备份需要的选项
	[mysqld]
	innodb_checksum_algorithm=crc32
	innodb_log_checksum_algorithm=strict_crc32
	innodb_data_file_path=ibdata1:4G:autoextend
	innodb_log_files_in_group=2
	innodb_log_file_size=4294967296
	innodb_fast_checksum=false
	innodb_page_size=16384
	innodb_log_block_size=512
	innodb_undo_directory=./
	innodb_undo_tablespaces=0
	server_id=1261261646
	redo_log_version=1
	server_uuid=f085ef25-dbf5-11e8-8813-ecf4bbf1f518
	master_key_id=0
* xtrabackup_checkpoints
	包含了LSN和备份的类型
	backup_type = full-backuped
	from_lsn = 0
	to_lsn = 74398727925
	last_lsn = 74398729153
	compact = 0
	recover_binlog_info = 0
* xtrabackup_info
	备份相关的日志信息
	uuid = dcfccff9-f174-11e8-a83b-ecf4bbf1f518
	name =
	tool_name = innobackupex
	tool_command = --defaults-file=/etc/my.cnf --user=pt_kill --password=... --socket=/tmp/mysql.sock --rsync --kill-long-queries-timeout=30 --kill-long-query-type=all --parallel=8 --tmpdir=/data/dbbackup --backup /data/dbbackup/
	tool_version = 2.4.12
	ibbackup_version = 2.4.12
	server_version = 5.7.21-log
	start_time = 2018-11-26 20:14:34
	end_time = 2018-11-26 20:14:46
	lock_time = 0
	binlog_pos = filename 'xx.bin.000001', position '18667', GTID of the last change 'f085ef25-dbf5-11e8-8813-ecf4bbf1f518:1-51'
	innodb_from_lsn = 0
	innodb_to_lsn = 74398957203
	partial = N
	incremental = N
	format = file
	compact = N
	compressed = N
	encrypted = N
* xtrabackup_binlog_info
    包含了备份时刻的binlog位置，主要用于在master上的备份，这个位置可以用来搭建new slave
    xx.bin.000818       3465069  f085ef25-dbf5-11e8-8813-ecf4bbf1f518:1-51
* xtrabackup_slave_info
	包含了show slave 的信息，可用于未来的搭建新的slave，change master用  
	SET GLOBAL gtid_purged='f085ef25-dbf5-11e8-8813-ecf4bbf1f518:1-1531';
	CHANGE MASTER TO MASTER_AUTO_POSITION=1;
* xtrabackup_logfile
	就是在备份的过程中，拷贝的redo log日志  
	这个日志会用在后面的 --apply-log中

local

* non-gtid
 $innobackupex --defaults-file=$defaultFile --user=$backupUser --password=$backupPwd --socket=$socket --ibbackup=$ibbackup $backupOptions $dataDIR >>$logFile 2>>$xtrabackupLogFile
* gtid
$innobackupex24 --defaults-file=$defaultFile --user=$backupUser --password=$backupPwd --socket=$socket --ibbackup=$ibbackup24 $backupOptions $dataDIR >>$logFile 2>>$xtrabackupLogFile

remote

* non-gtid
$innobackupex --defaults-file=$defaultFile --user=$backupUser --password=$backupPwd --socket=$socket --ibbackup=$ibbackup $backupOptions --stream=xbstream --tmpdir=$tmpDIR $tmpDIR  2>>$xtrabackupLogFile | ssh root@${destIP} "$xtrabackupDIR/xbstream -x -C ${dataDIR}"  >>$logFile 2>>$xtrabackupLogFile
* gtid
$innobackupex24 --defaults-file=$defaultFile --user=$backupUser --password=$backupPwd --socket=$socket --ibbackup=$ibbackup24 $backupOptions --stream=xbstream --tmpdir=$tmpDIR $tmpDIR  2>>$xtrabackupLogFile | ssh root@${destIP} "$xtrabackupDIR24/xbstream -x -C ${dataDIR}"  >>$logFile 2>>$xtrabackupLogFile

二、各种场景实战

2.1 gtid和non-gtid

gtid和non-gtid的备份都差不多，唯一的区别就在还原上

non-gtid，是change master file，position
gtid，是set globa gtid_purged=’’, 然后再change master而已。

接下来的案例全部以GTID为例，因为GTID是未来趋势

2.2 生产环境常用

2.2.1 备份

gtid：如何再master进行local备份

1
2
3

/data/Keithlan/pt_backup/bin/innobackupex --defaults-file=/etc/my.cnf  --user=pt_kill --password=pt_kill --socket=/tmp/mysql.sock  --rsync --kill-long-queries-timeout=30 --kill-long-query-type=all --parallel=8   --tmpdir=/data/dbbackup  --backup  --slave-info   /data/dbbackup
/data/dbbackup 生成一个timestamp(2018-11-27_13-40-33)的备份

gtid：如何再master进行local + compress备份


/data/Keithlan/pt_backup/bin/innobackupex --defaults-file=/etc/my.cnf  --user=pt_kill --password=pt_kill --socket=/tmp/mysql.sock  --rsync --kill-long-queries-timeout=30 --kill-long-query-type=all --parallel=8   --tmpdir=/data/dbbackup  --backup --compress --compress-threads=8 --slave-info   /data/dbbackup
/data/dbbackup 生成一个timestamp(2018-11-27_13-40-33)的压缩过后的备份

gtid: 如何再master进行remote备份

1
2
3

/data/Keithlan/pt_backup/bin/innobackupex --defaults-file=/etc/my.cnf  --user=pt_kill --password=pt_kill --socket=/tmp/mysql.sock  --stream=xbstream --kill-long-queries-timeout=30 --kill-long-query-type=all --parallel=8   --tmpdir=/data/dbbackup  --backup  --slave-info /data/dbbackup | ssh root@${destIP} "/data/Keithlan/pt_backup/bin/xbstream -x -C /data/dbbackup/remote_backup"
/data/dbbackup/remote_backup 目录必须在目标机器上存在，否则报错

gtid: 如何再master/slave进行remote的[部分库、表]备份

之前我一直想这样的备份有什么用？现在想想还是有些应用场景的
场景一、有些数据业务不要了，但是不确定能不能删，所以希望备份不需要的库、表，如果有异常，还希望能够恢复并查看解决问题

/data/Keithlan/pt_backup/bin/innobackupex --defaults-file=/etc/my.cnf  --user=pt_kill --password=pt_kill --socket=/tmp/mysql.sock  --stream=xbstream --kill-long-queries-timeout=30 --kill-long-query-type=all --parallel=8   --tmpdir=/data/dbbackup  --databases="mysql lc performance_schema sys"  --slave-info /data/dbbackup | ssh root@$dest_ip "/data/Keithlan/pt_backup/bin/xbstream -x -C /data/dbbackup/remote_partial_backup"
更多的【部分库、表】备份选项，请参考  https://www.percona.com/doc/percona-xtrabackup/2.4/innobackupex/partial_backups_innobackupex.html
简单的示例：
* USING THE --INCLUDE OPTION
$ innobackupex --include='^mydatabase[.]mytable' /path/to/backup
* USING THE --TABLES-FILE OPTION
$ echo "mydatabase.mytable" > /tmp/tables.txt
$ innobackupex --tables-file=/tmp/tables.txt /path/to/backup
* USING THE --DATABASES OPTION
$ innobackupex --databases="mydatabase.mytable mysql" /path/to/backup

gtid：如果再master进行remote + compress 备份 —推荐


/data/Keithlan/pt_backup/bin/innobackupex --defaults-file=/etc/my.cnf  --user=pt_kill --password=pt_kill --socket=/tmp/mysql.sock  --stream=xbstream --kill-long-queries-timeout=30 --kill-long-query-type=all --parallel=8   --tmpdir=/data/dbbackup  --backup --compress --compress-threads=8 --slave-info /data/dbbackup | ssh root@xx.xx.126.166 "/data/Keithlan/pt_backup/bin/xbstream -x -C /data/dbbackup/remote_compress_backup"
/data/dbbackup/remote_compress_backup 目录必须在目标机器上存在，否则报错

gtid：如何再slave上进行local备份

1	跟再master进行local备份一致，请参考

gtid：如何再slave上进行remote备份

1	跟在master上进行remote备份一样，请参考

gtid：如何再slave上进行local + compress备份 —推荐

1	跟在master上进行local+compress备份一致，请参考

gtid：如何再slave上进行remote + compress备份 —推荐

1	跟在master上进行remote+compress备份一致，请参考

2.2.2 还原备份

gtid：有一个完整的全备，如何快速还原整个实例

重点： /etc/my.cnf 要存在，这个需要自己备份  
innobackupex --defaults-file=/etc/my.cnf --apply-log 2018-11-26_20-20-21
innobackupex --defaults-file=/etc/my.cnf --copy-back 2018-11-26_20-20-21
chown -R mysql:mysql /data/mysql_data
mysqld_safe --user=mysql &
     cat xtrabackup_binlog_info : 如果是再master进行备份的话
     cat xtrabackup_slave_info  : 如果是在slave进行备份的话
reset slave all
reset master
set global gtid_purged='$gtid_xtrabackup_$binlog_$slave_info'
CHANGE MASTER TO MASTER_HOST = '$master_ip'  ,MASTER_PORT = 3306 , MASTER_USER = 'repl',MASTER_PASSWORD = 'xx' ,MASTER_AUTO_POSITION = 1
start slave;

gtid：有一个完整的压缩全备，如何快速还原整个实例

重点： /etc/my.cnf 要存在，这个需要自己备份
innobackupex --decompress --parallel=16 --remove-original  remote_compress_backup
innobackupex --defaults-file=/etc/my.cnf --apply-log remote_compress_backup
innobackupex --defaults-file=/etc/my.cnf --copy-back remote_compress_backup
chown -R mysql:mysql /data/mysql_data
mysqld_safe --user=mysql &
     cat xtrabackup_binlog_info : 如果是再master进行备份的话
     cat xtrabackup_slave_info  : 如果是在slave进行备份的话
reset slave all
reset master
set global gtid_purged='$gtid_xtrabackup_$binlog_$slave_info'
CHANGE MASTER TO MASTER_HOST = '$master_ip'  ,MASTER_PORT = 3306 , MASTER_USER = 'repl',MASTER_PASSWORD = 'xx' ,MASTER_AUTO_POSITION = 1
start slave;

gtid：有一个完整的压缩全备，如何快速还原单个表

* step1 创建一个部分表目录，用来存放未来需要恢复的表
mkdir partial_backup/
* step2 拷贝需要恢复的库、表到刚刚新建的目录中
cp -pr remote_compress_backup/*.qp  partial_backup/ --xtrabackup恢复需要的基本表
cp -pr remote_compress_backup/xtrabackup_checkpoints  partial_backup/   --xtrabackup恢复需要的基本表 
cp -pr remote_compress_backup/mysql partial_backup/  --MySQL系统表
cp -pr remote_compress_backup/sys   partial_backup/  --MySQL系统表
cp -pr remote_compress_backup/lc    partial_backup/  --用户需要恢复的表，我这里lc库就是要恢复的单库  
* step3 解压缩，并行16个线程  
/data/Keithlan/pt_backup/bin/innobackupex   --decompress --parallel=16 --remove-original partial_backup
* step4 prepare阶段，应用差异redo日志，让备份恢复到FTWRL的点
/data/Keithlan/pt_backup/bin/innobackupex --defaults-file=/etc/my.cnf --apply-log partial_backup
* step5 restore阶段，copy_back 将数据拷贝到配置文件所指定的目录  
/data/Keithlan/pt_backup/bin/innobackupex --defaults-file=/etc/my.cnf --copy-back partial_backup
* step6 赋予权限
chown -R mysql:mysql /data/mysql_data
* step7 启动MySQL
mysqld_safe --user=mysql &
* last
如果需要恢复到指定的点位的话，还需要设置配置文件，并且set global $gtid_purged='',change master xx auto_master=1, start slave until $gtid_set
    xtrabackup_binlog_info : 如果是再master进行备份的话
    xtrabackup_slave_info  : 如果是在slave进行备份的话
重要的是：配置文件需要设置过滤条件
replicate_do_DB: 如果是恢复一个库
replicate-do-table： 如果是恢复一个表

gtid: 如果给你一个只备份了部分库、表的备份（不是全部库的备份哦），应该如何恢复呢？

跟之前正常的套路一样，没啥区别，但是这是野路子，可能会有跟之前数据不一致的情况，但是基本可以放心用
1. /data/Keithlan/pt_backup/bin/innobackupex --defaults-file=/etc/my.cnf --apply-log remote_partial_backup
2. /data/Keithlan/pt_backup/bin/innobackupex --defaults-file=/etc/my.cnf --copy-back remote_partial_backup
3. chown -R mysql:mysql /data/mysql_data
4. mysqld_safe --user=mysql &
非常严谨的方法可以尝试用官方推荐（Restoring Individual Tables），但是我觉得麻烦，不乐意。
https://www.percona.com/doc/percona-xtrabackup/2.4/innobackupex/restoring_individual_tables_ibk.html

三、已知的限制和不足

known issues：

1. 5.1 , 5.5 版本有一些已知的还没修复的bug，比如：redo-logging on  压缩表上，有有很多问题。 bug #16267120  ， 5.6.12 修复
2. 5.6 版本，对于compress的innodb表，不推荐使用innodb_log_compressed_pages=OFF，这样会导致backup失败由于压缩算法的原因  
3. 如果backup和OPTIMIZE TABLE or ALTER TABLE ... TABLESPACE 同时发生，那么备份将还原失败    
4. Compact Backups 目前不支持，有bug #1192834. 
5. Error 24: 'Too many open files' 如果表很多，可能发生这样的错误，所以需要调整/etc/security/limits.conf ， 目前最大为1048576  
6. --throttle 参数建议不要使用
https://zhuanlan.zhihu.com/p/43913304

limitation

1
2
3

1. xtrabackup_logfile 如果超过4GB，那么--prepare在32位操作系统会失败
2. xtrabackup 将不明白--set-variable my.cnf 这样古老的语法，所以最好不要这样使用它

安装xtrabackup

安装qpress

一、生产环境默认参数

二、各种场景实战

2.1 gtid和non-gtid

2.2 生产环境常用

2.2.1 备份

2.2.2 还原备份

三、已知的限制和不足

四、频繁问到的问题