用户“root”@“localhost”(使用密码:YES)拒绝访问权限?

I am continuously receiving this error.

我不断地收到这个错误。

I am using mySQL Workbench and from what I am finding is that root's schema privileges are null. There are no privileges at all.

我正在使用mySQL工作台，我发现根的模式特权是空的。根本没有任何特权。

I am having troubles across platforms that my server is used for and this has been all of a sudden issue.

我在我的服务器使用的平台上遇到麻烦，这是一个突然的问题。

root@127.0.0.1 apparently has allot of access but I am logged in as that, but it just assigns to localhost anyways - localhost has no privileges.

显然，root@127.0.0.1有很多访问权限，但我是这样登录的，但它只分配给localhost——localhost没有特权。

I have done a few things like FLUSH HOSTS, FLUSH PRIVILEGES, ect but have found no success from that or the internet.

我已经做了一些事情，比如刷新主机，刷新特权，ect，但没有发现这或互联网上的成功。

How can I get root its access back? I find this frustrating because when I look around people expect you to "have access" but I don't have access so I can't go into command line or anything and GRANT myself anything.

我怎样才能找到它的访问路径呢?我觉得这很让人沮丧，因为当我环顾四周时，人们期望你“有访问权限”，但我没有权限，所以我不能进入命令行或任何东西，并给自己任何东西。

When running SHOW GRANTS FOR root this is what I get in return: Error Code: 1141. There is no such grant defined for user 'root' on host '%'

当运行SHOW grant FOR root时，这是我得到的回报:错误代码:1141。在主机'%'上没有为用户'root'定义这样的授予。

Thanks to anyone who can provide help

感谢任何能提供帮助的人。

12 个解决方案

#1

Use the instructions for resetting the root password - but instead of resetting the root password, we'll going to forcefully INSERT a record into the mysql.user table

使用重新设置根密码的说明——但是不要重新设置根密码，我们将强制插入一个记录到mysql中。用户表

In the init file, use this instead

在init文件中，使用这个代替。

INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', password('YOURPASSWORD'));
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

#2

It didn't like my user privilege so I SUDO it. (in bash << sudo set user and password) (this gives username of root and sets the password to nothing) (On Mac)

它不喜欢我的用户特权，所以我选择了它。(在bash << sudo设置用户和密码)中(这给出了root用户的用户名，并将密码设置为零)(在Mac上)

sudo mysql -uroot -p

#3

Try the following commands

试试下面的命令

~$ sudo /etc/init.d/mysql stop
~$ sudo mysqld_safe --skip-grant-tables &
~$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

mysql> use mysql;

mysql> update user set password=PASSWORD("root") where User='root';

mysql> flush privileges;

mysql> quit

~$ sudo /etc/init.d/mysql stop

Stopping MySQL database server: mysqld

STOPPING server from pid file /var/run/mysqld/mysqld.pid

mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

~$ sudo /etc/init.d/mysql start

~$ mysql -u root -p

* MySQL Community Server 5.6.35 is started
~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

#4

for the people who are facing below error in mysql 5.7+ version -

对于在mysql 5.7+版本中面临以下错误的人。

Access denied for user 'root'@'localhost' (using password: YES)

Open new terminal

打开新终端
sudo /etc/init.d/mysql stop ... MySQL Community Server 5.7.8-rc is stopped

sudo /etc/init.d / mysql停止……MySQL社区服务器5.7.8-rc停止。
sudo mysqld_safe --skip-grant-tables & this will skipp all grant level privileges and start the mysql in safe mode Sometimes the process got stucked just because of

sudo mysqld_safe——skipg -grant-tables &这将会使所有的授权级别的特权，并在安全模式下启动mysql，有时这个过程会因为。

grep: write error: Broken pipe 180102 11:32:28 mysqld_safe Logging to '/var/log/mysql/error.log'.

grep:写入错误:断开的管道180102 11:32:28 mysqld_safe日志到'/var/log/mysql/error.log'。

Simply press Ctrl+Z or Ctrl+C to interrupt and exit process

只需按Ctrl+Z或Ctrl+C来中断和退出过程。

mysql -u root
mysql - u根

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.8-rc MySQL Community Server (GPL)

欢迎来到MySQL监视器。命令结尾;或\ g。你的MySQL连接id是2个服务器版本:5.7.8-rc MySQL社区服务器(GPL)

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

甲骨文是甲骨文公司和/或其附属公司的注册商标。其他名称可能是其各自所有者的商标。

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

类型“帮助”或“\h”帮助。键入'\c'以清除当前输入语句。

mysql> use mysql;
mysql >使用mysql;

Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

阅读表格信息，完成表格和列名，你可以关闭这个功能以获得更快的启动和-A。

Database changed

数据库修改

mysql> update user set authentication_string=password('password') where user='root'; Query OK, 4 rows affected, 1 warning (0.03 sec) Rows matched: 4 Changed: 4 Warnings: 1

mysql>更新用户设置authentication_string=password('password')，用户='root';查询OK, 4行受影响，1警告(0.03秒)行匹配:4更改:4警告:1。
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

mysql >冲洗特权;查询OK, 0行受影响(0.00秒)
mysql> quit Bye

mysql >退出再见
sudo /etc/init.d/mysql stop

sudo /etc/init.d / mysql停止

..180102 11:37:12 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended . * MySQL Community Server 5.7.8-rc is stopped arif@ubuntu:~$ sudo /etc/init.d/mysql start .. * MySQL Community Server 5.7.8-rc is started

. .来自pid文件/var/run/mysqld/mysqld的mysqld_safe mysqld。pid结束。* MySQL社区服务器5.7.8-rc停止了arif@ubuntu:~$ sudo /etc/ init。d / mysql开始. .* MySQL社区服务器5.7.8-rc启动。

mysql -u root -p

mysql - u root - p

Enter password:

输入密码:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.8-rc MySQL Community Server (GPL)

欢迎来到MySQL监视器。命令结尾;或\ g。你的MySQL连接id是2个服务器版本:5.7.8-rc MySQL社区服务器(GPL)

after mysql 5.7+ version the column password replaced by name authentication_string from the mysql.user table.

在mysql 5.7+版本之后，从mysql中替换名为authentication_string的列密码。用户表。

hope these steps will help anyone, thanks.

希望这些步骤能帮助任何人，谢谢。

#5

A simple way to reset root password on Linux systems :

一种简单的方法来重置Linux系统上的root密码:

sudo dpkg-reconfigure mysql-server-5.5

Checkout some other reasons for Access denied :

结帐的其他原因被拒绝:

https://dev.mysql.com/doc/refman/5.7/en/problems-connecting.html

#6

Well the easiest way to reset root password is:

重置根密码最简单的方法是:

restart mysqld --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.

重启mysqld——skip-grant-tables选项。这使得任何人都可以在没有密码和所有特权的情况下连接。因为这是不安全的，所以您可能想要使用—skipg -grant-tables与—skip-network，以防止远程客户端连接。
Connect to the mysqld server with this command:

使用以下命令连接到mysqld服务器:
shell> mysql Issue the following statements in the mysql client. Replace the password with the password that you want to use.

在mysql客户机中，shell> mysql发出以下语句。用您想要使用的密码替换密码。
mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') -> WHERE User='root'; mysql> FLUSH PRIVILEGES;

mysql >更新mysql。用户设置密码=密码('MyNewPass') ->，用户='root';mysql >冲洗特权;
Stop the server, then restart it normally (without the --skip-grant-tables and --skip-networking options).

停止服务器，然后正常地重新启动它(没有- skipg -grant-tables和-skip-network选项)。

Source Mysql documentation and personal experience:

来源Mysql文档和个人经验:

http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html

#7

I worked on Access Denied for User 'root'@'localhost' (using password: YES) for several hours, I have found following solution,

我为用户“root”@“localhost”(使用密码:YES)拒绝了几个小时的访问权限，我找到了以下解决方案，

The answer to this problem was that in the my.cnf located within
/etc/mysql/my.cnf

the line was either 
bind-address = 127.0.0.1 
            (or)
bind-address = localhost
            (or)
bind-address = 0.0.0.0

I should prefer that 127.0.0.1

I should also prefer 0.0.0.0, it is more flexible 
because which will allow all connections

#8

for the above problem ur password in the system should matches with the password u have passed in the program because when u run the program it checks system's password as u have given root as a user so gives u an error and at the same time the record is not deleted from the database.

上面的问题你的密码与密码u系统应该匹配程序中过去了,因为当你运行程序它检查系统的密码u给根作为一个用户,所以给了一个错误,同时记录不是从数据库中删除。

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
class Delete
{
    public static void main(String []k)
    {
        String url="jdbc:mysql://localhost:3306/student";

        String user="root";
        String pass="jacob234";
        try
        {
            Connection myConnection=DriverManager.getConnection(url,user,pass);
            Statement myStatement=myConnection.createStatement();
            String deleteQuery="delete from students where id=2";
            myStatement.executeUpdate(deleteQuery);
            System.out.println("delete completed");
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
    }
}

Keep ur system password as jacob234 and then run the code.

保持您的系统密码为jacob234，然后运行代码。

#9

Try out the following steps to overcome this issue:

尝试以下步骤来解决这个问题:

Open terminal / command prompt and navigate to the bin folder of the MySQL installation folder. Then run the command mysqld --console.
打开终端/命令提示符，并导航到MySQL安装文件夹的bin文件夹。然后运行命令mysqld——控制台。
If you can see that line 171010 14:58:22 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled, after executing the above command from the command prompt.
如果您能看到这条线171010,14:58:22[注释]——securefile -priv被设置为NULL。从命令提示符执行上述命令后，将禁用与导入和导出数据相关的操作。
Then you need to check that the mysqld is either blocked by the Windows Firewall or another program.
然后需要检查mysqld是否被Windows防火墙或其他程序阻塞。
If it's blocked by Windows Firewall then need to unblock from it and save settings.
如果它被Windows防火墙阻止，那么需要打开它并保存设置。
To unblock the mysqld or mysql application, follow the below steps:
1. Go to command prompt and type wf.msc to open the firewall settings.
2. 转到命令提示符和类型wf。msc打开防火墙设置。
3. Click on Allow an app or feature through Windows Firewall.
4. 点击允许应用程序或功能通过Windows防火墙。
5. Check the mysqld or mysqld instances are available in the list and check the checkbox for the domain, public and private and save the settings.
6. 检查列表中的mysqld或mysqld实例，并检查域、公共和私有的复选框，并保存设置。
要打开mysqld或mysql应用程序，请遵循以下步骤:使用命令提示符和类型wf。msc打开防火墙设置。点击允许应用程序或功能通过Windows防火墙。检查列表中的mysqld或mysqld实例，并检查域、公共和私有的复选框，并保存设置。
Return to the bin folder and try the command from step 1 again.
返回bin文件夹并再次尝试第1步中的命令。
It should work fine and not show any errors.
它应该运行良好，不会出现任何错误。

It should be possible to run the MySQL console without any problems now!

现在应该可以运行MySQL控制台了!

#10

I resolved the same issue using next sql and restarting MySQL server:

我使用下一个sql和重新启动MySQL服务器解决了相同的问题:

update mysql.user set 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'
where user='root';

#11

I don't think you have to escape the --init-file parameter:

我认为你不必逃避——init文件参数:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.6\\my.ini" --init-file=C:\\mysql-init.txt

“C:\ Program Files \ MySQL \ 5.6 \ bin \ mysqld MySQL服务器。exe -默认文件="C:\\程序文件\\MySQL服务器5.6\\my。ini”——init文件= C:\ \ mysql-init.txt

Should be:

应该是:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.6\\my.ini" --init-file=C:\mysql-init.txt

“C:\ Program Files \ MySQL \ 5.6 \ bin \ mysqld MySQL服务器。exe -默认文件="C:\\程序文件\\MySQL服务器5.6\\my。ini”——init文件= C:\ mysql-init.txt

#12

I resolved the same issue by running Workbench as administrator.

我以管理员的身份运行工作台解决了相同的问题。

...I guess it's because of restrictions on company computers, in my case...

…我想是因为对公司电脑的限制，在我看来……

#1