未能连接到mysql(127.0.0.1:3306),用户root权限被拒绝,用户root用户“root”@“localhost”(使用密码:YES)

时间:2021-10-11 02:50:51

I've been trying to create a new database connection on workbench. However, every time I test a connection there is an error message that says

我一直试图在工作台创建一个新的数据库连接。然而,每当我测试一个连接时,都会出现一个错误消息。

Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user 'root'@'localhost'(using password:YES)

未能连接到mysql(127.0.0.1:3306),用户root权限被拒绝,用户root用户“root”@“localhost”(使用密码:YES)

I don't know what's the cause of this error. I have tried uninstalling and re-installing my workbench 6.1 and mysql server 5.6 but the error is still unsolved.

我不知道这个错误的原因。我已经尝试卸载和重新安装我的工作台6.1和mysql服务器5.6,但是错误仍然没有解决。

8 个解决方案

#1


25  

First check if it's a workbench or connection problem.

首先检查它是否是工作台或连接问题。

1) In your windows run 'cmd' to open a terminal

1)在你的windows运行'cmd'打开终端。

Try both

试试这两个

2a) mysql -u root -p -h 127.0.0.1 -P 3306

2a) mysql -u root -p -h 127.0.0.1 - p3306。

2b) mysql -u root -p -h > localhost -P 3306

2b) mysql -u root -p -h > localhost - p3306。

3) If the connection is good you will get a password prompt, see if you can connect with correct password.

3)如果连接很好,你会得到一个密码提示,看看你是否能连接正确的密码。

If your connection is denied, just grant the permission

如果您的连接被拒绝,请允许。

mysql >GRANT ALL ON [DatabaseName].* TO 'root'@'127.0.0.1' IDENTIFIED BY '[PASSWORD]';

If your connection is accepted here, it's like a workbench configuration problem.

如果您的连接在这里被接受,它就像一个工作台配置问题。

#2


3  

no idea how this happened, but i had the same problem. I had to reset my root password: http://www.jovicailic.org/2012/04/reset-forgotten-mysql-root-password-under-windows/

不知道这是怎么回事,但我有同样的问题。我必须重新设置我的根密码:http://www.jovicailic.org/2012/04/reset-forgotten-mysql-root- passwordwindows/。

But after my databases that i had previously were either dropped or the new connection did not connect to them. Either way i couldn't access them via workbench. But i could use MySQL again, which was a win for me

但是,在我之前的数据库要么被删除,要么新的连接没有连接到它们。无论哪种方式,我都无法通过工作台访问它们。但我可以再次使用MySQL,这对我来说是一场胜利。

#3


3  

i just encountered this problem now and with some tries i figured out that visiting services >> select MySQLxx service , then right click and hit start , that solved my problem and everything is working without the need to loss data, Just for those who will come here to this question topic .

我刚遇到这个问题现在和一些尝试我发现访问服务> >选择MySQLxx服务,然后右击,点击开始,这解决了我的问题,一切都不需要工作损失的数据,只是为了那些来这里这个问题的话题。

#4


2  

You should install the mysql server which hosts in your localhost. Then install the workbench. Still if you are getting the error GOTO:

您应该安装在本地主机中主机的mysql服务器。然后安装工作台。如果你得到错误,GOTO:

(In MAC:)

(在MAC:)

System preferences>Mysql(Will be listed at the bottom,else server is not installed in your machine).

系统首选项>Mysql(将在底部列出,否则服务器不会安装在您的机器上)。

Check server status and start the Mysql server.

检查服务器状态并启动Mysql服务器。

Try connecting to localhost with default port number.

尝试使用默认端口号连接到本地主机。

#5


2  

I had the same problem.
I've installed fresh mysql at Ubuntu but I left mysql password empty, and as a result I couldn't connect to mysql in any way.
Lately I've revealed that there is a table of users where are names, hosts, passwords and some plugins. So for my user root@localhost mysql while installing assigned a plugin called auth_socket, which let Unix user "root" log in as a mysql user "root" without password, but don't allow login as another Unix user. So to fix that you should turn off this plugin and set usual authentication:

我遇到了同样的问题。我在Ubuntu上安装了新的mysql,但是我的mysql密码是空的,因此我无法以任何方式连接到mysql。最近我发现有一个用户表,其中有姓名、主机、密码和一些插件。因此,对于我的用户root@localhost mysql,安装了一个名为auth_socket的插件,它允许Unix用户“root”登录作为mysql用户的“root”,而不使用密码,但是不允许作为另一个Unix用户登录。为了解决这个问题,你应该关闭这个插件并设置常规的认证:

  1. open Linux terminal
  2. 打开Linux终端
  3. enter "sudo mysql"
    you will see "mysql >" which means you've connected to mysql as a 'root' Unix user and you can type SQL queries.
  4. 输入“sudo mysql”,您将看到“mysql >”,这意味着您将mysql连接为“root”Unix用户,您可以输入SQL查询。
  5. enter SQL query to change a way how you will log in:
    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';
    where 'mysql_native_password' means - to turn off auth_socket plugin.
  6. 输入SQL查询以更改您将登录的方式:通过“your_new_password”将用户“根”@“localhost”标识为mysql_native_password;其中“mysql_native_password”的意思是——关闭auth_socket插件。

#6


1  

Try this,

试试这个,

I hope this will help You

我希望这对你有帮助。

  1. Uninstalled everything including installer from it's own uninstall option.
  2. 卸载一切,包括安装程序本身的卸载选项。
  3. Un-installed all the programs that were in any way related to mysql
  4. 取消了所有与mysql相关的程序。
  5. Deleted the mysql folder from C:\Program Files
  6. 删除从C:\Program Files mysql文件夹
  7. Deleted the mysql folder from C:\ProgramData
  8. 从C:\程序数据中删除mysql文件夹。
  9. Cleared my registry and then re-installed the program, but all in vain.
  10. 清空我的注册表,然后重新安装程序,但都是徒劳的。

How to repeat: Here is what I did:

如何重复:这是我做的:

  1. Installed it with mysql-installer-web-community-5.6.23.0.msi
  2. 安装用mysql-installer-web-community-5.6.23.0.msi
  3. For my purposes I selected "Custom" install with MySQL servers 5.6.22 - X64 and MySQL Connectors -> Connector ODBC/5.3.4 - 64
  4. 为了我的目的,我选择了“自定义”安装MySQL服务器5.6.22 - X64和MySQL连接器->连接器ODBC/5.3.4 - 64。
  5. Next -> Execute -(ready to configure) -> Next
  6. 下一个->执行-(准备好配置)->下一个。
  7. Config type: Development machine, all defaults (TCP/IP, Port: 3306, Open firewall)
  8. 配置类型:开发机器,所有默认设置(TCP/IP,端口:3306,打开防火墙)
  9. Typed in MySQL Root Password
  10. 输入MySQL根密码。
  11. MySQL user Accounts -> Add user (username, password, OK) -> Next
  12. MySQL用户帐户->添加用户(用户名,密码,OK) ->。
  13. Configure MySQL Server as a Windows Service (CHECKED)
  14. 将MySQL服务器配置为Windows服务(检查)
  15. Windows Service Name: MySQL56 (default)
  16. Windows服务名称:MySQL56(默认)
  17. Start the MySQL Serve at System Startup
  18. 在系统启动时启动MySQL服务。
  19. THIS IS CRUCIAL (BUG IS HERE): Run Service as... Standard System Account (CHECKED - but it will be disregarded and that is BUG)
  20. 这是至关重要的(BUG在这里):运行服务为…标准系统帐户(检查-但它将被忽略,这是错误)
  21. Next -> Execute, Installation hangs on Starting server, so wait for a while to time out (or don't, your choice). When Dialog (might be covered with other windows) popup with message "Configuration of MySQL Server is taking longer than expected..., here click OK (so to wait longer)
  22. Next ->执行,安装挂在启动服务器上,所以等待一段时间(或不,您的选择)。当对话框(可能被其他窗口覆盖)弹出消息“MySQL服务器的配置比预期的要长……”,点击OK(等待更久)

Suggested fix: 12. Meanwhile go to Start -> Control Panel -> Administrative Tools -> Services -> find MySQL56, right click on it -> Properties -> select Log On Tab AND HERE IS BUG -> Although Local System Account was selected, Somehow "This account: Network Service (with some password) was selected -> Select Log on as: Local System Account, Allow service to interact with desktop -> Apply -> Go back on general tab

建议解决办法:12。同时去开始- >控制面板- >管理工具- >服务- >找到MySQL56,右键点击- >属性- >选择登录选项卡,这是缺陷- >虽然本地系统帐户被选中,在某种程度上”这个帐户:网络服务(有密码)选择- >选择登录:本地系统帐户,允许服务与桌面- >应用- >返回general选项卡

  1. On general tab click on "Start" button to start service and here it is! Service is started! Click on OK to close MySQL56 Properties dialog. Close Services dialog. Close Administrative tools. Close control panel.

    在一般选项卡点击“开始”按钮开始服务,在这里它是!服务启动!单击OK关闭MySQL56属性对话框。关闭服务对话框。关闭管理工具。关闭控制面板。

  2. And by that time (while you were closing those dialogs) when you look at MySQl Installer Dialog all steps are finished and checked: Starting Server, Applying security... Creating user accounts.. Updating Start menu link

    到那时(当您关闭这些对话框时)当您查看MySQl安装程序对话框时,所有步骤都已完成并检查:启动服务器、应用安全性……创建用户帐户。更新开始菜单链接

  3. Confirm with Finish -> Next -> Finish That's it, happy MySQL-ing :)

    确认完成-> Next ->完成了,开心的MySQL-ing:)

Source: https://bugs.mysql.com/bug.php?id=76476

来源:https://bugs.mysql.com/bug.php?id=76476

#7


0  

I know this is old but I have come across this issue as well but found a fix for this that worked for me:

我知道这很老,但我也遇到过这个问题,但我找到了一个对我有效的方法:

  1. go to "Services"
  2. 去“服务”
  3. under "Name" find your username
  4. 在“名字”下面找到你的用户名。
  5. right click and select "Start"
  6. 右键单击并选择“Start”

Go to your MySQL workbench and select "Startup / Shutdown" under "INSTANCE" and you should be good to go. Hope this helps anyone that comes across this.

到你的MySQL工作台,在“实例”下选择“启动/关闭”,你应该很好。希望这能帮助遇到这个问题的人。

#8


-2  

Here was my solution:

这是我的解决方案:

  • press Ctrl + Alt + Del
  • 按Ctrl + Alt + Del。
  • Task Manager
  • 任务管理器
  • Select the Services Tab
  • 选择服务选项卡
  • Under name, right click on "MySql" and select Start
  • 在名称下,右键单击“MySql”并选择Start。

#1


25  

First check if it's a workbench or connection problem.

首先检查它是否是工作台或连接问题。

1) In your windows run 'cmd' to open a terminal

1)在你的windows运行'cmd'打开终端。

Try both

试试这两个

2a) mysql -u root -p -h 127.0.0.1 -P 3306

2a) mysql -u root -p -h 127.0.0.1 - p3306。

2b) mysql -u root -p -h > localhost -P 3306

2b) mysql -u root -p -h > localhost - p3306。

3) If the connection is good you will get a password prompt, see if you can connect with correct password.

3)如果连接很好,你会得到一个密码提示,看看你是否能连接正确的密码。

If your connection is denied, just grant the permission

如果您的连接被拒绝,请允许。

mysql >GRANT ALL ON [DatabaseName].* TO 'root'@'127.0.0.1' IDENTIFIED BY '[PASSWORD]';

If your connection is accepted here, it's like a workbench configuration problem.

如果您的连接在这里被接受,它就像一个工作台配置问题。

#2


3  

no idea how this happened, but i had the same problem. I had to reset my root password: http://www.jovicailic.org/2012/04/reset-forgotten-mysql-root-password-under-windows/

不知道这是怎么回事,但我有同样的问题。我必须重新设置我的根密码:http://www.jovicailic.org/2012/04/reset-forgotten-mysql-root- passwordwindows/。

But after my databases that i had previously were either dropped or the new connection did not connect to them. Either way i couldn't access them via workbench. But i could use MySQL again, which was a win for me

但是,在我之前的数据库要么被删除,要么新的连接没有连接到它们。无论哪种方式,我都无法通过工作台访问它们。但我可以再次使用MySQL,这对我来说是一场胜利。

#3


3  

i just encountered this problem now and with some tries i figured out that visiting services >> select MySQLxx service , then right click and hit start , that solved my problem and everything is working without the need to loss data, Just for those who will come here to this question topic .

我刚遇到这个问题现在和一些尝试我发现访问服务> >选择MySQLxx服务,然后右击,点击开始,这解决了我的问题,一切都不需要工作损失的数据,只是为了那些来这里这个问题的话题。

#4


2  

You should install the mysql server which hosts in your localhost. Then install the workbench. Still if you are getting the error GOTO:

您应该安装在本地主机中主机的mysql服务器。然后安装工作台。如果你得到错误,GOTO:

(In MAC:)

(在MAC:)

System preferences>Mysql(Will be listed at the bottom,else server is not installed in your machine).

系统首选项>Mysql(将在底部列出,否则服务器不会安装在您的机器上)。

Check server status and start the Mysql server.

检查服务器状态并启动Mysql服务器。

Try connecting to localhost with default port number.

尝试使用默认端口号连接到本地主机。

#5


2  

I had the same problem.
I've installed fresh mysql at Ubuntu but I left mysql password empty, and as a result I couldn't connect to mysql in any way.
Lately I've revealed that there is a table of users where are names, hosts, passwords and some plugins. So for my user root@localhost mysql while installing assigned a plugin called auth_socket, which let Unix user "root" log in as a mysql user "root" without password, but don't allow login as another Unix user. So to fix that you should turn off this plugin and set usual authentication:

我遇到了同样的问题。我在Ubuntu上安装了新的mysql,但是我的mysql密码是空的,因此我无法以任何方式连接到mysql。最近我发现有一个用户表,其中有姓名、主机、密码和一些插件。因此,对于我的用户root@localhost mysql,安装了一个名为auth_socket的插件,它允许Unix用户“root”登录作为mysql用户的“root”,而不使用密码,但是不允许作为另一个Unix用户登录。为了解决这个问题,你应该关闭这个插件并设置常规的认证:

  1. open Linux terminal
  2. 打开Linux终端
  3. enter "sudo mysql"
    you will see "mysql >" which means you've connected to mysql as a 'root' Unix user and you can type SQL queries.
  4. 输入“sudo mysql”,您将看到“mysql >”,这意味着您将mysql连接为“root”Unix用户,您可以输入SQL查询。
  5. enter SQL query to change a way how you will log in:
    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';
    where 'mysql_native_password' means - to turn off auth_socket plugin.
  6. 输入SQL查询以更改您将登录的方式:通过“your_new_password”将用户“根”@“localhost”标识为mysql_native_password;其中“mysql_native_password”的意思是——关闭auth_socket插件。

#6


1  

Try this,

试试这个,

I hope this will help You

我希望这对你有帮助。

  1. Uninstalled everything including installer from it's own uninstall option.
  2. 卸载一切,包括安装程序本身的卸载选项。
  3. Un-installed all the programs that were in any way related to mysql
  4. 取消了所有与mysql相关的程序。
  5. Deleted the mysql folder from C:\Program Files
  6. 删除从C:\Program Files mysql文件夹
  7. Deleted the mysql folder from C:\ProgramData
  8. 从C:\程序数据中删除mysql文件夹。
  9. Cleared my registry and then re-installed the program, but all in vain.
  10. 清空我的注册表,然后重新安装程序,但都是徒劳的。

How to repeat: Here is what I did:

如何重复:这是我做的:

  1. Installed it with mysql-installer-web-community-5.6.23.0.msi
  2. 安装用mysql-installer-web-community-5.6.23.0.msi
  3. For my purposes I selected "Custom" install with MySQL servers 5.6.22 - X64 and MySQL Connectors -> Connector ODBC/5.3.4 - 64
  4. 为了我的目的,我选择了“自定义”安装MySQL服务器5.6.22 - X64和MySQL连接器->连接器ODBC/5.3.4 - 64。
  5. Next -> Execute -(ready to configure) -> Next
  6. 下一个->执行-(准备好配置)->下一个。
  7. Config type: Development machine, all defaults (TCP/IP, Port: 3306, Open firewall)
  8. 配置类型:开发机器,所有默认设置(TCP/IP,端口:3306,打开防火墙)
  9. Typed in MySQL Root Password
  10. 输入MySQL根密码。
  11. MySQL user Accounts -> Add user (username, password, OK) -> Next
  12. MySQL用户帐户->添加用户(用户名,密码,OK) ->。
  13. Configure MySQL Server as a Windows Service (CHECKED)
  14. 将MySQL服务器配置为Windows服务(检查)
  15. Windows Service Name: MySQL56 (default)
  16. Windows服务名称:MySQL56(默认)
  17. Start the MySQL Serve at System Startup
  18. 在系统启动时启动MySQL服务。
  19. THIS IS CRUCIAL (BUG IS HERE): Run Service as... Standard System Account (CHECKED - but it will be disregarded and that is BUG)
  20. 这是至关重要的(BUG在这里):运行服务为…标准系统帐户(检查-但它将被忽略,这是错误)
  21. Next -> Execute, Installation hangs on Starting server, so wait for a while to time out (or don't, your choice). When Dialog (might be covered with other windows) popup with message "Configuration of MySQL Server is taking longer than expected..., here click OK (so to wait longer)
  22. Next ->执行,安装挂在启动服务器上,所以等待一段时间(或不,您的选择)。当对话框(可能被其他窗口覆盖)弹出消息“MySQL服务器的配置比预期的要长……”,点击OK(等待更久)

Suggested fix: 12. Meanwhile go to Start -> Control Panel -> Administrative Tools -> Services -> find MySQL56, right click on it -> Properties -> select Log On Tab AND HERE IS BUG -> Although Local System Account was selected, Somehow "This account: Network Service (with some password) was selected -> Select Log on as: Local System Account, Allow service to interact with desktop -> Apply -> Go back on general tab

建议解决办法:12。同时去开始- >控制面板- >管理工具- >服务- >找到MySQL56,右键点击- >属性- >选择登录选项卡,这是缺陷- >虽然本地系统帐户被选中,在某种程度上”这个帐户:网络服务(有密码)选择- >选择登录:本地系统帐户,允许服务与桌面- >应用- >返回general选项卡

  1. On general tab click on "Start" button to start service and here it is! Service is started! Click on OK to close MySQL56 Properties dialog. Close Services dialog. Close Administrative tools. Close control panel.

    在一般选项卡点击“开始”按钮开始服务,在这里它是!服务启动!单击OK关闭MySQL56属性对话框。关闭服务对话框。关闭管理工具。关闭控制面板。

  2. And by that time (while you were closing those dialogs) when you look at MySQl Installer Dialog all steps are finished and checked: Starting Server, Applying security... Creating user accounts.. Updating Start menu link

    到那时(当您关闭这些对话框时)当您查看MySQl安装程序对话框时,所有步骤都已完成并检查:启动服务器、应用安全性……创建用户帐户。更新开始菜单链接

  3. Confirm with Finish -> Next -> Finish That's it, happy MySQL-ing :)

    确认完成-> Next ->完成了,开心的MySQL-ing:)

Source: https://bugs.mysql.com/bug.php?id=76476

来源:https://bugs.mysql.com/bug.php?id=76476

#7


0  

I know this is old but I have come across this issue as well but found a fix for this that worked for me:

我知道这很老,但我也遇到过这个问题,但我找到了一个对我有效的方法:

  1. go to "Services"
  2. 去“服务”
  3. under "Name" find your username
  4. 在“名字”下面找到你的用户名。
  5. right click and select "Start"
  6. 右键单击并选择“Start”

Go to your MySQL workbench and select "Startup / Shutdown" under "INSTANCE" and you should be good to go. Hope this helps anyone that comes across this.

到你的MySQL工作台,在“实例”下选择“启动/关闭”,你应该很好。希望这能帮助遇到这个问题的人。

#8


-2  

Here was my solution:

这是我的解决方案:

  • press Ctrl + Alt + Del
  • 按Ctrl + Alt + Del。
  • Task Manager
  • 任务管理器
  • Select the Services Tab
  • 选择服务选项卡
  • Under name, right click on "MySql" and select Start
  • 在名称下,右键单击“MySql”并选择Start。