不允许主机'xxx.xx.xxx.xxx'连接到此 MySQL 服务器

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.

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

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

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

转载于:https://stackoverflow.com/questions/1559955/host-xxx-xx-xxx-xxx-is-not-allowed-to-connect-to-this-mysql-server

csdnceshi62
csdnceshi62 Can't login as root in most circumstances due to security precaution..
一年多之前 回复
csdnceshi64
游.程 This link explains about the error: webyog.com/faq/content/23/36/en/…
接近 8 年之前 回复

20个回答

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.

Edit:

From the MySQL FAQ:

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

csdnceshi63
elliott.david This is a correct answer for root, which always has a localhost entry. However, if you are creating a brand new, non-root user, you do not need to add a localhost entry. Just issue one command creating the user with % as the hostname.
2 年多之前 回复
csdnceshi78
程序go Thanks. Using GRANT ALL PRIVILEGES ON . TO 'root'@'%' helped me resolve the error
2 年多之前 回复
weixin_41568183
零零乙 I felt that providing access from all hosts to root was not a proper solution. Instead I created a new user and granted a reduced set of privileges, the set I used is described as 'DBManager' on MySQL Workbench. I also only allowed access from a certain group of hosts in my local network, particularly 192.168.0.%
接近 6 年之前 回复
weixin_41568184
叼花硬汉 That's right Yannick, I read to fast and will remove my comment. However, AFAIK, permissions are working fine in MySQL so: 1. maybe the OP modified the grant tables manually and then need to flush privileges. 2. maybe he didn't use the proper grant syntax for root. Adding another administrative user might be a workaround but it won't solve the real issue IMHO.
10 年多之前 回复
csdnceshi69
YaoRaoLov I think you miss my point Pascal. The point is that the 'root' user has those rights already, and he wants to let any ip authenticate as that user. So if this is really what he wants, the default example of creating a new administrator user (which has exactly the same rights) is an alternative to what he's trying.
10 年多之前 回复
csdnceshi69
YaoRaoLov Well, this indeed wouldn't be a good idea, but allowing 'root' to connect from all hosts is exactly the same, since it is at the same privilege level.
10 年多之前 回复
csdnceshi79
python小菜 Good catch Yannick, however I would not recommend him granting all privileges to a non-root user. Perhaps a reduced set?
10 年多之前 回复

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

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

csdnceshi77
狐狸.fox yeah creating a @localhost would make sense if the privileges were different somehow but here they're the same and it's probably going to be more confusing than anything (such as changing passwords on one not realizing the other exists and that your login might be hitting that one).
大约 3 年之前 回复
csdnceshi58
Didn"t forge Do you have to create username@localhost? Is it not enough if you just create username@% ? I mean, if you just create username@%, will you not be able to connect with that user from localhost?
3 年多之前 回复
csdnceshi63
elliott.david A new user for every new host, no....
接近 4 年之前 回复
weixin_41568131
10.24 Had to restart mysql after completing the above steps for this to work for me.
接近 5 年之前 回复
csdnceshi74
7*4 yes create new user with new password and grant privileges as mentioned above, it should work
大约 6 年之前 回复
csdnceshi79
python小菜 You have to create a new user?
大约 6 年之前 回复

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

Click on User manager: enter image description here

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)

enter image description here

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

flush privileges;

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

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

This is how you add new privilege from phpmyadmin

Goto Privileges > Add a new User

enter image description here

Select Any Host for the desired username

enter image description here

csdnceshi56
lrony* what would the cli command to do this be?
3 年多之前 回复

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

 FLUSH PRIVILEGES;
csdnceshi79
python小菜 This helped me combined with this: GRANT ALL PRIVILEGES ON . TO 'root'@'%' WITH GRANT OPTION;
大约 3 年之前 回复

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

bind-address = 127.0.0.1

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

Look this you can have idea form that.

this is real sol

weixin_41568134
MAO-EYE But Granting all the permission to the user will not be wise option for that bcoz if u have client user and u are not allowed to create user with all permission then what would be the Solution.?
6 年多之前 回复
weixin_41568134
MAO-EYE But this is address which will allow all the Host to get connected to the server na?
6 年多之前 回复
csdnceshi70
笑故挽风 This is related, but not the same problem. The error originally received indicates the client is connecting to the MySQL server successfully, but then failing authentication. If bind-address was set to 127.0.0.1, you would get a connection refused error instead.
6 年多之前 回复

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.

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.

csdnceshi68
local-host +1 I do agree with your recommendation, and the flush privileges might work if he made changes to the user table manually. (cleaned up old comments)
10 年多之前 回复

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

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

共20条数据 1 尾页
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问