技术分享 | innodb-cluster 扫盲-安装篇

最新推荐文章于 2025-10-14 18:16:40 发布
原创 最新推荐文章于 2025-10-14 18:16:40 发布 · 1.9k 阅读 · 2 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#MySQL #数据库

作者：杨涛涛

本文介绍用 MySQL Shell 搭建 MGR 的详细过程。

1、使用前，关掉防火墙，包括 selinux，firewalld，或者 MySQL 企业版的firewall（如果用了企业版的话）

2、两台机器：（4 台 MySQL 实例）

192.168.2.219 centos-ytt57-1 3311/3312
192.168.2.229 centos-ytt57-2 3311/3312

3、安装 MySQL（两台都装）, MySQL Shell（任意一台）, mysqlrouter(任意一台，官方建议和应用程序装在一个服务器上)

yum install mysql-community-server mysql-shell mysql-router-community

4、搭建 InnoDB-Cluster（两台都装）

1. 配置文件如下：

shell>vi /etc/my.cnf
master-info-repository=table
relay-log-info-repository=table
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_slave_updates=ON
binlog_format=ROW
transaction_write_set_extraction=XXHASH64

2. 系统 HOSTS 配置（两台都配）

shell>vi /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.2.219 centos-ytt57-2
192.168.2.229 centos-ytt57-3

用 MySQL coalesce 函数确认下 report-host 是否设置正确

(root@localhost) : [(none)] >SELECT coalesce(@@report_host, @@hostname) as r;
+----------------+
| r |
+----------------+
| centos-ytt57-1 |
+----------------+
1 row in set (0.00 sec)

3. 创建管理员用户搭建 GROUP REPLICATION （四个节点都要）

create user root identified by 'Root@123';
grant all on *.* to root with grant option;
flush privileges;

4. MySQLsh 连接其中一台节点：

[root@centos-ytt57-1 mysql]# mysqlsh root@localhost:3321

5. 检查配置正确性：（如果这里不显示 OK，把对应的参数加到配置文件重启 MySQL 再次检查） dba.checkInstanceConfiguration("root@centos-ytt57-2:3311"); dba.checkInstanceConfiguration("root@centos-ytt57-2:3312"); dba.checkInstanceConfiguration("root@centos-ytt57-3:3311"); dba.checkInstanceConfiguration("root@centos-ytt57-3:3312");

mysqlsh 执行检测

[root@centos-ytt57-1 mysql]# mysqlsh --log-level=8 root@localhost:3311

MySQL localhost:3311 ssl JS > dba.checkInstanceConfiguration("root@centos-ytt57-2:3311")
{
"status": "ok"
}

6. 创建集群，节点 1 上创建。（结果显示 Cluster successfully created 表示成功）

MySQL localhost:3311 ssl JS > var cluster = dba.createCluster('ytt_mgr');

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.

7. 添加节点 2，3，4（全部显示 OK，表示添加成功）

MySQL localhost:3311 ssl JS > cluster.addInstance('root@centos-ytt57-2:3312');
MySQL localhost:3311 ssl JS > cluster.addInstance('root@centos-ytt57-3:3311');
MySQL localhost:3311 ssl JS > cluster.addInstance('root@centos-ytt57-3:3312');

8. 查看拓扑图：（describe 简单信息，status 详细描述）

MySQL localhost:3311 ssl JS > cluster.describe()
{
"clusterName": "ytt_mgr",
"defaultReplicaSet": {
"name": "default",
"topology": [
{
"address": "centos-ytt57-2:3311",
"label": "centos-ytt57-2:3311",
"role": "HA",
"version": "8.0.17"
},
{
"address": "centos-ytt57-2:3312",
"label": "centos-ytt57-2:3312",
"role": "HA",
"version": "8.0.17"
},
{
"address": "centos-ytt57-3:3311",
"label": "centos-ytt57-3:3311",
"role": "HA",
"version": "8.0.17"
},
{
"address": "centos-ytt57-3:3312",
"label": "centos-ytt57-3:3312",
"role": "HA",
"version": "8.0.17"
}
],
"topologyMode": "Single-Primary"
}
}

MySQL localhost:3311 ssl JS > cluster.status()

"clusterName": "ytt_mgr",
"defaultReplicaSet": {
"name": "default",
"primary": "centos-ytt57-2:3311",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"centos-ytt57-2:3311": {
"address": "centos-ytt57-2:3311",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"centos-ytt57-2:3312": {
"address": "centos-ytt57-2:3312",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"centos-ytt57-3:3311": {
"address": "centos-ytt57-3:3311",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"centos-ytt57-3:3312": {
"address": "centos-ytt57-3:3312",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "centos-ytt57-2:3311"

9. 简单测试下数据是否同步

(root@localhost) : [(none)] >create database ytt;
Query OK, 1 row affected (0.03 sec)

(root@localhost) : [(none)] >use ytt;
Database changed
(root@localhost) : [ytt] >create table p1(id int primary key, log_time datetime);
Query OK, 0 rows affected (0.08 sec)

(root@localhost) : [ytt] >insert into p1 values (1,now());
Query OK, 1 row affected (0.04 sec)

(root@localhost) : [ytt] >show master status;
+---------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------+
| mysql0.000001 | 25496 | | | 6c7bb9db-b759-11e9-a9c0-0800276cf0fc:1-41 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

查看其他三个节点

(root@localhost) : [ytt] >show tables;
+---------------+
| Tables_in_ytt |
+---------------+
| p1 |
+---------------+
1 row in set (0.00 sec)

(root@localhost) : [ytt] >select * from p1;
+----+---------------------+
| id | log_time |
+----+---------------------+
| 1 | 2019-08-05 16:44:20 |
+----+---------------------+
1 row in set (0.00 sec)

停掉主节点：

[root@centos-ytt57-2 mysql0]# systemctl stop mysqld@0

现在查看，主节点已经变为本机 3312节点

"centos-ytt57-2:3312": {
"address": "centos-ytt57-2:3312",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}

10. 报错处理

错误日志里显示

2019-08-05T09:01:35.125591Z 0 [ERROR] Plugin group_replication reported: 'The group name option is mandatory'
2019-08-05T09:01:35.125622Z 0 [ERROR] Plugin group_replication reported: 'Unable to start Group Replication on boot'

同时用 cluster.rescan() 扫描，发现

The instance 'centos-ytt57-2:3311' is no longer part of the ReplicaSet.

重新加入此节点到集群：

cluster.rejoinInstance('centos-ytt57-2:3311')

再次执行cluster.status()查看集群状态："status": "OK",

11. 移除和加入

cluster.removeInstance("centos-ytt57-3:3312");
The instance 'centos-ytt57-3:3312' was successfully removed from the cluster.
cluster.addInstance("centos-ytt57-3:3312");
The instance 'centos-ytt57-3:3312' was successfully added to the cluster.

12. 用 mysqlrouter 生成连接 MGR 相关信息。

涉及到两个用户：

--user=mysqlrouter 是使用mysqlrouter的系统用户

自动创建的MySQL 用户是用来与MGR通信的用户。如果想查看这个用户的用户名以及密码，就加上

--force-password-validation，不过一般也没有必要查看。

[root@centos-ytt57-2 ytt]# mysqlrouter --bootstrap root@centos-ytt57-2:3311 --user=mysqlrouter --force-password-validation --report-host centos-ytt57-2
Please enter MySQL password for root:

# Reconfiguring system MySQL Router instance...

- Checking for old Router accounts
- Found old Router accounts, removing
- Creating mysql account mysql_router1_rdr89tx20r0a@'%' for cluster management
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /etc/mysqlrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB cluster 'ytt_mgr'

After this MySQL Router has been started with the generated configuration

$ /etc/init.d/mysqlrouter restart
or
$ systemctl start mysqlrouter
or
$ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

the cluster 'ytt_mgr' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: centos-ytt57-2:6446
- Read/Only Connections: centos-ytt57-2:6447

## MySQL X protocol

- Read/Write Connections: centos-ytt57-2:64460
- Read/Only Connections: centos-ytt57-2:64470

13. 启动 mysqlrouter，生效对应服务。

[root@centos-ytt57-2 mysqlrouter]# systemctl start mysqlrouter

14. 使用 ROUTER 连接 MySQL

创建一个普通用户使用 ROUTER (四个节点都建立，具体权限看个人)

[root@centos-ytt57-2 mysqlrouter]# /home/ytt/enter_mysql 80
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1779
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@centos-ytt57-2) : [(none)] >create user ytt_mysqlrouter;
Query OK, 0 rows affected (0.12 sec)

(root@centos-ytt57-2) : [(none)] >alter user ytt_mysqlrouter identified by 'mysql_router';
Query OK, 0 rows affected (0.09 sec)

(root@centos-ytt57-2) : [(none)] >grant all on ytt.* to ytt_mysqlrouter;
Query OK, 0 rows affected (0.05 sec)

(root@centos-ytt57-2) : [(none)] >flush privileges;
Query OK, 0 rows affected (0.07 sec)

14.1 写入端口连接：

[root@centos-ytt57-2 mysqlrouter]# mysql -uytt_mysqlrouter -pmysql_router -hcentos-ytt57-2 -P6446
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2030
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(ytt_mysqlrouter@centos-ytt57-2) : [(none)] >use ytt
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(ytt_mysqlrouter@centos-ytt57-2) : [ytt] >insert into p1 values (200,now());
Query OK, 1 row affected (0.04 sec)

(ytt_mysqlrouter@centos-ytt57-2) : [ytt] >select * from p1;
+-----+---------------------+
| id | log_time |
+-----+---------------------+
| 1 | 2019-08-05 23:15:38 |
| 2 | 2019-08-05 23:15:42 |
| 100 | 2019-08-05 23:52:58 |
| 200 | 2019-08-05 23:56:23 |
+-----+---------------------+
4 rows in set (0.00 sec)

(ytt_mysqlrouter@centos-ytt57-2) : [ytt] >\q
Bye
[root@centos-ytt57-2 mysqlrouter]#

14.2 读取端口连接：（默认循环选择读服务节点）

[root@centos-ytt57-2 mysqlrouter]# mysql -uytt_mysqlrouter -pmysql_router -hcentos-ytt57-2 -P6447
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 472
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(ytt_mysqlrouter@centos-ytt57-2) : [(none)] >use ytt
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(ytt_mysqlrouter@centos-ytt57-2) : [ytt] >insert into p1 values (300,now());
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
(ytt_mysqlrouter@centos-ytt57-2) : [ytt] >select * from p1;
+-----+---------------------+
| id | log_time |
+-----+---------------------+
| 1 | 2019-08-05 23:15:38 |
| 2 | 2019-08-05 23:15:42 |
| 100 | 2019-08-05 23:52:58 |
| 200 | 2019-08-05 23:56:23 |
+-----+---------------------+
4 rows in set (0.00 sec)

## (ytt_mysqlrouter@centos-ytt57-2) : [ytt] >\s

mysql Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id: 472
Current database: ytt
Current user: ytt_mysqlrouter@centos-ytt57-2
SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.17 MySQL Community Server - GPL
Protocol version: 10
Connection: centos-ytt57-2 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 6447
Uptime: 45 min 10 sec

## Threads: 5 Questions: 188 Slow queries: 0 Opens: 257 Flush tables: 3 Open tables: 161 Queries per second avg: 0.069



(ytt_mysqlrouter@centos-ytt57-2) : [ytt] >select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.01 sec)

(ytt_mysqlrouter@centos-ytt57-2) : [ytt] >\q
Bye

15. 机器全挂了，重启 MGR

MySQL localhost:3311 ssl JS > dba.rebootClusterFromCompleteOutage()
Reconfiguring the default cluster from complete outage...

The instance 'centos-ytt57-2:3312' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

The instance 'centos-ytt57-3:3311' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

The instance 'centos-ytt57-3:3312' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

The safest and most convenient way to provision a new instance is through
automatic clone provisioning, which will completely overwrite the state of
'localhost:3311' with a physical snapshot from an existing cluster member. To
use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental distributed state recovery may be safely used if you are sure
all updates ever executed in the cluster were done with GTIDs enabled, there
are no purged transactions and the new instance contains the same GTID set as
the cluster or a subset of it. To use this method by default, set the
'recoveryMethod' option to 'incremental'.

Incremental distributed state recovery was selected because it seems to be safely usable.

^C
The cluster was successfully rebooted.

Script execution interrupted by user.
MySQL localhost:3311 ssl JS > var cluster = dba.getCluster('ytt_mgr');
MySQL localhost:3311 ssl JS > cluste.status();
ReferenceError: cluste is not defined
MySQL localhost:3311 ssl JS > cluster.status();
{
   "clusterName": "ytt_mgr",
   "defaultReplicaSet": {
       "name": "default",
       "primary": "centos-ytt57-2:3311",
       "ssl": "REQUIRED",
       "status": "OK",
       "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
       "topology": {
           "centos-ytt57-2:3311": {
               "address": "centos-ytt57-2:3311",
               "mode": "R/W",
               "readReplicas": {},
               "role": "HA",
               "status": "ONLINE",
               "version": "8.0.17"
           },
           "centos-ytt57-2:3312": {
               "address": "centos-ytt57-2:3312",
               "mode": "R/O",
               "readReplicas": {},
               "role": "HA",
               "status": "ONLINE",
               "version": "8.0.17"
           },
           "centos-ytt57-3:3311": {
               "address": "centos-ytt57-3:3311",
               "mode": "R/O",
               "readReplicas": {},
               "role": "HA",
               "status": "ONLINE",
               "version": "8.0.17"
           },
           "centos-ytt57-3:3312": {
               "address": "centos-ytt57-3:3312",
               "mode": "R/O",
               "readReplicas": {},
               "role": "HA",
               "status": "ONLINE",
               "version": "8.0.17"
           }
       },
       "topologyMode": "Single-Primary"
   },
   "groupInformationSourceMember": "centos-ytt57-2:3311"
}

 

MySQL集群搭建
weixin_42700772的博客
12-30 1715
MySQL集群搭建准备CentOS环境更新系统（非必要）配置静态IP配置机器名称配置HOSTS文件安装MySQL下载安装文件Adding the MySQL Yum RepositoryInstalling MySQLStarting the MySQL Server查看MySQL安装后的临时root密码登录MySQL修改root的密码更改root可以从外网访问配置防火墙部署MySQL集群安装My...
MySQL-MGR报错MY-011526
sinat_36757755的博客
08-14 2346
单主MGR集群，主节点在服务器意外重启之后，无法重新加入现有MGR集群，报错误[MY-011526][Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions:
【数据库】MySQL InnoDB Cluster 高可用架构部署（MGR 3节点）
FixPng的博客
10-14 1005
InnoDB Cluster是MySQL官方的高可用方案，基于Group Replication实现，包含三个核心组件：MySQL Server节点（数据同步）、MySQL Shell（集群管理工具）和MySQL Router（请求路由）。本实践采用3节点架构（1主2从），支持自动故障转移与强一致性。所有节点运行MySQL 8.0.43，配置统一但需调整server-id和通信地址。关键配置包括GTID同步、组复制插件加载及单主模式设定，确保高可用性和透明扩展能力。架构通过PAXOS协议保障数据一致
[图文]搭建InnoDB Cluster详细流程
2201_75287294的博客
11-19 1285
搭建InnoDB Cluster详细流程
Mysql Group Replication
junqing124的专栏
08-20 963
主要安装方法在https://dev.mysql.com/doc/refman/5.7/en/group-replication-getting-started.html 碰到的异常有:Plugin group_replication reported: 'This member has more executed transactions than those present in the
mongodb 报"when a host is not a member of a replica set' on server 127.0.0.1:27017" 解决方案
lanhaics的专栏
04-10 1803
在部署本地服务器运行的MONGODB数据库，可能会报上述错误； 从字面意思上看，与服务器备份有关； stackoverflow.com给出的解决方案如下； 个人配置如下: <mongo:mongo-client id="mongo" host="${mongo.host}" port="${mongo.port}" credentials="${mongo.credenti...
seaworld008_auto-install-mysql-innodb-cluster_21708_1764960554197.zip
最新发布
12-06
在本次提供的压缩包文件中，包含了名为“auto-install-mysql-innodb-cluster-main”的文件，这可能是一个自动化脚本或者是一套完整的安装工具集。该工具集可能覆盖了从环境检查到集群节点部署的所有必要步骤，以达到...
mysql-cluster-8.0.20-winx64.zip
05-17
6. **安装与配置**：解压“mysql-cluster-8.0.20-winx64.zip”后，用户需要按照官方文档的步骤进行安装，包括设置环境变量、配置管理节点、数据节点和SQL节点，并进行集群初始化。 7. **管理工具**：MySQL Cluster...
MySQL-InnodbCluster安装部署
sinat_36757755的博客
05-24 1742
前言: 本文章为InnodbCluster集群安装文档，采用MySQL官方的InnodbCluster架构(MySQLRouter+MySQLShell+MGR),并通过KeepAlived实现MySQLRouter的高可用。 集群架构: 环境信息: 属性 值 MySQL版本 8.0.22 MySQLRouter版本 8.0.22 MySQLShell版本 8.0.22 MGR模式 单主模式(一主两从) MGR节点 30.1.15...
Percona-XtraDB-Cluster-57-5.7.28-31.41.rar
01-29
Percona XtraDB Cluster是Percona公司开发的一款高可用、高性能的MySQL集群解决方案，它基于XtraDB存储引擎和Galera同步技术。Percona XtraDB Cluster 5.7.28-31.41是该软件的一个特定版本，为用户提供了强大的...
Introducing InnoDB Cluster--2018
09-09
Set up and use InnoDB Cluster as an HA solution Migrate your existing servers to MySQL 8 Employ best practices for using InnoDB Cluster Configure servers for optimal automatic failover to ensure ...
InnoDB Cluster搭建
超频化石鱼的博客
08-15 5637
简介 InnoDB Cluster与MySQL Cluster是完全不同的：InnoDB Cluster用于InnoDB，而MySQL Cluster用于NDB。 InnoDB Cluster并没有单独的应用，它是由3个应用共同协作构成的： MySQL服务：提供数据库功能及组复制同步功能。 MySQL Shell：提供控制台功能，自带一些扩展的API。 MySQL Router：将用户应用透明连接到MySQL实例，以及故障转移功能。 准备工作 进入官方下载页面： https://dev.mysql.c
MySQL MGR搭建过程中常遇见的问题及解决办法
FFziyuan的博客
03-27 2282
这篇文章主要介绍了MySQL MGR搭建过程中常遇见的问题及解决办法，帮助大家更好的理解和学习使用MySQL，感兴趣的朋友可以了解下 MGR搭建过程中遇到的一些故障。更多资源请访问www.ffu9.com 实际中我一共部署了三套MGR环境，分别是单机多实例的MGR环境，多机同网段的MGR环境，多机不同网段的MGR环境，部署的过程大同小异，但是还是有一些有出入的地方，这里把部署过程遇到的故障列举出来，供大家参考，如果能有幸解决您在部署时候的问题，那是极好的。 01 常见故障1 1 ...
mysql 5.7 group replication 之七 [ERROR] [MY-011735] [Repl] Plugin group_replication reported
一名数据库爱好者的专栏
07-03 5242
os: centos 7.4.1708 db: mysql 8.0.20 标题写的是 mysql 5.7，实际使用的是 mysql 8 ip规划入下 192.168.56.61 node1 192.168.56.62 node2 192.168.56.63 node3 版本 # cat /etc/centos-release CentOS Linux release 7.4.1708 (Core) # # # yum list installed |grep -i mysql80 mysql
Mysql Group Replication搭建
Du.的博客
03-29 1203
1. MGR介绍 Group Replication is a built-in MySQL plugin provided with MySQL Server 5.7.17 and later. MGR利用的是mysql（社区版或商用版）5.7.17后内置的group_replication的插件实现的，有单主多从模式和多主模式，oracle官方推荐单主多从模式，实现读写分离 由若干个节点（最多9个）共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（N / 2 + 1）决议并通过，才...
MySQL 5.7 Group Replication错误总结(r11笔记第84天）
congbao6525的博客
02-22 1565
今天来总结下MySQL 5.7中的一些问题处理，相对来说常规一些。搭建的过程我就不用多说了，昨天的文章里面可以看到一个基本的方式，在测试环境很容易模拟，如果在多台物理机环境中搭建是不是也一样呢，答案是肯定的，我自己都...
mysql error 1126_install plugin group_replication ERROR 1126 (HY000)
weixin_42498163的博客
01-27 3113
在给MySQL安装插件的时候，你可能会遇到如题所示的报错。更详细的错误输出如下：mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';ERROR1126(HY000):mysql>mysql>造成该问题的原因是，MySQL的配置文件指定的plugin_dir中，找不到你要安装的【*.so】文件。...
MySQL InnoDB Cluster部署方案与实践
m0_38004228的博客
11-17 3669
MySQL InnoDB Cluster实验
技术分享 | MySQL Group Replication集群对IP地址的限制导致的一些问题与解决办法
GreatSQL2021的博客
01-13 1545
GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。 1. 遇到问题 测试人员小玲准备在docker环境中部署MGR集群进行一些测试，她有三个容器，容器IP分别是： 172.33.0.2 172.33.0.3 172.33.0.4每个容器中分别安装一个MySQL实例，每个实例的group_replication_local_address和group_replication_group_seeds两个配置项分别是： group_replication_local_address=.
+-------------------------------------+----------------+ | Variable_name | Value | +-------------------------------------+----------------+ | innodb_buffer_pool_chunk_size | 83886080 | | innodb_buffer_pool_dump_at_shutdown | ON | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 25 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_in_core_file | ON | | innodb_buffer_pool_instances | 64 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | ON | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 5368709120 | +-------------------------------------+----------------+
04-20
<think>好的，用户现在想在线修改MySQL的innodb_buffer_pool_size参数，并且已经提供了当前的配置详情。首先，我需要确认用户使用的MySQL版本，因为动态调整这个参数需要5.7及以上版本。用户给出的配置显示innodb_buffer_pool_size是1073741824字节，也就是1GB，这可能偏小，尤其是在有更多可用内存的情况下。 接下来，用户提供的引用里提到了几个关键点：动态调整的条件、缓冲池命中率的计算、实例数的设置以及如何判断是否需要增加缓冲池大小。结合这些信息，我需要指导用户如何安全地在线调整参数，同时避免常见错误。 用户可能需要知道具体的调整步骤，比如使用SET GLOBAL命令，但需要注意权限问题。同时，调整后的验证步骤也很重要，比如检查缓冲池命中率是否提升，或者通过SHOW STATUS命令查看使用情况。另外，用户提供的引用4提到实例数的设置，可能需要建议将实例数设置为8-16，尤其是在较大的缓冲池情况下。 另外，用户之前的问题中提到插入性能中等偏低，所以调整缓冲池的同时可能还需要其他优化措施，比如调整事务提交频率或日志设置。需要提醒用户调整后的风险，比如内存不足导致的服务不稳定，以及分阶段调整的建议。 最后，用户可能需要了解如何持久化配置到my.cnf，避免重启后失效，这也是需要注意的地方。总结来说，回答需要涵盖调整步骤、验证方法、相关参数优化（如实例数）、风险提示以及后续优化建议，确保用户能够全面理解并安全操作。</think>### 分步解决方案：在线调整`innodb_buffer_pool_size`参数 --- #### **第一步：验证动态调整可行性** ```sql -- 确认MySQL版本是否≥5.7（支持在线调整） SELECT VERSION(); -- 查看当前缓冲池配置 SHOW VARIABLES LIKE 'innodb_buffer_pool%'; ``` 根据你的配置信息[^1]： - `innodb_buffer_pool_size = 1GB`（当前值） - `innodb_buffer_pool_instances = 8`（已满足多实例要求[^4]） - `innodb_buffer_pool_chunk_size = 128MB`（需确保新尺寸是128MB的整数倍） --- #### **第二步：动态调整缓冲池大小** ```sql -- 在线调整（示例：从1GB调整到4GB） SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4GB（单位：字节） ``` **关键约束**： 1. 调整耗时约$8-20$秒（每GB需要$2-5$秒[^2]） 2. 新值必须是`innodb_buffer_pool_chunk_size * instances`的整数倍 本例中：$128MB \times 8 = 1GB$，因此可调整为$4GB = 4 \times 1GB$ --- #### **第三步：监控调整过程** ```sql -- 查看调整进度（观察状态是否为"Completed"） SHOW STATUS WHERE Variable_name LIKE 'Innodb_buffer_pool_resize_status'; ``` 典型输出： ``` +----------------------------------+--------------------------+ | Variable_name | Value | +----------------------------------+--------------------------+ | Innodb_buffer_pool_resize_status | Resizing buffer pool... | +----------------------------------+--------------------------+ ``` --- #### **第四步：验证调整结果** ```sql -- 确认新参数已生效 SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; -- 计算缓冲池利用率[^3] SELECT ROUND((SELECT Variable_value FROM information_schema.global_status WHERE Variable_name = 'Innodb_buffer_pool_pages_data') / (SELECT Variable_value FROM information_schema.global_status WHERE Variable_name = 'Innodb_buffer_pool_pages_total') * 100, 2) AS buffer_pool_usage; ``` **优化目标**： - 使用率≥95%：当前配置合理[^3] - 使用率<90%：可继续增加缓冲池（但不超过物理内存的80%[^4]） --- #### **第五步：持久化配置** ```ini # 修改my.cnf配置文件（防止重启失效） [mysqld] innodb_buffer_pool_size = 4G innodb_buffer_pool_instances = 8 innodb_buffer_pool_chunk_size = 128M ``` **重启生效**： ```bash sudo systemctl restart mysql # Linux系统示例 ``` --- ### **优化建议与风险控制** 1. **分阶段调整** 当内存调整超过$4GB$时，建议按$2GB$增量逐步调整，避免内存碎片 ```sql SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB -- 观察10分钟后继续调整 SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4GB ``` 2. **关联参数优化** ```ini innodb_io_capacity = 2000 # SSD建议值 innodb_flush_method = O_DIRECT # 避免双缓存 ``` 3. **内存溢出防护** 总内存占用应满足： $$ \text{innodb\_buffer\_pool\_size} + \text{其他内存消耗} ≤ 80\% \times \text{物理内存} $$ 可使用下方公式验证： ```sql SELECT (@@global.innodb_buffer_pool_size / 1024 / 1024) AS buffer_pool_mb, (@@global.key_buffer_size / 1024 / 1024) AS key_buffer_mb, (@@global.tmp_table_size / 1024 / 1024) AS tmp_table_mb; ``` --- ### **常见问题排查** **问题现象**：调整后出现`ERROR 1238 (HY000): Variable 'innodb_buffer_pool_size' is a read only variable` **解决方案**： 1. 确认MySQL版本≥5.7 2. 检查用户权限（需SUPER权限） 3. 验证`innodb_buffer_pool_chunk_size`是否为总大小的公约数[^1] --- ### **性能验证案例** **调整前**（1GB缓冲池）： ```sql SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'; +----------------------------------+---------+ | Variable_name | Value | +----------------------------------+---------+ | Innodb_buffer_pool_read_requests | 2839456 | -- 总读取请求 | Innodb_buffer_pool_reads | 12784 | -- 物理磁盘读取 +----------------------------------+---------+ ``` 命中率 = $(1 - 12784/2839456) \times 100 ≈ 99.55\%$ **调整后**（4GB缓冲池）： ```sql +----------------------------------+---------+ | Variable_name | Value | +----------------------------------+---------+ | Innodb_buffer_pool_read_requests | 3847563 | | Innodb_buffer_pool_reads | 235 | +----------------------------------+---------+ ``` 命中率提升至$99.99\%$，物理读取减少$94\%$[^3] --- ### 相关问题 1. 如何通过`SHOW ENGINE INNODB STATUS`分析缓冲池碎片率？ 2. 多实例缓冲池配置下，如何监控各实例的命中率差异？ 3. `innodb_buffer_pool_dump_at_shutdown`参数对性能恢复有何影响？
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值