mysql安装-MySQL MGR（Group Replication）+ ProxySQL 架构

发布于：2025-04-14 ⋅ 阅读:(26) ⋅ 点赞:(0)

文章目录

前言

我要安装mysql 8.0.36。 一台（主）在centos9服务器中，两台（从）在别的机器的docker中。

MySQL MGR（Group Replication）+ ProxySQL 架构
优势：
• 自动高可用
• 自动主从切换
• 写少读多的业务特别适配
• 架构清晰、官方支持、稳定成熟

一、环境规划

在这里插入图片描述
建议还是用3台物理机安装mysql集群，或在一台机器的docker中安装3个mysql容器组成集群。我在尝试1台物理机+2个docker容器时，遇到了各种网络问题。

二、安装 MySQL 8.0.36（主库，CentOS 9）

2.1 添加 Yum 源

sudo dnf install -y https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm
sudo dnf install https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm
sudo dnf config-manager --enable mysql80-community

2.2 安装 MySQL 8.0.36

dnf install -y mysql-community-server-8.0.36
# 如果安装不了，则执行：dnf install -y mysql-community-server-8.0.36 --nogpgcheck
systemctl enable mysqld
systemctl start mysqld

2.3 初始化

grep 'temporary password' /var/log/mysqld.log
#2025-04-12T08:28:59.067415Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: zMGCqs;WK6ee
mysql -uroot -p        # 使用初始密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPassw0rd!';

三、配置主库 my.cnf（192.168.1.101）

[mysqld]
server_id=1
log_bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
plugin_load_add='group_replication.so'
bind-address = 0.0.0.0
port = 3306
report_host = 192.168.0.101
group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot=OFF
group_replication_local_address="192.168.1.101:33061"
group_replication_group_seeds="192.168.1.101:33061,192.168.1.102:33061,192.168.1.103:33061"
group_replication_bootstrap_group=OFF
group_replication_single_primary_mode=ON
group_replication_enforce_update_everywhere_checks=OFF

auto_increment_increment=3
auto_increment_offset=1

保存后重启：systemctl restart mysqld

四、（可选）创建远程可访问的用户（在主库里执行）

SET SQL_LOG_BIN=0;
CREATE USER 'root'@'%' IDENTIFIED BY '你的密码';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

有防火墙的需关闭防火墙，或者确保防火墙放行 3306

五、 从库在 Docker 中安装 MySQL 8.0.36

5.1 拉取镜像

docker pull mysql:8.0.36

5.2 启动容器

docker run -d --name mysql2 \
  -e MYSQL_ROOT_PASSWORD=MyNewPassw0rd! \
  --network host \
  -v /root/mysql2/conf:/etc/mysql/conf.d \
  mysql:8.0.36

5.3 修改my.cnf

5.2步骤中先能启动成功，再修改my.cnf
在 /root/mysql2/conf/my.cnf 中配置：

[mysqld]
server_id=2
gtid_mode=ON
enforce-gtid-consistency=ON
log_bin=mysql-bin
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
plugin_load_add='group_replication.so'
bind-address = 0.0.0.0
port = 3306
report_host = 192.168.0.102
group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot=OFF
group_replication_local_address="192.168.1.102:33061"
group_replication_group_seeds="192.168.1.101:33061,192.168.1.102:33061,192.168.1.103:33061"
group_replication_bootstrap_group=OFF
group_replication_single_primary_mode=ON
group_replication_enforce_update_everywhere_checks=OFF

auto_increment_increment=3
auto_increment_offset=2

server3 的 Docker 配置类似，server_id改为3，IP设为 192.168.1.103，offset 改为 3
docker里启动数据库，有时特别慢，注意等待。

六、启动 MGR 集群

6.1 在主库执行（bootstrap）

SET SQL_LOG_BIN=0;
#CREATE USER bztc@'%' IDENTIFIED BY 'bztc的密码'; <----会出问题
CREATE USER 'bztc'@'%' IDENTIFIED WITH mysql_native_password BY 'bztc的密码';

GRANT REPLICATION SLAVE ON *.* TO bztc@'%';

SET SQL_LOG_BIN=1;

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

6.2 在从库执行（进入容器）

进入容器：

docker exec -it mysql2 bash
mysql -uroot -pMyNewPassw0rd!

进入sql命令模式后执行：

-- 1. 先停止 Group Replication
STOP GROUP_REPLICATION;

-- 2. 关闭 binlog 临时配置复制账号
SET SQL_LOG_BIN = 0;

-- 3. 设置复制账号信息（替换你的用户名密码）
CHANGE MASTER TO MASTER_USER = 'bztc', MASTER_PASSWORD = 'bztc的密码' FOR CHANNEL 'group_replication_recovery';

-- 4. 恢复 binlog 设置
SET SQL_LOG_BIN = 1;

-- 5. 启动 Group Replication
START GROUP_REPLICATION;

6.3 验证

SELECT * FROM performance_schema.replication_group_members;

如果出现以下图中的数据，即从节点的MEMBER_STATE一直是RECOVERING，那基本上是出问题了：
在这里插入图片描述
执行以下命令查看：

SHOW SLAVE STATUS FOR CHANNEL 'group_replication_recovery'\G

在这里插入图片描述
可根据其中问题询问大模型或查询相关资料解决。

最终成功截图：
在这里插入图片描述

此时在主库创建数据库，创建表会同步到从库。
注意：在主库创建用户，赋权一定要先关闭binlog（即：SET SQL_LOG_BIN = 0;）

6.4 问题

6.4.1 不能在主库执行 CREATE USER、GRANT、REVOKE、DROP USER。

如果执行了，那从库必挂。
解决办法：我试了很多方法，都没用。只能重新安装主从库。
彻底清空数据库：

#1.停掉 MySQL 服务：
sudo systemctl stop mysqld
#2.清空数据目录（确保路径对！ 通常是 /var/lib/mysql）：
mv /etc/my.cnf /etc/my.cnf.1
sudo rm -rf /var/lib/mysql/*
#3.重新初始化 MySQL：
sudo mysqld --initialize --user=mysql
#4.启动 MySQL：
sudo systemctl start mysqld
#5.查看初始 root 密码（从 error log 中找）
sudo grep 'temporary password' /var/log/mysqld.log
#6.从上面2.3步骤重新开始

七 安装 配置ProxySQL

7.1 安装 ProxySQL（推荐使用 Linux 主机部署）

7.1.1 如果你用的是 Debian / Ubuntu 系列：

wget https://github.com/sysown/proxysql/releases/download/v2.5.5/proxysql_2.5.5-debian11_amd64.deb
sudo dpkg -i proxysql_2.5.5-debian11_amd64.deb

7.1.2 如果你是 CentOS / RHEL 系列：

wget https://github.com/sysown/proxysql/releases/download/v2.5.5/proxysql-2.5.5-1-centos7.x86_64.rpm
sudo rpm -ivh proxysql-2.5.5-1-centos7.x86_64.rpm

7.1.3 也可以用 Docker：

docker run -d --name proxysql -p 6032:6032 -p 6033:6033 proxysql/proxysql:2.5.5

我选择的是docker安装ProxySQL

7.2 配置 ProxySQL（主流程）

你已经有 3 个节点：
在这里插入图片描述

7.2.1 登录到 ProxySQL 管理接口（默认管理端口 6032）

mysql -u admin -padmin -h 127.0.0.1 -P6032

7.2.2 添加后端 MySQL 节点

-- 添加主节点
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections, comment)
VALUES (10, 'IP_A', 3306, 100, 200, 'PRIMARY');

-- 添加从节点
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections, comment)
VALUES (20, 'IP_B', 3306, 100, 200, 'SECONDARY'),
       (20, 'IP_C', 3306, 100, 200, 'SECONDARY');

7.2.3 配置读写分离规则

-- 所有写操作走主库
-- 1. 精准 FOR UPDATE 语句，强制走主库
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, apply)
VALUES (1, '^SELECT.*FOR UPDATE', 10, 1);

-- 2. 所有 SELECT 都走从库（包括带空格的）
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, apply)
VALUES (2, '^SELECT .*', 20, 1);

-- 3. 所有 SHOW/SET/USE/EXPLAIN 等管理语句走主库（因为涉及状态或变量）
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, apply)
VALUES (3, '^(SHOW|SET|USE|EXPLAIN)', 10, 1);

-- 4. 默认兜底规则：其它全走主库
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, apply)
VALUES (100, '.*', 10, 1);
-- 5. 这一句很重要，active不等于1的不会加载到runtime_mysql_query_rules中。会导致读写分离规则实效。
UPDATE mysql_query_rules SET active=1;

7.2.4 添加用户认证信息（必须是 MySQL 中已存在的用户）

INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('bztc', 'bztc的密码', 10);

7.2.5 保存并加载配置到运行内存和磁盘

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
-- 一定要能查到你的规则
SELECT * FROM runtime_mysql_query_rules ORDER BY rule_id;

7.3 应用层连接 ProxySQL

应用层连接 ProxySQL 的 6033 端口：

mysql -u app_user -papp_password -h <ProxySQL_IP> -P6033

我的本地电脑使用的是sqlyog连接，如果出现错误：

Host '192.168.0.107' is not allowed to connect to this MySQL server

见下面问题7.4.1

全都配置完成后，就可以在客户端（如sqlyog）中连接ProxySQL，进行数据库操作了。

7.4 创建并配置一个 监控用账号（monitor user）

如果不创建监控用账号，那么ProxySQL日志会一直打印：

2025-04-13 20:35:25 2025-04-13 12:35:25 MySQL_Monitor.cpp:1347:monitor_connect_thread(): [ERROR] Server 192.168.0.104:3306 is returning "Access denied" for monitoring user
2025-04-13 20:35:26 2025-04-13 12:35:26 MySQL_Monitor.cpp:1347:monitor_connect_thread(): [ERROR] Server 192.168.0.118:3306 is returning "Access denied" for monitoring user
2025-04-13 20:35:27 2025-04-13 12:35:27 MySQL_Monitor.cpp:1347:monitor_connect_thread(): [ERROR] Server 192.168.0.102:3306 is returning "Access denied" for monitoring user

7.4.1 在所有 MySQL 节点创建一个监控账号（用户名通常为 monitor）

你可以使用如下语句在每个 MySQL 节点上执行（主库和两个从库都要）：

SET sql_log_bin = 0;
-- 首先检查当前只读状态，主库为OFF，从库为ON
SHOW VARIABLES LIKE 'read_only';
SHOW VARIABLES LIKE 'super_read_only';
-- 如果是普通从库，可以尝试关闭
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;
-- 然后执行您的语句
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT USAGE ON *.* TO 'monitor'@'%';
-- 操作完成后恢复只读状态（如果是从库）
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

SET SQL_LOG_BIN=1;
FLUSH PRIVILEGES;

USAGE 权限即可，不需要读写权限。你可以改成更强的密码，比如 ProxyMon@123!。

7.4.2 在 ProxySQL 配置监控账号

登录 ProxySQL 管理接口（6032 端口）：

mysql -u admin -padmin -h 127.0.0.1 -P6032

设置监控用户名和密码：

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor_pass' WHERE variable_name='mysql-monitor_password';

-- 应用配置
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

7.4.3 验证是否恢复正常

查看日志是否还报错。

7.5 问题

7.5.1 客户端连不上ProxySQL

每个mysql节点都要创建同一个用户、密码、赋权

SET sql_log_bin = 0;
-- 首先检查当前只读状态，主库为OFF，从库为ON
SHOW VARIABLES LIKE 'read_only';
SHOW VARIABLES LIKE 'super_read_only';
-- 如果是普通从库，可以尝试关闭
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;
-- 然后执行您的用户创建语句
CREATE USER 'bztc'@'%' IDENTIFIED BY '密码';
GRANT ALL PRIVILEGES ON *.* TO 'bztc'@'%';
-- 操作完成后恢复只读状态（如果是从库）
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

SET SQL_LOG_BIN=1;
FLUSH PRIVILEGES;

7.5.2 建好了表却无法insert

报错：

The table does not comply with the requirements by an external plugin

这个错误通常出现在 MySQL Group Replication (MGR) 或使用其他插件（如审计插件、验证插件等）的环境中，表示表结构不符合插件的要求。

常见原因

MGR 表要求不满足：表缺少主键或合适的主键
审计插件限制：表结构不符合审计要求
验证插件限制：表结构或内容不符合数据验证规则
其他插件限制：特定插件对表有特殊要求

八、验证ProxySQL读写分离已生效

8.1 配置前提检查

确认你已经正确配置：
• mysql_query_rules 中配置了根据 SQL 类型分流（SELECT 去读库，INSERT/UPDATE/DELETE 去写库）。
• mysql_servers 中设置了：
• 主库 → hostgroup_id = 写库组（如 10）
• 从库 → hostgroup_id = 读库组（如 20）
• mysql_users 设置了默认 hostgroup（一般设置为写库组）

8.2 开启 ProxySQL 日志记录查询（推荐）

-- 开启查询日志记录
UPDATE global_variables SET variable_value='ON' WHERE variable_name='mysql-query_digests';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- 查看最近的查询都发往了哪个 hostgroup
SELECT hostgroup, username, digest_text, count_star FROM stats_mysql_query_digest ORDER BY count_star DESC;

你应该能看到：
• 类似 SELECT * FROM table_name 的语句走的是 hostgroup_id = 20
• 类似 INSERT / UPDATE / DELETE 走的是 hostgroup_id = 10

8.3 监控 stats_mysql_connection_pool 表

这个表会告诉你各 hostgroup 实际连接的使用情况：

SELECT hostgroup, srv_host, srv_port, status, ConnUsed, Queries FROM stats_mysql_connection_pool;

你可以：
• 连上 ProxySQL 后执行多个 SELECT，观察 hostgroup_id=20 的连接 Queries 数是否增长。
• 执行 INSERT、UPDATE，观察 hostgroup_id=10 的主库 Queries 是否增长。

建议在服务器上进到mysql客户端中执行增删改查测试。
如果用 SQLyog、Navicat 等工具，在执行 SELECT * FROM daism_test WHERE id = 1; 前后，自动会发送如下语句：
• SELECT DATABASE();
• SHOW VARIABLES LIKE ‘…’
• SELECT @@version_comment LIMIT 1;

这些不一定走 ^SELECT 规则（匹配位置有限制），可能被规则 3（兜底规则）匹配到了主库！

8.4 常见误区排查

•	没有执行 LOAD MYSQL QUERY RULES TO RUNTIME → 改了规则没生效。
•	默认 hostgroup 设置不对。
•	所有 hostgroup 配成同一个，导致都走主库。
•	query rule 书写错误，没能正确匹配 SELECT 等语句。

总结

如果是第一次安装MySQL MGR（Group Replication）+ ProxySQL 架构，99%会出现各种问题，可以问问大模型能不能解决。

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布