11-MySQL-MGR初始化
本文档详细介绍MySQL MGR（Group Replication）集群的初始化步骤。
初始化前提
三个MySQL容器已正常运行
MySQL容器healthcheck通过
网络连通性正常
初始化步骤
步骤1：等待MySQL容器就绪
# 查看MySQL容器状态 docker ps | grep mysql # 等待healthcheck通过 until docker exec mysql-01 mysqladmin -uroot -p'YourStr0ng!Pass' ping -h127.0.0.1 &>/dev/null; do echo "Waiting for MySQL-01 to be ready..." sleep 5 done echo "MySQL-01 is ready!" until docker exec mysql-02 mysqladmin -uroot -p'YourStr0ng!Pass' ping -h127.0.0.1 &>/dev/null; do echo "Waiting for MySQL-02 to be ready..." sleep 5 done echo "MySQL-02 is ready!" until docker exec mysql-03 mysqladmin -uroot -p'YourStr0ng!Pass' ping -h127.0.0.1 &>/dev/null; do echo "Waiting for MySQL-03 to be ready..." sleep 5 done echo "MySQL-03 is ready!"
步骤2：在Node1创建复制用户
重要：必须使用mysql_native_password认证插件。
# 登录MySQL-01 docker exec -it mysql-01 mysql -uroot -p'YourStr0ng!Pass' -h127.0.0.1
在MySQL命令行中执行：
-- 创建复制用户（使用mysql_native_password） CREATE USER 'repl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'YourStr0ng!Pass'; -- 授予复制权限 GRANT BACKUP_ADMIN ON *.* TO 'repl_user'@'%'; GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%'; GRANT GROUP_REPLICATION_STREAM ON *.* TO 'repl_user'@'%'; -- 刷新权限 FLUSH PRIVILEGES; -- 退出 EXIT;
步骤3：在Node1启动MGR
docker exec -it mysql-01 mysql -uroot -p'YourStr0ng!Pass' -h127.0.0.1
在MySQL命令行中执行：
-- 设置恢复渠道凭据 CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='YourStr0ng!Pass' FOR CHANNEL 'group_replication_recovery'; -- 启动组复制（作为引导节点） START GROUP_REPLICATION; -- 验证MGR状态 SELECT * FROM performance_schema.replication_group_members;
预期输出：
+---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | 172.20.4.11 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+
MEMBER_STATE为ONLINE表示Node1已成功加入MGR。
步骤4：在Node2加入MGR
docker exec -it mysql-02 mysql -uroot -p'YourStr0ng!Pass' -h127.0.0.1
在MySQL命令行中执行：
-- 设置恢复渠道凭据 CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='YourStr0ng!Pass' FOR CHANNEL 'group_replication_recovery'; -- 启动组复制 START GROUP_REPLICATION; -- 验证MGR状态 SELECT * FROM performance_schema.replication_group_members;
预期输出：
+---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | 172.20.4.11 | 3306 | ONLINE | | group_replication_applier | yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy | 172.20.4.12 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+
步骤5：在Node3加入MGR
docker exec -it mysql-03 mysql -uroot -p'YourStr0ng!Pass' -h127.0.0.1
在MySQL命令行中执行：
-- 设置恢复渠道凭据 CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='YourStr0ng!Pass' FOR CHANNEL 'group_replication_recovery'; -- 启动组复制 START GROUP_REPLICATION; -- 验证MGR状态 SELECT * FROM performance_schema.replication_group_members;
预期输出：
+---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | 172.20.4.11 | 3306 | ONLINE | | group_replication_applier | yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy | 172.20.4.12 | 3306 | ONLINE | | group_replication_applier | zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz | 172.20.4.13 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+
三个节点都是ONLINE表示MGR初始化成功！
步骤6：验证Primary节点
docker exec -it mysql-01 mysql -uroot -p'YourStr0ng!Pass' -h127.0.0.1 -e " SELECT member_id, member_host, member_port, member_role, member_state FROM performance_schema.replication_group_members WHERE channel_name = 'group_replication_applier'; "
预期输出：
+--------------------------------------+-------------+-------------+-------------+--------------+ | member_id | member_host | member_port | member_role | member_state | +--------------------------------------+-------------+-------------+-------------+--------------+ | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | 172.20.4.11 | 3306 | PRIMARY | ONLINE | | yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy | 172.20.4.12 | 3306 | SECONDARY | ONLINE | | zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz | 172.20.4.13 | 3306 | SECONDARY | ONLINE | +--------------------------------------+-------------+-------------+-------------+--------------+
Node1为PRIMARY，Node2和Node3为SECONDARY。
数据同步测试
在Primary写入数据
docker exec -it mysql-01 mysql -uroot -p'YourStr0ng!Pass' -h127.0.0.1
-- 切换到数据库 USE app_db; -- 创建测试表 CREATE TABLE IF NOT EXISTS test_mgr ( id INT AUTO_INCREMENT PRIMARY KEY, content VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 插入测试数据 INSERT INTO test_mgr (content) VALUES ('Test from Primary'); -- 验证插入 SELECT * FROM test_mgr;在Secondary验证数据
docker exec -it mysql-02 mysql -uroot -p'YourStr0ng!Pass' -h127.0.0.1 -e " USE app_db; SELECT * FROM test_mgr; "
docker exec -it mysql-03 mysql -uroot -p'YourStr0ng!Pass' -h127.0.0.1 -e " USE app_db; SELECT * FROM test_mgr; "
预期：所有节点都能看到相同的数据。
完整一键初始化脚本
#!/bin/bash # MySQL MGR初始化脚本 set -e MYSQL_ROOT="root" MYSQL_PASS="YourStr0ng!Pass" REPL_USER="repl_user" REPL_PASS="YourStr0ng!Pass" echo "=== Waiting for MySQL containers ===" # 等待所有MySQL容器就绪 for container in mysql-01 mysql-02 mysql-03; do echo "Waiting for $container..." until docker exec $container mysqladmin -u$MYSQL_ROOT -p$MYSQL_PASS ping -h127.0.0.1 &>/dev/null; do sleep 2 done echo "$container is ready" done echo "=== All MySQL containers are ready ===" # 在Node1创建复制用户 echo "=== Creating replication user on Node1 ===" docker exec mysql-01 mysql -u$MYSQL_ROOT -p$MYSQL_PASS -h127.0.0.1 << EOF CREATE USER IF NOT EXISTS '$REPL_USER'@'%' IDENTIFIED WITH mysql_native_password BY '$REPL_PASS'; GRANT BACKUP_ADMIN ON *.* TO '$REPL_USER'@'%'; GRANT REPLICATION SLAVE ON *.* TO '$REPL_USER'@'%'; GRANT GROUP_REPLICATION_STREAM ON *.* TO '$REPL_USER'@'%'; FLUSH PRIVILEGES; EOF echo "=== Starting Group Replication on Node1 ===" docker exec mysql-01 mysql -u$MYSQL_ROOT -p$MYSQL_PASS -h127.0.0.1 << EOF CHANGE MASTER TO MASTER_USER='$REPL_USER', MASTER_PASSWORD='$REPL_PASS' FOR CHANNEL 'group_replication_recovery'; START GROUP_REPLICATION; EOF sleep 5 echo "=== Adding Node2 to Group Replication ===" docker exec mysql-02 mysql -u$MYSQL_ROOT -p$MYSQL_PASS -h127.0.0.1 << EOF CHANGE MASTER TO MASTER_USER='$REPL_USER', MASTER_PASSWORD='$REPL_PASS' FOR CHANNEL 'group_replication_recovery'; START GROUP_REPLICATION; EOF sleep 5 echo "=== Adding Node3 to Group Replication ===" docker exec mysql-03 mysql -u$MYSQL_ROOT -p$MYSQL_PASS -h127.0.0.1 << EOF CHANGE MASTER TO MASTER_USER='$REPL_USER', MASTER_PASSWORD='$REPL_PASS' FOR CHANNEL 'group_replication_recovery'; START GROUP_REPLICATION; EOF sleep 5 echo "=== Verifying Group Replication Status ===" docker exec mysql-01 mysql -u$MYSQL_ROOT -p$MYSQL_PASS -h127.0.0.1 -e " SELECT * FROM performance_schema.replication_group_members; " echo "=== MySQL MGR initialization completed ==="
验证命令汇总
# 查看MGR成员状态 docker exec mysql-01 mysql -uroot -p'YourStr0ng!Pass' -h127.0.0.1 -e " SELECT * FROM performance_schema.replication_group_members; " # 查看MGR统计信息 docker exec mysql-01 mysql -uroot -p'YourStr0ng!Pass' -h127.0.0.1 -e " SELECT * FROM performance_schema.replication_group_statistics; " # 查看一致性统计 docker exec mysql-01 mysql -uroot -p'YourStr0ng!Pass' -h127.0.0.1 -e " SELECT * FROM performance_schema.replication_connection_stats; " # 查看当前主节点 docker exec mysql-01 mysql -uroot -p'YourStr0ng!Pass' -h127.0.0.1 -e " SELECT member_id, member_host, member_role FROM performance_schema.replication_group_members WHERE member_role = 'PRIMARY'; " # 查看复制延迟 docker exec mysql-01 mysql -uroot -p'YourStr0ng!Pass' -h127.0.0.1 -e " SHOW SLAVE STATUS\G "
常见问题
Q1: START GROUP_REPLICATION失败
检查错误日志：
docker logs mysql-01确认复制用户认证插件正确
验证网络连通性
Q2: 节点一直处于RECOVERING状态
检查网络延迟
确认源数据同步完成
查看
performance_schema.replication_group_member_stats
Q3: 数据复制延迟
检查网络带宽
查看慢查询
调整
group_replication_member_weight
下一步
12-验证测试.md - 完整验证测试
13-快速部署.md - 一键部署脚本
