无法连接到数据库服务器（mysql 工作台）

Cannot connect to Database server (mysql workbench)

提问by user948950

Could you help me solve this problem ?

你能帮我解决这个问题吗？

When I try to click "query database" under database menu in Mysql workbench. it gives me an error:

当我尝试在 Mysql 工作台中的数据库菜单下单击“查询数据库”时。它给了我一个错误：

Cannot Connect to Database Server

Your connection attempt failed for user 'root' from your host to server at 
127.0.0.1:3306:Can't connect to mysql server on '127.0.0.1'(10061)

Please:

 1. Check that mysql is running on server 127.0.0.1
 2. Check that mysql is running on port 3306 (note: 3306 is the default, but this can 
    be changed)
 3. Check the root has rights to connect to 127.0.0.1 from your address (mysql rights 
    define what clients can connect to the server and from which machines) 
 4. Make sure you are both providing a password if needed and using the correct 
    password for 127.0.0.1 connecting from the host address you're connecting from

回答by Ayan

The issue is likely due to socket authentication being enabled for the root user by default when no password is set, during the upgrade to ubuntu 16.04.

该问题可能是由于在升级到 ubuntu 16.04 期间，在未设置密码时默认为 root 用户启用了套接字身份验证。

The solution is to revert back to native password authentication. You can do this by logging in to MySQL using socket authentication by doing:

解决方案是恢复到本机密码身份验证。您可以通过使用套接字身份验证登录 MySQL 来执行此操作：

sudo mysql -u root

Once logged in:

登录后：

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

which will revert back to the native (old default) password authentication.

这将恢复到本机（旧默认）密码身份验证。

Now use passwordas the password whenever required by MySQL.

现在，只要 MySQL 需要，就使用password作为密码。

回答by Colin

Try opening services.mscfrom the start menu search box and try manually starting the MySQL service or directly write services.mscin Runbox

尝试services.msc从开始菜单搜索框打开，尝试手动启动MySQL服务或直接写services.msc在Run框中

回答by AJ Richardson

It looks like there are a lot of causes of this error.

看起来这个错误有很多原因。

My Cause / Solution

我的原因/解决方案

In my case, the cause was that my server was configured to only accept connections from localhost. I fixed it by following this article: How Do I Enable Remote Access To MySQL Database Server?. My my.cnffile had no skip-networkingline, so I just changed the line

就我而言，原因是我的服务器配置为仅接受来自本地主机的连接。我按照这篇文章修复了它：如何启用对 MySQL 数据库服务器的远程访问？. 我的my.cnf文件没有skip-networking行，所以我只是改变了行

bind-address = 127.0.0.1

to

到

bind-address = 0.0.0.0

This allows connections from any IP, not just 127.0.0.1.

这允许来自任何 IP 的连接，而不仅仅是 127.0.0.1。

Then, I created a MySql user that could connect from my client machine by running the following terminal commands:

然后，我创建了一个 MySql 用户，该用户可以通过运行以下终端命令从我的客户端计算机连接：

# mysql -u root -p
mysql> CREATE USER 'username'@'1.2.3.4' IDENTIFIED BY 'password';
    -> GRANT ALL PRIVILEGES ON *.* TO 'username'@'1.2.3.4' WITH GRANT OPTION;
    -> \q

where 1.2.3.4is the IP of the client you are trying to connect from. If you really have trouble, you can use '%'instead of '1.2.3.4'to allow the user to connect from any IP.

1.2.3.4您尝试连接的客户端的 IP在哪里。如果你真的有问题，你可以使用'%'代替'1.2.3.4'来允许用户从任何 IP 连接。

Other Causes

其他原因

For a fairly extensive list, see Causes of Access-Denied Errors.

有关相当广泛的列表，请参阅拒绝访问错误的原因。

回答by Sergio

Did you try to determine if this is a problem with Workbench or a general connection problem? Try this:

您是否尝试确定这是 Workbench 的问题还是一般的连接问题？尝试这个：

1. Open a terminal
2. Type mysql -u root -p -h 127.0.0.1 -P 3306
3. If you can connect successfully you will see a mysql prompt after you type your password (type quitand Enter there to exit).

1. 打开终端
2. 类型 mysql -u root -p -h 127.0.0.1 -P 3306
3. 如果您可以成功连接，您将在输入密码后看到一个 mysql 提示（输入quit并回车退出）。

Report back how this worked.

报告这是如何工作的。

回答by almawhoob

I had a similar issue on Mac OSand I was able to fix it this way:

我在Mac OS上遇到了类似的问题，我能够通过这种方式修复它：

From the terminal, run:

从终端运行：

mysql -u root -p -h 127.0.0.1 -P 3306

Then, I was asked to enter the password. I just pressed entersince no password was setup.

然后，我被要求输入密码。由于没有设置密码，我只是按了Enter键。

I got a message as follows:

我收到一条消息如下：

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 181. Server version: 8.0.11 Homebrew.

欢迎使用 MySQL 监视器。命令以 ; 结尾 或\g。您的 MySQL 连接 ID 是 181。服务器版本：8.0.11 Homebrew。

If you succeeded to log into mysql>, run the following command:

如果您成功登录到 mysql>，请运行以下命令：

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

You should get a message like this:

你应该得到这样的消息：

Query OK, 0 rows affected (0.19 sec)

查询正常，0 行受影响（0.19 秒）

Now, your password is "password" and your username is "root".

现在，您的密码是“ password”，您的用户名是“ root”。

Happy coding :)

快乐编码:)

回答by Athira Ramachandran

Run the ALTER USER command. Be sure to change password to a strong password of your choosing.

运行 ALTER USER 命令。请务必将密码更改为您选择的强密码。

1. sudo mysql# Login to mysql`

2. Run the below command

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

1. sudo mysql#登录mysql`

2. 运行以下命令

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

Now you can access it by using the new password.

现在您可以使用新密码访问它。

Ref : https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-18-04

参考：https: //www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-18-04

回答by Steven Ryssaert

I had to start Workbench as Administrator. Apparently it didn't have the required permissions to connect to my localhost database server process.

我必须以管理员身份启动 Workbench。显然它没有连接到我的本地主机数据库服务器进程所需的权限。

Right-click the Workbench shortcut and select Run as Administrator. In the shortcut's Properties window, you can click on "Advanced" and tick the box next to "Run as Administrator" to always run the Workbench with Admin privileges.

右键单击 Workbench 快捷方式并选择Run as Administrator。在快捷方式的属性窗口中，您可以单击“高级”并勾选“以管理员身份运行”旁边的框，以始终以管理员权限运行工作台。

回答by Sanjaya

The error occur because the mysql server is not starting on your computer. You should start it manually. Do following steps:

发生错误是因为 mysql 服务器未在您的计算机上启动。您应该手动启动它。执行以下步骤：

1. Download and install wamp server according to your bit version(32bit or 64bit) in your computer(http://wampserver-64bit.en.softonic.com/) this link allows you to download wamp server for 64bit.

2. As soon as you install it you can double click and run it..(you can see a icon in the right hand of the taskbar.It may be hidden .so you can click the arrow which show you the hide apps runing).So click the icon and go to Mysql

3. Then go to Serviceand there you can find Start/Resume Servicesclick on it..

4. And now it is done.Open mysql workbench and see.It will work..

1. 根据您计算机中的位版本（32 位或 64 位）下载并安装 wamp 服务器（http://wampserver-64bit.en.softonic.com/）此链接允许您下载 64 位的 wamp 服务器。

2. 安装后，您可以双击并运行它..（您可以在任务栏的右侧看到一个图标。它可能是隐藏的。所以您可以单击显示隐藏应用程序正在运行的箭头）。所以单击图标并转到Mysql

3. 然后去服务，在那里你可以找到开始/恢复服务点击它..

4. 现在完成了。打开 mysql 工作台看看。它会工作..

回答by KuBand12

I struggled with this problem for awhile and did several reinstalls of MySQL before discovering this.

我在这个问题上挣扎了一段时间，并在发现这个问题之前重新安装了几次 MySQL。

I know that MySQL server was running OK because I could access all my DB's using the command line.

我知道 MySQL 服务器运行正常，因为我可以使用命令行访问我的所有数据库。

Hope this works for you.

希望这对你有用。

In MySQL Workbench (5.2.47 CE)

在MySQL Workbench (5.2.47 CE) 中

click Mange Server Instances(bottom right corner)

单击管理服务器实例（右下角）

click Connection

点击连接

in the Connectionbox select:

在连接框中选择：

Local Instance ($ServerName) - [email protected]:3306 '<'Standard(TCP/IP)>

本地实例 ($ServerName) - [email protected]:3306 '<'标准（TCP/IP）>

click Edit Selected...

单击编辑选定...

under Parameters, Hostnamechange localhost or 127.0.0.1to your NetBIOS name

在参数下，主机名将localhost 或 127.0.0.1更改为您的 NetBIOS 名称

click Test Connection

点击测试连接

If this works for you, great. If not change the hostname back to what it was.

如果这对你有用，那就太好了。如果没有，请将主机名改回原来的名称。

回答by alex

Forr me reason was that I tried to use newest MySQL Workbench 8.x to connect to MySQL Server 5.1 (both running on Windows Server 2012).

对于我来说，原因是我尝试使用最新的 MySQL Workbench 8.x 连接到 MySQL Server 5.1（均在 Windows Server 2012 上运行）。

When I uninstalled MySQL Workbench 8.x and installed MySQL Workbench 6.3.10 it successfully connected to localhostdatabase

当我卸载 MySQL Workbench 8.x 并安装 MySQL Workbench 6.3.10 时，它成功连接到localhost数据库