This should be dead simple, but I cannot get it to work for the life of me.

I'm just trying to connect remotely to my MySQL server.

这应该是非常简单的，但我不能让它为我的生活工作。我只是想远程连接到我的MySQL服务器。

• Connecting as:

  连接方式：

  
  

  mysql -u root -h localhost -p  
  

  
  



• works fine, but trying:

  运作良好，但正在尝试：

  
  

  mysql -u root -h 'any ip address here' -p
  

  
  



• fails with the error:

  失败，并显示以下错误：

  
  

  
  

  ERROR 1130 (00000): Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server

  错误1130(00000)：不允许主机‘xxx.xx.xxx.xxx’连接到此MySQL服务器

  
  

  
  

In the mysql.user table, there is exactly the same entry for user 'root' with host 'localhost' as another with host '%'.

在mysql.user表中，主机为‘localhost’的用户‘root’的条目与主机为‘%’的另一个用户的条目完全相同。

I'm at my wits' end and have no idea how to proceed.
Any ideas are welcome.

我束手无策，不知道该怎么做。欢迎任何想法。

优秀答案推荐

Possibly a security precaution. You could try adding a new administrator account:

可能是为了安全防范。您可以尝试添加新的管理员帐户：

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     WITH GRANT OPTION;

Although as Pascal and others have noted it's not a great idea to have a user with this kind of access open to any IP. If you need an administrative user, use root, and leave it on localhost. For any other action specify exactly the privileges you need and limit the accessibility of the user as Pascal has suggest below.

不过，正如帕斯卡和其他人所指出的，让拥有这种访问权限的用户对任何IP开放并不是一个好主意。如果您需要管理用户，请使用根用户，并将其保留在本地主机上。对于任何其他操作，准确地指定您需要的权限，并限制用户的可访问性，如Pascal下面所建议的那样。

Edit:

编辑：

From the MySQL FAQ:

在MySQL常见问题解答中：

If you cannot figure out why you get
Access denied, remove from the user
table all entries that have Host
values containing wildcards (entries
that contain '%' or '_' characters). A
very common error is to insert a new
entry with Host='%' and
User='some_user', thinking that this
allows you to specify localhost to
connect from the same machine. The
reason that this does not work is that
the default privileges include an
entry with Host='localhost' and
User=''. Because that entry has a Host
value 'localhost' that is more
specific than '%', it is used in
preference to the new entry when
connecting from localhost! The correct
procedure is to insert a second entry
with Host='localhost' and
User='some_user', or to delete the
entry with Host='localhost' and
User=''. After deleting the entry,
remember to issue a FLUSH PRIVILEGES
statement to reload the grant tables.
See also Section 5.4.4, “Access
Control, Stage 1: Connection
Verification”.

One has to create a new MySQL User and assign privileges as below in Query prompt via phpMyAdmin or command prompt:

用户必须创建新的MySQL用户，并通过phpMyAdmin或命令提示符在查询提示符中分配权限，如下所示：

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

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

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Once done with all four queries, it should connect with username / password

完成所有四个查询后，它应该使用用户名/密码进行连接

My error message was similar and said 'Host XXX is not allowed to connect to this MySQL server' even though I was using root. Here's how to make sure that root has the correct permissions.

我的错误消息与此类似，并显示‘主机XXX不允许连接到此MySQL服务器’，尽管我使用的是超级用户。下面介绍如何确保超级用户拥有正确的权限。

My setup:

我的设置：

• Ubuntu 14.04 LTS


• MySQL v5.5.37

Solution

解

1. Open up the file under etc/mysql/my.cnf

   
   



2. Check for:

   
   
   
   
   • port (by default this is port = 3306)
   
   
   • bind-address (by default this is bind-address = 127.0.0.1; if you want to open to all then just comment out this line. For my example, I'll say the actual server is on 10.1.1.7)
   
   
   
   



3. Now access the MySQL Database on your actual server (say your remote address is 123.123.123.123 at port 3306 as user root and I want to change permissions on database 'dataentry'. Remember to change the IP Address, Port, and database name to your settings)

   
   

   mysql -u root -p
   Enter password: <enter password>
   mysql>GRANT ALL ON *.* to root@'123.123.123.123' IDENTIFIED BY 'put-your-password';
   mysql>FLUSH PRIVILEGES;
   mysql>exit
   

   
   



4. sudo service mysqld restart

   
   



5. You should now be able to remote connect to your database. For example, I'm using MySQL Workbench and putting in 'Hostname:10.1.1.7', 'Port:3306', 'Username:root'

   
   

Just perform the following steps:

只需执行以下步骤：

1. Connect to MySQL (via localhost)

   
   

   mysql -uroot -p
   

   
   
   
   

   • If the MySQL server is running in Kubernetes (K8s) and being accessed via a NodePort

     
     

     kubectl exec -it [pod-name] -- /bin/bash
     mysql -uroot -p
     

     
     
   
   
   
   

2. Create user

   
   

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

   
   



3. Grant permissions

   
   

   GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;
   

   
   



4. Flush privileges

   
   

   FLUSH PRIVILEGES;
   

   
   

You need to grant access to the user from any hostname.

您需要从任何主机名向用户授予访问权限。

This is how you add new privilege from phpmyadmin

下面是从phpmyadmin添加新权限的方法

Goto Privileges > Add a new User

转至权限>添加新用户

Select Any Host for the desired username

为所需的用户名选择任何主机

Simple way:

简单的方法：

Grant All Privileges ON *.* to 'USER_NAME'@'%' Identified By 'YOUR_PASSWORD'; 

then

然后

FLUSH PRIVILEGES;

done!

搞定了！

The message *Host ''xxx.xx.xxx.xxx'' is not allowed to connect to this MySQL server is a reply from the MySQL server to the MySQL client. Notice how its returning the IP address and not the hostname.

消息*主机‘’xxx.xx.xxx.xxx‘’不允许连接到此MySQL服务器是MySQL服务器对MySQL客户端的回复。注意它是如何返回IP地址而不是主机名的。

If you're trying to connect with mysql -h<hostname> -u<somebody> -p and it returns this message with the IP address, then the MySQL server isn't able to do a reverse lookup on the client. This is critical because thats how it maps the MySQL client to the grants.

如果您尝试使用mySQL-h<主机名>-u<某人>-p进行连接，并且它返回带有IP地址的消息，那么MySQL服务器就不能在客户机上执行反向查找。这一点很关键，因为这是它将MySQL客户端映射到授权的方式。

Make sure you can do an nslookup <mysqlclient> FROM the MySQL server. If that doesn't work, then there's no entry in the DNS server. Alternatively, you can put an entry in the MySQL server's HOSTS file (<ipaddress> <fullyqualifiedhostname> <hostname> <- The order here might matter).

确保您可以从MySQL服务器执行nslookup 。如果这不起作用，那么在DNS服务器中就没有条目。或者，您可以在MySQL服务器的主机文件中放置一个条目( <-这里的顺序可能很重要)。

An entry in my server's host file allowing a reverse lookup of the MySQL client solved this very problem.

我的服务器主机文件中的一个条目允许反向查找MySQL客户端，解决了这个问题。

This working for any future remote mysql connection !

这适用于任何未来的远程mysql连接！

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

Navigate to the line that begins with the bind-address directive. It should look like this:

导航到以Bind-Address指令开头的行。它应该如下所示：

    bind-address            = 0.0.0.0

Login to your mysql as root terminal

以根终端身份登录到您的MySQL

    mysql -u root -p
    -- root password

    CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

    GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

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

    GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

    FLUSH PRIVILEGES;

    EXIT;

finally Grant that machine exclusive permission to connect to the database remotely with the following command.

最后，使用以下命令授予该计算机远程连接到数据库的独占权限。

    sudo ufw allow from remote_IP_address to any port 3306

If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute
a FLUSH PRIVILEGES statement to tell the server to reload the grant tables.

如果手动修改授权表（使用UPDATE、UPDATE等），您应该执行FLUSH PRIVILEGES语句来告诉服务器重新加载授权表。

PS: I wouldn't recommend to allow any host to connect for any user (especially not the root use). If you are using mysql for a client/server application, prefer a subnet address. If you are using mysql with a web server or application server, use specific IPs.

PS：我不建议允许任何用户(尤其是根用户)连接任何主机。如果您将MySQL用于客户端/服务器应用程序，请首选子网地址。如果您将MySQL与Web服务器或应用程序服务器一起使用，请使用特定的IP。

Just use the interface provided by MySql's GUI Tool (SQLyog):

只需使用MySQL的图形用户界面工具(SQLyog)即可：

Click on User manager:

点击用户管理器：

Now, if you want to grant access FOR ANY OTHER REMOTE PC, just make sure that, just like in the underneath picture, the Host field value is % (which is the wildcard)

现在，如果您想要授予对任何其他远程PC的访问权限，只需确保如下图所示，主机字段值为%(即通配符)

If you are using MySQL WorkBench, you can achieve this easily:

如果您使用的是MySQL工作台，您可以很容易地做到这一点：

1. From the menu, select Server -> Users And Privileges
   

   
   



2. On the lower left, click on "Add account"
   

   
   



3. Fill the form with username, host matching (% means every host) and the password
   

   
   



4. Click on "Apply" on the lower right
   

   
   

After this you are good to go. Then, if you want to refine your configuration, you can use the "Administrative Roles" tab to set the command that can be used by the user (SELECT, ALTER etc etc) and the "Schema privileges" tab to restrict the user interaction to specific schemas.

在这之后，你就可以走了。然后，如果您想要改进您的配置，您可以使用“管理角色”选项卡来设置用户可以使用的命令(SELECT、ALTER等)，并使用“架构权限”选项卡来将用户交互限制到特定的架构。

Most of the answers here show you creating users with two host values: one for localhost, and one for %.

这里的大多数答案显示您使用两个主机值创建用户：一个用于本地主机，另一个用于%。

Please note that except for a built-in localhost user like root, you don't need to do this. If you simply want to make a new user that can log in from anywhere, you can use

请注意，除了像超级用户这样的内置本地主机用户外，您不需要这样做。如果您只是想创建一个可以从任何地方登录的新用户，您可以使用

CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';
GRANT <whatever privileges are appropriate> ON <relevant tables> TO myuser;

and it will work just fine. (As others have mentioned, it's a terrible idea to grant administrative privileges to a user from any domain.)

而且它会运行得很好。(正如其他人所提到的，向来自任何域的用户授予管理权限是一个糟糕的想法。)

Well, nothing of the above answer worked for me. After a lot of research, I found a solution. Though I may be late this may help others in future.

好吧，上面的答案对我来说都不管用。经过大量的研究，我找到了一个解决方案。虽然我可能会迟到，但这可能会在未来帮助其他人。

Login to your SQL server from a terminal

从终端登录到您的SQL服务器

 mysql -u root -p
 -- root password
GRANT ALL ON *.* to root@'XX.XXX.XXX.XX' IDENTIFIED BY 'password';

This should solve the permission issue.

这应该可以解决许可问题。

Happy coding!!

编码快乐！！

simple way is to login to phpmyadmin with root account , there goto mysql database and select user table , there edit root account and in host field add % wild card . and then through ssh flush privileges

简单的方法是以超级用户登录到phpmyadmin，在那里转到MySQL数据库并选择用户表，在那里编辑超级用户并在主机字段中添加%通配符。然后通过ssh刷新权限

 FLUSH PRIVILEGES;

If this is a recent mysql install, then before changing anything else, try simply to execute this command and then try again:

如果这是最近安装的MySQL，则在更改任何其他内容之前，只需尝试执行此命令，然后重试：

flush privileges;

This alone fixes the issue for me on Ubuntu 16.04, mysql 5.7.20. YMMV.

仅此一项就解决了我在Ubuntu 16.04，MySQL 5.7.20上的问题。YMMV。

Just find a better way to do that from your hosting control panel (I'm using DirectAdmin here)

只需在您的主机控制面板中找到更好的方法(我在使用DirectAdmin)

simply go to the target server DB in your control panel, in my case:
MySQL management -> select your DB -> you will find: "Access Hosts", simply add your remote host here and its working now!

只需转到您的控制面板中的目标服务器数据库，在我的情况下：MySQL管理->选择您的数据库->您会发现：“访问主机”，只需将您的远程主机添加到这里，它现在就可以工作了！

I guess there is a similar option on other C.panels like plesk, etc..

我猜在Plesk等其他C.Panel上也有类似的选择。

I'm hope it was helpful to you too.

我希望这对你也有帮助。

I was also facing same issue, It resolved in 2 min for me i just white list ip through cpanel

我也面临着同样的问题，它在2分钟内解决了我刚刚通过cPanel将IP列入白名单

Suppose you are trying to connect database of server B from server A.
Go to Server B Cpanel->Remote MySQL-> enter Server A IP Address and That's it.

假设您正在尝试从服务器A连接服务器B的数据库。转到服务器B C面板->远程MySQL->输入服务器A的IP地址。

If you happen to be running on Windows; A simple solution is to run the MySQL server instance configuration wizard. It is in your MYSQL group in the start menu. On the second from last screen click the box that says "allow root access from remote machines".

如果您碰巧在Windows上运行，一个简单的解决方案是运行MySQL服务器实例配置向导。它位于开始菜单中的MySQL组中。在倒数第二个屏幕上，单击显示“允许从远程计算机进行超级用户访问”的框。

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

this error because no password to the root , and this Maybe occurred with you when you trying to connect from outside .

此错误是因为没有超级用户密码，当您尝试从外部连接时可能会出现此错误。

If you have WAMP Server + Windows 10 and you are using it for development than Right Click on Wamp Icon => Wamp Settings => Check Allow Virtual Hosts other than 127*

如果您使用的是WAMP服务器+Windows 10，并且您正在使用它进行开发，请右键单击WAMP图标=>WAMP设置=>选中允许除127之外的虚拟主机*

You need to allow users from other locations(x.x.x.x in your case) as well from where you are going to connect to the database.

您还需要允许来自其他位置(在您的情况下为x.x)的用户从您要连接的位置连接到数据库。

All the above answers do seem correct in the wildcard(%) declaration for allowing hosts from all locations but that opens it to all hosts and hence opening a security risk. Its better to explicitly specify the host as follows:

在通配符(%)声明中，所有上述答案似乎都是正确的，因为它允许来自所有位置的主机，但这会向所有主机开放，因此会带来安全风险。最好按如下方式明确指定主机：

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

FLUSH PRIVILEGES;

Replace x.x.x.x with the host ip that you are connecting from.

将x.x替换为您要连接的主机IP。

Well what you can do is just open mysql.cfg file and you have to change Bind-address to this

您所能做的就是打开mysql.cfg文件，您必须将绑定地址更改为

bind-address = 127.0.0.1

绑定地址=127.0.0.1

and then Restart mysql and you will able to connect that server to this.

然后重新启动MySQL，您将能够将该服务器连接到该服务器。

Look this you can have idea form that.

看这个，你可以从那个中得到想法。

this is real sol

这是真正的溶胶

This answer might help someone...

这个答案可能会帮助某些人..。

All these answers didnt help, then I realised I forgot to check one crucial thing.. The port :)

所有这些答案都没有用，然后我意识到我忘了检查一件关键的事情。端口：)

I have mysql running in a docker container running on a different port. I was pointing to my host machine on port 3306, which I have a mysql server running on. My container exposes the server on port 33060. So all this time, i was looking at the wrong server! doh!

我让MySQL在另一个端口上运行的码头容器中运行。我指的是端口3306上的主机，我在该主机上运行了一台MySQL服务器。我的容器在端口33060上公开服务器。所以一直以来，我看错了服务器！多！

This working for DirectAdmin;

这为DirectAdmin工作；

1. Go to your DirectAdmin.


2. Go to your MySQL Management.


3. Select your database.


4. Under your Accesse Host tab, there is a field.
   You should fill this field by xxx.xx.xxx.xx.


5. Click on Add Host.

Finished. Now you can access to this DB by your your_database_username & your_database_password.

So Simple!

完成了。现在，您可以通过您的YOUR_DATABASE_USERNAME和YOUR_DATABASE_PASSWORD访问这个数据库。太简单了！

CPANEL solution

CPanel解决方案

Go to Cpanel, look for Remote MySQL.
Add the the IP in the input field:

转到Cpanel，查找远程MySQL。在输入字段中添加IP：

Host (% wildcard is allowed)

主机(允许%通配符)

Comment to remember what IP that is.
That was it for me.

评论以记住那是什么IP。对我来说就是这样。

1. From a terminal, connect you to your MySQL running container

1.从终端连接到您的MySQL运行容器

docker exec -it your_container_name_or_id bash

2. In your container, connect you to the MySQL database

2.在容器中，连接到MySQL数据库

mysql -u your_user -p

enter your password to connect to database.

输入您的密码以连接到数据库。

3. execute this SQL script to list all existing database users:

3.执行此SQL脚本以列出所有现有数据库用户：

SELECT host, user FROM mysql.user;

The result will be some thing like below:

结果将如下所示：

you should add a new row:

您应该添加一个新行：

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

Some tips:

一些小贴士：

• list privileges using show grants;


• create a VPN and just add the ip of the tunnel

if you are trying to execute mysql query withouth defining connectionstring, you will get this error.

如果您试图在没有定义连接字符串的情况下执行MySQL查询，则会出现此错误。

Probably you forgat to define connection string before execution. have you check this out?
(sorry for bad english)

可能您忘记了在执行之前定义连接字符串。你看过这个了吗？(对不起，我的英语不好)

Problem: root@localhost is unable to connect to a fresh installation of mysql-community-server on openSUSE 42.2-1.150.x86_64.
Mysql refuses connections - period.

问题：Root@Localhost无法连接到在OpenSUSE 42.2-1.150.x86_64上全新安装的mysql-Community-server。MySQL拒绝连接-句号。

Solution:

解决方案：

$ ls -l /var/lib/mysql/mysql/user.*
-rw-rw---- 1 mysql mysql     0 Apr 29 19:44 /var/lib/mysql/mysql/user.MYD
-rw-rw---- 1 mysql mysql  1024 Apr 29 19:44 /var/lib/mysql/mysql/user.MYI
-rw-rw---- 1 mysql mysql 10684 Apr 29 19:44 /var/lib/mysql/mysql/user.frm

File user.MYD has 0 size (really ?!).
I copied all 3 files from another working system.

文件用户.MYD的大小为0(真的吗？！)。我从另一个正常工作的系统复制了所有3个文件。

$ /usr/sbin/rcmysql stop
$ cd /var/lib/mysql/mysql/
$ scp root@othersytem:/var/lib/mysql/mysql/user.* ./
$ /usr/sbin/rcmysql start
$ cd -
$ mysql -u root -p

I was able to log in. Then, it was just a matter of re-applying all schema privileges.
Also, if you disabled IPv6, re-enable it temporary so that root@::1 account can also work.

我可以登录了。然后，只需重新应用所有模式权限即可。此外，如果您禁用了IPv6，请临时重新启用它，以便根@：：1帐户也可以工作。

All of the answers here didn't work in my case so I guest this may help other users in the future. This can also be a problem in our code, not just in MySQL alone.

这里所有的答案都不适用于我的情况，所以我认为这可能会在未来帮助其他用户。这也可能是我们代码中的一个问题，不仅仅是MySQL。

If you are using VB.NET

如果您使用的是VB.NET

Instead of this code:

而不是这个代码：

 Dim server As String = My.Settings.DB_Server
 Dim username As String = My.Settings.DB_Username
 Dim password As String = My.Settings.DB_Password
 Dim database As String = My.Settings.DB_Database

 MysqlConn.ConnectionString = "server=" & server & ";" _
 & "user id=" & username & ";" _
 & "password=" & password & ";" _
 & "database=" & database

 MysqlConn = New MySqlConnection()

You need to move MysqlConn = New MySqlConnection() on the first line. So it would be like this

您需要将MysqlConn=New MySqlConnection()移到第一行。所以它会是这样的

 MysqlConn = New MySqlConnection()

 Dim server As String = My.Settings.DB_Server
 Dim username As String = My.Settings.DB_Username
 Dim password As String = My.Settings.DB_Password
 Dim database As String = My.Settings.DB_Database

 MysqlConn.ConnectionString = "server=" & server & ";" _
 & "user id=" & username & ";" _
 & "password=" & password & ";" _
 & "database=" & database