MySQL组复制的配置实践
第一步是部署三个MySQL Server实例。Group Replication是MySQL Server 5.7.17及更新版本提供的内置MySQL插件。
下面的过程使用一台物理机器，因此每个MySQL服务器实例都需要一个特定的数据目录。在名为mysqldata的目录中创建数据目录，并对每个目录进行初始化。
创建三个数据目录
[mysql@localhost mysqldata]$ mkdir mysql1 [mysql@localhost mysqldata]$ mkdir mysql2 [mysql@localhost mysqldata]$ mkdir mysql3 [mysql@localhost mysqldata]$ ll total 0 drwxr-xr-x. 2 mysql mysql 6 Jan 16 08:54 mysql1 drwxr-xr-x. 2 mysql mysql 6 Jan 16 08:55 mysql2 drwxr-xr-x. 2 mysql mysql 6 Jan 16 08:55 mysql3
创建三个实例的参数文件
[mysql@localhost mysql]# vi mysql1.cnf [mysqld] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql1 bind-address=0.0.0.0 user=mysql port=3306 log-error=/mysqldata/mysql1/mysql.err pid-file=/mysqldata/mysql1/mysqld.pid socket = /mysqldata/mysql1/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB [mysql@localhost mysql]$ vi mysql2.cnf [mysqld] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql2 bind-address=0.0.0.0 user=mysql port=3307 log-error=/mysqldata/mysql2/mysql.err pid-file=/mysqldata/mysql2/mysqld.pid socket = /mysqldata/mysql2/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB [mysql@localhost mysql]$ vi mysql3.cnf [mysqld] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql3 bind-address=0.0.0.0 user=mysql port=3308 log-error=/mysqldata/mysql3/mysql.err pid-file=/mysqldata/mysql3/mysqld.pid socket = /mysqldata/mysql3/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB
初始化三个实例
[root@localhost ~]# cd /mysqlsoft/mysql/bin [root@localhost bin]# ./mysqld --defaults-file=/mysqlsoft/mysql/mysql1.cnf --initialize -basedir=/mysqlsoft/mysql -- datadir=/mysqldata/mysql1 --user=mysql [root@localhost bin]# ./mysqld --defaults-file=/mysqlsoft/mysql/mysql2.cnf --initialize -basedir=/mysqlsoft/mysql -- datadir=/mysqldata/mysql2 --user=mysql [root@localhost bin]# ./mysqld --defaults-file=/mysqlsoft/mysql/mysql3.cnf --initialize -basedir=/mysqlsoft/mysql -- datadir=/mysqldata/mysql3 --user=mysql [mysql@localhost mysql1]$ cat mysql.err 2025-01-16T02:45:38.125723Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use -- explicit_defaults_for_timestamp server option (see documentation for more details). 2025-01-16T02:45:38.126190Z 0 [ERROR] Can't find error-message file 'asedir=/mysqlsoft/mysql/share/errmsg.sys'. Check error- message file location and 'lc-messages-dir' configuration directive. 2025-01-16T02:45:38.535031Z 0 [Warning] InnoDB: New log files created, LSN=45790 2025-01-16T02:45:38.597593Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2025-01-16T02:45:38.658289Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: f81625c0-d3b3-11ef-9c8d-005056a390e6. 2025-01-16T02:45:38.659975Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2025-01-16T02:45:38.661884Z 1 [Note] A temporary password is generated for root@localhost: LplkX((&j1&u [mysql@localhost mysql2]$ cat mysql.err 2025-01-16T02:45:51.143320Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use -- explicit_defaults_for_timestamp server option (see documentation for more details). 2025-01-16T02:45:51.143570Z 0 [ERROR] Can't find error-message file 'asedir=/mysqlsoft/mysql/share/errmsg.sys'. Check error- message file location and 'lc-messages-dir' configuration directive. 2025-01-16T02:45:51.439385Z 0 [Warning] InnoDB: New log files created, LSN=45790 2025-01-16T02:45:51.502171Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2025-01-16T02:45:51.561336Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ffc6ff15-d3b3-11ef-9e2d-005056a390e6. 2025-01-16T02:45:51.562821Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2025-01-16T02:45:51.564054Z 1 [Note] A temporary password is generated for root@localhost: OM,#w?fll2iT [mysql@localhost mysql3]$ cat mysql.err 2025-01-16T02:46:03.815721Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use -- explicit_defaults_for_timestamp server option (see documentation for more details). 2025-01-16T02:46:03.815972Z 0 [ERROR] Can't find error-message file 'asedir=/mysqlsoft/mysql/share/errmsg.sys'. Check error- message file location and 'lc-messages-dir' configuration directive. 2025-01-16T02:46:04.113214Z 0 [Warning] InnoDB: New log files created, LSN=45790 2025-01-16T02:46:04.171088Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2025-01-16T02:46:04.230517Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 07542900-d3b4-11ef-a088-005056a390e6. 2025-01-16T02:46:04.232171Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2025-01-16T02:46:04.233327Z 1 [Note] A temporary password is generated for root@localhost: wlh%+FHw(7Sk
如果想服务能够部署自动支持安全连接，使用mysql_ssl_rsa_setup工具来创建缺省SSL与RSA文件
[root@localhost bin]# ./mysql_ssl_rsa_setup --datadir=/mysqldata/mysql1 Generating a 2048 bit RSA private key ...........+++ ................................+++ writing new private key to 'ca-key.pem' ----- Generating a 2048 bit RSA private key .+++ .......................+++ writing new private key to 'server-key.pem' ----- Generating a 2048 bit RSA private key ...............................+++ ........................+++ writing new private key to 'client-key.pem' ----- [root@localhost bin]# ./mysql_ssl_rsa_setup --datadir=/mysqldata/mysql2 Generating a 2048 bit RSA private key ........................+++ ........................................................+++ writing new private key to 'ca-key.pem' ----- Generating a 2048 bit RSA private key ................................+++ ......................+++ writing new private key to 'server-key.pem' ----- Generating a 2048 bit RSA private key ........................+++ ......................................................................+++ writing new private key to 'client-key.pem' ----- [root@localhost bin]# ./mysql_ssl_rsa_setup --datadir=/mysqldata/mysql3 Generating a 2048 bit RSA private key ........................................................................................................+++ ...................+++ writing new private key to 'ca-key.pem' ----- Generating a 2048 bit RSA private key ..................................+++ .......................................................................+++ writing new private key to 'server-key.pem' ----- Generating a 2048 bit RSA private key ..............................................+++ ........................+++ writing new private key to 'client-key.pem' -----
配置启动脚本
[mysql@localhost mysql]$ cat my.cnf [mysqld_multi] mysqld=/mysqlsoft/mysql/bin/mysqld_safe mysqladmin =/mysqlsoft/mysql/bin/mysqladmin log =/mysqlsoft/mysql/mysqld_multi.log [mysqld1] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql1 bind-address=0.0.0.0 user=mysql port=3306 log-error=/mysqldata/mysql1/mysql.err pid-file=/mysqldata/mysql1/mysqld.pid socket = /mysqldata/mysql1/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB explicit_defaults_for_timestamp = true [mysqld2] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql2 bind-address=0.0.0.0 user=mysql port=3307 log-error=/mysqldata/mysql2/mysql.err pid-file=/mysqldata/mysql2/mysqld.pid socket = /mysqldata/mysql2/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB explicit_defaults_for_timestamp = true [mysqld3] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql3 bind-address=0.0.0.0 user=mysql port=3308 log-error=/mysqldata/mysql3/mysql.err pid-file=/mysqldata/mysql3/mysqld.pid socket = /mysqldata/mysql3/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB explicit_defaults_for_timestamp = true [mysql@localhost mysql]$ mysqld_multi start 1,2,3 [mysql@localhost mysql]$ tail -f mysqld_multi.log mysqld_multi log file version 2.16; run: Thu Jan 16 11:12:08 2025 Starting MySQL servers 2025-01-16T03:17:42.956897Z mysqld_safe Logging to '/mysqldata/mysql1/mysql.err'. 2025-01-16T03:17:42.968830Z mysqld_safe Logging to '/mysqldata/mysql3/mysql.err'. 2025-01-16T03:17:42.971621Z mysqld_safe Logging to '/mysqldata/mysql2/mysql.err'. 2025-01-16T03:17:42.978298Z mysqld_safe Logging to '/mysqldata/mysql1/mysql.err'. 2025-01-16T03:17:42.984997Z mysqld_safe Logging to '/mysqldata/mysql2/mysql.err'. 2025-01-16T03:17:42.999744Z mysqld_safe Logging to '/mysqldata/mysql3/mysql.err'. 2025-01-16T03:17:43.014252Z mysqld_safe A mysqld process already exists 2025-01-16T03:17:43.026350Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql3 2025-01-16T03:17:43.026973Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql2 2025-01-16T03:17:43.032460Z mysqld_safe A mysqld process already exists 2025-01-16T03:17:43.042893Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql2 2025-01-16T03:17:43.058374Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql3
组复制服务器设置
要安装和使用组复制插件，您必须正确配置MySQL服务器实例。建议将配置存储在实例的配置文件中。下面为mysql1服务器配置。
[mysql@localhost mysql]$ cat mysql1.cnf [mysqld] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql1 bind-address=0.0.0.0 user=mysql port=3306 log-error=/mysqldata/mysql1/mysql.err pid-file=/mysqldata/mysql1/mysqld.pid socket = /mysqldata/mysql1/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB
这些设置将MySQL服务器配置为使用前面创建的数据目录，以及服务器应该打开哪个端口并开始侦听传入的连接。
复制框架
以下设置根据“MySQL Group replication”的要求配置复制。
server_id=1 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog_format=ROW
这些设置将服务器配置为使用唯一标识符1，以启用全局事务标识符，并将复制元数据存储在系统表而不是文件中。此外，它指示服务器打开二进制日志记录，使用基于行的格式并禁用二进制日志事件校验和。
组复制设置
此时，mysql1.cnf文件确保配置了服务器，并指示在给定配置下实例化复制基础结构。以下部分为服务器配置组复制设置。
transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="2366d798-4dc1-421a-a9de-3c825bfada7d" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "127.0.0.1:24901" loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903" loose-group_replication_bootstrap_group= off
上面用于group_replication变量的loose前缀指示，如果在服务器启动时没有加载Group Replication插件，则服务器继续启动。
.第1行指示服务器，对于每个事务，它必须收集写集，并使用XXHASH64散列算法将其编码为散列。
.第2行告诉插件它要加入或创建的组的名称为“2366d798-4dc1-421a-a9de-3c825bfada7d”。
.第3行指示插件在服务器启动时不要自动启动操作。
.第4行告诉插件使用IP地址127.0.0.1或localhost和端口24901来处理来自组中其他成员的传入连接。
服务器在这个端口上监听成员到成员的连接。此端口绝对不能用于用户应用程序，必须在运行组复制时为组的不同成员之间的通信保留该端口。
group_replication_local_address配置的本地地址必须对所有组成员都可访问。例如，如果每个服务器实例都在不同的机器上，则使用该机器的IP和端口，例如10.0.0.1:33061。group_replication_local_address的推荐端口是33061，但是在本教程中，我们使用在一台机器上运行的三个服务器实例，因此使用端口24901到24903。
.第5行告诉插件，如果需要加入组，应该联系这些主机和端口上的以下成员。这些是种子成员，当该成员想要连接到组时使用。在加入时，服务器首先与其中一个（种子）联系，然后要求组重新配置以允许加入服务器被组接受。请注意，此选项不需要列出组中的所有成员，而是在此服务器希望加入组时应该联系的服务器列表。
启动组的服务器不使用此选项，因为它是初始服务器，因此它负责引导组。第二个服务器加入要求组中唯一的成员加入，然后扩展组。第三个服务器加入可以请求这两个服务器中的任何一个加入，然后这个群组再次扩展。后续服务器在加入时重复此过程。
当同时加入多个服务器时，确保它们指向已经在组中的种子成员。不要使用正在加入组的成员作为种子，因为他们在联系时可能还不在组中。
最好的做法是先启动bootstrap成员，然后让它创建组。然后将其作为其他加入的成员的种子成员。这确保在加入其他成员时形成一个组。
不支持创建群组并同时加入多个成员。它可能会起作用，但很可能是操作竞争，然后加入组的行为以错误或超时告终。
.第6行指示插件是否启动组
此选项在任何时候都只能在一个服务器实例上使用，通常是在您第一次引导组时（或者在整个组被关闭并重新启动的情况下）。如果您多次引导组，例如当多个服务器实例设置了此选项时，那么它们可能会创建一个人工分裂的大脑场景，其中存在两个具有相同名称的不同组。在第一个服务器实例联机后禁用此选项。
组中所有服务器的配置非常相似。需要修改每个服务器的具体信息（例如server_id、datadir、group_replication_local_address）。
用户凭证
“组复制”通过异步复制协议实现分布式恢复，在组成员加入组之前先同步组成员。分布式恢复进程依赖于一个名为group_replication_recovery的复制区域通道，该区域通道用于在组成员之间传输事务。因此，需要设置具有相应权限的复制用户，以便Group replication建立成员间直接恢复复制通道。
[mysql@localhost mysql]$ mysqld --defaults-file=/mysqlsoft/mysql/mysql1.cnf &
创建一个具有REPLICATION-SLAVE权限的MySQL用户。不应该在二进制日志中捕获此过程，以避免将更改传播到其他服务器实例。在下面的示例中，显示了用户rpl_user和密码password。在配置服务器时，请使用合适的用户名和密码。连接到服务器mysql1，发出以下语句：
[root@localhost bin]# mysql -h 10.138.130.250 -P 3306 -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.26-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER rpl_user@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec)
按照上述方式配置用户后，使用CHANGE MASTER TO语句配置服务器，以便在下一次需要从另一个成员恢复其状态时，为group_replication_recovery复制通道使用给定的凭据。执行以下命令，将rpl_user和password替换为创建用户时使用的值。
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.03 sec)
分布式恢复是加入组的服务器所采取的第一步。如果这些凭据设置不正确，服务器将无法运行恢复过程并获得与其他组成员的同步，因此最终无法加入组成员。类似地，如果成员不能通过服务器的主机名正确识别其他成员，则恢复过程可能会失败。建议运行MySQL的操作系统使用正确配置的唯一主机名，可以使用DNS或本地设置。这个主机名可以在performance_schema.replication_group_members表的Member_host列中进行验证。如果多个组成员外部化操作系统设置的默认主机名，则成员有可能无法解析到正确的成员地址，从而无法加入组。在这种情况下，使用report_host配置一个唯一的主机名，由每个服务器外部化。
启动组复制
配置并启动服务mysql1后，安装Group Replication插件。连接到服务器并发出以下命令：
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.06 sec)
重点:
mysql.session在加载组复制之前必须已经存在。mysql.ession在MySQL 5.7.19版本中被添加。如果您的数据字典是使用较早版本初始化的，则必须运行mysql_upgrade过程。如果不执行升级，则启动“组复制”失败，并提示错误信息
There was an error when trying to access the server with user: mysql.session@localhost. Make sure the user is present in the server and that mysql_upgrade was ran after a server update..
要检查插件是否已成功安装，请执行SHOW PLUGINS；然后检查输出。它应该显示如下内容：
mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 45 rows in set (0.01 sec)
要启动组，请指示服务器mysql1引导组，然后启动组复制。这个引导应该只由单个服务器完成，即启动组的服务器，并且只能一次。这就是为什么引导配置选项的值没有保存在配置文件中。如果将其保存在配置文件中，则重启后服务器将自动引导具有相同名称的第二个组。这将导致两个具有相同名称的不同组。同样的道理也适用于将此选项设置为ON时停止和重新启动插件。
mysql> SET GLOBAL group_replication_bootstrap_group=ON; Query OK, 0 rows affected (0.00 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (2.09 sec) mysql> SET GLOBAL group_replication_bootstrap_group=OFF; Query OK, 0 rows affected (0.00 sec)
一旦START GROUP_REPLICATION语句返回，这个组就已经启动了。你可以检查组现在已经创建，并且里面有一个成员：
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | f81625c0-d3b3-11ef-9c8d-005056a390e6 | mysqlcs | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 1 row in set (0.01 sec)
该表中的信息确认组中有一个具有唯一标识符f81625c0-d3b3-11ef-9c8d-005056a390e6的成员，它是ONLINE的，并且在mysqlcs上监听端口3306上的客户端连接。
为了演示服务器确实在一个组中，并且它能够处理负载，创建一个表并向其中添加一些内容。
mysql> CREATE DATABASE test; Query OK, 1 row affected (0.01 sec) mysql> USE test; Database changed mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t1 VALUES (1, 'Luis'); Query OK, 1 row affected (0.04 sec)
检查表t1和二进制日志的内容。
mysql> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 1 | Luis | +----+------+ 1 row in set (0.00 sec) mysql> SHOW BINLOG EVENTS; +---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+ | binlog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 | | binlog.000001 | 123 | Previous_gtids | 1 | 150 | | | binlog.000001 | 150 | Gtid | 1 | 211 | SET @@SESSION.GTID_NEXT= 'f81625c0-d3b3-11ef-9c8d-005056a390e6:1' | | binlog.000001 | 211 | Query | 1 | 301 | CREATE DATABASE test | | binlog.000001 | 301 | Gtid | 1 | 362 | SET @@SESSION.GTID_NEXT= 'f81625c0-d3b3-11ef-9c8d-005056a390e6:2' | | binlog.000001 | 362 | Query | 1 | 486 | use `test`; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL) | | binlog.000001 | 486 | Gtid | 1 | 547 | SET @@SESSION.GTID_NEXT= 'f81625c0-d3b3-11ef-9c8d-005056a390e6:3' | | binlog.000001 | 547 | Query | 1 | 615 | BEGIN | | binlog.000001 | 615 | Table_map | 1 | 658 | table_id: 108 (test.t1) | | binlog.000001 | 658 | Write_rows | 1 | 700 | table_id: 108 flags: STMT_END_F | | binlog.000001 | 700 | Xid | 1 | 727 | COMMIT /* xid=23 */ | +---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+ 11 rows in set (0.00 sec) 如上所述，创建了数据库和表对象，并将它们对应的DDL语句写入二进制日志。此外，还将数据插入表并写入二进制日志。下一节将说明二进制日志条目的重要性，当组增长时，当新成员试图赶上并联机时，将执行分布式恢复。 向组中添加实例 此时，组中有一个成员服务器mysql1，其中包含一些数据。现在是时候通过添加前面配置的另外两个服务器来扩展组了。 添加第二个实例 为了添加第二个实例，服务器mysql2，首先为它创建配置文件。该配置类似于服务器mysql1所使用的配置，除了数据目录的位置、mysql2将要侦听的端口或其server_id等内容。这些不同的行在下面的清单中突出显示。
[mysql@localhost mysql]$ vi mysql2.cnf [mysqld] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql2 bind-address=0.0.0.0 user=mysql port=3307 log-error=/mysqldata/mysql2/mysql.err pid-file=/mysqldata/mysql2/mysqld.pid socket = /mysqldata/mysql2/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB server_id=2 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog_format=ROW transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="2366d798-4dc1-421a-a9de-3c825bfada7d" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "127.0.0.1:24902" loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903" loose-group_replication_bootstrap_group= off
与服务器mysql1的过程类似，配置文件就绪后启动服务器
[mysql@localhost mysql]$ mysqld --defaults-file=/mysqlsoft/mysql/mysql2.cnf &
然后配置恢复凭据，如下所示。这些命令与将服务器mysql1设置为用户在组内共享时使用的命令相同。在mysql2上发表以下声明。
[root@localhost /]# mysql -h 10.138.130.250 -P 3307 -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.26-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER rpl_user@'%'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'password'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.02 sec)
安装Group Replication插件并启动将服务器加入组的过程。下面的示例以与部署服务器mysql1时相同的方式安装插件。
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.01 sec) mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 45 rows in set (0.01 sec)
将服务器mysql2加入组。
mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (5.85 sec)
与之前在mysql1在执行的步骤不相同，差异在于没有执行SET GLOBAL group_replication_bootstrap_group=ON；在启动组复制之前，因为组已经创建并且由服务器mysql1启动。所以这时服务器mysql2只需要被加入到现有的组中。
当组复制成功启动并且服务器加入组时，它会检查super_read_only变量。通过在成员的配置文件中将super_read_only设置为ON，可以确保在启动Group Replication时由于任何原因而失败的服务器不接受事务。如果服务器应该作为读写实例加入组，例如作为单主组中的主实例或作为多主组的成员，当super_read_only变量设置为ON时，则在加入组时将其设置为OFF。
检查performance_schema.replication_group_members表再次显示，现在组中有两个ONLINE服务器。
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | f81625c0-d3b3-11ef-9c8d-005056a390e6 | mysqlcs | 3306 | ONLINE | | group_replication_applier | ffc6ff15-d3b3-11ef-9e2d-005056a390e6 | mysqlcs | 3307 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 2 rows in set (0.00 sec)
由于服务器mysql2也被标记为ONLINE，它一定已经自动赶上了服务器mysql1。验证它确实已与服务器mysql1同步，如下所示。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> select * from test.t1; +----+------+ | c1 | c2 | +----+------+ | 1 | Luis | +----+------+ 1 row in set (0.00 sec) mysql> SHOW BINLOG EVENTS; +---------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+----------------+-----------+-------------+---------------------------------------+ | binlog.000001 | 4 | Format_desc | 2 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 | | binlog.000001 | 123 | Previous_gtids | 2 | 150 | | +---------------+-----+----------------+-----------+-------------+---------------------------------------+ 2 rows in set (0.00 sec)
如上所述，第二台服务器已添加到组中，并且它自动复制了服务器mysql1的更改。根据分布式恢复过程，这意味着在加入组之后，在被声明为在线之前，服务器mysql2已经自动连接到服务器mysql1，并从中获取丢失的数据。换句话说，它从它缺失的二进制日志mysql1中复制事务，直到它加入组的时间点。
添加其他实例
向组中添加更多实例的步骤基本上与添加第二台服务器时相同，只是配置需要像为服务器mysql2 操作那样进行更改。要总结所需的命令：
1.修改配置文件
[mysql@localhost mysql]$ vi mysql3.cnf [mysqld] basedir=/mysqlsoft/mysql datadir=/mysqldata/mysql3 bind-address=0.0.0.0 user=mysql port=3308 log-error=/mysqldata/mysql3/mysql.err pid-file=/mysqldata/mysql3/mysqld.pid socket = /mysqldata/mysql3/mysql.sock character-set-server=utf8mb4 default-storage-engine=INNODB server_id=3 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog_format=ROW transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="2366d798-4dc1-421a-a9de-3c825bfada7d" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "127.0.0.1:24903" loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903" loose-group_replication_bootstrap_group= off
2.启动服务器
[mysql@localhost mysql]$ mysqld --defaults-file=/mysqlsoft/mysql/mysql3.cnf &
3.配置group_replication_recovery通道的恢复凭据。
[root@mysqlcs ~]# mysql -h 10.138.130.250 -P 3308 -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.26-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER rpl_user@'%'; ERROR 1396 (HY000): Operation CREATE USER failed for 'rpl_user'@'%' mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.02 sec)
4.安装Group Replication插件并启动它。
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.02 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (3.29 sec)
此时，服务器mysql3已经启动并运行，加入了组，并赶上了组中的其他服务器。查询performance_schema。Replication_group_members表再次
证实了这一点。
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 07542900-d3b4-11ef-a088-005056a390e6 | mysqlcs | 3308 | ONLINE | | group_replication_applier | f81625c0-d3b3-11ef-9c8d-005056a390e6 | mysqlcs | 3306 | ONLINE | | group_replication_applier | ffc6ff15-d3b3-11ef-9e2d-005056a390e6 | mysqlcs | 3307 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec)
在服务器mysql2或服务器mysql1上执行相同的查询会产生相同的结果。此外，您可以验证服务器mysql3也赶上了：
mysql> SHOW DATABASES LIKE 'test'; +-----------------+ | Database (test) | +-----------------+ | test | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM test.t1; +----+------+ | c1 | c2 | +----+------+ | 1 | Luis | +----+------+ 1 row in set (0.00 sec) mysql> SHOW BINLOG EVENTS; +---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ | binlog.000001 | 4 | Format_desc | 3 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 | | binlog.000001 | 123 | Previous_gtids | 3 | 150 | | | binlog.000001 | 150 | Gtid | 1 | 211 | SET @@SESSION.GTID_NEXT= 'f81625c0-d3b3-11ef-9c8d-005056a390e6:1' | | binlog.000001 | 211 | Query | 1 | 301 | CREATE DATABASE test | | binlog.000001 | 301 | Gtid | 1 | 362 | SET @@SESSION.GTID_NEXT= 'f81625c0-d3b3-11ef-9c8d-005056a390e6:2' | | binlog.000001 | 362 | Query | 1 | 486 | use `test`; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL) | | binlog.000001 | 486 | Gtid | 1 | 547 | SET @@SESSION.GTID_NEXT= 'f81625c0-d3b3-11ef-9c8d-005056a390e6:3' | | binlog.000001 | 547 | Query | 1 | 606 | BEGIN | | binlog.000001 | 606 | Table_map | 1 | 649 | table_id: 108 (test.t1) | | binlog.000001 | 649 | Write_rows | 1 | 691 | table_id: 108 flags: STMT_END_F | | binlog.000001 | 691 | Xid | 1 | 718 | COMMIT /* xid=26 */ | | binlog.000001 | 718 | Gtid | 1 | 779 | SET @@SESSION.GTID_NEXT= '2366d798-4dc1-421a-a9de-3c825bfada7d:1' | | binlog.000001 | 779 | Query | 1 | 838 | BEGIN | | binlog.000001 | 838 | View_change | 1 | 977 | view_id=17375148526840614:1 | | binlog.000001 | 977 | Query | 1 | 1042 | COMMIT | | binlog.000001 | 1042 | Gtid | 1 | 1103 | SET @@SESSION.GTID_NEXT= '2366d798-4dc1-421a-a9de-3c825bfada7d:2' | | binlog.000001 | 1103 | Query | 1 | 1162 | BEGIN | | binlog.000001 | 1162 | View_change | 1 | 1341 | view_id=17375148526840614:2 | | binlog.000001 | 1341 | Query | 1 | 1406 | COMMIT | | binlog.000001 | 1406 | Gtid | 1 | 1467 | SET @@SESSION.GTID_NEXT= '2366d798-4dc1-421a-a9de-3c825bfada7d:3' | | binlog.000001 | 1467 | Query | 1 | 1526 | BEGIN | | binlog.000001 | 1526 | View_change | 1 | 1705 | view_id=17375148526840614:3 | | binlog.000001 | 1705 | Query | 1 | 1770 | COMMIT | +---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+ 23 rows in set (0.00 sec)