AWS RDS MySQL无法创建'root'@'localhost'用户求助

问题根源 #

AWS RDS MySQL中，mysql.user表未显示的用户，可能在其他权限关联表（如mysql.db、tables_priv等）存在残留记录，导致创建操作触发"用户已存在"的1396错误；另外RDS默认的admin用户已具备超级权限，官方不建议自定义root用户。

操作步骤 #

1. 排查残留权限记录
   执行以下SQL，检查是否有root'@'localhost的残留权限条目：

SELECT * FROM mysql.db WHERE User = 'root' AND Host = 'localhost';
SELECT * FROM mysql.tables_priv WHERE User = 'root' AND Host = 'localhost';
SELECT * FROM mysql.columns_priv WHERE User = 'root' AND Host = 'localhost';
SELECT * FROM mysql.procs_priv WHERE User = 'root' AND Host = 'localhost';

2. 清理残留记录后重试创建
   如果上述查询返回结果，先删除残留条目并刷新权限：

DELETE FROM mysql.db WHERE User = 'root' AND Host = 'localhost';
DELETE FROM mysql.tables_priv WHERE User = 'root' AND Host = 'localhost';
DELETE FROM mysql.columns_priv WHERE User = 'root' AND Host = 'localhost';
DELETE FROM mysql.procs_priv WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

然后重新执行创建命令：

CREATE USER 'root'@'localhost' IDENTIFIED BY 'your_secure_password';

3. 更优方案：使用默认admin用户
   RDS默认的admin@'%'用户已拥有全部数据库管理权限，完全可替代root用户。若需限制其访问范围，可修改Host属性：

RENAME USER 'admin'@'%' TO 'admin'@'localhost';
FLUSH PRIVILEGES;

内容的提问来源于stack exchange，提问作者Meow