无法在 127.0.0.1:3306 连接到 mysql，用户 'root'@'localhost' 的用户 root 访问被拒绝（使用密码：YES）

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

提问by cpl

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)

无法在 127.0.0.1:3306 连接到 mysql，用户 'root'@'localhost' 的用户 root 访问被拒绝（使用密码：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，但错误仍未解决。

回答by ydoow

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 -P 3306

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

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

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.

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

回答by Vladimir Voznyi

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. 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.
3. 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.

1. 打开 Linux 终端
2. 输入“ sudo mysql”，
   您将看到“mysql >”，这意味着您已以“root”Unix 用户身份连接到 mysql，并且可以键入 SQL 查询。
3. 输入 SQL 查询以更改登录方式：
   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';
   其中 'mysql_native_password' 表示 - 关闭 auth_socket 插件。

回答by Diogo Quaresma

Try this, it worked for me!

试试这个，它对我有用！

sudo mysql -u root -p

Inside the terminal enter:

在终端内输入：

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

Then in the MySQL Workbench edit the connection and change the root name to admin.

然后在 MySQL Workbench 中编辑连接并将根名称更改为 admin。

回答by Eric Korolev

In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket pluginby default rather than with a password. This allows for some greater security and usability in many cases, but it can also complicate things when you need to allow an external program (e.g., phpMyAdmin) to access the user.

If you prefer to use a password when connecting to MySQL as root, you will need to switch its authentication method from auth_socketto mysql_native_password. source

在运行 MySQL 5.7（及更高版本）的 Ubuntu 系统中，root MySQL 用户默认设置为使用auth_socket 插件而不是密码进行身份验证。在许多情况下，这允许更高的安全性和可用性，但是当您需要允许外部程序（例如，phpMyAdmin）访问用户时，它也会使事情复杂化。

如果您更喜欢在以root身份连接到 MySQL 时使用密码，则需要将其身份验证方法从auth_socket 切换到mysql_native_password。 来源

Open up the MySQL prompt from your terminal:

从终端打开 MySQL 提示符：

$ sudo mysql

Next, check which authentication method each of your MySQL user accounts use with the following command:

接下来，使用以下命令检查每个 MySQL 用户帐户使用的身份验证方法：

mysql > SELECT user,authentication_string,plugin,host FROM mysql.user;

You will see that the root user does in fact authenticate using the auth_socket plugin. To configure the root account to authenticate with a password, run the following ALTER USERcommand. Be sure to change password to a strong password of your choosing:

您将看到 root 用户实际上使用 auth_socket 插件进行身份验证。要将 root 帐户配置为使用密码进行身份验证，请运行以下ALTER USER命令。请务必将密码更改为您选择的强密码：

mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Then, run FLUSH PRIVILEGESwhich tells the server to reload the grant tables and put your new changes into effect:

然后，运行FLUSH PRIVILEGES它告诉服务器重新加载授权表并使您的新更改生效：

mysql > FLUSH PRIVILEGES;

Check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:

再次检查每个用户使用的身份验证方法，以确认 root 不再使用auth_socket 插件进行身份验证：

mysql > SELECT user,authentication_string,plugin,host FROM mysql.user;

You will see in output that the root MySQL user now authenticates using a password.

您将在输出中看到 root MySQL 用户现在使用密码进行身份验证。

回答by CriminalDuck

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.

我现在刚刚遇到这个问题，通过一些尝试，我发现访问服务 >> 选择 MySQLxx 服务，然后右键单击并点击开始，这解决了我的问题，一切正常，无需丢失数据。

回答by J Seaton

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/

不知道这是怎么发生的，但我遇到了同样的问题。我不得不重置我的 root 密码：http: //www.jovicailic.org/2012/04/reset-forgotten-mysql-root-password-under-windows/

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，这对我来说是一个胜利

回答by Renjith Bhattathiripad

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.

尝试使用默认端口号连接到 localhost。

回答by Supriyo Banerjee

I was facing the same problem in windows.

我在 Windows 中遇到了同样的问题。

1. Goto services in task manager
2. start the service called MySQL80
3. Restart workbench

1. 任务管理器中的转到服务
2. 启动名为 MySQL80 的服务
3. 重启工作台

回答by Venkat Kallem

Try this,

尝试这个，

I hope this will help You

我希望这能帮到您

1. Uninstalled everything including installer from it's own uninstall option.
2. Un-installed all the programs that were in any way related to mysql
3. Deleted the mysql folder from C:\Program Files
4. Deleted the mysql folder from C:\ProgramData
5. Cleared my registry and then re-installed the program, but all in vain.

1. 从它自己的卸载选项中卸载了包括安装程序在内的所有内容。
2. 卸载所有与 mysql 相关的程序
3. 从 C:\Program Files 中删除了 mysql 文件夹
4. 从 C:\ProgramData 删除 mysql 文件夹
5. 清除了我的注册表，然后重新安装了程序，但都是徒劳的。

How to repeat: Here is what I did:

如何重复：这是我所做的：

1. Installed it with mysql-installer-web-community-5.6.23.0.msi
2. For my purposes I selected "Custom" install with MySQL servers 5.6.22 - X64 and MySQL Connectors -> Connector ODBC/5.3.4 - 64
3. Next -> Execute -(ready to configure) -> Next
4. Config type: Development machine, all defaults (TCP/IP, Port: 3306, Open firewall)
5. Typed in MySQL Root Password
6. MySQL user Accounts -> Add user (username, password, OK) -> Next
7. Configure MySQL Server as a Windows Service (CHECKED)
8. Windows Service Name: MySQL56 (default)
9. Start the MySQL Serve at System Startup
10. THIS IS CRUCIAL (BUG IS HERE): Run Service as... Standard System Account (CHECKED - but it will be disregarded and that is BUG)
11. 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)

1. 用 mysql-installer-web-community-5.6.23.0.msi 安装它
2. 出于我的目的，我选择了“自定义”安装 MySQL 服务器 5.6.22 - X64 和 MySQL 连接器 -> 连接器 ODBC/5.3.4 - 64
3. 下一步 -> 执行 -（准备配置） -> 下一步
4. 配置类型：开发机，全部默认（TCP/IP，端口：3306，打开防火墙）
5. 输入 MySQL Root 密码
6. MySQL 用户帐户 -> 添加用户（用户名、密码、确定） -> 下一步
7. 将 MySQL 服务器配置为 Windows 服务（已检查）
8. Windows 服务名称：MySQL56（默认）
9. 在系统启动时启动 MySQL 服务
10. 这很重要（错误在这里）：将服务作为...标准系统帐户运行（已检查 - 但它将被忽略，这是错误）
11. 下一步 -> 执行，安装挂在启动服务器上，所以等待一段时间超时（或者不，你的选择）。当对话框（可能被其他窗口覆盖）弹出消息“MySQL 服务器的配置时间比预期的要长......，在这里单击确定（以便等待更长时间）

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，右键单击它 -> 属性 -> 选择登录选项卡，这是错误 -> 虽然选择了本地系统帐户，不知何故“此帐户：网络服务（带有一些密码）被选中 -> 选择登录为：本地系统帐户，允许服务与桌面交互 -> 应用 -> 返回常规选项卡

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.

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

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

13. 在常规选项卡上单击“开始”按钮以启动服务，就在这里！服务开始了！单击确定关闭 MySQL56 属性对话框。关闭服务对话框。关闭管理工具。关闭控制面板。

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

15. 确认完成 -> 下一步 -> 完成 就是这样，MySQL 快乐 :)

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

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

回答by sarah s

After viewing so many solution answers, here is my summary which works for me. I only installed workbench 6.2 at first, when connecting to localhost it failed.

在查看了这么多解决方案的答案后，这里是我的总结，它对我有用。我一开始只安装了工作台 6.2，当连接到本地主机时它失败了。

step1: check if you have installed mysql server. If not, download and install.

step1：检查是否安装了mysql服务器。如果没有，请下载并安装。

step2: the mysql server configuration recommend strong password, ignore it, choose the legacy password.

step2：mysql服务器配置推荐强密码，无视，选择legacy密码。

step3: start mysql server (windows system: services-->mysql-->start)

step3：启动mysql服务器（windows系统：services-->mysql-->start）

step4: open workbench and create local connection.

step4：打开工作台，创建本地连接。