xtrabackup搭建MySQL 8.0 主从复制

安装MySQL 8.0.37
安装xtrabackup
GTIDs
初始化从库
参考：
GTID概述
GTID相较与传统复制的优势
GTID自身存在哪些限制
GTID工作原理简单介绍
如何开启GTID复制
GTID与传统模式建立复制时候语句的不同点
- 传统复制
- GTID复制
GTID同步状态简单解析

本次搭建的环境信息如下：
OS版本:Red Hat Enterprise Linux Server release 7.6 (Maipo)
MySQL版本：Server version: 8.0.37
xtrabackup版本：xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)
在这里插入图片描述
本次采用物理备份和还原的方式来进行主从复制的初始化，相比于mysqldump，本方案适合数据量比较大的情况下，初始化很快，而且不像mysqldump的方式，从库不需要先进行初始化。

安装MySQL 8.0.37

groupadd mysql
useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -MN mysql
cd /opt
unxz mysql-8.0.37-linux-glibc2.17-x86_64.tar.xz 
tar -xvf mysql-8.0.37-linux-glibc2.17-x86_64.tar 
cd /usr/local
ln -s /opt/mysql-8.0.37-linux-glibc2.17-x86_64 mysql
chown -R mysql:mysql /usr/local/mysql/
mkdir -p /data/mysql/mysql3306/{data,logs,tmp}
mv /opt/my.cnf /data/mysql/mysql3306/my3306.cnf
chown -R mysql:mysql /usr/local/mysql/
chown -R mysql:mysql /data/mysql/mysql3306//usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &/usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3306.sock
alter user user() identified by 'mysql';

安装xtrabackup

xtrabackup是社区开源产品。但是它不是MySQL自带的工具，需要我们自己进行安装。下面就说下安装过程
1、下载安装包
由于xtrabackup有版本的区别，根据最佳实践，我们安装的MySQL版本为8.0.37，xtrabackup选择8.0以上最新版本即可，不选择2.4的版本，rpm安装，方便简单。
下载地址：https://www.percona.com/downloads

在这里插入图片描述

version:选择最新的
software：选择rpm，就选RHELx。如果是二进制，就选 Linux-Generic
2、安装，为了解决软件依赖问题，采用yum localinstall来安装
在这里插入图片描述
缺两个包:zstd和libev.so.4()(64bit)，镜像里面没有这两个包，我们手动下载匹配版本进行安装：

# rpm -ivh libev-4.15-7.el7.x86_64.rpm 
# rpm -ivh zstd-1.5.5-1.el7.x86_64.rpm

重新安装成功

[root@postgre opt]# yum localinstall percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm 
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Examining percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm: percona-xtrabackup-80-8.0.35-31.1.el7.x86_64
Marking percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package percona-xtrabackup-80.x86_64 0:8.0.35-31.1.el7 will be installed
--> Processing Dependency: perl(DBD::mysql) for package: percona-xtrabackup-80-8.0.35-31.1.el7.x86_64
--> Processing Dependency: perl(Digest::MD5) for package: percona-xtrabackup-80-8.0.35-31.1.el7.x86_64
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be installed
---> Package perl-Digest-MD5.x86_64 0:2.52-3.el7 will be installed
--> Processing Dependency: perl(Digest::base) >= 1.00 for package: perl-Digest-MD5-2.52-3.el7.x86_64
--> Running transaction check
---> Package perl-Digest.noarch 0:1.17-245.el7 will be installed
--> Finished Dependency ResolutionDependencies Resolved==========================================================================================================================================================================================Package                                     Arch                         Version                               Repository                                                           Size
==========================================================================================================================================================================================
Installing:percona-xtrabackup-80                       x86_64                       8.0.35-31.1.el7                       /percona-xtrabackup-80-8.0.35-31.1.el7.x86_64                       219 M
Installing for dependencies:perl-DBD-MySQL                              x86_64                       4.023-6.el7                           base                                                                140 kperl-Digest                                 noarch                       1.17-245.el7                          base                                                                 23 kperl-Digest-MD5                             x86_64                       2.52-3.el7                            base                                                                 30 kTransaction Summary
==========================================================================================================================================================================================
Install  1 Package (+3 Dependent packages)Total size: 219 M
Total download size: 193 k
Installed size: 219 M
Is this ok [y/d/N]: y
Downloading packages:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                     464 kB/s | 193 kB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.Installing : perl-DBD-MySQL-4.023-6.el7.x86_64                                                                                                                                      1/4 Installing : perl-Digest-1.17-245.el7.noarch                                                                                                                                        2/4 Installing : perl-Digest-MD5-2.52-3.el7.x86_64                                                                                                                                      3/4 Installing : percona-xtrabackup-80-8.0.35-31.1.el7.x86_64                                                                                                                           4/4 
base/productid                                                                                                                                                     | 1.6 kB  00:00:00     Verifying  : perl-Digest-1.17-245.el7.noarch                                                                                                                                        1/4 Verifying  : perl-Digest-MD5-2.52-3.el7.x86_64                                                                                                                                      2/4 Verifying  : percona-xtrabackup-80-8.0.35-31.1.el7.x86_64                                                                                                                           3/4 Verifying  : perl-DBD-MySQL-4.023-6.el7.x86_64                                                                                                                                      4/4 Installed:percona-xtrabackup-80.x86_64 0:8.0.35-31.1.el7                                                                                                                                          Dependency Installed:perl-DBD-MySQL.x86_64 0:4.023-6.el7                           perl-Digest.noarch 0:1.17-245.el7                           perl-Digest-MD5.x86_64 0:2.52-3.el7                          Complete!

xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)

GTIDs

[root@postgre data]# cat /data/mysql/mysql3306/data/auto.cnf 
[auto]
server-uuid=f25d3a39-53a2-11ef-9508-000c29a318a4

是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。GTID在一组复制中，全局唯一。
GTID用来代替传统复制方法，不再使用MASTER_LOG_FILE+MASTER_LOG_POS开启复制。而是使用MASTER_AUTO_POSTION=1的方式开始复制；从服务器连接到主服务器之后，把自己执行过的GTID (Executed_Gtid_Set: 即已经执行的事务编码)<SQL线程> 、获取到的GTID (Retrieved_Gtid_Set: 即从库已经接收到主库的事务编号) <IO线程>发给主服务器，主服务器把从服务器缺少的GTID及对应的transactions发过去补全即可。当主服务器挂掉的时候，找出同步最成功的那台从服务器，直接把它提升为主即可。如果硬要指定某一台不是最新的从服务器提升为主，先change到同步最成功的那台从服务器，等把GTID全部补全了，就可以把它提升为主了。

GTID是用区间来表示的事务：
第一个事务：8e14e45d-4ff1-11ef-9808-000c29e3c118:1
第二个事务：8e14e45d-4ff1-11ef-9808-000c29e3c118:1-2
第三个事务：8e14e45d-4ff1-11ef-9808-000c29e3c118:1-3
原则上来讲，主从结构的GTIDs是连续的，如果从库上的GTID是不连续，那么肯定就是丢数据了。
既然开启GTID较之前有很多的优势，那么在5.7值后，我们一般都建议开启GTID进行复制。

初始化从库

[root@postgre data]# xtrabackup --defaults-file=/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql3306.sock -uroot -p --backup --target-dir=/data/backup/db3306_full
2024-08-06T13:54:21.177384+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=3306 --datadir=/data/mysql/mysql3306/data --open_files_limit=65535 --log_bin=/data/mysql/mysql3306/data/mybinlog --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:12M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=1G --innodb_log_files_in_group=3 --innodb_io_capacity=4000 --innodb_open_files=65535 --innodb_flush_method=O_DIRECT --innodb_adaptive_hash_index=0 --innodb_adaptive_hash_index=0 
2024-08-06T13:54:21.177553+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/tmp/mysql3306.sock --socket=/tmp/mysql3306.sock --user=root --password --backup=1 --target-dir=/data/backup/db3306_full 
Enter password: 
xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)
240806 13:54:23  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql3306.sock' as 'root'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql3306.sock','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1535.
2024-08-06T13:54:23.640205+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql3306.sock
2024-08-06T13:54:23.655405+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.37
2024-08-06T13:54:23.681865+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2024-08-06T13:54:23.682950+08:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2024-08-06T13:54:23.682988+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/mysql/mysql3306/data
2024-08-06T13:54:23.683006+08:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 65535, set to 65535
........
performance_schema/keyring_componen_191.sdi to /data/backup/db3306_full/performance_schema/keyring_componen_191.sdi
2024-08-06T13:54:25.828058+08:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files
2024-08-06T13:54:25.828098+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
2024-08-06T13:54:25.884799+08:00 0 [Note] [MY-011825] [Xtrabackup] Selecting LSN and binary log position from p_s.log_status
2024-08-06T13:54:25.950500+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /data/mysql/mysql3306/data/mybinlog.000003 to /data/backup/db3306_full/mybinlog.000003 up to position 197
2024-08-06T13:54:25.950570+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /data/mysql/mysql3306/data/mybinlog.000003 to /data/backup/db3306_full/mybinlog.000003
2024-08-06T13:54:25.951697+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db3306_full/mybinlog.index
2024-08-06T13:54:25.951768+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db3306_full/mybinlog.index
2024-08-06T13:54:25.960548+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db3306_full/xtrabackup_binlog_info
2024-08-06T13:54:25.960619+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db3306_full/xtrabackup_binlog_info
2024-08-06T13:54:25.961518+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
2024-08-06T13:54:25.988881+08:00 0 [Note] [MY-011825] [Xtrabackup] The latest check point (for incremental): '20338027'
2024-08-06T13:54:25.988974+08:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 20338027
2024-08-06T13:54:25.991165+08:00 1 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 20337695
2024-08-06T13:54:26.016990+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE
2024-08-06T13:54:26.024865+08:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked
2024-08-06T13:54:26.025041+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ib_buffer_pool to /data/backup/db3306_full/ib_buffer_pool
2024-08-06T13:54:26.025488+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ib_buffer_pool to /data/backup/db3306_full/ib_buffer_pool
2024-08-06T13:54:26.026709+08:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory '/data/backup/db3306_full/'
2024-08-06T13:54:26.026735+08:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'mybinlog.000003', position '197', GTID of the last change 'f25d3a39-53a2-11ef-9508-000c29a318a4:1-4'
2024-08-06T13:54:26.036177+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db3306_full/backup-my.cnf
2024-08-06T13:54:26.036245+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db3306_full/backup-my.cnf
2024-08-06T13:54:26.078730+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db3306_full/xtrabackup_info
2024-08-06T13:54:26.078833+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db3306_full/xtrabackup_info
2024-08-06T13:54:27.088852+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (20338027) to (20338893) was copied.
2024-08-06T13:54:27.319018+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[root@postgre data]#

还原
–prepare的过程，为了避免影响原备份文件，可以先把备份文件拷贝到其他路径，进行prepare。

[root@postgre ~]# xtrabackup --prepare --target-dir=/data/backup/db3306_full
2024-08-06T14:05:25.906594+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=1073741824 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=3306 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 
2024-08-06T14:05:25.906778+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=/data/backup/db3306_full 
xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)
2024-08-06T14:05:25.906804+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/backup/db3306_full/
2024-08-06T14:05:25.906880+08:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be not prepared yet.
2024-08-06T14:05:25.920383+08:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile detected: size=8388608, start_lsn=(20338027)
2024-08-06T14:05:25.924383+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2024-08-06T14:05:25.924414+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2024-08-06T14:05:25.924420+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2024-08-06T14:05:25.924482+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2024-08-06T14:05:25.924492+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2024-08-06T14:05:25.924503+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2024-08-06T14:05:25.924769+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2024-08-06T14:05:25.925177+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2024-08-06T14:05:25.925192+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2024-08-06T14:05:25.925198+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2024-08-06T14:05:25.925209+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2024-08-06T14:05:25.925215+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2024-08-06T14:05:25.925220+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2024-08-06T14:05:25.925229+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting InnoDB instance for recovery.
2024-08-06T14:05:25.925235+08:00 0 [Note] [MY-011825] [Xtrabackup] Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2024-08-06T14:05:25.925265+08:00 0 [Note] [MY-012932] [InnoDB] PUNCH HOLE support available
2024-08-06T14:05:25.925280+08:00 0 [Note] [MY-012944] [InnoDB] Uses event mutexes
2024-08-06T14:05:25.925286+08:00 0 [Note] [MY-012945] [InnoDB] GCC builtin __atomic_thread_fence() is used for memory barrier
2024-08-06T14:05:25.925296+08:00 0 [Note] [MY-012948] [InnoDB] Compressed tables use zlib 1.2.13
2024-08-06T14:05:25.925464+08:00 0 [Note] [MY-012951] [InnoDB] Using hardware accelerated crc32 and polynomial multiplication.
2024-08-06T14:05:25.925838+08:00 0 [Note] [MY-012203] [InnoDB] Directories to scan './'
2024-08-06T14:05:25.925873+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2024-08-06T14:05:25.928700+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 5 files.
2024-08-06T14:05:25.929854+08:00 0 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M 
2024-08-06T14:05:25.936379+08:00 0 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool
2024-08-06T14:05:26.059786+08:00 0 [Note] [MY-011951] [InnoDB] page_cleaner coordinator priority: -20
2024-08-06T14:05:26.121777+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2024-08-06T14:05:26.156695+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2024-08-06T14:05:26.187928+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2024-08-06T14:05:26.279548+08:00 0 [Note] [MY-013883] [InnoDB] The latest found checkpoint is at lsn = 20338027 in redo log file ./#innodb_redo/#ib_redo0.
2024-08-06T14:05:26.280684+08:00 0 [Note] [MY-012560] [InnoDB] The log sequence number 19876654 in the system tablespace does not match the log sequence number 20338027 in the redo log files!
2024-08-06T14:05:26.280706+08:00 0 [Note] [MY-012551] [InnoDB] Database was not shutdown normally!
2024-08-06T14:05:26.280716+08:00 0 [Note] [MY-012552] [InnoDB] Starting crash recovery.
2024-08-06T14:05:26.291356+08:00 0 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 20337695, whereas checkpoint_lsn = 20338027 and start_lsn = 20337664
2024-08-06T14:05:26.291382+08:00 0 [Note] [MY-012550] [InnoDB] Doing recovery: scanned up to log sequence number 20338027
2024-08-06T14:05:26.308762+08:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2024-08-06T14:05:26.565440+08:00 0 [Note] [MY-012532] [InnoDB] Applying a batch of 0 redo log records ...
2024-08-06T14:05:26.565476+08:00 0 [Note] [MY-012535] [InnoDB] Apply batch completed!
2024-08-06T14:05:26.666404+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2024-08-06T14:05:26.673366+08:00 0 [Note] [MY-013888] [InnoDB] Upgrading redo log: 1032M, LSN=20338027.
2024-08-06T14:05:26.673404+08:00 0 [Note] [MY-012968] [InnoDB] Starting to delete and rewrite redo log files.
2024-08-06T14:05:26.673476+08:00 0 [Note] [MY-011825] [InnoDB] Removing redo log file: ./#innodb_redo/#ib_redo0
2024-08-06T14:05:26.738599+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-08-06T14:05:26.738808+08:00 0 [Note] [MY-013623] [InnoDB] fallocate() failed with errno 95 - falling back to writing NULLs.
2024-08-06T14:05:26.802147+08:00 0 [Note] [MY-011825] [InnoDB] Renaming redo log file from ./#innodb_redo/#ib_redo0_tmp to ./#innodb_redo/#ib_redo0
2024-08-06T14:05:26.808247+08:00 0 [Note] [MY-012893] [InnoDB] New redo log files created, LSN=20338188
2024-08-06T14:05:26.808323+08:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2024-08-06T14:05:27.065327+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2024-08-06T14:05:27.066124+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2024-08-06T14:05:27.067420+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2024-08-06T14:05:27.067537+08:00 0 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 1567
2024-08-06T14:05:27.113147+08:00 0 [Note] [MY-013777] [InnoDB] Time taken to initialize rseg using 1 thread: 38436 ms.
2024-08-06T14:05:27.116099+08:00 0 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables
2024-08-06T14:05:27.116255+08:00 0 [Note] [MY-012265] [InnoDB] Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-08-06T14:05:27.135449+08:00 0 [Note] [MY-012266] [InnoDB] File './ibtmp1' size is now 12 MB.
2024-08-06T14:05:27.135653+08:00 0 [Note] [MY-013627] [InnoDB] Scanning temp tablespace dir:'./#innodb_temp/'
2024-08-06T14:05:27.148888+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-08-06T14:05:27.180331+08:00 0 [Note] [MY-012976] [InnoDB] 8.0.35 started; log sequence number 20338198
2024-08-06T14:05:27.180953+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
2024-08-06T14:05:27.181888+08:00 0 [Note] [MY-011825] [Xtrabackup] Completed loading of 3 tablespaces into cache in 0.00126516 seconds
2024-08-06T14:05:27.205447+08:00 0 [Note] [MY-011825] [Xtrabackup] Time taken to build dictionary: 0.0235197 seconds
2024-08-06T14:05:27.249975+08:00 0 [Note] [MY-011825] [Xtrabackup] starting shutdown with innodb_fast_shutdown = 1
2024-08-06T14:05:27.250190+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting.
2024-08-06T14:05:28.227326+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown...
2024-08-06T14:05:28.332511+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2024-08-06T14:05:28.349406+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 20338198
2024-08-06T14:05:28.356359+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

–copy-back，此处是把文件从备份处拷贝到datadir下，所以指定了3308的配置文件，里面有datadir具体的路径。

[root@postgre db3306_full]# xtrabackup --defaults-file=/data/mysql/mysql3308/my3308.cnf --copy-back --target-dir=/data/backup/db3306_full/
2024-08-06T14:10:35.140533+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=3308 --datadir=/data/mysql/mysql3308/data --open_files_limit=65535 --log_bin=/data/mysql/mysql3308/data/mybinlog --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:12M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=1G --innodb_log_files_in_group=3 --innodb_io_capacity=4000 --innodb_open_files=65535 --innodb_flush_method=O_DIRECT --innodb_adaptive_hash_index=0 --innodb_adaptive_hash_index=0 
2024-08-06T14:10:35.140688+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3308 --socket=/tmp/mysql3308.sock --copy-back=1 --target-dir=/data/backup/db3306_full/ 
xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)
2024-08-06T14:10:35.140711+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/backup/db3306_full/
2024-08-06T14:10:35.141507+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_001 to /data/mysql/mysql3308/data/undo_001
2024-08-06T14:10:35.200687+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_001 to /data/mysql/mysql3308/data/undo_001
2024-08-06T14:10:35.209907+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_002 to /data/mysql/mysql3308/data/undo_002
2024-08-06T14:10:35.243725+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_002 to /data/mysql/mysql3308/data/undo_002
2024-08-06T14:10:35.260550+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ibdata1 to /data/mysql/mysql3308/data/ibdata1
......
2024-08-06T14:10:35.557160+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./xtrabackup_info to /data/mysql/mysql3308/data/xtrabackup_info
2024-08-06T14:10:35.557220+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./xtrabackup_info to /data/mysql/mysql3308/data/xtrabackup_info
2024-08-06T14:10:35.557845+08:00 1 [Note] [MY-011825] [Xtrabackup] Creating directory ./#innodb_redo
2024-08-06T14:10:35.557876+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: creating directory ./#innodb_redo
2024-08-06T14:10:35.557954+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ibtmp1 to /data/mysql/mysql3308/data/ibtmp1
2024-08-06T14:10:35.595855+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibtmp1 to /data/mysql/mysql3308/data/ibtmp1
2024-08-06T14:10:35.618690+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

启动

 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &[root@postgre data]# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3308.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show master status;
+-----------------+----------+--------------+------------------+------------------------------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-----------------+----------+--------------+------------------+------------------------------------------+
| mybinlog.000004 |      197 |              |                  | f25d3a39-53a2-11ef-9508-000c29a318a4:1-4 |
+-----------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

xtrabackup还原后，可以看到从库的GTID信息已经有了，而且是和主库一致的，那么此时我们就可以直接change

mysql> change master to master_host='10.1.11.30', master_port=3306, master_user='repl', master_password='repl',master_auto_position=1;
Query OK, 0 rows affected, 8 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.37 sec)mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Connecting to sourceMaster_Host: 10.1.11.30Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: Read_Master_Log_Pos: 4Relay_Log_File: postgre-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: Slave_IO_Running: ConnectingSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 0Relay_Log_Space: 157Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 2061Last_IO_Error: Error connecting to source 'repl@10.1.11.30:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0Master_UUID: Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: 240806 14:22:03Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: f25d3a39-53a2-11ef-9508-000c29a318a4:1-4Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 
1 row in set, 1 warning (0.35 sec)mysql> show slave status\G
*************************** 1. row ***************************
......Slave_IO_Running: ConnectingSlave_SQL_Running: Yes
.....Last_IO_Error: Error connecting to source 'repl@10.1.11.30:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
......Retrieved_Gtid_Set: Executed_Gtid_Set: f25d3a39-53a2-11ef-9508-000c29a318a4:1-4
.....
1 row in set, 1 warning (0.07 sec)

直接start slave，会报错。这是MySQL 8.0新的密码机制决定的，我们手动登录一次即可或者change加参数GET_MASTER_PUBLIC_KEY=1或者改为原始的认证方式mysql_native_passwor ,三者选其一即可。
我们采用第二种：

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.07 sec)mysql> change master to Get_master_public_key=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.12 sec)

恢复正常:

mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 10.1.11.30Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mybinlog.000004Read_Master_Log_Pos: 737Relay_Log_File: postgre-relay-bin.000002Relay_Log_Pos: 911Relay_Master_Log_File: mybinlog.000004Slave_IO_Running: YesSlave_SQL_Running: Yes
.......Retrieved_Gtid_Set: f25d3a39-53a2-11ef-9508-000c29a318a4:5-6Executed_Gtid_Set: f25d3a39-53a2-11ef-9508-000c29a318a4:1-6Auto_Position: 1
......Get_master_public_key: 1

还有一点，在上面start slave之前，状态是这样的：
在这里插入图片描述
Executed_Gtid_Set:表示从库已经执行了的GTID，就跟前面一致，现在从库的GTID已经执行到4 seq#,，如果此时主库的GTID执行比4大，那么Retrieved_Gtid_Set就会显示新的值，比4大的，从库缺少的从4以来的记录。

参考：

MySQL主从复制之GTID模式介绍

GTID概述

MySQL5.6 在原有主从复制的基础上增加了一个新的复制方式，即基于GTID的复制方式，它由UUID和事务ID两个部分组成，具有如下特点。
GTID事务是全局唯一性的，并且一个事务对应一个GTID值。
一个GTID值在同一个MySQL实例上只会执行一次。

GTID相较与传统复制的优势

主从搭建更加简便，不用手动特地指定position位置。
复制集群内有一个统一的标识，识别、管理上更方便。
故障转移更容易，不用像传统复制那样需要找 log_file 和 log_Pos的位置。
通常情况下GTID是连续没有空洞的，更能保证数据的一致性，零丢失。
相对于ROW复制模式，数据安全性更高，切换更简单。
比传统的复制更加安全，一个GTID在一个MySQL实例上只会执行一次，避免重复执行导致数据混乱或者主从不一致。

GTID自身存在哪些限制

在一个复制组中，必须都要开启GTID。
MySQL5.6开启GTID需要重启。
不支持sql_slave_skip_counte操作，传统复制可以使用这个命令跳过事务。
不允许在一个SQL同时更新一个事务引擎和非事务引擎的表，如InnoDB和MyISAM。
对于create temporary table 和drop temporary table语句不支持。
不支持create table … select 语句复制。

GTID工作原理简单介绍

master节点在更新数据的时候，会在事务前产生GTID信息，一同记录到binlog日志中。
slave节点的io线程将binlog写入到本地relay log中。
然后SQL线程从relay log中读取GTID，设置gtid_next的值为该gtid，然后对比slave端的binlog是否有记录。
如果有记录的话，说明该GTID的事务已经运行，slave会忽略。
如果没有记录的话，slave就会执行该GTID对应的事务，并记录到binlog中。

如何开启GTID复制

除传统复制需要开启的binlog相关参数之外，GTID同步需额外开启如下参数设置，注意主从节点需要同步开启。
在这里插入图片描述

查看GTID相关参数
在这里插入图片描述

参数简要说明
在这里插入图片描述

GTID与传统模式建立复制时候语句的不同点

传统复制

change master to master_host=“127.0.0.1”,master_port=3310,MASTER_USER=‘sync’,MASTER_PASSWORD=‘GreatSQL’,MASTER_LOG_FILE=‘log-bin.000005’, MASTER_LOG_POS=4111;

GTID复制

change master to master_host=“127.0.0.1”,master_port=3310,MASTER_USER=‘sync’,MASTER_PASSWORD=‘GreatSQL’,MASTER_AUTO_POSITION=1
GTID同步在建立复制的时候，将传统复制由人为指定binlog的pos位点改为了MASTER_AUTO_POSITION=1自动获取binlog的pos位点。

GTID同步状态简单解析

除了传统的查看binlog和pos值之外，GTID模式可以更直观的查看某个事务执行的情况。

[root@GreatSQL][(none)]>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.6.215
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 2425
Relay_Log_File: mgr2-relay-bin.000002
Relay_Log_Pos: 2634
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2425
Relay_Log_Space: 2842
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2153306
Master_UUID: 613743f5-8b1c-11ec-9922-00155dcff911
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 613743f5-8b1c-11ec-9922-00155dcff911:1-10
Executed_Gtid_Set: 613743f5-8b1c-11ec-9922-00155dcff911:1-10,
652ade08-8b1c-11ec-9f62-00155dcff90a:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified
GTID相关键参数说明
在这里插入图片描述

整个过程如下：

groupadd mysql
useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -MN mysql
cd /opt
unxz mysql-8.0.37-linux-glibc2.17-x86_64.tar.xz 
tar -xvf mysql-8.0.37-linux-glibc2.17-x86_64.tar 
cd /usr/local
ln -s /opt/mysql-8.0.37-linux-glibc2.17-x86_64 mysql
chown -R mysql:mysql /usr/local/mysql/
mkdir -p /data/mysql/mysql3306/{data,logs,tmp}
mv /opt/my.cnf /data/mysql/mysql3306/my3306.cnf
chown -R mysql:mysql /usr/local/mysql/
chown -R mysql:mysql /data/mysql/mysql3306//usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &/usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3306.sock
alter user user() identified by 'mysql';
show databases;
create database test;
use test;
create table test(id int,name char);
insert into test values(1,'a');
create user repl@'%' identified by 'repl';
grant replication slave on *.* to repl@'%';cd /data/
mkdir -p backup/db3306_full/
xtrabackup --defaults-file=/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql3306.sock -uroot -p --backup --target-dir=/data/backup/db3306_full
xtrabackup --prepare --target-dir=/data/backup/db3306_full
mkdir -p /data/mysql/mysql3308/{data,logs,tmp}
cp /data/mysql/mysql3306/my3306.cnf /data/mysql/mysql3308/my3308.cnf
sed -i 's/3306/3308/g' /data/mysql/mysql3308/my3308.cnf
xtrabackup --defaults-file=/data/mysql/mysql3308/my3308.cnf --copy-back --target-dir=/data/backup/db3306_full/
chown -R mysql:mysql /data/mysql/mysql3308//usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
/usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3308.sock
/usr/local/mysql/bin/mysql -h 10.1.11.30 -P3306 -urepl -prepl
change master to master_host='10.1.11.30', master_port=3306, master_user='repl', master_password='repl',master_auto_position=1;
start slave;
show slave status\G或者不用登录一次，直接加参数解决：
change master to master_host='10.1.11.30', master_port=3306, master_user='repl', master_password='repl',master_auto_position=1 GET_MASTER_PUBLIC_KEY=1;