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 |
组复制的部署:
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 #不启用引导组
重启数据库:
[[email protected] ~]# systemctl restart mysqld [[email protected] ~]# systemctl restart mysqld [[email protected] ~]# 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> grant replication slave on *.* to [email protected]‘192.168.91.%‘ identified by ‘kavl7kAkkle!‘;
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> grant replication slave on *.* to [email protected]‘192.168.91.%‘ identified by ‘kavl7kAkkle!‘;
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)
至此组搭建完成,组中有三个成员。
集群部署: