Docker容器化高可用架构部署方案（十二）

11-MySQL-MGR初始化本文档详细介绍MySQL MGRGroup Replication集群的初始化步骤。初始化前提三个MySQL容器已正常运行MySQL容器healthcheck通过网络连通性正常初始化步骤步骤1等待MySQL容器就绪# 查看MySQL容器状态 docker ps | grep mysql ​ # 等待healthcheck通过 until docker exec mysql-01 mysqladmin -uroot -pYourStr0ng!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 -pYourStr0ng!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 -pYourStr0ng!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 -pYourStr0ng!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启动MGRdocker exec -it mysql-01 mysql -uroot -pYourStr0ng!Pass -h127.0.0.1在MySQL命令行中执行-- 设置恢复渠道凭据 CHANGE MASTER TO MASTER_USERrepl_user, MASTER_PASSWORDYourStr0ng!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加入MGRdocker exec -it mysql-02 mysql -uroot -pYourStr0ng!Pass -h127.0.0.1在MySQL命令行中执行-- 设置恢复渠道凭据 CHANGE MASTER TO MASTER_USERrepl_user, MASTER_PASSWORDYourStr0ng!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加入MGRdocker exec -it mysql-03 mysql -uroot -pYourStr0ng!Pass -h127.0.0.1在MySQL命令行中执行-- 设置恢复渠道凭据 CHANGE MASTER TO MASTER_USERrepl_user, MASTER_PASSWORDYourStr0ng!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 -pYourStr0ng!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为PRIMARYNode2和Node3为SECONDARY。数据同步测试在Primary写入数据docker exec -it mysql-01 mysql -uroot -pYourStr0ng!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 -pYourStr0ng!Pass -h127.0.0.1 -e USE app_db; SELECT * FROM test_mgr; docker exec -it mysql-03 mysql -uroot -pYourStr0ng!Pass -h127.0.0.1 -e USE app_db; SELECT * FROM test_mgr; 预期所有节点都能看到相同的数据。完整一键初始化脚本#!/bin/bash # MySQL MGR初始化脚本 set -e MYSQL_ROOTroot MYSQL_PASSYourStr0ng!Pass REPL_USERrepl_user REPL_PASSYourStr0ng!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 -pYourStr0ng!Pass -h127.0.0.1 -e SELECT * FROM performance_schema.replication_group_members; # 查看MGR统计信息 docker exec mysql-01 mysql -uroot -pYourStr0ng!Pass -h127.0.0.1 -e SELECT * FROM performance_schema.replication_group_statistics; # 查看一致性统计 docker exec mysql-01 mysql -uroot -pYourStr0ng!Pass -h127.0.0.1 -e SELECT * FROM performance_schema.replication_connection_stats; # 查看当前主节点 docker exec mysql-01 mysql -uroot -pYourStr0ng!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 -pYourStr0ng!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_statsQ3: 数据复制延迟检查网络带宽查看慢查询调整group_replication_member_weight下一步12-验证测试.md - 完整验证测试13-快速部署.md - 一键部署脚本