本文共 9701 字，大约阅读时间需要 32 分钟。

本文章讲解如何使用三个MySQL Server实例创建一个复制组，每个实例在不同的主机上运行。

1、部署实例组

第一步是部署至少三个MySQL Server实例，此过程演示了如何为实例使用多个主机，分别名为S1，S2和S3。

组架构如下：

2、配置实例

修改实例的配置文件my.cnf并重启：

#对于组复制，数据必须存储在InnoDB事务存储引擎中disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"#组复制对于某些参数的要求，以下参数按照环境修改server_id=1gtid_mode=ONenforce_gtid_consistency=ONbinlog_checksum=NONElog_bin=binloglog_slave_updates=ONbinlog_format=ROWmaster_info_repository=TABLErelay_log_info_repository=TABLE#组复制设置参数transaction_write_set_extraction=XXHASH64plugin_load_add='group_replication.so'group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"#设置一个有效值即可，可以用select uuid()生成group_replication_start_on_boot=offgroup_replication_local_address= "s1:33061"group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"group_replication_bootstrap_group=offgroup_replication_ip_whitelist="s1,s2,s3"

小编的架构IP为：

S1    10.186.61.40

S2    10.186.61.41

S3    10.186.61.46

以下为小编的my.cnf配置文件可供参考：

[mysql]default-character-set=utf8mb4socket = /usr/local/mysql/mysql.sock[mysqld]port = 3306server_id = 33061000basedir = /usr/local/mysqldatadir = /usr/local/mysql/datasocket = /usr/local/mysql/mysql.socklog_bin = /usr/local/mysql/log/mysql-binrelay_log = /usr/local/mysql/log/mysql-relayinnodb_log_group_home_dir = /usr/local/mysql/logtmpdir = /tmplog-error=/usr/local/mysql/log/error.logreport_host = 10.186.61.40# BINLOGbinlog_error_action = ABORT_SERVERbinlog_format = rowbinlog_checksum = NONEbinlog_rows_query_log_events = 1log_slave_updates = 1master_info_repository = TABLEmax_binlog_size = 250Mrelay_log_info_repository = TABLErelay_log_recovery = 1sync_binlog = 1# GTID #gtid_mode = ONenforce_gtid_consistency = 1# ENGINEdefault_storage_engine = InnoDBinnodb_buffer_pool_size = 128Minnodb_data_file_path = ibdata1:1G:autoextendinnodb_file_per_table = 1innodb_io_capacity = 200innodb_log_buffer_size = 64M[mysql]default-character-set=utf8mb4socket = /usr/local/mysql/mysql.sock[mysqld]port = 3306server_id = 33061000basedir = /usr/local/mysqldatadir = /usr/local/mysql/datasocket = /usr/local/mysql/mysql.socklog_bin = /usr/local/mysql/log/mysql-binrelay_log = /usr/local/mysql/log/mysql-relayinnodb_log_group_home_dir = /usr/local/mysql/logtmpdir = /tmplog-error=/usr/local/mysql/log/error.logreport_host = 10.186.61.40# BINLOGbinlog_error_action = ABORT_SERVERbinlog_format = rowbinlog_checksum = NONEbinlog_rows_query_log_events = 1log_slave_updates = 1master_info_repository = TABLEmax_binlog_size = 250Mrelay_log_info_repository = TABLErelay_log_recovery = 1sync_binlog = 1# GTID #gtid_mode = ONenforce_gtid_consistency = 1# ENGINEdefault_storage_engine = InnoDBinnodb_buffer_pool_size = 128Minnodb_data_file_path = ibdata1:1G:autoextendinnodb_file_per_table = 1innodb_io_capacity = 200innodb_log_buffer_size = 64Minnodb_log_file_size = 256Minnodb_log_files_in_group = 2innodb_max_dirty_pages_pct = 60innodb_print_all_deadlocks=1innodb_stats_on_metadata = 0innodb_strict_mode = 1# CACHEtmp_table_size = 32Mtable_open_cache = 1024max_connections = 1000thread_cache_size = 1024open_files_limit = 65535# SLOW LOGslow_query_log = 1slow_query_log_file = /usr/local/mysql/log/mysql-slow.loglog_slow_admin_statements = 1log_slow_slave_statements = 1long_query_time  = 1#GROUOP REPLICATIONtransaction_write_set_extraction=XXHASH64plugin_load_add='group_replication.so'group_replication_group_name="35cd6fde-3287-11ea-b2ec-02000aba3d28"group_replication_start_on_boot=offgroup_replication_local_address= "10.186.61.40:33061"group_replication_group_seeds= "10.186.61.40:33061,10.186.61.41:33061,10.186.61.46:33061"group_replication_bootstrap_group=offgroup_replication_ip_whitelist="10.186.61.40,10.186.61.41,10.186.61.46"

3、创建组复制的用户

1. 启动MySQL服务器实例，然后将客户端连接到它。

2. 如果要禁用二进制日志记录以便在每个实例上分别创建复制用户，请执行以下语句：

mysql> SET SQL_LOG_BIN=0;

3. 创建一个具有特权的MySQL用户。

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';mysql> ALTER USER rpl_user@'%' IDENTIFIED WITH sha256_password BY '123';mysql> FLUSH PRIVILEGES;

4. 如果禁用了二进制日志记录，请在创建用户后立即通过发出以下语句再次启用它：

mysql> SET SQL_LOG_BIN=1;

5. 配置用户后，使用 语句将服务器配置为使用给定凭据通过分布式恢复或远程克隆操作进行状态转移。发出以下语句，替换rpl_user并 替换 password为创建用户时使用的值：

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';

4、启动组复制

配置并启动服务器s1后，安装组复制插件。如果您已安装group_replication.so组复制插件，可以直接执行下一步。如果您决定手动安装插件，请连接到服务器并发出以下命令：

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

要检查插件是否已成功安装，请发出 SHOW PLUGINS;并检查输出。它应该显示如下内容:

mysql> SHOW PLUGINS;+----------------------------+----------+--------------------+----------------------+-------------+| Name                       | Status   | Type               | Library              | License     |+----------------------------+----------+--------------------+----------------------+-------------+| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | PROPRIETARY |(...)| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | PROPRIETARY |+----------------------------+----------+--------------------+----------------------+-------------+

首次启动组的过程称为引导，执行以下命令：

mysql> SET GLOBAL group_replication_bootstrap_group=ON;mysql> START GROUP_REPLICATION;mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

一旦 语句返回，该集团已启动。您可以检查是否已创建该组，并且其中有一个成员：

mysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+| group_replication_applier | 1def12c1-3155-11ea-930c-02000aba3d28 | 10.186.61.40 |        3306 | ONLINE       | PRIMARY     | 8.0.15         |+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+

为了说明服务器确实在一个组中，并且能够处理负载，创建表并向其中添加一些内容。

mysql> CREATE DATABASE test;mysql> USE test;mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);mysql> INSERT INTO t1 VALUES (1, 'Luis');mysql> SELECT * FROM t1;+----+------+| c1 | c2   |+----+------+|  1 | Luis |+----+------+

5、将实例添加到组

此时，该组中有一个成员，服务器s1，其中有一些数据。现在是时候通过添加先前配置的其他两个服务器来扩展组。

1. 修改S2的my.cnf文件，并重启服务器。

配置文件和S1相同，注意修改SERVER_ID和group_replication_local_address等参数的值。

2. 在S2服务器上连接客户端，创建和S1相同的的复制用户。

mysql> SET SQL_LOG_BIN=0;mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';mysql> ALTER USER rpl_user@'%' IDENTIFIED WITH sha256_password BY '123';mysql> FLUSH PRIVILEGES;mysql> SET SQL_LOG_BIN=1;mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery'

3、启动组复制，启动后再次检查 表，看组中是否有两个ONLINE服务器。

mysql> START GROUP_REPLICATION;mysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+| group_replication_applier | 47dfdfb5-32b5-11ea-a738-02000aba3d28 | 10.186.61.40 |        3306 | ONLINE       | PRIMARY     | 8.0.15         || group_replication_applier | a1796612-32b6-11ea-a32b-02000aba3d29 | 10.186.61.41 |        3306 | ONLINE       | SECONDARY   | 8.0.15         |+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+

4. 检查S2联机后，是否与服务器S1的数据同步。

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)

添加S3实例与上述步骤相同。

mysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+| group_replication_applier | 47dfdfb5-32b5-11ea-a738-02000aba3d28 | 10.186.61.40 |        3306 | ONLINE       | PRIMARY     | 8.0.15         || group_replication_applier | 897b6353-32b9-11ea-a7e9-02000aba3d2e | 10.186.61.46 |        3306 | ONLINE       | SECONDARY   | 8.0.15         || group_replication_applier | a1796612-32b6-11ea-a32b-02000aba3d29 | 10.186.61.41 |        3306 | ONLINE       | SECONDARY   | 8.0.15         |+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+

 经验证数据也与组内其他实例同步：

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)

 基于MySQL8.0.15在单主模式下部署MGR完成。

参考资料：

转载地址：http://pbjxi.baihongyu.com/