2. 首页 > MySQL教程 > 正文

MySQL教程FG289-MySQL集群管理实战

教程整理：风哥教程  |  更新时间：2026-01-18  |  教程分类：MySQL教程  |  文档学习：32

内容简介：本文主要介绍MySQL存储过程参数与返回值的相关知识，包括参数类型、使用方法和最佳实践等内容。风哥教程参考MySQL官方文档MySQL Cluster、Group Replication。

Part01-基础概念与理论知识

1.1 集群的重要性

MySQL集群提供高可用性、可扩展性和负载均衡能力，是现代数据库架构的核心。 01 更多视频教程www.fgedu.net.cn

1.2 集群类型

MySQL集群主要分为以下类型： 02 学习交流加群风哥微信: itpux-com

# 集群类型

1. 主从复制集群
– 一主多从
– 读写分离
– 异步复制

2. 组复制集群
– 多主架构
– 自动故障转移
– 强一致性

3. InnoDB Cluster
– 基于组复制
– 自动管理
– 高可用性

4. MySQL Cluster (NDB)
– 内存数据库
– 高可用性
– 实时性

Part02-生产环境规划与建议

2.1 主从复制集群

部署主从复制集群：

# 主从复制集群部署

1. 主库配置
vim /etc/my.cnf

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW

2. 重启主库
systemctl restart mysqld

3. 创建复制用户
mysql> CREATE USER ‘repl’@’192.168.1.%’ IDENTIFIED BY ‘repl_password’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.1.%’;

4. 查看主库状态
mysql> SHOW MASTER STATUS;

输出示例：
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 12345 | | | |
+——————+———-+————–+——————+——————-+

5. 从库配置
vim /etc/my.cnf

[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1

6. 重启从库
systemctl restart mysqld

7. 配置从库连接主库
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.100′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’repl_password’,
-> MASTER_LOG_FILE=’mysql-bin.000001′,
-> MASTER_LOG_POS=12345;

8. 启动复制
mysql> START SLAVE;

9. 查看从库状态
mysql> SHOW SLAVE STATUS\G;

输出示例：
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 12345
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 12345
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 12345
Relay_Log_Space: 12345
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 12345678-1234-1234-1234-1234567890ab
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:

2.2 InnoDB Cluster

部署InnoDB Cluster： 03 学习交流加群风哥QQ113257174

# InnoDB Cluster部署

1. 安装MySQL Shell
wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.36-linux-glibc2.12-x86_64.tar.gz
tar -xzf mysql-shell-8.0.36-linux-glibc2.12-x86_64.tar.gz
cd mysql-shell-8.0.36-linux-glibc2.12-x86_64/bin

2. 连接到第一个节点
./mysqlsh root@192.168.1.100

3. 检查实例状态
MySQL JS> dba.checkInstanceConfiguration(‘root@192.168.1.100:3306’)

输出示例：
{“status”: “ok”, “message”: “The instance is ready for InnoDB Cluster”}

4. 配置实例
MySQL JS> dba.configureInstance(‘root@192.168.1.100:3306’)

5. 创建集群
MySQL JS> var cluster = dba.createCluster(‘myCluster’)

6. 添加其他节点
MySQL JS> cluster.addInstance(‘root@192.168.1.101:3306’)
MySQL JS> cluster.addInstance(‘root@192.168.1.102:3306’)

7. 查看集群状态
MySQL JS> cluster.status()

输出示例：
{
“clusterName”: “myCluster”,
“defaultReplicaSet”: {
“name”: “default”,
“primary”: “192.168.1.100:3306”,
“ssl”: “REQUIRED”,
“status”: “OK”,
“statusText”: “Cluster is ONLINE and can tolerate up to ONE failure.”,
“topology”: {
“192.168.1.100:3306”: {
“address”: “192.168.1.100:3306”,
“mode”: “R/W”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“192.168.1.101:3306”: {
“address”: “192.168.1.101:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“192.168.1.102:3306”: {
“address”: “192.168.1.102:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
}
}
}
}

Part03-生产环境项目实施方案

3.1 集群监控

监控集群状态和性能： 04 风哥提示：

# 集群监控

1. 查看集群状态
MySQL JS> cluster.status()

2. 查看集群拓扑
MySQL JS> cluster.describe()

输出示例：
{
“clusterName”: “myCluster”,
“defaultReplicaSet”: {
“name”: “default”,
“topology”: [
{
“address”: “192.168.1.100:3306”,
“label”: “192.168.1.100:3306”,
“role”: “HA”
},
{
“address”: “192.168.1.101:3306”,
“label”: “192.168.1.101:3306”,
“role”: “HA”
},
{
“address”: “192.168.1.102:3306”,
“label”: “192.168.1.102:3306”,
“role”: “HA”
}
]
}
}

3. 查看集群健康
MySQL JS> cluster.describe()

4. 设置监控告警
# 配置Prometheus采集集群指标
# 配置Grafana显示集群状态

3.2 集群扩容

扩展集群节点：

# 集群扩容

1. 准备新节点
# 安装MySQL
# 配置my.cnf
# 启动MySQL服务

2. 检查新节点状态
MySQL JS> dba.checkInstanceConfiguration(‘root@192.168.1.103:3306’)

输出示例：
{“status”: “ok”, “message”: “The instance is ready for InnoDB Cluster”}

3. 配置新节点
MySQL JS> dba.configureInstance(‘root@192.168.1.103:3306’)

4. 添加新节点到集群
MySQL JS> cluster.addInstance(‘root@192.168.1.103:3306’)

5. 验证新节点
MySQL JS> cluster.status()

输出示例：
{
“clusterName”: “myCluster”,
“defaultReplicaSet”: {
“name”: “default”,
“primary”: “192.168.1.100:3306”,
“ssl”: “REQUIRED”,
“status”: “OK”,
“statusText”: “Cluster is ONLINE and can tolerate up to ONE failure.”,
“topology”: {
“192.168.1.100:3306”: {
“address”: “192.168.1.100:3306”,
“mode”: “R/W”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“192.168.1.101:3306”: {
“address”: “192.168.1.101:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“192.168.1.102:3306”: {
“address”: “192.168.1.102:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“192.168.1.103:3306”: {
“address”: “192.168.1.103:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
}
}
}
}

Part04-生产案例与实战讲解

4.1 故障转移

集群故障转移处理： 05更多学习教程公众号风哥教程itpux_com

# 故障转移

1. 模拟主库故障
# 在主库上执行
shutdown -h now

2. 查看集群状态
MySQL JS> cluster.status()

输出示例：
{
“clusterName”: “myCluster”,
“defaultReplicaSet”: {
“name”: “default”,
“primary”: “192.168.1.101:3306”,
“ssl”: “REQUIRED”,
“status”: “OK”,
“statusText”: “Cluster is ONLINE and can tolerate up to ONE failure.”,
“topology”: {
“192.168.1.100:3306”: {
“address”: “192.168.1.100:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “OFFLINE”
},
“192.168.1.101:3306”: {
“address”: “192.168.1.101:3306”,
“mode”: “R/W”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“192.168.1.102:3306”: {
“address”: “192.168.1.102:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
}
}
}
}

3. 故障节点恢复后重新加入集群
# 启动故障节点
poweron

# 重新加入集群
MySQL JS> cluster.rejoinInstance(‘root@192.168.1.100:3306’)

# 查看集群状态
MySQL JS> cluster.status()

输出示例：
{
“clusterName”: “myCluster”,
“defaultReplicaSet”: {
“name”: “default”,
“primary”: “192.168.1.101:3306”,
“ssl”: “REQUIRED”,
“status”: “OK”,
“statusText”: “Cluster is ONLINE and can tolerate up to ONE failure.”,
“topology”: {
“192.168.1.100:3306”: {
“address”: “192.168.1.100:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“192.168.1.101:3306”: {
“address”: “192.168.1.101:3306”,
“mode”: “R/W”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“192.168.1.102:3306”: {
“address”: “192.168.1.102:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
}
}
}
}

4.2 集群维护

集群日常维护： 06 from mysql视频:www.itpux.com

# 集群维护

1. 滚动升级
# 逐个节点升级MySQL版本
# 升级第一个节点
systemctl stop mysqld
yum install mysql-community-server-8.0.36
systemctl start mysqld

# 验证节点状态
MySQL JS> cluster.status()

# 升级第二个节点
systemctl stop mysqld
yum install mysql-community-server-8.0.36
systemctl start mysqld

# 验证节点状态
MySQL JS> cluster.status()

# 升级第三个节点
systemctl stop mysqld
yum install mysql-community-server-8.0.36
systemctl start mysqld

# 验证节点状态
MySQL JS> cluster.status()

2. 备份集群
# 使用XtraBackup备份主库
xtrabackup –backup –target-dir=/backup/full –user=root –password=password

3. 监控集群性能
# 使用Performance Schema监控
mysql> SELECT * FROM performance_schema.replication_group_members;

输出示例：
+—————————+————————————–+————-+————-+—————+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+—————————+————————————–+————-+————-+—————+
| group_replication_applier | 12345678-1234-1234-1234-1234567890ab | 192.168.1.100 | 3306 | ONLINE |
| group_replication_applier | 23456789-2345-2345-2345-234567890abc | 192.168.1.101 | 3306 | ONLINE |
| group_replication_applier | 34567890-3456-3456-3456-34567890abcd | 192.168.1.102 | 3306 | ONLINE |
+—————————+————————————–+————-+————-+—————+

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息