Recently using Navicat for MySQL access to the remote MySQL database, an error occurred, showing "1130-host ' xxx.xxx.xxx.xxx ' is not allowed to connect to this MySQL server". The solution is as follows:
Method/Step
- Reading the wrong window first.
- The error is due to the fact that the local IP (xxx.xxx.xxx.xxx) does not have permission to access the remote database.
The following enables local IP (xxx.xxx.xxx.xxx) access to the remote MySQL database.
- First, the remote connection enters the server, enters the mysql-u root-p in the CMS, then returns, enters the password, enters the MySQL command line.
- Input use MySQL;
- Enter select User,password,host from user;
You can see that there is only localhost in host. We need to add xxx.xxx.xxx.xxx to this.
- Add the following method:
Enter grant all privileges on * * to [email protected] ' xxx.xxx.xxx.xxx ' identified by ' password '; This is equivalent to giving ip-xxx.xxx.xxx.xxx all the permissions, including remote access permissions. Then enter the flush privileges, which is equivalent to reloading MySQL permissions, which must be done.
- Re-enter select User,password,host from user;
You can see that the new IP has been added to the host.
- Now again with Navicat for MySQL access to the remote MySQL database, has been able to open the normal.
Problem solving.
- However, there is another problem, found that the double-click to open a table is very slow, at least 3 seconds.
The reason is: when the remote access to MySQL, MySQL will resolve the domain name, so it will result in very slow access, there will be 2, 3 seconds delay! Workaround: Modify the My.ini in the MySQL installation directory, and add the following configuration to resolve this issue. Under [Mysqld], add: skip-name-resolve Save to restart the MySQL service after exiting. Then the access speed is as fast as the local.
1130-host ' xxx.xxx.xxx.xxx ' is not allowed to connect to this MySQL server resolves navicat connection mysql error