mysql版本升级后如何处理权限问题_mysql权限迁移方案

mysql 8.0升级后因user表结构变更（移除password字段、改用authentication_string、默认plugin变为caching_sha2_password）及权限表字段扩展，直接迁移旧数据会导致权限丢失和登录失败。

升级后 mysql.user 表结构变化导致权限丢失

MySQL 8.0 起彻底移除了 password 字段，改用 authentication_string；同时 plugin 默认值从 mysql_native_password 变为 caching_sha2_password。如果直接复制旧版本的 mysql.user 行数据到新实例，会导致用户无法登录，报错类似：Access denied for user 'xxx'@'%' (using password: YES)。

实操建议：

CG Faces

免费的 AI 人物图像素材网站

下载

• 不要直接 INSERT INTO mysql.user SELECT ... FROM old_db.mysql.user 迁移整行——字段对不上，plugin 和 authentication_string 值无效
• 用 SHOW CREATE USER 'u'@'h' 在旧库查建用户语句，再手动在新库执行（注意替换密码哈希或重置密码）
• 若旧库是 MySQL 5.7 且用户用的是 mysql_native_password，新库需显式指定：CREATE USER 'u'@'h' IDENTIFIED WITH mysql_native_password BY 'xxx';
• 升级后首次登录推荐用 mysql -u root -p --default-auth=mysql_native_password，避免因默认插件不兼容卡住

权限表迁移时 mysql.db 和 mysql.tables_priv 的兼容性风险

MySQL 8.0 对权限表增加了字段（如 mysql.db 新增 password_required），但这些字段在旧版中不存在。如果用 mysqldump 导出整个 mysql 库再导入，会因列数不匹配失败。

实操建议：

• 禁止 dump 整个 mysql 库——只导出业务相关用户和权限，用 mysqldump --no-create-info --where="Host!='localhost' AND User!='mysql.infoschema'" mysql user db tables_priv columns_priv
• 导入前先清空目标库的权限表（TRUNCATE mysql.user; TRUNCATE mysql.db; 等），再导入，否则可能触发唯一键冲突
• mysql.db 中的 Db 字段在 8.0 后区分大小写（受 lower_case_table_names 影响），若旧库有大写 DB 名，需确认新库配置一致，否则权限不生效

用 mysqldump --all-databases 备份时漏掉权限的真相

mysqldump --all-databases 默认不包含 mysql 库，除非显式加上 --databases mysql。很多运维误以为“all”含系统库，结果升级后所有权限全丢。

实操建议：

• 备份权限必须单独处理：mysqldump --no-create-info --skip-extended-insert mysql user db tables_priv procs_priv columns_priv > mysql-grants.sql
• 导入前检查 SQL 文件是否含 CREATE DATABASE IF NOT EXISTS `mysql` ——如有，删掉，否则会覆盖新库的系统表结构
• 导入后务必运行 FLUSH PRIVILEGES;，尤其当手动修改了 mysql 表但未重启时

升级后 root@localhost 权限异常的典型场景

MySQL 8.0 安装后默认创建 root@localhost 用户，使用 caching_sha2_password 插件，且 host 限定为 localhost（不是 127.0.0.1）。如果旧环境习惯用 127.0.0.1 连接，会提示拒绝访问，误判为权限丢失。

实操建议：

• 检查连接方式：mysql -h 127.0.0.1 -u root -p 和 mysql -h localhost -u root -p 是两个不同用户（host 匹配规则不同）
• 如需统一，可删掉 root@localhost，新建 root@'127.0.0.1' 并赋权，或修改 root@localhost 的 plugin 为 mysql_native_password
• 本地开发常用 socket 连接，此时 host 解析为 localhost，必须确保该用户存在且 plugin 兼容客户端（如老版 PHP mysqli 扩展不支持 caching_sha2_password）