MySQL inodb cluster部署

innodb cluster是基于组复制来实现的。

搭建一套MySQL的高可用集群innodb。

实验环境：

IP	主机名	系统	软件
192.168.91.46	master	RHEL7.4	mysqlshell8.0.17，mysqlrouter8.0.17，mysql8.0.17
192.168.91.35	node1	RHEL7.4	mysql8.0.17
192.168.91.36	node2	RHEL7.4	mysql8.0.17

[root@master thunder]# rpm -ivh mysql-community-common-8.0.17-1.el7.x86_64.rpm

warning: mysql-community-common-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...    1:mysql-community-common-8.0.17-1.e################################# [100%]

[root@master thunder]# rpm -ivh mysql-community-libs-8.0.17-1.el7.x86_64.rpm

warning: mysql-community-libs-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...    1:mysql-community-libs-8.0.17-1.el7################################# [100%]

[root@master thunder]# rpm -ivh mysql-community-client-8.0.17-1.el7.x86_64.rpm

warning: mysql-community-client-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...    1:mysql-community-client-8.0.17-1.e################################# [100%]

[root@master thunder]# rpm -ivh mysql-community-server-8.0.17-1.el7.x86_64.rpm

warning: mysql-community-server-8.0.17-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...    1:mysql-community-server-8.0.17-1.e################################# [100%]

[root@master thunder]# more /var/log/mysqld.log |grep password

2019-09-10T03:42:07.276423Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: *gYT4CrqCFTr

[root@master thunder]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.17

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> alter user 'root'@'localhost' identified by 'kavl7kAkkle!'; Query OK, 0 rows affected (0.01 sec)

组复制的部署：

master：

server_id = 100  #服务ID
gtid_mode = ON  #全局事务
enforce_gtid_consistency = ON  #强制GTID的一致性
log-slave-updates=on
master_info_repository = TABLE  #将master.info元数据保存在系统表中
relay_log_info_repository = TABLE  #将relay.info元数据保存在系统表中
binlog_checksum = NONE  #禁用二进制日志事件校验
log_slave_updates = ON  #级联复制
log_bin = binlog   #开启二进制日志记录
binlog_format= ROW  #以行的格式记录
transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' #加入的组名
loose-group_replication_start_on_boot = off #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。
loose-group_replication_local_address = 'master:33061' #以本机端口33061接受来自组中成员的传入连接   根据实际情况填写
loose-group_replication_group_seeds ='master:33061, node1:33062, node2:33063' #组中成员访问表    根据实际情况填写
loose-group_replication_bootstrap_group = off #不启用引导组 

重启数据库：

[root@master ~]# systemctl restart mysqld

[root@node1 ~]# systemctl restart mysqld

[root@node2 ~]# systemctl restart mysqld

安装插件（三台都需要安装）：

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

-- 查看group replication组件

mysql> show plugins;

master操作：

mysql> set SQL_LOG_BIN=0;   #停掉日志记录

mysql> create user repl@'192.168.91.%' identified with mysql_native_password by 'kavl7kAkkle!';

mysql> grant replication slave on *.* to repl@'192.168.91.%'

mysql> flush privileges;

mysql> set SQL_LOG_BIN=1;  #开启日志记录

mysql> change master to master_user='repl',master_password='kavl7kAkkle!'  for channel 'group_replication_recovery';  #构建group replication集群

mysql> SET GLOBAL group_replication_bootstrap_group=ON;    

mysql> START GROUP_REPLICATION;

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

mysql> SELECT * FROM performance_schema.replication_group_members;

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 | e784156a-daa5-11e9-9184-000c29094ab4 | master      |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
+------------------------

node1和node2上操作：

mysql> set SQL_LOG_BIN=0;   #停掉日志记录

mysql> create user repl@'192.168.91.%' identified with mysql_native_password by 'kavl7kAkkle!';

mysql> grant replication slave on *.* to repl@'192.168.91.%'

mysql> flush privileges;

mysql> set SQL_LOG_BIN=1;  #开启日志记录

mysql> change master to master_user='repl',master_password='kavl7kAkkle!'  for channel 'group_replication_recovery';  #构建group replication集群

 mysql> set global group_replication_allow_local_disjoint_gtids_join=ON; Query OK, 0 rows affected (0.01 sec)

mysql> reset master;

mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (4.02 sec)

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 | e784156a-daa5-11e9-9184-000c29094ab4 | master      |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
| group_replication_applier | ed5719f8-daa5-11e9-b9f5-000c29824893 | node2       |        3306 | ONLINE       | SECONDARY   | 8.0.17         |
| group_replication_applier | f8cad554-dae8-11e9-84d3-000c29641ef8 | node1       |        3306 | ONLINE       | SECONDARY   | 8.0.17         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

至此组搭建完成，组中有三个成员。

集群部署：

 

转载于:https://www.cnblogs.com/winter1519/p/11559633.html