Shulou(Shulou.com)06/01 Report--

Host 'xxx' is not allowed to connect to this MySQL server.

Mysql opens a remote connection

An error occurred when mysql was installed on the server today and was ready to log in remotely with mysqlguitools. Prompt: Host 'xxx' is not allowed to connect to this MySQL server. Some information was found on the Internet, which was caused by the fact that mysql did not enable mysql remote access.

Document the solution for future reference. :

1. Log in to mysql: mysql-uroot-ppwd

two。 View the user table:

Mysql > use mysql

Database changed

Mysql > select host,user,password from user

+-- +

| | host | user | password | |

+-- +

| | localhost | root | * 826960FA9CC8A87953B3156951F3634A80BF9853 |

+-- +

1 row in set (0.00 sec)

The host and user fields in the table identify the hosts and users who can access the database. For example, the above data means that the local host can only be accessed through root users. I see. No wonder remote connections can't be connected to each other.

In order for the database to support remote host access, there are two ways to turn on remote access.

The first (table change method):

Modify the value of the host field to change the localhost to an ip address that requires a remote connection to the database. Or modify it directly to%. Modified to% means that all hosts can access the database through root users. For convenience, I will directly modify it to%. Command: mysql > update user set host ='% 'where user =' root'

View the user table again

+-- +

| | host | user | password | |

+-- +

| |% | root | * 826960FA9CC8A87953B3156951F3634A80BF9853 |

+-- +

1 row in set (0.00 sec)

If the modification is successful, enter the command mysql > FLUSH PRIVILEGES; enter to make the modification take effect, and connect to the database remotely again.

The second (authorization law):

For example, if you want root to connect to the mysql server from any host using mypassword.

GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' IDENTIFIED BY' mypassword' WITH GRANT OPTION

If you want to allow the user myuser to connect to the mysql server from the host with ip 192.168.1.3, and use mypassword as the password

GRANT ALL PRIVILEGES ON *. * TO 'root'@'192.168.1.3' IDENTIFIED BY

'mypassword' WITH GRANT OPTION

Enter the command mysql > FLUSH PRIVILEGES; enter to make the changes take effect and connect to the database remotely again. Bingo.

Don't forget the last FLUSH PRIVILEGES; to refresh the previous changes.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.