MySQL cannot connect remotely to a workaround

A remote connection to the MySQL database prompts the following message: "MySQL connect error–1130:host ' 202.43.**.** ' is not allowed to connect to this MySQL server"

Originally in the Create MySQL account is, limited Connection account remote login. That is, other than the current MySQL installation server, the other IP (host) is not allowed to access, although your username and password is correct.

Actually not MySQL default does not support remote, is mysql default user root default does not support remote, because it is localhost ...

To be blunt, that is, the permission problem, root user's host default is localhost ...

The following workaround is to re-create a user to support remote access without having to modify root permissions, suggesting that ...

Know the cause of the problem, the following to solve the problem.

First you need to log on to the MySQL installation host and then go to MySQL:

The code is as follows	Copy Code
Mysql/usr/local/mysql/bin Mysql-u Root-p

Enter

Root is the highest authorized user name for MySQL, you will be prompted to enter the password, correct input password, enter the MySQL.

Then enter the following command:

The code is as follows	Copy Code
Grant all on database name. * to ' Database account name ' @ '% ' identified by ' password ' with GRANT option;

Enter

The code is as follows	Copy Code
Flush privileges;

Enter
Note: The above single quotation marks cannot be saved, the database name. * Indicates that all tables under the database to be opened, if all the databases of the connection are open, can be replaced with *.*.
The ' Database account name ' @ '% ' here represents the account to be opened, and the hundred-semicolon indicates that access is allowed on any host.
If both of these steps show "Query OK, 0 rows Affected (0.00 sec)", then the command has been successfully executed and you can now remotely connect to your MySQL database.

Key Note: (I experienced, (~ o ~) ~zz)

(1). If you want to give permission to all operations

Grant all on ... to user name ...

(2). If you want to give permission to all tables that operate on all databases

Grant ... on *.* to user name ...

(3). If you want to give all the tables of a database

Grant ... on database name. ' * ' to User name ... ([*] must add single quotation marks on both sides)

(4). If you want to give a table to a database

Grant ... on database name. Table name to User name ...

(5). If you want any client to be able to access this user name remotely

Grant ... on ... to user name @% ..... (To change the IP address to [%])

(6). Be sure to restart the MySQL service after the change is done

Users will be saved to the user table on the MySQL server

So you can also do this:

1. Change Table method. Your account may not be allowed to log in remotely, only in localhost. This time as long as the computer in localhost, login mysql, change the "MySQL" Database in the "User" table in the "host" item, from "localhost" renamed "%"

The code is as follows	Copy Code
Mysql-u root-pvmwaremysql>use mysql;mysql>update User Set host = '% ' where user = ' root '; Mysql>select host, use R from user;

2. Authorization law. For example, you want to myuser use MyPassword to connect to a MySQL server from any host.

The code is as follows	Copy Code
Grant all privileges in *.* to ' myuser ' @ '% ' identified by ' MyPassword ' with GRANT OPTION;

If you want to allow the user to connect to the MySQL server from the IP-192.168.1.3 host, and use MyPassword as the password myuser

The code is as follows	Copy Code
Grant all privileges in *.* to ' myuser ' @ ' 192.168.1.3 ' identified by ' MyPassword ' with GRANT OPTION;

If you still can't remote, we can refer to

1, MySQL port is correct, through the NETSTAT-NTLP view of the port occupancy situation, the general situation of the lower mouth is 3306. The port is used to connect MySQL with the tool. For example, my adminmy Query browsermysql front.

2, check the user permissions are correct.
For example: The User Tester,user table has two records: host is localhost and% (for security,% can be replaced by the IP you need the external connection).

3, check the/ETC/MY.CNF, skip-networking whether has been injected, need to be dropped.
Error: Error 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.51.112 ' (111)

6
-To see if iptables is stopped or not, you cannot connect.
Through: Service iptables stop temporary shutdown.
Error: Error 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.51.112 ' (113) 51Testing Software test network