MySQL 8.4 配置SSL组复制(八个步骤)

环境这里有三台MySQL主机，分别是192.168.3.71，72，73，主机名分别对应71.3_mgr1，72.3_mgr2，73.3_mgr3，操作系统均为Oracle Linux 8.10 X64,MySQL版本均为MySQL 8.4.4-commercial

1.我们在/etc/hosts文件添加以下解析，每台主机的hosts文件都添加

vim /etc/hosts127.0.0.1  localhost localhost.localdomain localhost4 localhost4.localdomain4::1  localhost localhost.localdomain localhost6 localhost6.localdomain6192.168.3.71  71.3_mgr1192.168.3.72  72.3_mgr2192.168.3.73  73.3_mgr3

2.然后我们禁用一些不支持组复制的存储引擎和启用gtid,同时启用组复制

192.168.3.71[mysqld]require_secure_transport=ONssl_ca=/u01/mysql3308/data/ca.pemssl_cert=/u01/mysql3308/data/server-cert.pemssl_key=/u01/mysql3308/data/server-key.pemgroup_replication_ssl_mode= REQUIREDdisabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"server-id = 03713308gtid_mode=onenforce_gtid_consistency=onplugin_load_add='group_replication.so'group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"group_replication_start_on_boot=offgroup_replication_local_address= "71.3_mgr1:33081"group_replication_group_seeds= "71.3_mgr1:33081,72.3_mgr2:33081,73.3_mgr3:33081"group_replication_bootstrap_group=off
192.168.3.72[mysqld]require_secure_transport=ONssl_ca=/u01/mysql3308/data/ca.pemssl_cert=/u01/mysql3308/data/server-cert.pemssl_key=/u01/mysql3308/data/server-key.pemgroup_replication_ssl_mode= REQUIREDdisabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"server-id = 03723308gtid_mode=onenforce_gtid_consistency=onplugin_load_add='group_replication.so'group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"group_replication_start_on_boot=offgroup_replication_local_address= "72.3_mgr2:33081"group_replication_group_seeds= "71.3_mgr1:33081,72.3_mgr2:33081,73.3_mgr3:33081"group_replication_bootstrap_group=off
192.168.3.73[mysqld]require_secure_transport=ONssl_ca=/u01/mysql3308/data/ca.pemssl_cert=/u01/mysql3308/data/server-cert.pemssl_key=/u01/mysql3308/data/server-key.pemgroup_replication_ssl_mode= REQUIREDdisabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"server-id=03723308gtid_mode=onenforce_gtid_consistency=onplugin_load_add='group_replication.so'group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"group_replication_start_on_boot=offgroup_replication_local_address= "73.3_mgr3:33081"group_replication_group_seeds= "71.3_mgr1:33081,72.3_mgr2:33081,73.3_mgr3:33081"group_replication_bootstrap_group=off

3.为每个实例创建复制用户

[root@71,2,3mysql3308]# mysql -uroot -pEnter password:mysql> SET SQL_LOG_BIN=0;Query OK, 0 rows affected (0.00 sec)mysql> CREATE USER 'rec_ssl_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;Query OK, 0 rows affected (0.03 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO 'rec_ssl_user'@'%';Query OK, 0 rows affected (0.26 sec)mysql> GRANT CONNECTION_ADMIN ON *.* TO 'rec_ssl_user'@'%';Query OK, 0 rows affected (0.06 sec)mysql> GRANT BACKUP_ADMIN ON *.* TO 'rec_ssl_user'@'%';Query OK, 0 rows affected (0.10 sec)mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO rec_ssl_user@'%';Query OK, 0 rows affected (0.03 sec)mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec)mysql> SET SQL_LOG_BIN=1;Query OK, 0 rows affected (0.00 sec)

4.为每个实例创建组复制同步

mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rec_ssl_user', SOURCE_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';Query OK, 0 rows affected, 2 warnings (1.17 sec)

5.查看组复制插件安装情况

mysql> SHOW PLUGINS;

+----------------------------+----------+--------------------+----------------------+-------------+

| Name                       | Status   | Type               | Library              | License     |

+----------------------------+----------+--------------------+----------------------+-------------+

| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | PROPRIETARY |

(...)

| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | PROPRIETARY |

+----------------------------+----------+--------------------+----------------------+-------------+

6.在192.168.3.71上引导启动组复制

mysql> SET GLOBAL group_replication_bootstrap_group=ON;Query OK, 0 rows affected (0.00 sec)mysql> START GROUP_REPLICATION USER='rec_ssl_user', PASSWORD='password';Query OK, 0 rows affected (1.43 sec)mysql> SET GLOBAL group_replication_bootstrap_group=OFF;Query OK, 0 rows affected (0.00 sec)

7.引导组复制，写入同步测试数据

mysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+| CHANNEL_NAME| MEMBER_ID| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+| group_replication_applier | 4966325d-1509-11f0-a15f-525400381571 | 71.3_mgr1|3308 | ONLINE| PRIMARY| 8.4.4| XCom|+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+1 row in set (0.00 sec)

CREATE DATABASE test;USE test;CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);INSERT INTO t1 VALUES (1, 'Luis');

mysql>CREATE DATABASE test;Query OK, 1 row affected (0.08 sec)mysql> USE test1;Database changedmysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);Query OK, 0 rows affected (1.46 sec)mysql> INSERT INTO t1 VALUES (1, 'Luis');Query OK, 1 row affected (0.22 sec)

8.分别在192.168.3.72，73上启动组复制

mysql> START GROUP_REPLICATION USER='rec_ssl_user', PASSWORD='password';

参考文档:

https://dev.mysql.com/doc/refman/8.4/en/group-replication-configuring-instances.html

https://dev.mysql.com/doc/refman/8.4/en/group-replication-secure-socket-layer-support-ssl.html

https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html

https://dev.mysql.com/doc/refman/8.4/en/creating-ssl-rsa-files.html

https://dev.mysql.com/doc/refman/8.4/en/group-replication-secure-socket-layer-support-ssl.html

https://dev.mysql.com/doc/refman/8.4/en/creating-ssl-files-using-openssl.html