MySQL集群部署

Author： Mia
发布时间：March 30, 2022
2081views
3 comments
5420 words
Categories：通用技术

MySQL安装与部署

通过Yum安装指定版本MySQL

测试环境为CentOS 7

下载和安装

先下载Yum Repository包

https://dev.mysql.com/downloads/repo/yum/

https://repo.mysql.com//mysql80-community-release-el7-6.noarch.rpm

安装Repository包

rpm -ivh mysql80-community-release-el7-1.noarch.rpm

设置Yum缓存

yum makecache

可以看一下有哪些版本

yum repolist all| grep mysql

可以选择性激活版本

vim /etc/yum.repos.d/mysql-community.repo

默认安装最新版本，可以指定到enabled=1，需要保证只有一个enabled=1

# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-preview]
name=MySQL Tools Preview
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-7.5-community]
name=MySQL Cluster 7.5 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.5-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-7.6-community]
name=MySQL Cluster 7.6 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.6-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-8.0-community]
name=MySQL Cluster 8.0 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-8.0-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

验证一下激活的版本

yum repolist enabled | grep mysql

安装MySQL服务器

yum install -y mysql-community-server

启动和使用

开启和设置开机启动

systemctl start mysqld
systemctl enable mysqld

查看默认的密码

grep 'password' /var/log/mysqld.log

命令行登录MySQL，输入以下命令后再输入密码

mysql -u root -p

MySQL互为主从集群部署

说明：参考前面的Yum安装，示例为8.2.18版本，两台服务器互为Master和Slave

通用配置

编辑MySQL配置文件

vi /etc/my.cnf

节点1的配置

server-id=1
log-bin=mysql-bin
auto_increment_offset=1
auto_increment_increment=2

节点2的配置

server-id=2
log-bin=mysql-bin
auto_increment_offset=2
auto_increment_increment=2

*如果没有修改初始密码会有提示需要修改初始密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@a22w0rd';

节点1配置

创建同步用户与权限分配

use mysql;
create user 'syncuser'@'10.60.146.181' identified with mysql_native_password by 'P@ssw0rd';
grant replication slave on *.* to 'syncuser'@'10.60.146.181'; 
grant replication client on *.* to 'syncuser'@'10.60.146.181';             
flush privileges;

查看权限

show grants for 'syncuser'@'10.60.146.181';

查看节点1Master的状态

根据BIN Log的文件和Position配置下面的Master参数

show master status;

配置连接Master的参数

此时Master为对端，但是master_log_file和master_log_pos填写自己的

change master to
master_host='10.60.146.181',
master_user='syncuser',
master_password='P@ssw0rd',
master_log_file='mysql-bin.000002',
master_log_pos=157;

开启Slave节点

start slave;

查看Slave状态

show slave status\G

节点2配置

节点2进行同步账号的配置

use mysql;
create user 'syncuser'@'10.60.158.133' identified with mysql_native_password by 'P@ssw0rd';      
grant replication slave on *.* to 'syncuser'@'10.60.158.133'; 
grant replication client on *.* to 'syncuser'@'10.60.158.133';                         
flush privileges;

查看权限

show grants for 'syncuser'@'10.60.158.133';

查看节点2Master的状态

show master status;

根据Master的状态信息配置连接信息

change master to
master_host='10.60.158.133',
master_user='syncuser',
master_password='P@ssw0rd',
master_log_file='mysql-bin.000002',
master_log_pos=157;

开启Slave节点

start slave;

查看节点状态

show slave status\G

MySQL InnoDB Cluster 与 ClusterSet部署

ClusterSet为新的多集群架构，基于InnoDB引擎，在InnoDB Cluster基础之上

逻辑结构

MySQL Replication->MySQL ReplicaSet->MySQL Group Replication->MySQL InnoDB Cluster->MySQL InnoDB ClusterSet

因此部署ClusterSet先要部署InnoDB Cluster

InnoDB Cluster部署

部署准备

先确保防火墙等安全工具是关闭状态

setenforce 0
getenforce
systemctl stop firewalld

此处设置密码P@a22w0rd

配置hosts文件

cat >>/etc/hosts <<EOF
10.60.29.237 mysql-1
10.60.64.182 mysql-2
EOF

下载和安装

安装MySQL-Shell

找到对应版本，https://downloads.mysql.com/archives/shell/
获取下载链接，可以通过wget下载，以下为CentOS7 8.0.28的版本链接

wget https://cdn.mysql.com/archives/mysql-shell/mysql-shell-8.0.28-1.el7.x86_64.rpm
yum localinstall mysql-shell-8.0.28-1.el7.x86_64.rpm -y

安装MySQL-Router

找到对应版本，https://downloads.mysql.com/archives/router/
获取下载链接，可以通过wget下载，以下为CentOS7 8.0.28的版本链接

wget https://downloads.mysql.com/archives/get/p/41/file/mysql-router-community-8.0.28-1.el7.x86_64.rpm
yum localinstall mysql-router-community-8.0.28-1.el7.x86_64.rpm -y

*MySQL 8.0.28 Allinone安装参考

wget https://repo.mysql.com//mysql80-community-release-el7-6.noarch.rpm
rpm -ivh mysql80-community-release-el7-6.noarch.rpm
yum makecache

vi /etc/yum.repos.d/mysql-community.repo

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-preview]
name=MySQL Tools Preview
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

yum install -y mysql-community-server
systemctl start mysqld
systemctl enable mysqld

grep 'password' /var/log/mysqld.log
mysql -u root -p

alter user 'root'@'localhost' identified by 'P@22w0rd';
create user 'root'@'%' identified with mysql_native_password by 'P@22w0rd';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

vi /etc/my.cnf

server-id=1

setenforce 0
getenforce
systemctl stop firewalld

cat >>/etc/hosts <<EOF
10.60.29.237 mysql-1
10.60.64.182 mysql-2
EOF

wget https://cdn.mysql.com/archives/mysql-shell/mysql-shell-8.0.28-1.el7.x86_64.rpm
yum localinstall mysql-shell-8.0.28-1.el7.x86_64.rpm -y

wget https://downloads.mysql.com/archives/get/p/41/file/mysql-router-community-8.0.28-1.el7.x86_64.rpm
yum localinstall mysql-router-community-8.0.28-1.el7.x86_64.rpm -y

MySQL Shell 配置Cluster

注意：最好改密码并赋予远程登录的权限

alter user 'root'@'localhost' identified by 'P@22w0rd';
create user 'root'@'%' identified with mysql_native_password by 'P@22w0rd';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

打开 mysqlsh 控制界面

分别连接各个节点，连接后输入Y则确认记住密码，后续使用MySQL Shell可以直接登录

配置第1个节点，连接后执行实例配置，分别输入y

\c root@mysql-1:3306
dba.configureInstance();

检查配置，最后结果要出现"status": "ok"

dba.checkInstanceConfiguration("root@mysql-1:3306");

配置第2个节点

\c root@mysql-2:3306
dba.configureInstance();

检查配置，最后结果要出现"status": "ok"

dba.checkInstanceConfiguration("root@mysql-2:3306");

创建cluster

连接任意一台机器，此处选择mysql-1，创建节点之后添加实例，选择C克隆模式，如果还需多加几个节点则继续按此方式添加

\c root@mysql-1:3306
var cluster = dba.createCluster("mycluster");
cluster.addInstance('root@mysql-2:3306')

最后查看集群状态

cluster.status();

Cluster TroubleShooting

Cannot add an instance with the same server UUID

Cluster.addInstance: Cannot add an instance with the same server UUID (16f40394-eac7-11ec-80a4-5254003ad219) of an active member of the cluster '10-60-29-237:3306'. Please change the server UUID of the instance to add, all members must have a unique server UUID. (RuntimeError)

解决方法：https://zhuanlan.zhihu.com/p/375776925

登录MysQL中生成新的UUID

select uuid();
b2b9b7ab-eaea-11ec-9428-5254009a3cf1

登录MysQL中查看数据目录

show variables like 'datadir';

vi /var/lib/mysql/auto.cnf
server-uuid=b2b9b7ab-eaea-11ec-9428-5254009a3cf1
systemctl restart mysqld

The instance 'x' has a populated Metadata schema

Dba.createCluster: dba.createCluster: Unable to create cluster. The instance 'mysql1:3306' has a populated Metadata schema and belongs to that Metadata. Use either dba.dropMetadataSchema() to drop the schema, or dba.rebootClusterFromCompleteOutage() to reboot the cluster from complete outage. (RuntimeError)

解决办法：重启集群

dba.rebootClusterFromCompleteOutage()

The START GROUP_REPLICATION command failed

Unable to start Group Replication for instance 'mysql3:3306'. Please check the MySQL server error log for more information.
Cluster.addInstance: Group Replication failed to start: MySQL Error 3096 (HY000): mysql3:3306: The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer. (RuntimeError)

解决办法：检查hosts配置

InnoDB ClusterSet but is not ONLINE

Dba.getCluster: This function is not available through a session to an InnoDB Cluster that belongs to an InnoDB ClusterSet but is not ONLINE (MYSQLSH 51314)

解决办法：重启集群

dba.rebootClusterFromCompleteOutage()

如果ClusterSet集群都挂掉的时候可以分别登录两个集群重启集群

TS Reference：http://t.zoukankan.com/zhi-leaf-p-12599822.html

InnoDB ClusterSet部署

连接到主集群主节点

\c root@mysql-1:3306

获取之前创建的Cluster并创建集群，命名为myclusterset

cluster = dba.getCluster();
clusterset = cluster.createClusterSet('myclusterset');

或者使用clusterset = cluster.create_cluster_set('myclusterset')命令，但是官方的命令是createClusterSet

查看ClusterSet状态，集群角色PRIMARY，状态HEALTHY

clusterset.status();

查看ClusterSet状态，可以看到集群的拓扑

clusterset.describe();

配置第2各集群的实例

\c root@mysql-3:3306
dba.configureInstance()

\c root@mysql-4:3306
dba.configureInstance()

*配置实例并创建 MICS 管理用户，用来同步主库集群数据。测试过程中使用root账号也行

dba.configureInstance("root@mysql-3:3306",{"clusterAdmin":"ics_admin","clusterAdminPassword":"P@55w0rd"})

dba.configureInstance('root@127.0.0.1:4410', {clusterAdmin: 'icadmin'})

添加 REPLICA 集群

REPLICA 集群 MIC 命名为 urcluster

urcluster=clusterset.createReplicaCluster("root@mysql-3:3306","urcluster",{"recoveryMethod":"clone","recoveryProgress":2})

其它参考命令
urcluster=clusterset.create_replica_cluster("root@mysql-3:3306","urcluster",{"recoveryMethod":"clone","recoveryProgress":2})
cluster2 = myclusterset.createReplicaCluster("127.0.0.1:4410", "clustertwo", {recoveryProgress: 1, timeout: 10})

REPLICA 集群添加其他实例

urcluster.addInstance('root@mysql-4:3306')

其它参考命令
urcluster.add_instance("root@mysql-4:3306",{"recoveryMethod":"clone"})

查看ClusterSet状态

mycluster 为主，urcluster 为备

clusterset.describe()

验证完成的REPLICA集群和 InnoDB ClusterSet 部署是否健康

myclusterset.status({extended: 1})

数据写入与同步测试

在PRIMARY集群主节点写条数据简单测试下数据同步：主库表 t1 插一条记录

首先连接到主集群主节点，开启SQL模式，创建数据库和表并插入数据

\c root@mysql-1:3306
\sql
create database bcdr;
use ytt;
create table t1(id int primary key,r1 int);
insert t1 values (1,100);
table t1;

+----+-----+
| id | r1  |
+----+-----+
|  1 | 100 |
+----+-----+
1 row in set (0.0006 sec)

切换到REPLICA集群节点，查看效果，查看表 t1 数据已经正常同步，并且只读

\c root@mysql-3:3306
\sql
use ytt;
table t1;

+----+-----+
| id | r1  |
+----+-----+
|  1 | 100 |
+----+-----+
1 row in set (0.0006 sec)

尝试创建数据库

create table t2 like t1;

ERROR: 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

查看ClusterSet状态

clusterset = dba.getClusterSet();
clusterset.status();

ClusterSet集群切换测试

手动主备Cluster集群切换

手动进行主备切换 urcluster 已经被提升了新的Primary Cluster

clusterset = dba.getClusterSet();
clusterset.setPrimaryCluster('urcluster')

执行结果示例

Switching the primary cluster of the clusterset to 'urcluster'
* Verifying clusterset status
** Checking cluster mycluster
  Cluster 'mycluster' is available
** Checking cluster urcluster
  Cluster 'urcluster' is available

* Refreshing replication account of demoted cluster
* Synchronizing transaction backlog at 10-60-208-94:3306
** Transactions replicated  ############################################################  100% 

* Updating metadata

* Updating topology
** Changing replication source of 10-60-250-91:3306 to 10-60-208-94:3306
** Changing replication source of 10-60-128-196:3306 to 10-60-208-94:3306
* Acquiring locks in replicaset instances
** Pre-synchronizing SECONDARIES
** Acquiring global lock at PRIMARY
** Acquiring global lock at SECONDARIES

* Synchronizing remaining transactions at promoted primary
** Transactions replicated  ############################################################  100% 

* Updating replica clusters
Cluster 'urcluster' was promoted to PRIMARY of the clusterset. The PRIMARY instance is '10-60-208-94:3306'

查看集群状态

新 PRIMARY 为 urcluster，mycluster 变为 REPLICA

clusterset.status();

{
    "clusters": {
        "mycluster": {
            "clusterRole": "REPLICA", 
            "clusterSetReplicationStatus": "OK", 
            "globalStatus": "OK"
        }, 
        "urcluster": {
            "clusterRole": "PRIMARY", 
            "globalStatus": "OK", 
            "primary": "10-60-208-94:3306"
        }
    }, 
    "domainName": "myclusterset", 
    "globalPrimaryInstance": "10-60-208-94:3306", 
    "primaryCluster": "urcluster", 
    "status": "HEALTHY", 
    "statusText": "All Clusters available."
}

切换集群参考：https://dev.mysql.com/doc/mysql-shell/8.0/en/innodb-clusterset-switchover.html

Cluster 切换与数据同步测试

1.创建数据库，其它节点同步

2.关闭主节点systemctl stop mysqld，切换到其它节点

3.登录（可以使用Router地址）发现仍可以访问
mysql -u root -h192.168.1.1 -P6446 –p

4.查看数据

5.写入数据测试

操作命令说明

MySQL Shell连接到数据库

\c root@mysql-1:3306

或者

\connect root@127.0.0.1:3306

MySQL Shell SQL模式下，查看数据库

show databases;

连接到一个节点

\connect root@127.0.0.1:3310

获取Cluster实例

cluster = dba.getCluster();

Use current cluster as main cluster to create a Clusterset

clusterset = cluster1.createClusterSet('myclusterset');

查看cluster集群状态

var cluster = dba.getCluster()
cluster.status()

删除Cluster集群

var cluster = dba.getCluster()
cluster.dissolve()

查看 ClusterSet 状态

clusterset = dba.getClusterSet();
clusterset.status();

验证完成的REPLICA集群和 InnoDB ClusterSet 部署是否健康

clusterset.status({extended: 1})

配置MySQL-Router

注意：MySQL Router是路由节点，仅需配置在一个节点上即可

配置文件

暂时参考

[DEFAULT]
#日志存放目录
logging_folder = /usr/local/mysql-router/myrouter/log
#插件存放目录
plugin_folder = /usr/local/mysql-router/lib/mysqlrouter
#配置文件存放目录
config_folder = /etc/mysql-route
#运行目录
runtime_folder = /usr/local/mysql-router/myrouter/run
[routing:read_writer]
#写节点地址
bind_address = 192.168.1.13
#写节点端口
bind_port = 7001
#MySQL router提供两种mode：read-only和read-write，设置为read-write，常用于设置destinations为master时，实现master的高可用
#模式:读还是写
mode = read-write
#主节点地址：默认情况下第一台主数据库为写主库，当第一台主数据库DOWN机后，第二台数据库被提升为主库
destinations = node1:3306,node2:3306,node3:3306
max_connections = 1024
[routing:read_only]
#从节点负载均衡配置
#绑定的IP地址
bind_address = 192.168.1.13
#监听的端口
bind_port = 7002
mode = read-only
#主节点地址：默认情况下第一台主数据库为写主库，当第一台主数据库DOWN机后，第二台数据库被提升为主库
destinations = node1:3306,node2:3306,node3:3306
max_connections = 1024


lower_case_table_names=1

其它参考

配置文件参考（暂时没用）

[mysqld]
#mysql自身配置，这里默认即可
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address    = 0.0.0.0

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#每台要不一样
server_id = 1 
#开启gitd复制
gtid_mode = ON
log_bin = bilog
log_slave_updates = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
transaction_write_set_extraction = XXHASH64
enforce_gtid_consistency = ON
#确保GR_applier按照一致的顺序复现事务
slave_preserve_commit_order = ON
binlog_checksum = NONE
#UUID号，集群中要一致
loose-group_replication_group_name = a38e32fd-5fb6-11e8-ad7a-00259015d941
#默认关闭, 防止重启后自动创建新的Group
loose-group_replication_start_on_boot = OFF 
#默认关闭, 防止自动创建新的Group
loose-group_replication_bootstrap_group = OFF 
loose-group_replication_allow_local_disjoint_gtids_join = ON
#在单主模式中为ON，在多主模式中为OFF
loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

参考链接

InnoDB Cluster部署上中下
https://cloud.tencent.com/developer/article/1834948

Cluster部署参考

https://blog.csdn.net/qq_42890862/article/details/120308009

Cluster详细部署参考
https://blog.csdn.net/weixin_42089154/article/details/119685457

ClusterSet部署

详细介绍部署切换测试说明

https://www.modb.pro/db/397134

ClusterSet简单部署参考

https://blog.csdn.net/yangjianrong1985/article/details/121917801

ClusterSet官网介绍
https://dev.mysql.com/doc/mysql-shell/8.0/en/innodb-clusterset.html
Galera部署集群和原理，有Galera和MGR的对比
https://blog.csdn.net/wzy0623/article/details/102522268

MGR

MySQL Group Replication
是MySQL在2016年12月推出的一个全新的高可用与高扩展的解决方案。

为什么要使用MGR

MySQL推出MGR之前，传统复制分为两种，异步复制、半同步复制。

MySQL Asynchronous Replication
异步复制：master执行事务，写入binlog日志，然后提交。slave接收binlog日志事务并将事务先写入relay日志，然后重做事务。当master宕机时有可能会造成数据不一致情况。

MySQL Semisynchronous Replication
半同步复制：master执行事务，写入二进制日志，将binlog事务日志传送到slave，slave接收到binlog事务日志后，将其写到relay log，然后向master返回传送成功ACK；master收到ACK后，再在存储引擎中提交事务。(两种提交方式，由参数rpl_semi_sync_master_wait_point决定)

但是以上两种主从复制存在以下弊端：
1、写操作集中在MASTER服务器上；
2、可能产生数据不一致的问题；
3、MASTER宕机后，需要人为选择新主并重新给其他的slave端执行change master。

MGR提供了哪些功能？

1、同步复制；
2、单主模式、多主模式；
3、自动切换；
4、弹性扩展。

MGR限制？

1、仅支持InnoDB引擎；
2、所有新增表必须显式创建主键；
3、建议隔离级别为READ COMMITED，多主模式下，不支持SERIALIZABLE；
4、多主模式下，不建议使用外键；
5、需开启bin_log_format=row、log_slave_updates=ON;
6、需开启GTID；
7、需安装引擎group_replication.so；
8、MGR最多支持9个节点。

MySQL InnoDB ClusterSet的限制和要求有哪些
MySQL服务器版本为8.0.27之后
新加入的InnoDB Cluster不能是已有MySQL InnoDB ClusterSet的一部分
InnoDB Cluster必须使用单主模式
任何成员服务器上都不能有来自组外服务器的入站复制通道
在创建 InnoDB ClusterSet 部署时，InnoDB Cluster 必须在线，并且其主要成员服务器必须可以使用 MySQL Shell 访问
MySQL Shell与MySQL Router均须使用8.0.27之后版本
InnoDB ClusterSet 优先考虑可用性而不是一致性，以最大限度地提高容灾能力。如果用户不能容忍故障转移期间事务或数据的任何丢失，那么不要使用 InnoDB ClusterSet ，可以考虑使用单个 InnoDB Cluster
InnoDB ClusterSet 不会自动故障转移到副本集群。需要管理员确认后执行操作。
InnoDB ClusterSet 只支持异步复制，不能使用半同步复制
InnoDB ClusterSet 部署只能包含一个读写主集群。所有副本集群都是只读的。不允许使用具有多个主集群的双活设置，因为在集群发生故障时无法保证数据一致性。
一个 InnoDB Cluster 只能参与一个 InnoDB ClusterSet 部署
InnoDB ClusterSet 不支持使用运行 MySQL Server 5.7 的实例

MGR原理

单主模式

单主模式下，只有一个节点可以读写，其他节点提供只读服务。单主模式下，当主节点宕掉，其他节点自动会根据服务器的server_uuid变量和group_replication_member_weight变量值，选择下一个slave作为主节点，group_replication_member_weight的值最高的成员被选为新的主节点，该参数默认为50，可以在节点上设置不同值；在group_replication_member_weight值相同的情况下，group根据数据字典中server_uuid排序，排序在最前的被选择为主节点。

多主模式

多主模式下,在组复制中通过Group Replication Protocol协议及Paxos协议，形成的整体高可用解决方案，同时增加了certify的概念，负责检查事务是否允许提交，是否与其它事务存在冲突，Group Replication是由多个节点共同组成一个数据库集群，每个节点都可以单独执行事务，但是read-write(RW)的操作只有在组内验证后才可以commit，Read-only (RO)事务是不需要验证可以立即执行，当一个事务在一个节点上提交之前，会在组内自动进行原子性的广播，告知其他节点变更了什么内容/执行了什么事务，然后为该事物建立一个全局的排序，最终，这意味着所有的服务器都以相同的顺序接收相同的事务集。因此，所有服务器都按照相同的顺序应用相同的变更集，因此它们在组中保持一致。在多主模式下，该组的所有成员都设置为读写模式，在多主模式下，不支持SERIALIZABLE事务隔离级别，且不能完全支持级联外键约束。

参考链接

https://blog.csdn.net/dahai533418/article/details/105074779

Last modification：June 16th, 2022 at 04:35 pm

硬币投入口

3 comments

isnzezrzah
November 20th, 2024 at 12:20 pm

真好呢

Reply
viqlrrprjk
September 23rd, 2024 at 05:02 pm

叼茂SEO.bfbikes.com

Reply
九凌网络
November 17th, 2022 at 04:24 pm

很详细，简单学习一下

Reply

MySQL集群部署

Mia • 2022 年 03 月 30 日

MySQL安装与部署

通过Yum安装指定版本MySQL

测试环境为CentOS 7

下载和安装

先下载Yum Repository包

https://dev.mysql.com/downloads/repo/yum/

https://repo.mysql.com//mysql80-community-release-el7-6.noarch.rpm

安装Repository包

rpm -ivh mysql80-community-release-el7-1.noarch.rpm

设置Yum缓存

yum makecache

可以看一下有哪些版本

yum repolist all| grep mysql

可以选择性激活版本

vim /etc/yum.repos.d/mysql-community.repo

默认安装最新版本，可以指定到enabled=1，需要保证只有一个enabled=1

# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-preview]
name=MySQL Tools Preview
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-7.5-community]
name=MySQL Cluster 7.5 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.5-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-7.6-community]
name=MySQL Cluster 7.6 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.6-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-8.0-community]
name=MySQL Cluster 8.0 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-8.0-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

验证一下激活的版本

yum repolist enabled | grep mysql

安装MySQL服务器

yum install -y mysql-community-server

启动和使用

开启和设置开机启动

systemctl start mysqld
systemctl enable mysqld

查看默认的密码

grep 'password' /var/log/mysqld.log

命令行登录MySQL，输入以下命令后再输入密码

mysql -u root -p

MySQL互为主从集群部署

说明：参考前面的Yum安装，示例为8.2.18版本，两台服务器互为Master和Slave

通用配置

编辑MySQL配置文件

vi /etc/my.cnf

节点1的配置

server-id=1
log-bin=mysql-bin
auto_increment_offset=1
auto_increment_increment=2

节点2的配置

server-id=2
log-bin=mysql-bin
auto_increment_offset=2
auto_increment_increment=2

*如果没有修改初始密码会有提示需要修改初始密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@a22w0rd';

节点1配置

创建同步用户与权限分配

use mysql;
create user 'syncuser'@'10.60.146.181' identified with mysql_native_password by 'P@ssw0rd';
grant replication slave on *.* to 'syncuser'@'10.60.146.181'; 
grant replication client on *.* to 'syncuser'@'10.60.146.181';             
flush privileges;

查看权限

show grants for 'syncuser'@'10.60.146.181';

查看节点1Master的状态

根据BIN Log的文件和Position配置下面的Master参数

show master status;

配置连接Master的参数

此时Master为对端，但是master_log_file和master_log_pos填写自己的

change master to
master_host='10.60.146.181',
master_user='syncuser',
master_password='P@ssw0rd',
master_log_file='mysql-bin.000002',
master_log_pos=157;

开启Slave节点

start slave;

查看Slave状态

show slave status\G

节点2配置

节点2进行同步账号的配置

use mysql;
create user 'syncuser'@'10.60.158.133' identified with mysql_native_password by 'P@ssw0rd';      
grant replication slave on *.* to 'syncuser'@'10.60.158.133'; 
grant replication client on *.* to 'syncuser'@'10.60.158.133';                         
flush privileges;

查看权限

show grants for 'syncuser'@'10.60.158.133';

查看节点2Master的状态

show master status;

根据Master的状态信息配置连接信息

change master to
master_host='10.60.158.133',
master_user='syncuser',
master_password='P@ssw0rd',
master_log_file='mysql-bin.000002',
master_log_pos=157;

开启Slave节点

start slave;

查看节点状态

show slave status\G

MySQL InnoDB Cluster 与 ClusterSet部署

ClusterSet为新的多集群架构，基于InnoDB引擎，在InnoDB Cluster基础之上

逻辑结构

MySQL Replication->MySQL ReplicaSet->MySQL Group Replication->MySQL InnoDB Cluster->MySQL InnoDB ClusterSet

因此部署ClusterSet先要部署InnoDB Cluster

InnoDB Cluster部署

部署准备

先确保防火墙等安全工具是关闭状态

setenforce 0
getenforce
systemctl stop firewalld

此处设置密码P@a22w0rd

配置hosts文件

cat >>/etc/hosts <<EOF
10.60.29.237 mysql-1
10.60.64.182 mysql-2
EOF

下载和安装

安装MySQL-Shell

找到对应版本，https://downloads.mysql.com/archives/shell/
获取下载链接，可以通过wget下载，以下为CentOS7 8.0.28的版本链接

wget https://cdn.mysql.com/archives/mysql-shell/mysql-shell-8.0.28-1.el7.x86_64.rpm
yum localinstall mysql-shell-8.0.28-1.el7.x86_64.rpm -y

安装MySQL-Router

找到对应版本，https://downloads.mysql.com/archives/router/
获取下载链接，可以通过wget下载，以下为CentOS7 8.0.28的版本链接

wget https://downloads.mysql.com/archives/get/p/41/file/mysql-router-community-8.0.28-1.el7.x86_64.rpm
yum localinstall mysql-router-community-8.0.28-1.el7.x86_64.rpm -y

*MySQL 8.0.28 Allinone安装参考

wget https://repo.mysql.com//mysql80-community-release-el7-6.noarch.rpm
rpm -ivh mysql80-community-release-el7-6.noarch.rpm
yum makecache

vi /etc/yum.repos.d/mysql-community.repo

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-preview]
name=MySQL Tools Preview
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

yum install -y mysql-community-server
systemctl start mysqld
systemctl enable mysqld

grep 'password' /var/log/mysqld.log
mysql -u root -p

alter user 'root'@'localhost' identified by 'P@22w0rd';
create user 'root'@'%' identified with mysql_native_password by 'P@22w0rd';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

vi /etc/my.cnf

server-id=1

setenforce 0
getenforce
systemctl stop firewalld

cat >>/etc/hosts <<EOF
10.60.29.237 mysql-1
10.60.64.182 mysql-2
EOF

wget https://cdn.mysql.com/archives/mysql-shell/mysql-shell-8.0.28-1.el7.x86_64.rpm
yum localinstall mysql-shell-8.0.28-1.el7.x86_64.rpm -y

wget https://downloads.mysql.com/archives/get/p/41/file/mysql-router-community-8.0.28-1.el7.x86_64.rpm
yum localinstall mysql-router-community-8.0.28-1.el7.x86_64.rpm -y

MySQL Shell 配置Cluster

注意：最好改密码并赋予远程登录的权限

alter user 'root'@'localhost' identified by 'P@22w0rd';
create user 'root'@'%' identified with mysql_native_password by 'P@22w0rd';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

打开 mysqlsh 控制界面

分别连接各个节点，连接后输入Y则确认记住密码，后续使用MySQL Shell可以直接登录

配置第1个节点，连接后执行实例配置，分别输入y

\c root@mysql-1:3306
dba.configureInstance();

检查配置，最后结果要出现"status": "ok"

dba.checkInstanceConfiguration("root@mysql-1:3306");

配置第2个节点

\c root@mysql-2:3306
dba.configureInstance();

检查配置，最后结果要出现"status": "ok"

dba.checkInstanceConfiguration("root@mysql-2:3306");

创建cluster

连接任意一台机器，此处选择mysql-1，创建节点之后添加实例，选择C克隆模式，如果还需多加几个节点则继续按此方式添加

\c root@mysql-1:3306
var cluster = dba.createCluster("mycluster");
cluster.addInstance('root@mysql-2:3306')

最后查看集群状态

cluster.status();

Cluster TroubleShooting

Cannot add an instance with the same server UUID

Cluster.addInstance: Cannot add an instance with the same server UUID (16f40394-eac7-11ec-80a4-5254003ad219) of an active member of the cluster '10-60-29-237:3306'. Please change the server UUID of the instance to add, all members must have a unique server UUID. (RuntimeError)

解决方法：https://zhuanlan.zhihu.com/p/375776925

登录MysQL中生成新的UUID

select uuid();
b2b9b7ab-eaea-11ec-9428-5254009a3cf1

登录MysQL中查看数据目录

show variables like 'datadir';

vi /var/lib/mysql/auto.cnf
server-uuid=b2b9b7ab-eaea-11ec-9428-5254009a3cf1
systemctl restart mysqld

The instance 'x' has a populated Metadata schema

Dba.createCluster: dba.createCluster: Unable to create cluster. The instance 'mysql1:3306' has a populated Metadata schema and belongs to that Metadata. Use either dba.dropMetadataSchema() to drop the schema, or dba.rebootClusterFromCompleteOutage() to reboot the cluster from complete outage. (RuntimeError)

解决办法：重启集群

dba.rebootClusterFromCompleteOutage()

The START GROUP_REPLICATION command failed

Unable to start Group Replication for instance 'mysql3:3306'. Please check the MySQL server error log for more information.
Cluster.addInstance: Group Replication failed to start: MySQL Error 3096 (HY000): mysql3:3306: The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer. (RuntimeError)

解决办法：检查hosts配置

InnoDB ClusterSet but is not ONLINE

Dba.getCluster: This function is not available through a session to an InnoDB Cluster that belongs to an InnoDB ClusterSet but is not ONLINE (MYSQLSH 51314)

解决办法：重启集群

dba.rebootClusterFromCompleteOutage()

如果ClusterSet集群都挂掉的时候可以分别登录两个集群重启集群

TS Reference：http://t.zoukankan.com/zhi-leaf-p-12599822.html

InnoDB ClusterSet部署

连接到主集群主节点

\c root@mysql-1:3306

获取之前创建的Cluster并创建集群，命名为myclusterset

cluster = dba.getCluster();
clusterset = cluster.createClusterSet('myclusterset');

或者使用clusterset = cluster.create_cluster_set('myclusterset')命令，但是官方的命令是createClusterSet

查看ClusterSet状态，集群角色PRIMARY，状态HEALTHY

clusterset.status();

查看ClusterSet状态，可以看到集群的拓扑

clusterset.describe();

配置第2各集群的实例

\c root@mysql-3:3306
dba.configureInstance()

\c root@mysql-4:3306
dba.configureInstance()

*配置实例并创建 MICS 管理用户，用来同步主库集群数据。测试过程中使用root账号也行

dba.configureInstance("root@mysql-3:3306",{"clusterAdmin":"ics_admin","clusterAdminPassword":"P@55w0rd"})

dba.configureInstance('root@127.0.0.1:4410', {clusterAdmin: 'icadmin'})

添加 REPLICA 集群

REPLICA 集群 MIC 命名为 urcluster

urcluster=clusterset.createReplicaCluster("root@mysql-3:3306","urcluster",{"recoveryMethod":"clone","recoveryProgress":2})

REPLICA 集群添加其他实例

urcluster.addInstance('root@mysql-4:3306')

其它参考命令
urcluster.add_instance("root@mysql-4:3306",{"recoveryMethod":"clone"})

查看ClusterSet状态

mycluster 为主，urcluster 为备

clusterset.describe()

验证完成的REPLICA集群和 InnoDB ClusterSet 部署是否健康

myclusterset.status({extended: 1})

数据写入与同步测试

在PRIMARY集群主节点写条数据简单测试下数据同步：主库表 t1 插一条记录

首先连接到主集群主节点，开启SQL模式，创建数据库和表并插入数据

\c root@mysql-1:3306
\sql
create database bcdr;
use ytt;
create table t1(id int primary key,r1 int);
insert t1 values (1,100);
table t1;

+----+-----+
| id | r1  |
+----+-----+
|  1 | 100 |
+----+-----+
1 row in set (0.0006 sec)

切换到REPLICA集群节点，查看效果，查看表 t1 数据已经正常同步，并且只读

\c root@mysql-3:3306
\sql
use ytt;
table t1;

+----+-----+
| id | r1  |
+----+-----+
|  1 | 100 |
+----+-----+
1 row in set (0.0006 sec)

尝试创建数据库

create table t2 like t1;

ERROR: 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

查看ClusterSet状态

clusterset = dba.getClusterSet();
clusterset.status();

ClusterSet集群切换测试

手动主备Cluster集群切换

手动进行主备切换 urcluster 已经被提升了新的Primary Cluster

clusterset = dba.getClusterSet();
clusterset.setPrimaryCluster('urcluster')

执行结果示例

Switching the primary cluster of the clusterset to 'urcluster'
* Verifying clusterset status
** Checking cluster mycluster
  Cluster 'mycluster' is available
** Checking cluster urcluster
  Cluster 'urcluster' is available

* Refreshing replication account of demoted cluster
* Synchronizing transaction backlog at 10-60-208-94:3306
** Transactions replicated  ############################################################  100% 

* Updating metadata

* Updating topology
** Changing replication source of 10-60-250-91:3306 to 10-60-208-94:3306
** Changing replication source of 10-60-128-196:3306 to 10-60-208-94:3306
* Acquiring locks in replicaset instances
** Pre-synchronizing SECONDARIES
** Acquiring global lock at PRIMARY
** Acquiring global lock at SECONDARIES

* Synchronizing remaining transactions at promoted primary
** Transactions replicated  ############################################################  100% 

* Updating replica clusters
Cluster 'urcluster' was promoted to PRIMARY of the clusterset. The PRIMARY instance is '10-60-208-94:3306'

查看集群状态

新 PRIMARY 为 urcluster，mycluster 变为 REPLICA

clusterset.status();

{
    "clusters": {
        "mycluster": {
            "clusterRole": "REPLICA", 
            "clusterSetReplicationStatus": "OK", 
            "globalStatus": "OK"
        }, 
        "urcluster": {
            "clusterRole": "PRIMARY", 
            "globalStatus": "OK", 
            "primary": "10-60-208-94:3306"
        }
    }, 
    "domainName": "myclusterset", 
    "globalPrimaryInstance": "10-60-208-94:3306", 
    "primaryCluster": "urcluster", 
    "status": "HEALTHY", 
    "statusText": "All Clusters available."
}

切换集群参考：https://dev.mysql.com/doc/mysql-shell/8.0/en/innodb-clusterset-switchover.html

Cluster 切换与数据同步测试

1.创建数据库，其它节点同步

2.关闭主节点systemctl stop mysqld，切换到其它节点

3.登录（可以使用Router地址）发现仍可以访问
mysql -u root -h192.168.1.1 -P6446 –p

4.查看数据

5.写入数据测试

操作命令说明

MySQL Shell连接到数据库

\c root@mysql-1:3306

或者

\connect root@127.0.0.1:3306

MySQL Shell SQL模式下，查看数据库

show databases;

连接到一个节点

\connect root@127.0.0.1:3310

获取Cluster实例

cluster = dba.getCluster();

Use current cluster as main cluster to create a Clusterset

clusterset = cluster1.createClusterSet('myclusterset');

查看cluster集群状态

var cluster = dba.getCluster()
cluster.status()

删除Cluster集群

var cluster = dba.getCluster()
cluster.dissolve()

查看 ClusterSet 状态

clusterset = dba.getClusterSet();
clusterset.status();

验证完成的REPLICA集群和 InnoDB ClusterSet 部署是否健康

clusterset.status({extended: 1})

配置MySQL-Router

注意：MySQL Router是路由节点，仅需配置在一个节点上即可

配置文件

暂时参考

[DEFAULT]
#日志存放目录
logging_folder = /usr/local/mysql-router/myrouter/log
#插件存放目录
plugin_folder = /usr/local/mysql-router/lib/mysqlrouter
#配置文件存放目录
config_folder = /etc/mysql-route
#运行目录
runtime_folder = /usr/local/mysql-router/myrouter/run
[routing:read_writer]
#写节点地址
bind_address = 192.168.1.13
#写节点端口
bind_port = 7001
#MySQL router提供两种mode：read-only和read-write，设置为read-write，常用于设置destinations为master时，实现master的高可用
#模式:读还是写
mode = read-write
#主节点地址：默认情况下第一台主数据库为写主库，当第一台主数据库DOWN机后，第二台数据库被提升为主库
destinations = node1:3306,node2:3306,node3:3306
max_connections = 1024
[routing:read_only]
#从节点负载均衡配置
#绑定的IP地址
bind_address = 192.168.1.13
#监听的端口
bind_port = 7002
mode = read-only
#主节点地址：默认情况下第一台主数据库为写主库，当第一台主数据库DOWN机后，第二台数据库被提升为主库
destinations = node1:3306,node2:3306,node3:3306
max_connections = 1024


lower_case_table_names=1

其它参考

配置文件参考（暂时没用）

[mysqld]
#mysql自身配置，这里默认即可
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address    = 0.0.0.0

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#每台要不一样
server_id = 1 
#开启gitd复制
gtid_mode = ON
log_bin = bilog
log_slave_updates = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
transaction_write_set_extraction = XXHASH64
enforce_gtid_consistency = ON
#确保GR_applier按照一致的顺序复现事务
slave_preserve_commit_order = ON
binlog_checksum = NONE
#UUID号，集群中要一致
loose-group_replication_group_name = a38e32fd-5fb6-11e8-ad7a-00259015d941
#默认关闭, 防止重启后自动创建新的Group
loose-group_replication_start_on_boot = OFF 
#默认关闭, 防止自动创建新的Group
loose-group_replication_bootstrap_group = OFF 
loose-group_replication_allow_local_disjoint_gtids_join = ON
#在单主模式中为ON，在多主模式中为OFF
loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

参考链接

InnoDB Cluster部署上中下
https://cloud.tencent.com/developer/article/1834948

Cluster部署参考

https://blog.csdn.net/qq_42890862/article/details/120308009

Cluster详细部署参考
https://blog.csdn.net/weixin_42089154/article/details/119685457

ClusterSet部署

详细介绍部署切换测试说明

https://www.modb.pro/db/397134

ClusterSet简单部署参考

https://blog.csdn.net/yangjianrong1985/article/details/121917801

MGR

MySQL Group Replication
是MySQL在2016年12月推出的一个全新的高可用与高扩展的解决方案。

为什么要使用MGR

MySQL推出MGR之前，传统复制分为两种，异步复制、半同步复制。

MGR提供了哪些功能？

1、同步复制；
2、单主模式、多主模式；
3、自动切换；
4、弹性扩展。

MGR限制？

MGR原理

单主模式

多主模式

参考链接

https://blog.csdn.net/dahai533418/article/details/105074779

MySQL安装与部署

通过Yum安装指定版本MySQL

下载和安装

启动和使用

MySQL互为主从集群部署

通用配置

节点1配置

节点2配置

MySQL InnoDB Cluster 与 ClusterSet部署

InnoDB Cluster部署

部署准备

配置hosts文件

下载和安装

安装MySQL-Shell

安装MySQL-Router

*MySQL 8.0.28 Allinone安装参考

MySQL Shell 配置Cluster

Cluster TroubleShooting

InnoDB ClusterSet部署

添加 REPLICA 集群

REPLICA 集群添加其他实例

查看ClusterSet状态

数据写入与同步测试

ClusterSet集群切换测试

手动主备Cluster集群切换

查看集群状态

Cluster 切换与数据同步测试

操作命令说明

配置MySQL-Router

配置文件

其它参考

配置文件参考（暂时没用）

参考链接

MGR

为什么要使用MGR

MGR提供了哪些功能？

MGR限制？

MGR原理

单主模式

多主模式

参考链接

3 comments