如何在MySQL主从同步环境中保持用户权限一致性-mysql教程-PHP中文网

MySQL主从权限默认不同步，因mysql.user变更不写入binlog或被replicate_ignore_db=mysql跳过；必须人工用CREATE USER+GRANT、导出导入或pt-show-grants同步，严禁直改系统表。

如何在mysql主从同步环境中保持用户权限一致性

MySQL主从同步环境中，用户权限默认不会一致 —— 这不是配置错了，而是设计如此。必须人工干预，否则从库永远查不到主库创建的用户，应用连从库必报 Access denied for user。

根本原因是：MySQL 默认不把 mysql.user 表的变更写入 binlog，或写了但被显式跳过。

MySQL 5.7 及更早版本中，GRANT、CREATE USER 等语句默认不记 binlog（除非 log_bin_trust_function_creators=1 且 binlog_format 为 ROW 或 MIXED）
MySQL 8.0+ 虽默认记录 DDL，但前提是主库 log_bin=ON，且从库未配置 replicate_ignore_db=mysql —— 这个配置在绝大多数 Ansible 模板、Docker 镜像里都默认存在
执行 SHOW SLAVE STATUS\G，检查 Replicate_Ignore_DB 字段是否含 mysql；若含，则权限变更压根不会被读取和回放
即使没忽略，mysql 库的 DML/DDL 在复制链路中会被 MySQL 硬编码跳过（尤其在 GTID 模式下），INSERT INTO mysql.user 会直接报错 ERROR 1786 (HY000): Statement violates GTID consistency

直接操作系统表不仅无效，还会导致复制中断、密码加密方式错乱（authentication_string vs password）、字段缺失（如 account_locked、password_expired）。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

在主库执行标准语句：CREATE USER 'reporter'@'%' IDENTIFIED BY 'pwd'; GRANT SELECT ON db.* TO 'reporter'@'%';
避免一步 GRANT 隐式建用户：MySQL 5.7+ 密码策略不同可能导致从库报 ERROR 1827 (HY000)，拆成两步更可控
执行后无需手动 FLUSH PRIVILEGES（GRANT 自动触发），但若从库因延迟暂未生效，可临时在从库执行一次（仅用于确认，非常规流程）
严禁在从库执行任何权限语句（哪怕加了 SET SQL_LOG_BIN=0），否则主库后续同名用户变更会冲突

没有“开箱即用”的自动同步，必须选一种人工路径并坚持使用。

导出导入（最稳妥，适合低频变更）：主库执行 mysqldump --single-transaction --skip-triggers --databases mysql > mysql_privs.sql，删掉 general_log、slow_log 等无关表，停掉从库 SQL_THREAD 后导入
复刻权限语句（推荐用于 MySQL 8.0+）：主库运行 SELECT CONCAT('CREATE USER ''',user,'''@''',host,''' IDENTIFIED WITH ',plugin,' AS ''',authentication_string,''';') FROM mysql.user WHERE user NOT IN ('mysql.infoschema','mysql.session','mysql.sys');，再补 GRANT，在从库用 SET SQL_LOG_BIN=0; 关闭 binlog 后执行
用 pt-show-grants（DBA 级别首选）：pt-show-grants --host=master_host --user=root --password=xxx --no-create-user 生成可重放语句；注意加 --columns 才输出列级权限，否则静默忽略

GTID 并不自动解决权限一致性 —— 它让问题更隐蔽，也更严格。

主库执行 CREATE ROLE r1; 后，若从库是 5.7 或未启用角色支持，会直接报 ERROR 3547 (HY000): Role does not exist，不能跳过，必须先升级或对齐版本
FLUSH PRIVILEGES 在 GTID 集群中完全无意义：它不写 binlog，从库执行无效，还可能掩盖真实未同步的问题
权限变更前务必在从库执行 SELECT VERSION(); SHOW VARIABLES LIKE 'version%'; 校验兼容性，尤其是动态权限（如 BACKUP_ADMIN）、密码历史策略等新特性
很多团队只检查主库 log_bin=ON，却忘了从库的 replicate_ignore_db 是从配置模板继承来的 —— 权限不同步，八成卡在这一步