启用远程MySQL连接:错误1045(28000):拒绝用户访问

MySQL 5.1.31 running on Windows XP.

MySQL 5.1.31运行在Windows XP上。

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

通过本地MySQL服务器(192.168.233.142)，我可以作为根连接如下:

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

From a remote machine (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 remote machine, 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中只有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。有IP特定条目的%的用户条目，因此我的用户表现在看起来是这样的:

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

I then restarted the machine, but the problem persists.

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

12 个解决方案

#1

190

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 and USERNAME is the user you use to connect

IP是你想要访问的IP，用户名是你用来连接的用户

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服务器。

#2

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

然后运行服务mysql重启。

#3

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

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

Go to my sql bin folder or add it to PATH
转到我的sql bin文件夹或将它添加到PATH
Login to root by mysql -uroot -proot (or whatever the root password is.)
通过mysql -uroot -proot(或任何根密码)登录到根。
On success you will get mysql>
成功后，你将得到mysql>。
Provide grant access all for that user.
为该用户提供授予访问权限。

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

希望这将帮助

#4

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 expansion to an environment variable, so we need to escape it with a backslash. Incidentally, we don't have 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

#5

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根帐户只允许从localhost访问，除非您在安装期间选择了允许从远程机器访问的选项。

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

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

example :

例子:

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

#6

Try to flush privileges again.

尝试再次刷新特权。
Try to restart server to reload grants.

尝试重新启动服务器以重新加载授予。
Try create a user with host "192.168.233.163". "%" appears to not allow all (it's weird)

尝试使用主机“192.168.233.163”创建一个用户。“%”似乎不允许所有(这很奇怪)

#7

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]

telnet (mysql服务器ip) [portNo]
Adding ip table rule to allow connections on the port:

增加ip表规则，允许端口上的连接:

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

iptables -A输入-i eth0 -p tcp -m tcp -dport 3306 -j接受
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:

对于生产环境不建议这样做，但是如果您的iptables配置不正确，添加规则可能仍然不能解决这个问题。在这种情况下，应做以下工作:

service iptables stop

iptables停止服务

Hope this helps.

希望这个有帮助。

#8

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地址。

#9

New location for mysql config file is

mysql配置文件的新位置是

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

#10

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)。一旦我添加了这个规则，立即可以远程连接。

#11

MySQL ODBC 3.51 Driver is that special characters in the password aren't handled.

MySQL ODBC 3.51驱动程序不处理密码中的特殊字符。

"Warning – You might have a serious headache with MySQL ODBC 3.51 if the password in your GRANT command contains special characters, such as ! @ # $ % ^ ?. MySQL ODBC 3.51 ODBC Driver does not support these special characters in the password box. The only error message you would receive is “Access denied” (using password: YES)" - from http://www.plaintutorials.com/install-and-create-mysql-odbc-connector-on-windows-7/

“警告——如果GRANT命令中的密码包含特殊字符，比如!”@ # $ % ^ ?。MySQL ODBC 3.51 ODBC驱动程序不支持密码框中的这些特殊字符。您将收到的唯一错误消息是“拒绝访问”(使用密码:YES)——从http://www.plaintutorials.com/install-and-create-mysql-odbc-connector-on-windows-7/

#12

The user/host combination may have been created without password.

用户/主机组合可能是在没有密码的情况下创建的。

I was assuming that when adding a new host for an existing user (using a GUI app), the existing password would also be used for the new user/host combination.

我假设在为现有用户(使用GUI应用程序)添加新主机时，新用户/主机组合也将使用现有密码。

I could log in with

我可以登录

mysql -u username -p PASSWORD

locally, but not from IPADDRESS with

本地，但不是IPADDRESS

mysql -u --host=HOST -p PASSWORD

(I could actually log in from IPADDRESS without using a password)

(我可以不用密码从IPADDRESS登录)

mysql -u --host=HOST

Setting the password allowed access:

设置允许访问的密码:

set password for '<USER>'@'<IPADDRESS>' = '<PASSWORD>';

#1

190