10-MySQL配置详解本文档详细介绍MySQL MGRGroup Replication的配置包括三个节点的配置差异。MySQL MGR架构┌─────────────────────────────────────────────────────────────┐ │ MySQL MGR (单主模式) │ ├─────────────────────────────────────────────────────────────┤ │ │ │ 33061端口 (Group Replication通信) │ │ │ │ │ │ │ ┌─────────┴────┐ ┌───────┴─────┐ ┌───────┴──────┐ │ │ │ MySQL-01 │ │ MySQL-02 │ │ MySQL-03 │ │ │ │ 172.20.4.11 │ │ 172.20.4.12 │ │ 172.20.4.13 │ │ │ │ (PRIMARY) │ │ (SECONDARY)│ │ (SECONDARY) │ │ │ │ member_ │ │ member_ │ │ member_ │ │ │ │ weight70 │ │ weight60 │ │ weight50 │ │ │ └──────────────┘ └──────────────┘ └──────────────┘ │ │ │ │ │ │ │ └──────────────┼──────────────┘ │ │ │ │ │ 3306端口 (客户端连接) │ │ │ │ │ ▼ │ │ PHP服务 │ │ (172.20.2.11/12/13) │ │ │ └─────────────────────────────────────────────────────────────┘MGR特性说明单主模式只有一个节点可写Primary其他节点自动变为只读Secondary故障转移时自动切换GTID复制使用全局事务ID追踪复制状态支持自动跳过错误事务更可靠的故障恢复Group Replication基于Paxos协议的一致性保证多节点同时写入冲突检测自动故障转移Node1配置文件 (my-node1.cnf)cat /opt/cluster-deploy/config/mysql/my-node1.cnf EOF [mysqld] server-id1 bind-address0.0.0.0 port3306 basedir/usr datadir/var/lib/mysql socket/var/run/mysqld/mysqld.sock pid-file/var/run/mysqld/mysqld.pid log-error/var/log/mysql/error.log ​ report_host172.20.4.11 ​ gtid_modeON enforce_gtid_consistencyON log_slave_updatesON binlog_checksumNONE skip-name-resolve ​ loose-group_replication_group_nameaaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee loose-group_replication_start_on_bootOFF loose-group_replication_local_address172.20.4.11:33061 loose-group_replication_group_seeds172.20.4.11:33061,172.20.4.12:33061,172.20.4.13:33061 loose-group_replication_ip_allowlist172.20.4.0/24 loose-group_replication_single_primary_modeON loose-group_replication_enforce_update_everywhere_checksOFF loose-group_replication_poll_spin_loops100 loose-group_replication_recovery_reconnect_interval10 loose-group_replication_member_weight70 ​ master_info_repositoryTABLE relay_log_info_repositoryTABLE transaction_write_set_extractionXXHASH64 binlog_formatROW ​ [client] socket/var/run/mysqld/mysqld.sock ​ [mysql] socket/var/run/mysqld/mysqld.sock EOFNode2配置文件 (my-node2.cnf)cat /opt/cluster-deploy/config/mysql/my-node2.cnf EOF [mysqld] server-id2 bind-address0.0.0.0 port3306 basedir/usr datadir/var/lib/mysql socket/var/run/mysqld/mysqld.sock pid-file/var/run/mysqld/mysqld.pid log-error/var/log/mysql/error.log ​ report_host172.20.4.12 ​ gtid_modeON enforce_gtid_consistencyON log_slave_updatesON binlog_checksumNONE skip-name-resolve ​ loose-group_replication_group_nameaaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee loose-group_replication_start_on_bootOFF loose-group_replication_local_address172.20.4.12:33061 loose-group_replication_group_seeds172.20.4.11:33061,172.20.4.12:33061,172.20.4.13:33061 loose-group_replication_ip_allowlist172.20.4.0/24 loose-group_replication_single_primary_modeON loose-group_replication_enforce_update_everywhere_checksOFF loose-group_replication_poll_spin_loops100 loose-group_replication_recovery_reconnect_interval10 loose-group_replication_member_weight60 ​ master_info_repositoryTABLE relay_log_info_repositoryTABLE transaction_write_set_extractionXXHASH64 binlog_formatROW ​ [client] socket/var/run/mysqld/mysqld.sock ​ [mysql] socket/var/run/mysqld/mysqld.sock EOFNode3配置文件 (my-node3.cnf)cat /opt/cluster-deploy/config/mysql/my-node3.cnf EOF [mysqld] server-id3 bind-address0.0.0.0 port3306 basedir/usr datadir/var/lib/mysql socket/var/run/mysqld/mysqld.sock pid-file/var/run/mysqld/mysqld.pid log-error/var/log/mysql/error.log ​ report_host172.20.4.13 ​ gtid_modeON enforce_gtid_consistencyON log_slave_updatesON binlog_checksumNONE skip-name-resolve ​ loose-group_replication_group_nameaaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee loose-group_replication_start_on_bootOFF loose-group_replication_local_address172.20.4.13:33061 loose-group_replication_group_seeds172.20.4.11:33061,172.20.4.12:33061,172.20.4.13:33061 loose-group_replication_ip_allowlist172.20.4.0/24 loose-group_replication_single_primary_modeON loose-group_replication_enforce_update_everywhere_checksOFF loose-group_replication_poll_spin_loops100 loose-group_replication_recovery_reconnect_interval10 loose-group_replication_member_weight50 ​ master_info_repositoryTABLE relay_log_info_repositoryTABLE transaction_write_set_extractionXXHASH64 binlog_formatROW ​ [client] socket/var/run/mysqld/mysqld.sock ​ [mysql] socket/var/run/mysqld/mysqld.sock EOF配置项详解1. 基础配置server-id1 # 节点唯一ID每个节点不同 bind-address0.0.0.0 # 监听所有接口 port3306 # MySQL端口 socket/var/run/mysqld/mysqld.sock # Socket文件2. report_hostreport_host172.20.4.11重要排错经验Macvlan网络无DNS解析必须使用IP地址。3. GTID配置gtid_modeON # 启用GTID模式MGR必须 enforce_gtid_consistencyON # 强制GTID一致性MGR必须 log_slave_updatesON # 从节点记录主从日志 binlog_checksumNONE # 禁用binlog校验 skip-name-resolve # 跳过域名解析重要排错经验MGR要求gtid_modeON不开启会导致无法加入集群。4. MGR配置loose-前缀loose-group_replication_group_nameaaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee说明loose-前缀表示插件未加载时忽略此配置UUID可使用uuidgen命令生成重要排错经验MGR参数必须写在[mysqld]段不是[mysql]段。参数说明Node1Node2Node3server-id唯一ID123report_host报告IP172.20.4.11172.20.4.12172.20.4.13local_address通信地址172.20.4.11:33061172.20.4.12:33061172.20.4.13:33061member_weight优先级7060505. 复制配置master_info_repositoryTABLE # 主信息存储表 relay_log_info_repositoryTABLE # 中继日志存储表 transaction_write_set_extractionXXHASH64 # 事务集合提取算法 binlog_formatROW # 行格式支持MGRDocker Compose配置Node1mysql-01: image: mysql:8.0 container_name: mysql-01 hostname: mysql-node1 networks: database-net: ipv4_address: 172.20.4.11 volumes: - mysql-01-data:/var/lib/mysql - ./config/mysql/my-node1.cnf:/etc/mysql/conf.d/my.cnf:ro - ./config/mysql/init.sql:/docker-entrypoint-initdb.d/init.sql:ro environment: - MYSQL_ROOT_PASSWORDYourStr0ng!Pass - MYSQL_DATABASEapp_db restart: unless-stopped healthcheck: test: [CMD, mysqladmin, -uroot, -pYourStr0ng!Pass, ping, -h, 127.0.0.1] interval: 15s timeout: 10s retries: 5Node2/Node3mysql-02: image: mysql:8.0 container_name: mysql-02 hostname: mysql-node2 networks: database-net: ipv4_address: 172.20.4.12 volumes: - mysql-02-data:/var/lib/mysql - ./config/mysql/my-node2.cnf:/etc/mysql/conf.d/my.cnf:ro environment: - MYSQL_ROOT_PASSWORDYourStr0ng!Pass - MYSQL_DATABASEapp_db restart: unless-stopped healthcheck: test: [CMD, mysqladmin, -uroot, -pYourStr0ng!Pass, ping, -h, 127.0.0.1] interval: 15s timeout: 10s retries: 5重要排错经验1. healthcheck不支持--no-auth-warning问题mysqladmin命令不支持--no-auth-warning参数错误配置healthcheck: test: [CMD, mysqladmin, -uroot, -pYourStr0ng!Pass, ping, --no-auth-warning] # 错误正确配置healthcheck: test: [CMD, mysqladmin, -uroot, -pYourStr0ng!Pass, ping, -h, 127.0.0.1]2. 使用-h 127.0.0.1走TCP问题socket连接可能失败解决方法使用-h 127.0.0.1强制TCP连接3. caching_sha2_password认证问题问题MGR复制用户不支持新认证插件解决方法创建用户时使用mysql_native_passwordCREATE USER repl_user% IDENTIFIED WITH mysql_native_password BY YourStr0ng!Pass; GRANT REPLICATION SLAVE ON *.* TO repl_user%;服务IP分配节点MySQLIP容器名MGR通信端口member_weightNode1Primary172.20.4.11mysql-013306170Node2Secondary172.20.4.12mysql-023306160Node3Secondary172.20.4.13mysql-033306150常见问题Q1: MGR无法启动检查gtid_modeON是否设置检查server-id是否唯一查看错误日志docker logs mysql-01Q2: 节点无法加入集群检查网络连通性ping 172.20.4.11检查防火墙端口3306、33061是否开放确认report_host使用IP地址Q3: 复制延迟检查网络带宽调整member_weight优先级查看慢查询日志下一步11-MySQL-MGR初始化.md - 初始化MGR集群12-验证测试.md - 验证MySQL MGR状态