Xtrabackup-MySQL8.0备份初体验

sjy_随风

已于 2024-04-11 17:44:33 修改

阅读量252

点赞数 7

文章标签： mysql 数据库 dba

于 2024-04-10 22:22:56 首次发布

本文链接：https://blog.csdn.net/qq_42895240/article/details/137613111

版权

一、简介

Xtrabackup 是 MySQL 社区唯一一款开源物理热备工具，早期xtrabackup包含两个主要的工具，即xtrabackup和innobackupex。

二者区别如下：

1）xtrabackup 2.3 只能备份innodb和xtradb两种引擎的表，而不能备份myisam引擎的表，2.3版本以后支持myisam引擎表；

2）innobackupex是一个封装了xtrabackup的Perl脚本，支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁。myisam不支持增量备份
2.4版本后innobackupex被集成到了xtrabackup ，并从产品中删除innobackupex指令
Xtrabackup 2.4 适用于 MySQL 5.6 和 MySQL 5.7。
Xtrabackup 8.0 适用于 MySQL 8.0。
Xtrabackup 8.1 适用于 MySQL 8.1。

注意，三个版本不能混用，因为 MySQL 8.0 版本 redo log 和数据字典格式都发生了变化，可能会出现不兼容的情况。

二、全量备份：

会同时备份当前binlog文件，如需更多binlong需额外备份

从cnf文件中读取datadir路径或指定--datadir 或指定port

sock：如使用默认位置/tmp可以不加-S

--target-dir ：备份存在路径

--parallel：并行

 --stream=xbstream  8版本和2.4版本都支持，xtrabackup8.0.33版本不支持tar

流式备份指将备份数据通过流的方式输出到 STDOUT，而不是备份文件中。结合管道，可将多个功能组合在一起，如压缩、加密、流控等。

在 xtrabackup 2.4 版中支持 tar 和 xbstream 流格式，但 tar 格式不支持并行备份。

在 xtrabackup 8.0 中，仅支持 xbstream 流格式，不再支持 tar 格式。

--compress：压缩所有输出数据，包括事务日志文件和元数据文件，通过指定的压缩算法，目前唯一支持的算法是quicklz.结果文件是qpress归档格式，每个xtrabackup创建的*.qp文件都可以通过qpress程序提取或者解压缩

--compress-threads=#：xtrabackup进行并行数据压缩时的worker线程的数量，该选项默认值是1，并行压缩（'compress-threads'）可以和并行文件拷贝('parallel')一起使用。例如:'--parallel=4 --compress --compress-threads=2'会创建4个IO线程读取数据并通过管道传送给2个压缩线程。$\textcolor{Red}{58M 压缩后3.3M，压缩率94\%左右} $

--decompress：对压缩的文件使用qpress程序进行解压，$\textcolor{Red}{需安装qpress}$，

--decompress-threads：解压缩线程

--extra-lsndir：存放本次备份的xtrabackup_checkpoints，后面的增量备份时，--incremental-basedir就指向前一日的extra-lsndir目录便可。

全库备份有如下几种方式

1、xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup --parallel=16 2> /mysql/backup/xtrabackup/fullback.log

2、xtrabackup --datadir=/data/mysql/mysql3307/data -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/full_backup_4 --parallel=16 2> /mysql/backup/xtrabackup/fullback.log

3、xtrabackup  -uroot -pmysql -P3307 --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/full_backup_3 --parallel=16 2> /mysql/backup/xtrabackup/fullback.log

4、xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup --parallel=4 --compress --compress-threads=2 2> /mysql/backup/xtrabackup/fullback.log

5、xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/ --parallel=4 --compress --compress-threads=2 --stream=xbstream  >/mysql/backup/xtrabackup/full_backup_9.xbstream

各个方式的输出结果：

1、[root@mysql8 xtrabackup]# xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup --parallel=16
2024-03-20T20:33:17.160665+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/data/mysql/mysql3307/data --tmpdir=/data/mysql/mysql3307/tmp 
2024-03-20T20:33:17.160784+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3307 --user=root --password=* --backup=1 --socket=/data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/full_backup_1 --parallel=16 
xtrabackup version 8.0.33-28 based on MySQL server 8.0.33 Linux (x86_64) (revision id: b3a3c3dd)
Can't locate Data/Dumper.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor
_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 749.
BEGIN failed--compilation aborted at - line 749.
2024-03-20T20:33:17.185133+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3307, 
socket: /data/mysql/mysql3307/data/mysql3307.sock
2024-03-20T20:33:17.187494+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.18
2024-03-20T20:33:17.188735+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...

2、[root@mysql8 xtrabackup]# xtrabackup --datadir=/data/mysql/mysql3307/data -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/full_backup_4 --parallel=16 
2024-03-20T20:57:12.528140+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/data/mysql/mysql3307/data 
2024-03-20T20:57:12.528265+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --password=* --backup=1 --socket=/data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/full_backup_4 --parallel=16 
xtrabackup version 8.0.33-28 based on MySQL server 8.0.33 Linux (x86_64) (revision id: b3a3c3dd)
Can't locate Data/Dumper.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 749.
BEGIN failed--compilation aborted at - line 749.
2024-03-20T20:57:12.555404+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /data/mysql/mysql3307/data/mysql3307.sock
2024-03-20T20:57:12.558311+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.18
2024-03-20T20:57:12.560249+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
………………………………………………
2024-03-20T20:27:15.078976+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (20496193) to (20496203) was copied.
2024-03-20T20:27:15.291166+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK

3、[root@mysql8 mysql3307]# xtrabackup  -uroot -pmysql -P3307 --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/full_backup_3 --parallel=16 
2024-03-20T20:53:11.116058+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --password=* --port=3307 --backup=1 --socket=/data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/full_backup_3 --parallel=16 
xtrabackup version 8.0.33-28 based on MySQL server 8.0.33 Linux (x86_64) (revision id: b3a3c3dd)
Can't locate Data/Dumper.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 749.
BEGIN failed--compilation aborted at - line 749.
2024-03-20T20:53:11.151123+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3307, socket: /data/mysql/mysql3307/data/mysql3307.sock
2024-03-20T20:53:11.154679+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.18
2024-03-20T20:53:11.158725+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...

4、[root@mysql8 xtrabackup]# xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/full_backup_10 --parallel=4 --compress --compress-threads=2 
2024-03-20T22:15:30.149891+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/data/mysql/mysql3307/data --tmpdir=/data/mysql/mysql3307/tmp 
2024-03-20T22:15:30.150774+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3307 --user=root --password=* --backup=1 --socket=/data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/full_backup_10 --parallel=4 --compress --compress-threads=2 
xtrabackup version 8.0.33-28 based on MySQL server 8.0.33 Linux (x86_64) (revision id: b3a3c3dd)
Can't locate Data/Dumper.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 749.
BEGIN failed--compilation aborted at - line 749.
2024-03-20T22:15:30.188730+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3307, socket: /data/mysql/mysql3307/data/mysql3307.sock
2024-03-20T22:15:30.194302+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.18
2024-03-20T22:15:30.199827+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...

5、[root@mysql8 xtrabackup]# xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/ --parallel=4 --compress --compress-threads=2 --stream=xbstream  >/mysql/backup/xtrabackup/full_backup_9.xbstream
2024-03-20T22:17:26.233200+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/data/mysql/mysql3307/data --tmpdir=/data/mysql/mysql3307/tmp 
2024-03-20T22:17:26.233474+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3307 --user=root --password=* --backup=1 --socket=/data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/ --parallel=4 --compress --compress-threads=2 --stream=xbstream 
xtrabackup version 8.0.33-28 based on MySQL server 8.0.33 Linux (x86_64) (revision id: b3a3c3dd)
Can't locate Data/Dumper.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 749.
BEGIN failed--compilation aborted at - line 749.
2024-03-20T22:17:26.281646+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3307, socket: /data/mysql/mysql3307/data/mysql3307.sock
2024-03-20T22:17:26.286485+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.18
2024-03-20T22:17:26.289883+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...

全量恢复

解压

[root@mysql8 xtrabackup]# mkdir back_9
[root@mysql8 xtrabackup]# xbstream -x  --decompress --decompress-threads=2 -C /mysql/backup/xtrabackup/back_9 < /mysql/backup/xtrabackup/full_backup_9.xbstream

[root@mysql8 xtrabackup]# xbstream -x  --decompress --decompress-threads=2 -C /mysql/backup/xtrabackup/back_9 < /mysql/backup/xtrabackup/full_backup_9.xbstream 
[root@mysql8 xtrabackup]# ll back_9
total 57392
-rw-r----- 1 root root      447 Mar 25 16:36 backup-my.cnf
-rw-r----- 1 root root      155 Mar 25 16:36 binlog.000027
-rw-r----- 1 root root       16 Mar 25 16:36 binlog.index
-rw-r----- 1 root root     3379 Mar 25 16:36 ib_buffer_pool
-rw-r----- 1 root root 12582912 Mar 25 16:36 ibdata1
drwxr-x--- 2 root root      143 Mar 25 16:36 mysql
-rw-r----- 1 root root 25165824 Mar 25 16:36 mysql.ibd
drwxr-x--- 2 root root     8192 Mar 25 16:36 performance_schema
drwxr-x--- 2 root root       28 Mar 25 16:36 sys
drwxr-x--- 2 root root       38 Mar 25 16:36 test
-rw-r----- 1 root root 10485760 Mar 25 16:36 undo_001
-rw-r----- 1 root root 10485760 Mar 25 16:36 undo_002
-rw-r----- 1 root root       18 Mar 25 16:36 xtrabackup_binlog_info
-rw-r----- 1 root root      134 Mar 25 16:36 xtrabackup_checkpoints
-rw-r----- 1 root root      648 Mar 25 16:36 xtrabackup_info
-rw-r----- 1 root root     2560 Mar 25 16:36 xtrabackup_logfile
-rw-r----- 1 root root       39 Mar 25 16:36 xtrabackup_tablespaces

恢复

不建议在原环境直接恢复，会覆盖掉data你内容，建议新环境中恢复，然后在原环境补充数据.
--perpare 应用redolog 将数据文件恢复到备份结束时的一致性状态，会生成redo log和临时表空间文件
--use-memory：启动嵌入的InnoDB实例来进行crash recovery ，这个实例缓冲池的大小--use-memory，默认100MB，加快prepare速度

xtrabackup --prepare --use-memory=2G --target-dir=/mysql/backup/xtrabackup/back_9

--拷贝数据文件到从cnf文件中读取datadir路径，也可以指定--datadir -datadir必须为空
--copy-back：拷贝备份文件到datadir
--move-back：移动备份文件到datadir

systemctl stop mysql8

不建议在原环境直接恢复，会覆盖掉data内容，建议新环境中恢复，然后在原环境补充数据.
mv /data/mysql/mysql3307/data /data/mysql/mysql3307/data_bak1

xtrabackup  --defaults-file=/data/mysql/mysql3307/my3307.cnf --copy-back --target-dir=/mysql/backup/xtrabackup/back_9

chown -R mysql:mysql /data/mysql/mysql3307/data

chmod -R 755 /data/mysql/mysql3307/data

systemctl start mysql8

[root@mysql8 mysql3306]# xtrabackup --prepare --target-dir=/mysql/backup/xtrabackup/back_9
2024-03-25T16:54:44.866360+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 
2024-03-25T16:54:44.866461+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=/mysql/backup/xtrabackup/back_9 
xtrabackup version 8.0.33-28 based on MySQL server 8.0.33 Linux (x86_64) (revision id: b3a3c3dd)
2024-03-25T16:54:44.866484+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /mysql/backup/xtrabackup/back_9/
2024-03-25T16:54:44.866534+08:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be not prepared yet.
2024-03-25T16:54:44.869820+08:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile detected: size=8388608, start_lsn=(20496469)
2024-03-25T16:54:44.884561+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2024-03-25T16:54:44.884653+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2024-03-25T16:54:44.884664+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2024-03-25T16:54:44.884724+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2024-03-25T16:54:44.884740+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2024-03-25T16:54:44.884768+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2024-03-25T16:54:44.885216+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2024-03-25T16:54:44.885388+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2024-03-25T16:54:44.885400+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2024-03-25T16:54:44.885406+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2024-03-25T16:54:44.885419+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2024-03-25T16:54:44.885425+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2024-03-25T16:54:44.885430+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2024-03-25T16:54:44.885438+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting InnoDB instance for recovery.
2024-03-25T16:54:44.885445+08:00 0 [Note] [MY-011825] [Xtrabackup] Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2024-03-25T16:54:44.885471+08:00 0 [Note] [MY-012932] [InnoDB] PUNCH HOLE support available
2024-03-25T16:54:44.885485+08:00 0 [Note] [MY-012944] [InnoDB] Uses event mutexes
2024-03-25T16:54:44.885490+08:00 0 [Note] [MY-012945] [InnoDB] GCC builtin __atomic_thread_fence() is used for memory barrier
2024-03-25T16:54:44.885499+08:00 0 [Note] [MY-012948] [InnoDB] Compressed tables use zlib 1.2.13
2024-03-25T16:54:44.885759+08:00 0 [Note] [MY-012951] [InnoDB] Using hardware accelerated crc32 and polynomial multiplication.
2024-03-25T16:54:44.885973+08:00 0 [Note] [MY-012203] [InnoDB] Directories to scan './'
2024-03-25T16:54:44.886004+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2024-03-25T16:54:44.977411+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 6 files.
2024-03-25T16:54:44.977763+08:00 0 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M 
2024-03-25T16:54:44.982911+08:00 0 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool
2024-03-25T16:54:45.158440+08:00 0 [Note] [MY-011951] [InnoDB] page_cleaner coordinator priority: -20
2024-03-25T16:54:45.194563+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2024-03-25T16:54:45.227770+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2024-03-25T16:54:45.244160+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2024-03-25T16:54:45.301880+08:00 0 [Note] [MY-013883] [InnoDB] The latest found checkpoint is at lsn = 20496469 in redo log file ./#innodb_redo/#ib_redo0.
2024-03-25T16:54:45.301941+08:00 0 [Note] [MY-012560] [InnoDB] The log sequence number 20466995 in the system tablespace does not match the log sequence number 20496469 in the redo log files!
2024-03-25T16:54:45.301969+08:00 0 [Note] [MY-012551] [InnoDB] Database was not shutdown normally!
2024-03-25T16:54:45.301975+08:00 0 [Note] [MY-012552] [InnoDB] Starting crash recovery.
2024-03-25T16:54:45.314417+08:00 0 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 20496399, whereas checkpoint_lsn = 20496469 and start_lsn = 20496384
2024-03-25T16:54:45.314460+08:00 0 [Note] [MY-012550] [InnoDB] Doing recovery: scanned up to log sequence number 20496469
2024-03-25T16:54:45.370773+08:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2024-03-25T16:54:45.799267+08:00 0 [Note] [MY-012532] [InnoDB] Applying a batch of 0 redo log records ...
2024-03-25T16:54:45.799298+08:00 0 [Note] [MY-012535] [InnoDB] Apply batch completed!
2024-03-25T16:54:45.899421+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2024-03-25T16:54:45.911441+08:00 0 [Note] [MY-013888] [InnoDB] Upgrading redo log: 1032M, LSN=20496469.
2024-03-25T16:54:45.911479+08:00 0 [Note] [MY-012968] [InnoDB] Starting to delete and rewrite redo log files.
2024-03-25T16:54:45.911520+08:00 0 [Note] [MY-011825] [InnoDB] Removing redo log file: ./#innodb_redo/#ib_redo0
2024-03-25T16:54:45.971979+08:00 0 [Note] [MY-011825] [InnoDB] Creating redo log file at ./#innodb_redo/#ib_redo0_tmp with file_id 0 with size 33554432 bytes
2024-03-25T16:54:45.976159+08:00 0 [Note] [MY-011825] [InnoDB] Renaming redo log file from ./#innodb_redo/#ib_redo0_tmp to ./#innodb_redo/#ib_redo0
2024-03-25T16:54:45.977682+08:00 0 [Note] [MY-012893] [InnoDB] New redo log files created, LSN=20496908
2024-03-25T16:54:45.977746+08:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2024-03-25T16:54:46.430359+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2024-03-25T16:54:46.431763+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2024-03-25T16:54:46.458708+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2024-03-25T16:54:46.458769+08:00 0 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 7716
2024-03-25T16:54:46.858814+08:00 0 [Note] [MY-013777] [InnoDB] Time taken to initialize rseg using 1 thread: 400034 ms.
2024-03-25T16:54:46.858900+08:00 0 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables
2024-03-25T16:54:46.858946+08:00 0 [Note] [MY-012265] [InnoDB] Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-03-25T16:54:46.892304+08:00 0 [Note] [MY-012266] [InnoDB] File './ibtmp1' size is now 12 MB.
2024-03-25T16:54:46.892478+08:00 0 [Note] [MY-013627] [InnoDB] Scanning temp tablespace dir:'./#innodb_temp/'
2024-03-25T16:54:46.913999+08:00 0 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
2024-03-25T16:54:46.968663+08:00 0 [Note] [MY-012976] [InnoDB] 8.0.33 started; log sequence number 20496918
2024-03-25T16:54:46.970033+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
2024-03-25T16:54:47.007246+08:00 0 [Note] [MY-011825] [Xtrabackup] Completed loading of 4 tablespaces into cache in 0.0384792 seconds
2024-03-25T16:54:47.127728+08:00 0 [Note] [MY-011825] [Xtrabackup] Time taken to build dictionary: 0.120399 seconds
2024-03-25T16:54:47.187221+08:00 0 [Note] [MY-011825] [Xtrabackup] starting shutdown with innodb_fast_shutdown = 1
2024-03-25T16:54:47.187298+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting.
2024-03-25T16:54:48.159900+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown...
2024-03-25T16:54:48.261952+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2024-03-25T16:54:48.296607+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 20496918
2024-03-25T16:54:48.298397+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

[root@mysql8 mysql3307]# xtrabackup  --defaults-file=/data/mysql/mysql3307/my3307.cnf --copy-back --target-dir=/mysql/backup/xtrabackup/back_9
2024-03-25T17:00:34.158236+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/data/mysql/mysql3307/data --tmpdir=/data/mysql/mysql3307/tmp 
2024-03-25T17:00:34.158346+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3307 --copy-back=1 --target-dir=/mysql/backup/xtrabackup/back_9 
xtrabackup version 8.0.33-28 based on MySQL server 8.0.33 Linux (x86_64) (revision id: b3a3c3dd)
2024-03-25T17:00:34.158368+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /mysql/backup/xtrabackup/back_9/
2024-03-25T17:00:34.159104+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_001 to /data/mysql/mysql3307/data/undo_001
2024-03-25T17:00:34.170378+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_001 to /data/mysql/mysql3307/data/undo_001
2024-03-25T17:00:34.552395+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_002 to /data/mysql/mysql3307/data/undo_002
2024-03-25T17:00:34.600739+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_002 to /data/mysql/mysql3307/data/undo_002
2024-03-25T17:00:34.620462+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ibdata1 to /data/mysql/mysql3307/data/ibdata1
2024-03-25T17:00:34.985994+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ibdata1 to /data/mysql/mysql3307/data/ibdata1
2024-03-25T17:00:35.024106+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying binlog.000027 to /data/mysql/mysql3307/data/binlog.000027
2024-03-25T17:00:35.024307+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying binlog.000027 to /data/mysql/mysql3307/data/binlog.000027
2024-03-25T17:00:35.025767+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying binlog.index to /data/mysql/mysql3307/data/binlog.index
2024-03-25T17:00:35.025864+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying binlog.index to /data/mysql/mysql3307/data/binlog.index
2024-03-25T17:00:35.049700+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./sys/sys_config.ibd to /data/mysql/mysql3307/data/sys/sys_config.ib
……………………………………………………………………
2024-03-25T17:00:35.907870+08:00 1 [Note] [MY-011825] [Xtrabackup] Creating directory ./#innodb_redo
2024-03-25T17:00:35.907926+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: creating directory ./#innodb_redo
2024-03-25T17:00:35.907967+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ibtmp1 to /data/mysql/mysql3307/data/ibtmp1
2024-03-25T17:00:35.925304+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibtmp1 to /data/mysql/mysql3307/data/ibtmp1
2024-03-25T17:00:36.057956+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

[root@mysql8 mysql3307]# cat data/xtrabackup_info 
uuid = 9577ef6e-e6c4-11ee-8439-080027fc03d2
name = 
tool_name = xtrabackup
tool_command = --defaults-file=/data/mysql/mysql3307/my3307.cnf -uroot -p=... --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/ --parallel=4 --compress --compress-threads=2 --stream=xbstream
tool_version = 8.0.33-28
ibbackup_version = 8.0.33-28
server_version = 8.0.18
start_time = 2024-03-20 22:17:26
end_time = 2024-03-20 22:17:29
lock_time = 1
binlog_pos = filename 'binlog.000027', position '155'
innodb_from_lsn = 0
innodb_to_lsn = 20496469
partial = N
incremental = N
format = xbstream
compressed = compressed
encrypted = N

三、增量备份

xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/fullback_1 --parallel=16 2> /mysql/backup/xtrabackup/fullback_1.log

xtrabackup  --defaults-file=/data/mysql/mysql3307/my3307.cnf --no-server-version-check -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/inc_back_1 --parallel=16 --incremental-basedir=/mysql/backup/xtrabackup/fullback_1 2> /mysql/backup/xtrabackup/inc_back_1.log

–target-dir：增量备份目录

–incremental-basedir：全量备份目录，为了获取上次的LSN

–extra-lsndir：存放本次备份的xtrabackup_checkpoints，后面的增量备份时，–incremental-basedir就指向前一日的extra-lsndir目录便可

增量恢复

不建议在原环境直接恢复，会覆盖掉data内容，建议新环境中恢复，然后在原环境补充数据.
--perpare 应用redolog 将数据文件恢复到备份结束时的一致性状态，会生成redo log和临时表空间文件
--use-memory：启动嵌入的InnoDB实例来进行crash recovery ，这个实例缓冲池的大小--use-memory，默认100MB，加快prepare速度
--apply-log-only：只应用redo log，不回滚备份时未提交的事务，多次增量都需要指定，最后一次不需要

xtrabackup --no-server-version-check --prepare --apply-log-only --use-memory=2G --target-dir=/mysql/backup/xtrabackup/fullback_1

xtrabackup --no-server-version-check --prepare --apply-log-only --use-memory=2G --target-dir=/mysql/backup/xtrabackup/fullback_1 --incremental-dir=/mysql/backup/xtrabackup/inc_back_1

………………………………………………………………

xtrabackup --no-server-version-check --prepare  --use-memory=2G  --target-dir=/mysql/backup/xtrabackup/fullback_1 --incremental-dir=/mysql/backup/xtrabackup/inc_back_n

可查看全备的xtrabackup_checkpoints，发现lsn已变更为增量备份对应的lsn

--拷贝数据文件到从cnf文件中读取datadir路径，也可以指定--datadir -datadir必须为空
--copy-back：拷贝备份文件到datadir
--move-back：移动备份文件到datadir

systemctl stop mysql8

不建议在原环境直接恢复，会覆盖掉data内容，建议新环境中恢复，然后在原环境补充数据.
mv /data/mysql/mysql3307/data /data/mysql/mysql3307/data_bak1

xtrabackup  --defaults-file=/data/mysql/mysql3307/my3307.cnf --copy-back --parallel=10 --target-dir=/mysql/backup/xtrabackup/fullback_1

chown -R mysql:mysql /data/mysql/mysql3307/data

chmod -R 755 /data/mysql/mysql3307/data

systemctl start mysql8