MySQ8.0创建innodb cluster

872 字
4 分钟
MySQ8.0创建innodb cluster
2025-05-06

简介#

MySQL InnoDB Cluster 是Oracle从MySQL 8.0开始内置在MySQL中的高可用集群方案,提供了Group Replication,一种新的主从复制技术,同时InnoDB Cluster内置了failover机制,当主库发生故障时,集群会自动选举新主库,如果结合mysql router,是MHA在8.0版本中的很好的替代。

MySQL Group Replication与传统主从复制的差异#

特性传统复制InnoDB Cluster
复制方式binlog 异步复制group replication
一致性最终一致准同步(事务级一致)
主节点故障需要人工切换自动选主
集群状态无统一管理cluster metadata
节点写入只能主库有多主(多写)模式
数据冲突可能group replication 会检测

部署innodb cluster#

一、实验环境#

  • 三台虚拟机:mysql-11, mysql-12, mysql-13
  • 系统:Rocky Linux 9
  • MySQL版本:8.0.45

二、安装mysqlsh#

mysqlsh是支持js、Python等多种语言在内的高级MySQL客户端,可以利用内置的函数实现对MySQL的管理。

1. 下载并安装#

Terminal window
# 下载
wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.45-linux-glibc2.28-x86-64bit.tar.gz
# 解压
tar -xf mysql-shell-8.0.45-linux-glibc2.28-x86-64bit.tar.gz
# 重命名
mv mysql-shell-8.0.45-linux-glibc2.28-x86-64bit mysql-shell
# 配置环境变量
echo 'export PATH=/opt/mysql-shell/bin:$PATH' >> /etc/profile
# 生效环境变量
source /etc/profile

2. 验证#

Terminal window
mysqlsh --version

应出现如下结果

mysqlsh Ver 8.0.45 for Linux on x86_64 - for MySQL 8.0.45 (MySQL Community Server (GPL))

三、创建用户#

InnoDB Cluster创建时,如果hostname和report_host配置不一致,很容易直接崩溃。

在mysqlsh连接到数据库时,会自动将主机名解析为ip,因此必须创建'root'@'192.168.100.%'用户,以允许root从所有192.168.100.0/24网段下的主机登录。相比’%‘,此方法的安全性更好。

CREATE USER 'root'@'192.168.100.%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.100.%' WITH GRANT OPTION;
flush privileges;

四、实例初始化#

为集群中的每个实例执行:

  1. 初始化当前实例
dba.configureInstance()
  1. 检查服务器节点配置
dba.checkInstanceConfiguration('root@mysql-11:3306')
dba.checkInstanceConfiguration('root@mysql-12:3306')
dba.checkInstanceConfiguration('root@mysql-13:3306')
  1. 预期结果
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as [mysql-master-02:3306/mysql-replica-11:3306/mysql-replica-12:3306]
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected
Checking instance configuration...
Instance configuration is compatible with InnoDB cluster
The instance 'mysql-master-02:3306' is valid to be used in an InnoDB cluster.
{
"status": "ok"
}

五、创建集群#

在选定的主库中执行

var cluster = dba.createCluster('mainCluster',{multiPrimary:false})
// {multiPrimary:false}: 不启用多主模式(默认单主)

应该出现如下结果

A new InnoDB Cluster will be created on instance 'mysql-11:3306'.
Validating instance configuration at mysql-11:3306...
This instance reports its own address as mysql-11:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mysql-11:3306'. Use the localAddress option to override.
* Checking connectivity and SSL configuration...
Creating InnoDB Cluster 'mainCluster' on 'mysql-11:3306'...
Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

六、将其他节点加入集群#

cluster.addInstance('root@mysql-12:3306')
cluster.addInstance('root@mysql-13:3306')
Tip

注意:在将节点加入到集群中后,需要重新启动mysqld,使用systemctl status mysqld查看目标节点状态,如果不是running状态,则需要systemctl start mysqld

之后会提示The instance 'your-instance:3306' was successfully added to the cluster metadata.

七、查看集群状态#

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

应显示json格式的集群信息

{
"clusterName": "mainCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "mysql-11:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"mysql-11:3306": {
"address": "mysql-11:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.45"
},
"mysql-12:3306": {
"address": "mysql-12:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.45"
},
"mysql-13:3306": {
"address": "mysql-13:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.45"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "mysql-11:3306"
}

集群重启后恢复集群#

dba.rebootClusterFromCompleteOutage()

支持与分享

如果这篇文章对你有帮助,欢迎分享给更多人或赞助支持!

赞助
MySQ8.0创建innodb cluster
https://white-festa.net/posts/mysql创建innodb-cluster/
作者
FreelyTomorrow
发布于
2025-05-06
许可协议
CC BY-NC-SA 4.0
最后更新于 2025-05-06,距今已过 344 天

部分内容可能已过时

评论区

Profile Image of the Author
FreelyTomorrow
If you shout loud enough, you'll be the one.
公告 - 2025/12/21
本站框架已由Ghost迁移到Astro!
音乐
封面

音乐

暂未播放

0:00 0:00
暂无歌词
分类
标签
站点统计
文章
68
分类
12
标签
40
总字数
125,818
运行时长
0
最后活动
0 天前

目录