mysql access denied for user 'root'@' % ' to database

grantguan · 发表于 2012-7-23 14:17

RogerZhuo 发表于 2012-7-23 12:48
你看你配置文件里是不是做了bind-adreess=192.168.10.78的？

我感觉是做了绑定IP的才会出现你这种情况， ...

没有设置bind-ip 呀，我的一台production 同样问题，都是重新安装过的mySQL,其它几台没有重新安装的机器没有这个问题，这是我有问题pc的my.ini

[client]

port=3306

[mysql]

default-character-set=latin1

[mysqld]
federated
skip-external-locking
skip-name-resolve
server-id=1
binlog-do-db=demo
log-bin=D:\mySQLSlave\log-bin.log
port=3306
basedir="C:/mySQL/"

#Path to the database root
#datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
datadir="C:/mysqlsata/Data/"

character-set-server=latin1

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=800
query_cache_size=350M
table_cache=1520
tmp_table_size=125M
thread_cache_size=38

myisam_max_sort_file_size=100G
myisam_sort_buffer_size=125M
key_buffer_size=548M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K

#*** INNODB Specific options ***
innodb_data_home_dir="C:/mysqlsata/"
#skip-innodb
innodb_additional_mem_pool_size=22M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=11M
innodb_buffer_pool_size=1G
innodb_log_file_size=213M
innodb_thread_concurrency=8

lixiaxin200319 · 发表于 2012-7-23 14:21

请用select * from mysql.user\G的方式查询user表，这样才能正确显示user表中数据存储的顺序，把你查询的结果贴出来看下各用户的权限，用select  user,host from user;的方式查询mysql把结果重新排了序，无法看出允许登录的用户是哪个。

在my.cnf 中设置 bind-adreess是设置本机的哪一个ip上监听，不会控制访问的IP。

下面的例子中，实际是root@localhost这个用户存储在第一行，本地登录时匹配的是root@localhost。

mysql> select user,host from mysql.user;
+------+-----------+
| user | host    |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1    |
|    | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.02 sec)

mysql> select * from mysql.user\G
*************************** 1. row **
               Host: localhost
               User: root
            Password: *F1384DB76D81
         Select_priv: Y
         Insert_priv: Y
         Update_priv: Y
         Delete_priv: Y
         Create_priv: Y
         Drop_priv: Y
         Reload_priv: Y
      Shutdown_priv: Y
      Process_priv: Y
         File_priv: Y
         Grant_priv: Y
   References_priv: Y
         Index_priv: Y
         Alter_priv: Y
      Show_db_priv: Y
         Super_priv: Y
Create_tmp_table_priv: Y
   Lock_tables_priv: Y
      Execute_priv: Y
   Repl_slave_priv: Y
   Repl_client_priv: Y
   Create_view_priv: Y
      Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
   Create_user_priv: Y
         Event_priv: Y
      Trigger_priv: Y
Create_tablespace_priv: Y
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string:
*************************** 2. row **
               Host: 127.0.0.1
               User: root
            Password:
         Select_priv: Y
         Insert_priv: Y
         Update_priv: Y
         Delete_priv: Y
         Create_priv: Y
         Drop_priv: Y
         Reload_priv: Y
      Shutdown_priv: Y
      Process_priv: Y
         File_priv: Y
         Grant_priv: Y
   References_priv: Y
         Index_priv: Y
         Alter_priv: Y
      Show_db_priv: Y
         Super_priv: Y
Create_tmp_table_priv: Y
   Lock_tables_priv: Y
      Execute_priv: Y
   Repl_slave_priv: Y
   Repl_client_priv: Y
   Create_view_priv: Y
      Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
   Create_user_priv: Y
         Event_priv: Y
      Trigger_priv: Y
Create_tablespace_priv: Y
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string:
*************************** 3. row **
               Host: ::1
               User: root
            Password:
         Select_priv: Y
         Insert_priv: Y
         Update_priv: Y
         Delete_priv: Y
         Create_priv: Y
         Drop_priv: Y
         Reload_priv: Y
      Shutdown_priv: Y
      Process_priv: Y
         File_priv: Y
         Grant_priv: Y
   References_priv: Y
         Index_priv: Y
         Alter_priv: Y
      Show_db_priv: Y
         Super_priv: Y
Create_tmp_table_priv: Y
   Lock_tables_priv: Y
      Execute_priv: Y
   Repl_slave_priv: Y
   Repl_client_priv: Y
   Create_view_priv: Y
      Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
   Create_user_priv: Y
         Event_priv: Y
      Trigger_priv: Y
Create_tablespace_priv: Y
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string:
*************************** 4. row **
               Host: localhost
               User:
            Password:
         Select_priv: N
         Insert_priv: N
         Update_priv: N
         Delete_priv: N
         Create_priv: N
         Drop_priv: N
         Reload_priv: N
      Shutdown_priv: N
      Process_priv: N
         File_priv: N
         Grant_priv: N
   References_priv: N
         Index_priv: N
         Alter_priv: N
      Show_db_priv: N
         Super_priv: N
Create_tmp_table_priv: N
   Lock_tables_priv: N
      Execute_priv: N
   Repl_slave_priv: N
   Repl_client_priv: N
   Create_view_priv: N
      Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
   Create_user_priv: N
         Event_priv: N
      Trigger_priv: N
Create_tablespace_priv: N
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string:
4 rows in set (0.00 sec)

lixiaxin200319 · 发表于 2012-7-23 14:22

请用select * from mysql.user\G的方式查询user表，这样才能正确显示user表中数据存储的顺序，把你查询的结果贴出来看下各用户的权限，用select  user,host from user;的方式查询mysql把结果重新排了序，无法看出允许登录的用户是哪个。

在my.cnf 中设置 bind-adreess是设置本机的哪一个ip上监听，不会控制访问的IP。

下面的例子中，实际是root@localhost这个用户存储在第一行，本地登录时匹配的是root@localhost。

mysql> select user,host from mysql.user;
+------+-----------+
| user | host    |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1    |
|    | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.02 sec)

mysql> select * from mysql.user\G
*************************** 1. row **
               Host: localhost
               User: root
            Password: *F1384DB76D81
         Select_priv: Y
         Insert_priv: Y
         Update_priv: Y
         Delete_priv: Y
         Create_priv: Y
         Drop_priv: Y
         Reload_priv: Y
      Shutdown_priv: Y
      Process_priv: Y
         File_priv: Y
         Grant_priv: Y
   References_priv: Y
         Index_priv: Y
         Alter_priv: Y
      Show_db_priv: Y
         Super_priv: Y
Create_tmp_table_priv: Y
   Lock_tables_priv: Y
      Execute_priv: Y
   Repl_slave_priv: Y
   Repl_client_priv: Y
   Create_view_priv: Y
      Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
   Create_user_priv: Y
         Event_priv: Y
      Trigger_priv: Y
Create_tablespace_priv: Y
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string:
*************************** 2. row **
               Host: 127.0.0.1
               User: root
            Password:
         Select_priv: Y
         Insert_priv: Y
         Update_priv: Y
         Delete_priv: Y
         Create_priv: Y
         Drop_priv: Y
         Reload_priv: Y
      Shutdown_priv: Y
      Process_priv: Y
         File_priv: Y
         Grant_priv: Y
   References_priv: Y
         Index_priv: Y
         Alter_priv: Y
      Show_db_priv: Y
         Super_priv: Y
Create_tmp_table_priv: Y
   Lock_tables_priv: Y
      Execute_priv: Y
   Repl_slave_priv: Y
   Repl_client_priv: Y
   Create_view_priv: Y
      Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
   Create_user_priv: Y
         Event_priv: Y
      Trigger_priv: Y
Create_tablespace_priv: Y
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string:
*************************** 3. row **
               Host: ::1
               User: root
            Password:
         Select_priv: Y
         Insert_priv: Y
         Update_priv: Y
         Delete_priv: Y
         Create_priv: Y
         Drop_priv: Y
         Reload_priv: Y
      Shutdown_priv: Y
      Process_priv: Y
         File_priv: Y
         Grant_priv: Y
   References_priv: Y
         Index_priv: Y
         Alter_priv: Y
      Show_db_priv: Y
         Super_priv: Y
Create_tmp_table_priv: Y
   Lock_tables_priv: Y
      Execute_priv: Y
   Repl_slave_priv: Y
   Repl_client_priv: Y
   Create_view_priv: Y
      Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
   Create_user_priv: Y
         Event_priv: Y
      Trigger_priv: Y
Create_tablespace_priv: Y
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string:
*************************** 4. row **
               Host: localhost
               User:
            Password:
         Select_priv: N
         Insert_priv: N
         Update_priv: N
         Delete_priv: N
         Create_priv: N
         Drop_priv: N
         Reload_priv: N
      Shutdown_priv: N
      Process_priv: N
         File_priv: N
         Grant_priv: N
   References_priv: N
         Index_priv: N
         Alter_priv: N
      Show_db_priv: N
         Super_priv: N
Create_tmp_table_priv: N
   Lock_tables_priv: N
      Execute_priv: N
   Repl_slave_priv: N
   Repl_client_priv: N
   Create_view_priv: N
      Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
   Create_user_priv: N
         Event_priv: N
      Trigger_priv: N
Create_tablespace_priv: N
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string:
4 rows in set (0.00 sec)

lixiaxin200319 · 发表于 2012-7-23 14:25

请用select * from mysql.user\G的方式查询user表，这样才能正确显示user表中数据存储的顺序，把你查询的结果贴出来看下各用户的权限，用select  user,host from user;的方式查询mysql把结果重新排了序，无法看出允许登录的用户是哪个。

在my.cnf 中设置 bind-adreess是设置本机的哪一个ip上监听，不会控制访问的IP。

下面的例子中，实际是root@localhost这个用户存储在第一行，本地登录时匹配的是root@localhost。

mysql> select user,host from mysql.user;
+------+-----------+
| user | host    |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1    |
|    | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.02 sec)

mysql> select * from mysql.user\G
*************************** 1. row **
               Host: localhost
               User: root
            Password: *F1384DB76D81
         Select_priv: Y
         Insert_priv: Y
         Update_priv: Y
         Delete_priv: Y
         Create_priv: Y
         Drop_priv: Y
         Reload_priv: Y
      Shutdown_priv: Y
      Process_priv: Y
         File_priv: Y
         Grant_priv: Y
   References_priv: Y
         Index_priv: Y
         Alter_priv: Y
      Show_db_priv: Y
         Super_priv: Y
Create_tmp_table_priv: Y
   Lock_tables_priv: Y
      Execute_priv: Y
   Repl_slave_priv: Y
   Repl_client_priv: Y
   Create_view_priv: Y
      Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
   Create_user_priv: Y
         Event_priv: Y
      Trigger_priv: Y
Create_tablespace_priv: Y
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string:
*************************** 2. row **
               Host: 127.0.0.1
               User: root
            Password:
         Select_priv: Y
         Insert_priv: Y
         Update_priv: Y
         Delete_priv: Y
         Create_priv: Y
         Drop_priv: Y
         Reload_priv: Y
      Shutdown_priv: Y
      Process_priv: Y
         File_priv: Y
         Grant_priv: Y
   References_priv: Y
         Index_priv: Y
         Alter_priv: Y
      Show_db_priv: Y
         Super_priv: Y
Create_tmp_table_priv: Y
   Lock_tables_priv: Y
      Execute_priv: Y
   Repl_slave_priv: Y
   Repl_client_priv: Y
   Create_view_priv: Y
      Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
   Create_user_priv: Y
         Event_priv: Y
      Trigger_priv: Y
Create_tablespace_priv: Y
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string:
*************************** 3. row **
               Host: ::1
               User: root
            Password:
         Select_priv: Y
         Insert_priv: Y
         Update_priv: Y
         Delete_priv: Y
         Create_priv: Y
         Drop_priv: Y
         Reload_priv: Y
      Shutdown_priv: Y
      Process_priv: Y
         File_priv: Y
         Grant_priv: Y
   References_priv: Y
         Index_priv: Y
         Alter_priv: Y
      Show_db_priv: Y
         Super_priv: Y
Create_tmp_table_priv: Y
   Lock_tables_priv: Y
      Execute_priv: Y
   Repl_slave_priv: Y
   Repl_client_priv: Y
   Create_view_priv: Y
      Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
   Create_user_priv: Y
         Event_priv: Y
      Trigger_priv: Y
Create_tablespace_priv: Y
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string:
*************************** 4. row **
               Host: localhost
               User:
            Password:
         Select_priv: N
         Insert_priv: N
         Update_priv: N
         Delete_priv: N
         Create_priv: N
         Drop_priv: N
         Reload_priv: N
      Shutdown_priv: N
      Process_priv: N
         File_priv: N
         Grant_priv: N
   References_priv: N
         Index_priv: N
         Alter_priv: N
      Show_db_priv: N
         Super_priv: N
Create_tmp_table_priv: N
   Lock_tables_priv: N
      Execute_priv: N
   Repl_slave_priv: N
   Repl_client_priv: N
   Create_view_priv: N
      Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
   Create_user_priv: N
         Event_priv: N
      Trigger_priv: N
Create_tablespace_priv: N
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string:
4 rows in set (0.00 sec)

grantguan · 发表于 2012-7-23 14:27

lixiaxin200319 发表于 2012-7-23 14:22
请用select * from mysql.user\G的方式查询user表，这样才能正确显示user表中数据存储的顺序，把你查询的结 ...

来了：
mysql> select * from user\G;
*************************** 1. row ***************************
               Host: localhost
               User: root
            Password: *5138B3991A524DF36C3FEFCC7A991276CB81A367
         Select_priv: Y
         Insert_priv: Y
         Update_priv: Y
         Delete_priv: Y
         Create_priv: Y
         Drop_priv: Y
         Reload_priv: Y
      Shutdown_priv: Y
      Process_priv: Y
         File_priv: Y
         Grant_priv: Y
   References_priv: Y
         Index_priv: Y
         Alter_priv: Y
      Show_db_priv: Y
         Super_priv: Y
Create_tmp_table_priv: Y
   Lock_tables_priv: Y
      Execute_priv: Y
   Repl_slave_priv: Y
   Repl_client_priv: Y
   Create_view_priv: Y
      Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
   Create_user_priv: Y
         Event_priv: Y
      Trigger_priv: Y
Create_tablespace_priv: Y
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string:
*************************** 2. row ***************************
               Host: 192.168.10.249
               User: backup
            Password: *5138B3991A524DF36C3FEFCC7A991276CB81A367
         Select_priv: N
         Insert_priv: N
         Update_priv: N
         Delete_priv: N
         Create_priv: N
         Drop_priv: N
         Reload_priv: Y
      Shutdown_priv: N
      Process_priv: N
         File_priv: N
         Grant_priv: N
   References_priv: N
         Index_priv: N
         Alter_priv: N
      Show_db_priv: N
         Super_priv: Y
Create_tmp_table_priv: N
   Lock_tables_priv: N
      Execute_priv: N
   Repl_slave_priv: Y
   Repl_client_priv: N
   Create_view_priv: N
      Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
   Create_user_priv: N
         Event_priv: N
      Trigger_priv: N
Create_tablespace_priv: N
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string: NULL
*************************** 3. row ***************************
               Host: 192.168.10.78
               User: root
            Password: *5138B3991A524DF36C3FEFCC7A991276CB81A367
         Select_priv: Y
         Insert_priv: Y
         Update_priv: Y
         Delete_priv: Y
         Create_priv: Y
         Drop_priv: Y
         Reload_priv: Y
      Shutdown_priv: Y
      Process_priv: Y
         File_priv: Y
         Grant_priv: Y
   References_priv: Y
         Index_priv: Y
         Alter_priv: Y
      Show_db_priv: Y
         Super_priv: Y
Create_tmp_table_priv: Y
   Lock_tables_priv: Y
      Execute_priv: Y
   Repl_slave_priv: Y
   Repl_client_priv: Y
   Create_view_priv: Y
      Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
   Create_user_priv: Y
         Event_priv: N
      Trigger_priv: N
Create_tablespace_priv: N
            ssl_type:
         ssl_cipher:
         x509_issuer:
      x509_subject:
      max_questions: 0
         max_updates: 0
   max_connections: 0
  max_user_connections: 0
            plugin:
authentication_string: NULL
3 rows in set (0.00 sec)

ERROR:
No query specified

mysql>

mchdba · 发表于 2012-7-23 14:40

grantguan 发表于 2012-7-23 11:56
用127.0.0.1或者localhost登陆，自动转换为root@%了，什么原因？

C:\mySQL\bin>mysql -hlocalhost -ur ...

登录的时候，记得加上-hlocalhost，这样就表名你用户名锁绑定的ip地址了。

C:\mySQL\bin>mysql -hlocalhost -uroot -p -hlocalhost

lixiaxin200319 · 发表于 2012-7-23 14:49

你现在能登录的是root@192.168.10.78,但是这个用户没有Event_priv的权限，你要赋予root@%用户Event_priv,必须把my.cnf 中的skip_name_resolve注释掉再重启mysql,然后用root@localhost登录给root@%用户赋予Event_priv权限，建议你新建root@127.0.0.1后把root@localhost拥有的所有权限都赋予root@127.0.0.1，这样启用了skip_name_resolve，root@127.0.0.1可以取代root@localhost

grantguan · 发表于 2012-7-23 14:57

解决，是skip-name-resolve 问题，comment 就解决问题了，感谢各位鼎力相助。

RogerZhuo · 发表于 2012-7-23 15:00

grantguan 发表于 2012-7-23 14:57
解决，是skip-name-resolve 问题，comment 就解决问题了，感谢各位鼎力相助。

呵呵，理解背后的本质问题就好，上次我们老大也犯同样的错误，把root@localhost清理了，
root@127.0.0.1没有with grant option.
然后因为是双网卡，bind-address了，一个劲的登不上。

今天晴天了 · 发表于 2016-11-16 14:51

RogerZhuo 发表于 2012-7-23 10:49
skip-name-resolve 这不能解析hostname, 所以为何登录用户匹配的时候不走root@'localhost', 一个劲的走root ...

我现在遇到了和楼主一样的问题；
怎么弄，都不行；都是提示 root没有权限；
而且，这样做了，还是不行；
我的mysql是 5.7 版本；
不知道该如何弄了；