【本人亲测 已解决】044 - Access denied for user ‘root‘@‘%‘ to database ‘数据库名称‘

问题描述

情况一: Navicat连接远程mysql数据库,创建数据库时出现错误:1044 - Access denied for user 'root'@'%' to database '数据库名称'
在这里插入图片描述
情况二: 在linux中命令创建数据库出现如下报错:
在这里插入图片描述


原因分析:

root权限不够,登录mysql后通过 SELECT * FROM mysql.user;命令查询权限信息,可以看到root对应的很多权限都是no,如下图所示:

在这里插入图片描述
补充:至于为什么会出现root权限不够,可能是安装mysql的时候MySQL权限配置不正确,或者修改了相关的配置文件等。


解决方案:

将root权限全部修改为yes,执行如下代码(记得退出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
exit
// 重启mysql服务
service mysqld restart

当你再去查询权限的时候已经全部修改为yes,问题就已经解决了,可以对数据库进行操作啦!!
在这里插入图片描述