xtrabackup8.0介绍

最新推荐文章于 2024-06-25 07:30:12 发布

qq_38461377

最新推荐文章于 2024-06-25 07:30:12 发布

阅读量1.3k

点赞数

文章标签： linux mysql java cobol dom

原文链接：https://blog.51cto.com/lee90/2502005

版权

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）

qq_38461377

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
xtrabackup8.0介绍

xtrabackup 从2.4 直接跳到了8.0 ，命令大体上保持不变。少量的变化，这里记录下。回顾下 xtrabackup2.4的备份的过程：发起备份命令先开始redo copy然后进行ibd copy在拷贝ibd期间，xtrabackup会在后台一直监视redo log，一直进行拷贝拷贝完ibd后，开始锁表 lock binlog for backup ，然后开始拷贝 myisam引擎的表拷贝完成后，记录下记录下binlog位置，然后释放锁停止r...
复制链接

扫一扫