xtrabackup搭建MySQL 8.0 主从复制
本次搭建的环境信息如下：
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 Resolution
Dependencies 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 k
perl-Digest noarch 1.17-245.el7 base 23 k
perl-Digest-MD5 x86_64 2.52-3.el7 base 30 k
Transaction 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
搭建复制最基础的参数：
| gtid_mode | ON |
| binlog_format | ROW |
那么GTID是什么东西哪？为什么在复制环境中这么重要？
他其实就是一串数字，给每个事务做个唯一标识，一般表示为：server_uuid：transaction_id,如：8e14e45d-4ff1-11ef-9808-000c29e3c118:1-6 。其中UUID(即server_uuid) 存于文件：
[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 - GPL
Copyright (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 source
Master_Host: 10.1.11.30
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: postgre-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
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: 0
Relay_Log_Space: 157
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: 2061
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.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 240806 14:22:03
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: f25d3a39-53a2-11ef-9508-000c29a318a4:1-4
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.35 sec)
mysql> show slave status\G
*************************** 1. row ***************************
......
Slave_IO_Running: Connecting
Slave_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 event
Master_Host: 10.1.11.30
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mybinlog.000004
Read_Master_Log_Pos: 737
Relay_Log_File: postgre-relay-bin.000002
Relay_Log_Pos: 911
Relay_Master_Log_File: mybinlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.......
Retrieved_Gtid_Set: f25d3a39-53a2-11ef-9508-000c29a318a4:5-6
Executed_Gtid_Set: f25d3a39-53a2-11ef-9508-000c29a318a4:1-6
Auto_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;
475
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
