1. 节点信息
hostname
IP地址
mysql MGR
mysql1
172.16.230.51
mysql2
172.16.230.52
mysql3
172.16.230.53
2. 三个节点安装mysql8 实例
3. 每个节点初始化mysql账号
create user 'fengjian'@'%' identified by '123456';
grant all on*.* to 'fengjian'@'%' with grant option;
4. 下载 mysql shell
[root@mysql1 ~]# wget https://cdn.mysql.com//Downloads/MySQL-Shell/mysql-shell-8.0.23-linux-glibc2.12-x86-64bit.tar.gz
[root@mysql1 ~]# tar -zxvf mysql-shell-8.0.23-linux-glibc2.12-x86-64bit.tar.gz
[root@mysql1 ~]# mv mysql-shell-8.0.23-linux-glibc2.12-x86-64bit /usr/local/mysqlshell[root@mysql3 ~]# chown -R mysql.mysql /usr/local/mysqlshell
5. 拷贝mysqlshell 到其他节点
[root@mysql1 ~]#scp - /usr/local/mysqlshell root@172.16.230.52:/usr/local/[root@mysql2~]#chown -R mysql.mysql /usr/local/mysqlshell
[root@mysql1~]# scp - /usr/local/mysqlshell root@172.16.230.52:/usr/local/[root@mysql3~]# chown -R mysql.mysql /usr/local/mysqlshell
6. 利用mysql shell 构建MGR
[root@mysql1 ~]# /usr/local/mysqlshell/bin/mysqlsh
# 链接一个mysql节点
shell.connect('fengjian@172.16.230.51:3306')
#定义一个集群var cluster = dba.createCluster('SenyintCluster')
# 其他节点实例加入到集群
cluster.addinstance('fengjian@172.16.230.52:3306')
cluster.addinstance('fengjian@172.16.230.53:3306')
#查看集群状态
cluster.status()
# 当链接超时需要 重新链接
MySQL 172.16.230.51:3306 ssl JS > shell.connect('fengjian@172.16.230.51:3306')
#cluster 表示获取var cluster = dba.getCluster('SenyintCluster')
报错信息:
MySQL 172.16.230.51:3306 ssl JS > var cluster=dba.createCluster('SenyintCluster')
Anew InnoDB cluster will be created on instance '172.16.230.51:3306'.
Validating instance configuration at172.16.230.51:3306...
This instance reports its own addressas mysql1:3306NOTE: Some configuration options need to befixed:+-----------------------------+---------------+----------------+----------------------------+
| Variable | Current Value | Required Value | Note |
+-----------------------------+---------------+----------------+----------------------------+
| slave_preserve_commit_order | OFF | ON | Update the server variable |
+-----------------------------+---------------+----------------+----------------------------+NOTE: Please use the dba.configureInstance() command to repair these issues.
ERROR: Instance must be configured and validated with dba.checkInstanceConfiguration() and dba.configureInstance() before it can be usedinan InnoDB cluster.
Dba.createCluster: Instance check failed (RuntimeError)
修复:
set global slave_preserve_commit_order = 1;
数据复制: 分两种情况
1. 正常复制通道 group_replication_applier 状态: online 无延迟状态下复制
2. 延迟复制通道 group_replication_recovery 出现在复制延迟扬中, MGR 节点退化recovery 状态, SQL_Thread 从 relay_log中应用数据.
多主模式如何实现多点写入(multi-master)
1. 修改了每个节点的 auto_increment_offset, auto_increment_increment
2. 每个节点一个独立的GTID区间
3. 实现同一条数据, 同时一时间只能在一个节点上update, 同时下次修改必须保证前面的数据应用完毕
MySQL 172.16.230.51:3306 ssl JS >cluster.status()
{"clusterName": "SenyintCluster","defaultReplicaSet": {"name": "default","primary": "mysql1:3306","ssl": "REQUIRED","status": "OK","statusText": "Cluster is ONLINE and can tolerate up to ONE failure.","topology": {"mysql1:3306": {"address": "mysql1:3306","mode": "R/W","readReplicas": {},"replicationLag": null,"role": "HA","status": "ONLINE","version": "8.0.22"},"mysql2:3306": {"address": "mysql2:3306","mode": "R/O","readReplicas": {},"replicationLag": null,"role": "HA","status": "ONLINE","version": "8.0.22"},"mysql3:3306": {"address": "mysql3:3306","mode": "R/O","readReplicas": {},"replicationLag": null,"role": "HA","status": "ONLINE","version": "8.0.22"}
},"topologyMode": "Single-Primary"},"groupInformationSourceMember": "mysql1:3306"}
mysql shell 切换成 multi-master 模式
MySQL 172.16.230.51:3306 ssl JS >cluster.switchToMultiPrimaryMode()
Switching cluster'SenyintCluster' to Multi-Primary mode...
Instance'mysql1:3306'remains PRIMARY.
Instance'mysql2:3306' was switched fromSECONDARY to PRIMARY.
Instance'mysql3:3306' was switched fromSECONDARY to PRIMARY.
The cluster successfully switched to Multi-Primary mode.