xtrabackup8.0介绍
xtrabackup 从2.4 直接跳到了8.0 ， 命令大体上保持不变。少量的变化，这里记录下。
回顾下 xtrabackup2.4的备份的过程：
发起备份命令
先开始redo copy
然后进行ibd copy
在拷贝ibd期间，xtrabackup会在后台一直监视redo log，一直进行拷贝
拷贝完ibd后，开始锁表 lock binlog for backup ，然后开始拷贝 myisam引擎的表
拷贝完成后，记录下 记录下binlog位置，然后释放锁
停止redolog的拷贝
整个流程结束
xtrabackup8.0部分：
官方文档： https://www.percona.com/doc/percona-xtrabackup/8.0/index.html
howto文档： https://www.percona.com/doc/percona-xtrabackup/8.0/index.html
安装
yum install percona-xtrabackup-80-8.0.12-1.el7.x86_64.rpm
$ rpm -qpl percona-xtrabackup-80-8.0.12-1.el7.x86_64.rpm
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-80-8.0.12
/usr/share/doc/percona-xtrabackup-80-8.0.12/LICENSE
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz备份：
/usr/bin/xtrabackup --user=root --password=123456 --backup --target-dir=/data/baks/xtarbackup需要的备份账号需要的权限：
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cr%T';
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;备份的过程大致如下：
xtrabackup: recognized server arguments: --server-id=123306 --log_bin=mysql-bin --innodb_file_per_table=1 --innodb_use_native_aio=1 --innodb_flush_log_at_trx_commit=0 --innodb_flush_method=O_DIRECT --innodb_io_capacity=200 --innodb_max_dirty_pages_pct=90 --innodb_buffer_pool_size=5G
xtrabackup: recognized client arguments: --user=root --port=3306 --socket=/tmp/mysql.sock --user=root --password=* --user=root --password=* --backup=1 --target-dir=/data/baks/
/usr/bin/xtrabackup version 8.0.12 based on MySQL server 8.0.20 Linux (x86_64) (revision id: 01cce6d)
200606 16:48:53 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root' (using password: YES).
200606 16:48:53 version_check Connected to MySQL server
200606 16:48:53 version_check Executing a version check against the server...
200606 16:48:53 version_check Done.
200606 16:48:53 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock
Using server version 8.0.19-10
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 0, set to 65536
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
2020-06-06 16:48:53 0x7fd48f8b9940 InnoDB: Using Linux native AIO
xtrabackup: using O_DIRECT
Number of pools: 1
200606 16:48:53 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock
200606 16:48:53 >> log scanned up to (9093130058)
xtrabackup: Redo Log Archiving is not set up.
xtrabackup: Generating a list of tablespaces
xtrabackup: Generating a list of tablespaces
Scanning './'
Directory '/var/lib/mysql/.rocksdb' will not be scanned because it is a hidden directory.
Completed space ID check of 2 files.
Allocated tablespace ID 1747 for cmdb/auth_group, old maximum was 0
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
200606 16:48:54 [01] Copying ./ibdata1 to /data/baks/ibdata1
[01] xtrabackup: Page 64 is a doublewrite buffer page, skipping.
[01] xtrabackup: Page 65 is a doublewrite buffer page, skipping.
200606 16:48:54 >> log scanned up to (9093130058)
200606 16:48:55 >> log scanned up to (9093130058)
200606 16:48:55 [01] ...done
200606 16:48:55 [01] Copying ./grafana/quota.ibd to /data/baks/grafana/quota.ibd
200606 16:48:55 [01] ...done
200606 16:48:55 [01] Copying ./test/t_rocks.ibd to /data/baks/test/t_rocks.ibd
200606 16:48:55 [01] ...done
200606 16:49:02 [01] Copying performance_schema/variables_info_440.sdi to /data/baks/performance_schema/variables_info_440.sdi
200606 16:49:02 [01] ...done
200606 16:49:02 [01] Copying performance_schema/persisted_variab_441.sdi to /data/baks/performance_schema/persisted_variab_441.sdi
200606 16:49:02 [01] ...done
200606 16:49:02 [01] Copying performance_schema/user_defined_fun_442.sdi to /data/baks/performance_schema/user_defined_fun_442.sdi
200606 16:49:02 [01] ...done
200606 16:49:02 Finished backing up non-InnoDB tables and files
200606 16:49:02 xtrabackup: Creating RocksDB checkpoint
200606 16:49:02 >> log scanned up to (9093130058)
200606 16:49:02 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS # 看这里
200606 16:49:02 Selecting LSN and binary log position from p_s.log_status
200606 16:49:02 [00] Copying /var/lib/mysql/mysql-bin.000034 to /data/baks/mysql-bin.000034 up to position 195
200606 16:49:02 [00] ...done
200606 16:49:02 [00] Writing /data/baks/mysql-bin.index
200606 16:49:02 [00] ...done
200606 16:49:02 [00] Writing /data/baks/xtrabackup_binlog_info
200606 16:49:02 [00] ...done
200606 16:49:02 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... # 看这里
xtrabackup: The latest check point (for incremental): '9093130058'
xtrabackup: Stopping log copying thread at LSN 9093130068.
200606 16:49:02 >> log scanned up to (9093130078)
Starting to parse redo log at lsn = 9093129807
200606 16:49:03 All tables unlocked # 看这里
200606 16:49:03 [00] Copying ib_buffer_pool to /data/baks/ib_buffer_pool
200606 16:49:03 [00] ...done
200606 16:49:03 [00] Copying .xtrabackup_rocksdb_checkpoint_1591433342477/000224.sst to /data/baks/.rocksdb/000224.sst
200606 16:49:03 [00] ...done
200606 16:49:03 [00] Copying .xtrabackup_rocksdb_checkpoint_1591433342477/000221.sst to /data/baks/.rocksdb/000221.sst
200606 16:49:03 [00] ...done
200606 16:49:03 [00] Copying .xtrabackup_rocksdb_checkpoint_1591433342477/MANIFEST-000214 to /data/baks/.rocksdb/MANIFEST-000214
200606 16:49:03 [00] ...done
200606 16:49:03 [00] Copying .xtrabackup_rocksdb_checkpoint_1591433342477/OPTIONS-000222 to /data/baks/.rocksdb/OPTIONS-000222
200606 16:49:03 [00] ...done
200606 16:49:03 [00] Copying .xtrabackup_rocksdb_checkpoint_1591433342477/CURRENT to /data/baks/.rocksdb/CURRENT
200606 16:49:03 [00] ...done
200606 16:49:03 xtrabackup: Removing RocksDB checkpoint
200606 16:49:03 Backup created in directory '/data/baks/'
MySQL binlog position: filename 'mysql-bin.000034', position '195', GTID of the last change 'fd2adbd9-e263-11e8-847a-141877487b3d:1-2647510' # 看这里
200606 16:49:03 [00] Writing /data/baks/backup-my.cnf # 看这里
200606 16:49:03 [00] ...done
200606 16:49:03 [00] Writing /data/baks/xtrabackup_info # 看这里
200606 16:49:03 [00] ...done
xtrabackup: Transaction log of lsn (9093130058) to (9093130088) was copied.
200606 16:49:05 completed OK!备份过程中的mysq层记录到的general_log如下：
2020-06-06T16:48:53.309951+08:00 8004 Connectroot@localhost on using Socket
2020-06-06T16:48:53.310264+08:00 8004 QuerySET SESSION wait_timeout=2147483
2020-06-06T16:48:53.310551+08:00 8004 QuerySELECT CONCAT(@@hostname, @@port)
2020-06-06T16:48:53.310866+08:00 8004 Quit
2020-06-06T16:48:53.313871+08:00 8005 Connectroot@localhost on using Socket
2020-06-06T16:48:53.314010+08:00 8005 QuerySET SESSION wait_timeout=2147483
2020-06-06T16:48:53.314099+08:00 8005 QuerySET SESSION autocommit=1
2020-06-06T16:48:53.314253+08:00 8005 QuerySET NAMES utf8
2020-06-06T16:48:53.314387+08:00 8005 QuerySHOW VARIABLES
2020-06-06T16:48:53.318447+08:00 8005 QuerySELECT COUNT(*) FROM information_schema.tables WHERE engine = 'MyISAM' OR engine = 'RocksDB'
2020-06-06T16:48:53.321804+08:00 8005 QuerySHOW ENGINE INNODB STATUS
2020-06-06T16:48:53.332412+08:00 8005 QuerySELECT PLUGIN_NAME, PLUGIN_LIBRARY FROM information_schema.plugins WHERE PLUGIN_STATUS = 'ACTIVE' AND PLUGIN_TYPE = 'KEYRING'
2020-06-06T16:48:53.409869+08:00 8005 QuerySELECT CONCAT(table_schema, '/', table_name), engine FROM information_schema.tables WHERE engine NOT IN ('MyISAM', 'InnoDB', 'CSV', 'MRG_MYISAM', 'ROCKSDB') AND table_schema NOT IN ( 'performance_schema', 'information_schema', 'mysql')
2020-06-06T16:48:53.518201+08:00 8006 Connectroot@localhost on using Socket
2020-06-06T16:48:53.518354+08:00 8006 QuerySET SESSION wait_timeout=2147483
2020-06-06T16:48:53.518375+08:00 8005 QuerySELECT T2.PATH, T2.NAME, T1.SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES T1 JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF T2 USING (SPACE) WHERE T1.SPACE_TYPE = 'Single' && T1.ROW_FORMAT != 'Undo'UNION SELECT T2.PATH, SUBSTRING_INDEX(SUBSTRING_INDEX(T2.PATH, '/', -1), '.', 1) NAME, T1.SPACE_TYPE FROM INFORMATION_SCHEMA .INNODB_TABLESPACES T1 JOIN INFORMATION_SCHEMA .INNODB_TABLESPACES_BRIEF T2 USING (SPACE) WHERE T1.SPACE_TYPE = 'General' && T1.ROW_FORMAT != 'Undo'
2020-06-06T16:48:53.518436+08:00 8006 QuerySET SESSION autocommit=1
2020-06-06T16:48:53.518535+08:00 8006 QuerySET NAMES utf8
2020-06-06T16:48:53.518680+08:00 8006 QuerySHOW VARIABLES
2020-06-06T16:48:53.525590+08:00 8006 Quit
2020-06-06T16:49:02.477052+08:00 8005 QuerySET SESSION rocksdb_disable_file_deletions = TRUE
2020-06-06T16:49:02.477330+08:00 8005 QuerySET SESSION rocksdb_create_temporary_checkpoint = '.xtrabackup_rocksdb_checkpoint_1591433342477'
2020-06-06T16:49:02.529259+08:00 8005 QueryFLUSH NO_WRITE_TO_BINLOG BINARY LOGS
2020-06-06T16:49:02.618888+08:00 8005 QuerySELECT server_uuid, local, replication, storage_engines FROM performance_schema.log_status
2020-06-06T16:49:02.619759+08:00 8005 QuerySHOW VARIABLES
2020-06-06T16:49:02.654790+08:00 8005 QuerySET SESSION rocksdb_disable_file_deletions = FALSE
2020-06-06T16:49:02.655093+08:00 8005 QuerySHOW VARIABLES
2020-06-06T16:49:02.674922+08:00 8005 QueryFLUSH NO_WRITE_TO_BINLOG ENGINE LOGS
2020-06-06T16:49:03.831368+08:00 8005 QuerySET SESSION rocksdb_create_temporary_checkpoint = NULL
2020-06-06T16:49:03.847467+08:00 8005 QuerySELECT UUID()
2020-06-06T16:49:03.847738+08:00 8005 QuerySELECT VERSION()
2020-06-06T16:49:05.190831+08:00 8005 Quit恢复：
$ xtrabackup --prepare --target-dir=/data/baks日志如下：
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=123306 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/data/baks
xtrabackup version 8.0.12 based on MySQL server 8.0.20 Linux (x86_64) (revision id: 01cce6d)
xtrabackup: cd to /data/baks/
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(9093130058)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
PUNCH HOLE support available
Mutexes and rw_locks use GCC atomic builtins
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.7
Number of pools: 1
Using CPU crc32 instructions
Directories to scan './'
Scanning './'
Directory '/data/baks/.rocksdb' will not be scanned because it is a hidden directory.
Completed space ID check of 111 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
The log sequence number 9089852034 in the system tablespace does not match the log sequence number 9093130058 in the ib_logfiles!
Database was not shutdown normally!
Starting crash recovery.
Starting to parse redo log at lsn = 9093129807, whereas checkpoint_lsn = 9093130058
Doing recovery: scanned up to log sequence number 9093130088
Log background threads are being started...
Applying a batch of 1 redo log records ...
100%
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 381628717
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.20 started; log sequence number 9093130088
Allocated tablespace ID 53 for grafana/data_source, old maximum was 0
xtrabackup: Last MySQL binlog file position 2301243, file name mysql-bin.000018
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
FTS optimize thread exiting.
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 9093130088
Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
PUNCH HOLE support available
Mutexes and rw_locks use GCC atomic builtins
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.7
Number of pools: 1
Using CPU crc32 instructions
Directories to scan './'
Scanning './'
Directory '/data/baks/.rocksdb' will not be scanned because it is a hidden directory.
Completed space ID check of 111 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
Creating log file ./ib_logfile101
Creating log file ./ib_logfile1
Renaming log file ./ib_logfile101 to ./ib_logfile0
New log files created, LSN=9093130252
Starting to parse redo log at lsn = 9093130252, whereas checkpoint_lsn = 9093130252
Log background threads are being started...
Applying a batch of 0 redo log records ...
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 381628717
Removed temporary tablespace data file: "ibtmp1"
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.20 started; log sequence number 9093130252
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
FTS optimize thread exiting.
Trying to access missing tablespace 4294967294
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 9093130252
200606 17:07:44 completed OK!恢复到数据库，我们可以使用 linux的mv命令即可。 也可以使用 rsync 或 xtrabackup --copy-back --target-dir=/data/baks/
####################################################################################
xtrabackup 的 增量备份， 我们生产上很少用到，这里就不做过多介绍了。
其它的一些命令：
压缩备份与解压缩：
压缩备份：
$ xtrabackup --backup --compress --compress-threads=4 --target-dir=/data/compressed/
解压并恢复：
$ xtrabackup --decompress --target-dir=/data/compressed/
$ xtrabackup --prepare --target-dir=/data/compressed/
#####################################################################################
stream流式备份
xtrabackup --user=root --password=123456 --backup --stream=xbstream --target-dir=./ > /data/baks_stream/baks_20200606 # 备份到文件baks_20200606中
带压缩的stream备份
xtrabackup --user=root --password=123456 --backup --stream=xbstream --compress --target-dir=./ > backup.xbstream
压缩备份直接发送到远程服务器并解压出来
xtrabackup --backup --compress --stream=xbstream --target-dir=./ | ssh user@otherhost "xbstream -x"
多线程stream方式备份
xtrabackup --backup --compress --compress-threads=8 --stream=xbstream --parallel=4 --target-dir=./ > backup.xbstream
解压
xbstream -x < backup.xbstream
#####################################################################################
加密方式备份
xtrabackup --user=root --password=123456 --backup --encrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" --target-dir=/data/backup # 直接将key写在命令行里面
echo -n "GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" > /data/backups/keyfile
xtrabackup --user=root --password=123456 --backup --encrypt=AES256 --encrypt-key-file=/data/backups/keyfile --target-dir=/data/backup # 将key写在文件里面
加密后的备份文件，后缀都带上了 .xbcrypt 后缀名
解密
xtrabackup --decrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" --target-dir=/data/backup/
多线程解密
xtrabackup --parallel=4 --decrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" --target-dir=/data/backup/
加密备份的恢复方法（先--decrypt解密，然后再--prepare）
xtrabackup --prepare --target-dir=/data/backup/
#####################################################################################
备份过程中，遇到ftwrl被阻塞时最多等180秒，获取不到就退出备份进程。最多等待40秒的查询的结束，否则也退出备份进程。
发出带有读取锁的刷新表后，xtrabackup将等待20秒以获取锁。如果20秒钟后仍未获得锁定，它将杀死所有运行时间超过 FLUSH TABLES WITH READ LOCK的查询。
$ xtrabackup --backup --ftwrl-wait-threshold=40 \
--ftwrl-wait-query-type=all --ftwrl-wait-timeout=180 \
--kill-long-queries-timeout=20 --kill-long-query-type=all \
--target-dir=/data/backups/
#####################################################################################
备份部分库或者表：
$ xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ --tables="^test[.].*" # 备份test库下的全部表
$ xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ --tables="^test[.]t1" # 只备份test.t1表
基于文件的备份部分表：
$ echo "mydatabase.mytable" > /tmp/tables.txt
$ xtrabackup --backup --tables-file=/tmp/tables.txt --datadir=/var/lib/mysql --target-dir=/data/backups/
备份指定的几个库
$ xtrabackup --databases='mysql sys performance_schema ...' --backup --tables-file=/tmp/tables.txt --datadir=/var/lib/mysql
#####################################################################################
还原部分表或库的备份文件
$ xtrabackup --prepare --export --target-dir=/data/backups
然后，将恢复后的表，通过表空间传输的方式导入到目标实例里面即可。（discard tablespace然后再import tablespace）
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2020/06/06 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
腾讯云开发者
