mysql如何迁移用户权限数据_mysql用户授权语句导出

导出mysql用户权限必须用show grants而非直接查mysql.user表，因其权限分散在多张系统表中且版本差异大；show grants生成标准可执行sql，需逐个用户+主机执行，并注意8.0角色、认证插件及语法兼容性。

导出 MySQL 用户权限要用 SHOW GRANTS，不是 select * FROM mysql.user

直接查 mysql.user 表只能拿到密码哈希、账户状态等元信息，权限实际分散在 mysql.db、mysql.tables_priv 等多张表里，结构复杂且版本差异大。真正可迁移、可复用的权限定义，必须靠 SHOW GRANTS for 'user'@'host' 生成标准 SQL 语句。

常见错误现象：
– 导出 mysql.user 后在目标库执行 INSERT INTO mysql.user ...，结果权限不生效，甚至导致登录失败
– 忘记 FLUSH PRIVILEGES，误以为插入完就自动生效

• SHOW GRANTS 输出的是完整授权语句（含 WITH GRANT OPTION），可直接在新实例执行
• 必须对每个用户+主机组合单独执行，比如 'app'@'10.20.%' 和 'app'@'localhost' 是两个不同账号
• MySQL 8.0+ 引入角色（role），若用户被赋予了角色，SHOW GRANTS 会一并输出 SET default ROLE，别漏掉

批量导出所有用户的权限：用 mysql 命令 + awk 或简单脚本

MySQL 没有内置“导出全部权限”的命令，但可以组合系统表和 SHOW GRANTS 动态生成。最稳妥的方式是先查出所有非内置账号，再逐个调用 SHOW GRANTS：

示例（linux 终端）：

mysql -Nse "SELECT CONCAT(''', user, ''@'', host, ''') FROM mysql.user WHERE user NOT IN ('mysql.infoschema','mysql.session','mysql.sys','root') AND user NOT LIKE 'performance_schema%'" | while read u; do echo "SHOW GRANTS FOR $u;"; done | mysql -N | sed 's/$/;/g'

说明：
– -N 关闭列名输出，-s 精简格式，避免多余空格干扰解析
– 过滤掉系统用户（如 mysql.session）和 performance_schema 相关账号，否则会报错
– sed 's/$/;/g' 补上分号，方便后续执行

• 如果目标 MySQL 版本低于 5.7，注意 mysql.user 表中 plugin 字段可能为空，这类账号导出后需手动确认认证方式
• 脚本输出的是纯 SQL，建议重定向保存为 grants.sql，再人工检查是否有敏感库名或临时测试账号
• 不要用 mysqldump --all-databases 来导权限——它不会 dump mysql 系统库（默认排除），加 --databases mysql 又极危险，容易覆盖系统表结构

导入时权限不生效？重点检查 sql_mode 和 skip-grant-tables

权限 SQL 执行成功不代表立即可用。典型问题出在环境配置上：

• 目标库开启了 skip-grant-tables：此时所有权限校验被跳过，GRANT 语句看似执行成功，实则没写入磁盘，重启后全丢
• sql_mode 包含 NO_AUTO_CREATE_USER（MySQL 5.7 默认）：会导致旧式 GRANT ... IDENTIFIED BY 语法报错，必须拆成 CREATE USER + GRANT 两步
• 用户 host 匹配顺序问题：MySQL 按 user@host 的 host 字段做最长后缀匹配，'user'@'%' 和 'user'@'192.168.%' 共存时，后者优先级更高；导出时若没保留原始 host，可能误用通配符

验证是否生效：用对应账号连接后执行 SELECT CURRENT_USER(), USER();，确认实际匹配的账号身份，再跑 SHOW GRANTS; 对照。

跨大版本迁移（如 5.7 → 8.0）要重置密码字段和认证插件

MySQL 8.0 彻底废弃 mysql.user.password 字段，改用 authentication_string，且默认认证插件从 mysql_native_password 变为 caching_sha2_password。即使你只导出了 GRANT 语句，目标库创建用户时仍依赖 CREATE USER 的隐式行为。

• 如果源库用户用的是老式密码（OLD_PASSWORD() 或空密码字段），在 8.0 上执行 GRANT 会报 Error 1827 (HY000)，必须先显式指定插件：CREATE USER 'u'@'h' IDENTIFIED WITH mysql_native_password BY 'pwd';
• 迁移脚本里若混用了 IDENTIFIED BY 和 IDENTIFIED WITH，8.0 会拒绝执行，需统一替换
• 8.0.27+ 开始禁用 GRANT ... IDENTIFIED BY 语法，必须拆解——这是最容易被忽略的兼容性断点

权限迁移从来不是单纯复制 SQL，关键在理解账号生命周期：创建 → 授权 → 认证方式 → 主机匹配规则。少一个环节，就会卡在连不上、查不到、改不了的边缘。