MySQL新建表：1044 – Access denied for user ‘root‘@%‘ to database ‘XXX‘终极解决方案

1.问题描述

情况一： Navicat连接远程mysql数据库，创建数据库时出现错误：

在这里插入图片描述

情况二：在linux中命令创建数据库出现如下报错：

在这里插入图片描述

2.原因分析

root权限不够，先使用“SELECT host,user,Grant_priv,Super_priv FROM mysql.user;”查询，发现已经是Y了。

在这里插入图片描述

通过SELECT * FROM mysql.user;命令查询权限信息，可以看到root对应的很多权限都是N，如下图所示：

在这里插入图片描述

至于为什么会出现root权限不够，可能是安装mysql的时候MySQL权限配置不正确，或者修改了相关的配置文件等。

3.解决方案

将root权限全部修改为Y，执行如下代码（记得退出mysql，重启服务）：

修改权限

use mysql; 
update user set Update_priv ='Y' where user = 'root';
update user set Select_priv ='Y' where user = 'root';
update user set Insert_priv ='Y' where user = 'root';
update user set Update_priv ='Y' where user = 'root';
update user set Delete_priv ='Y' where user = 'root';
update user set Create_priv ='Y' where user = 'root';
update user set Drop_priv ='Y' where user = 'root';
update user set Reload_priv ='Y' where user = 'root';
update user set Shutdown_priv ='Y' where user = 'root';
update user set Process_priv ='Y' where user = 'root';
update user set File_priv ='Y' where user = 'root';
update user set Grant_priv ='Y' where user = 'root';
update user set References_priv ='Y' where user = 'root';
update user set Index_priv ='Y' where user = 'root';
update user set Alter_priv ='Y' where user = 'root';
update user set Show_db_priv ='Y' where user = 'root';
update user set Super_priv ='Y' where user = 'root';
update user set Create_tmp_table_priv ='Y' where user = 'root';
update user set Lock_tables_priv ='Y' where user = 'root';
update user set Execute_priv ='Y' where user = 'root';
update user set Repl_slave_priv ='Y' where user = 'root';
update user set Repl_client_priv ='Y' where user = 'root';
update user set Create_view_priv ='Y' where user = 'root';
update user set Show_view_priv ='Y' where user = 'root';
update user set Create_routine_priv ='Y' where user = 'root';
update user set Alter_routine_priv ='Y' where user = 'root';
update user set Create_user_priv ='Y' where user = 'root';
update user set Event_priv ='Y' where user = 'root';
update user set Trigger_priv ='Y' where user = 'root';

退出mysql