启用远程 MySQL 连接：ERROR 1045 (28000): Access denied for user

Enable remote MySQL connection: ERROR 1045 (28000): Access denied for user

提问by Mike Chamberlain

MySQL 5.1.31 running on Windows XP.

MySQL 5.1.31 在 Windows XP 上运行。

From the localMySQL server (192.168.233.142) I can connect as root as follows:

我可以从本地MySQL 服务器 (192.168.233.142) 以 root 身份连接，如下所示：

>mysql --host=192.168.233.142 --user=root --password=redacted

From a remotemachine (192.168.233.163), I can see that the mysql port is open:

从远程机器（192.168.233.163），我可以看到mysql端口是开放的：

# telnet 192.168.233.142 3306
Trying 192.168.233.142...
Connected to 192.168.233.142 (192.168.233.142).

But when trying to connect to mysql from the remotemachine, I receive:

但是当尝试从远程机器连接到 mysql 时，我收到：

# mysql --host=192.168.233.142 --user=root --password=redacted
ERROR 1045 (28000): Access denied for user 'root'@'192.168.233.163' (using password: YES)

I have only 2 entries in mysql.user:

我在 mysql.user 中只有 2 个条目：

Host         User     Password
--------------------------------------
localhost    root     *blahblahblah
%            root     [same as above]

What more do I need to do to enable remote access?

我还需要做什么才能启用远程访问？

EDIT

编辑

As suggested by Paulo below, I tried replacing the mysql.user entry for % with an IP specific entry, so my user table now looks like this:

正如下面 Paulo 所建议的，我尝试将 % 的 mysql.user 条目替换为特定于 IP 的条目，因此我的用户表现在如下所示：

Host             User     Password
------------------------------------------
localhost        root     *blahblahblah
192.168.233.163  root     [same as above]

I then restarted the machine, but the problem persists.

然后我重新启动了机器，但问题仍然存在。

采纳答案by Mike Chamberlain

Paulo's help lead me to the solution. It was a combination of the following:

保罗的帮助引导我找到解决方案。它是以下各项的组合：

• the password contained a dollar sign
• I was trying to connect from a Linux shell

• 密码包含一个美元符号
• 我试图从 Linux shell 连接

The bash shell treats the dollar sign as a special character for expansionto an environment variable, so we need to escape it with a backslash. Incidentally, we don'thave to do this in the case where the dollar sign is the final character of the password.

bash shell 将美元符号视为扩展到环境变量的特殊字符，因此我们需要使用反斜杠对其进行转义。顺便说一下，在美元符号是密码的最后一个字符的情况下，我们不必这样做。

As an example, if your password is "pas$word", from Linux bash we must connect as follows:

例如，如果您的密码是“pas$word”，那么从 Linux bash 我们必须按如下方式连接：

# mysql --host=192.168.233.142 --user=root --password=pas$word

回答by Octavio

You have to put this as root:

你必须把它作为根：

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD' with grant option;

;

;

where IP is the IP you want to allow access, USERNAME is the user you use to connect, and PASSWORD is the relevant password.

其中 IP 是您要允许访问的 IP，USERNAME 是您用来连接的用户，PASSWORD 是相关密码。

If you want to allow access from any IP just put %instead of your IP

如果您想允许从任何 IP 访问，只需输入%而不是您的 IP

and then you only have to put

然后你只需要把

FLUSH PRIVILEGES;

Or restart mysql server and that's it.

或者重启mysql服务器就可以了。

回答by chaps

I was getting the same error after granting remote access until I made this:

授予远程访问权限后，我遇到了同样的错误，直到我这样做：

From /etc/mysql/my.cnf

从 /etc/mysql/my.cnf

In newer versions of mysql the location of the file is /etc/mysql/mysql.conf.d/mysqld.cnf

在较新版本的 mysql 中，文件的位置是 /etc/mysql/mysql.conf.d/mysqld.cnf

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1

(comment this line: bind-address = 127.0.0.1)

（注释此行：bind-address = 127.0.0.1）

Then run service mysql restart.

然后运行service mysql restart。

回答by Ravi Kant

By default in MySQL server remote access is disabled. The process to provide a remote access to user is.

默认情况下，在 MySQL 服务器中禁用远程访问。向用户提供远程访问的过程是。

1. Go to my sql bin folder or add it to PATH
2. Login to root by mysql -uroot -proot(or whatever the root password is.)
3. On success you will get mysql>
4. Provide grant access all for that user.

1. 转到我的 sql bin 文件夹或将其添加到 PATH
2. 通过mysql -uroot -proot（或任何 root 密码）登录到 root 。
3. 成功后你会得到 mysql>
4. 为该用户提供全部授予访问权限。

---

---

GRANT ALL PRIVILEGES ON *.* TO 'username'@'IP' IDENTIFIED BY 'password';

Here IP is IP address for which you want to allow remote access, if we put %any IP address can access remotely.

这里IP是你要允许远程访问的%IP地址，如果我们放任何IP地址都可以远程访问。

Example:

例子：

C:\Users\UserName> cd C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin

C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.27 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.25 sec)

This for a other user.

这对于其他用户。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'%' IDENTIFIED BY 'testUser';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Hope this will help

希望这会有所帮助

回答by Vamsi Krishna B

Do you have a firewall ? make sure that port 3306 is open.

你有防火墙吗？确保端口 3306 已打开。

On windows , by default mysql root account is created that is permitted to have access from localhost only unless you have selected the option to enable access from remote machines during installation .

在 Windows 上，默认情况下创建的 mysql root 帐户只允许从 localhost 访问，除非您在安装期间选择了启用从远程机器访问的选项。

creating or update the desired user with '%' as hostname .

使用 '%' 作为主机名创建或更新所需的用户。

example :

例子 ：

CREATE USER 'krish'@'%' IDENTIFIED BY 'password';

回答by Paulo H.

1. Try to flush privilegesagain.

2. Try to restart server to reload grants.

3. Try create a user with host "192.168.233.163". "%" appears to not allow all (it's weird)

1. 再试flush privileges一次。

2. 尝试重新启动服务器以重新加载授权。

3. 尝试创建一个主机为“192.168.233.163”的用户。"%" 似乎不允许所有（这很奇怪）

回答by Kushal

In my case I was trying to connect to a remote mysql server on cent OS. After going through a lot of solutions (granting all privileges, removing ip bindings,enabling networking) problem was still not getting solved.

就我而言，我试图连接到 Cent OS 上的远程 mysql 服务器。在经历了很多解决方案（授予所有权限、删除 ip 绑定、启用网络）之后，问题仍然没有得到解决。

As it turned out, while looking into various solutions,I came across iptables, which made me realize mysql port 3306 was not accepting connections.

事实证明，在研究各种解决方案时，我遇到了 iptables，这让我意识到 mysql 端口 3306 不接受连接。

Here is a small note on how I checked and resolved this issue.

这是关于我如何检查和解决此问题的小说明。

• Checking if port is accepting connections:

  telnet (mysql server ip) [portNo]

• Adding ip table rule to allow connections on the port:

  iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

• Would not recommend this for production environment, but if your iptables are not configured properly, adding the rules might not still solve the issue. In that case following should be done:

  service iptables stop

• 检查端口是否接受连接：

  telnet (mysql 服务器 ip) [端口号]

• 添加ip表规则以允许端口上的连接：

  iptables -A 输入 -i eth0 -p tcp -m tcp --dport 3306 -j 接受

• 不建议在生产环境中使用此方法，但如果您的 iptables 配置不正确，添加规则可能仍然无法解决问题。在这种情况下，应执行以下操作：

  服务 iptables 停止

Hope this helps.

希望这可以帮助。

回答by Nitin Jaiman

if you are using dynamic ip just grant access to 192.168.2.% so now you dont have to worry about granting access to your ip address every time.

如果您使用的是动态 ip，只需授予对 192.168.2.% 的访问权限，现在您不必担心每次都授予对您的 ip 地址的访问权限。

回答by Brendan Moore

I was struggling with remote login to MYSQL for my Amazon EC2 Linux instance. Found the solution was to make sure my security group included an inbound rule for MySQL port 3306 to include my IP address (or 0.0.0.0/0 for anywhere). Immediately could connect remotely as soon as I added this rule.

我在为 Amazon EC2 Linux 实例远程登录 MYSQL 时遇到了困难。找到的解决方案是确保我的安全组包含 MySQL 端口 3306 的入站规则，以包含我的 IP 地址（或任何地方的 0.0.0.0/0）。添加此规则后，立即可以远程连接。

回答by stackMonk

New location for mysql config file is

mysql 配置文件的新位置是

/etc/mysql/mysql.conf.d/mysqld.cnf