admin管理员组

文章数量:1029016

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文件都添加

代码语言：javascript代码运行次数：0运行复制
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,同时启用组复制

代码语言：javascript代码运行次数：0运行复制
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.为每个实例创建复制用户

代码语言：javascript代码运行次数：0运行复制
[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.为每个实例创建组复制同步

代码语言：javascript代码运行次数：0运行复制
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上引导启动组复制

代码语言：javascript代码运行次数：0运行复制
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.引导组复制，写入同步测试数据

代码语言：javascript代码运行次数：0运行复制
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)
代码语言：javascript代码运行次数：0运行复制
CREATE DATABASE test;USE test;CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);INSERT INTO t1 VALUES (1, 'Luis');
代码语言：javascript代码运行次数：0运行复制
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上启动组复制

代码语言：javascript代码运行次数：0运行复制
mysql> START GROUP_REPLICATION USER='rec_ssl_user', PASSWORD='password';
图片

参考文档:

.4/en/group-replication-configuring-instances.html

.4/en/group-replication-secure-socket-layer-support-ssl.html

.4/en/using-encrypted-connections.html

.4/en/creating-ssl-rsa-files.html

.4/en/group-replication-secure-socket-layer-support-ssl.html

.4/en/creating-ssl-files-using-openssl.html

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文件都添加

代码语言：javascript代码运行次数：0运行复制
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,同时启用组复制

代码语言：javascript代码运行次数：0运行复制
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.为每个实例创建复制用户

代码语言：javascript代码运行次数：0运行复制
[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.为每个实例创建组复制同步

代码语言：javascript代码运行次数：0运行复制
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上引导启动组复制

代码语言：javascript代码运行次数：0运行复制
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.引导组复制，写入同步测试数据

代码语言：javascript代码运行次数：0运行复制
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)
代码语言：javascript代码运行次数：0运行复制
CREATE DATABASE test;USE test;CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);INSERT INTO t1 VALUES (1, 'Luis');
代码语言：javascript代码运行次数：0运行复制
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上启动组复制

代码语言：javascript代码运行次数：0运行复制
mysql> START GROUP_REPLICATION USER='rec_ssl_user', PASSWORD='password';
图片

参考文档:

.4/en/group-replication-configuring-instances.html

.4/en/group-replication-secure-socket-layer-support-ssl.html

.4/en/using-encrypted-connections.html

.4/en/creating-ssl-rsa-files.html

.4/en/group-replication-secure-socket-layer-support-ssl.html

.4/en/creating-ssl-files-using-openssl.html

本文标签： MySQL 84 配置SSL组复制(八个步骤)

版权声明：本文标题：MySQL 8.4 配置SSL组复制(八个步骤) 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/jiaocheng/1747559429a2176312.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。