Use MySQLWorkbench remote management tools to remotely manage Mysql errors and solutions _ MySQL

Overview: recently, MySQLWorkbench remote management tool is used to remotely manage MySQL on a virtual machine. However, some problems are encountered during the configuration process. The following is an overview of this problem (HOStipisnotallowedtoconnecttothisMySqlserver:

Recently, MySQL Workbench is used for remote management of MySQL on virtual machines. However, some problems are encountered during the configuration process. The following describes the problem (HOSt ip is not allowed to connect to this MySql server) and the solution.

Author's environment:

Local system: Window 7

Virtual machine system: CentOS 6.5

MySQL version: mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

MySQL Workbench version: MySQL Workbench 6.2 CE

Error:

HOSt ip is not allowed to connect to this MySql server

Solution process:

I am still struggling with this issue. When I added remote control to two virtual machines, I found that the first one was allowed, but the second one was not allowed. The following is my processing process:

The error message above is because MySQL does not provide remote control services. We can solve this problem by using the following methods:

1. change the table method:
It may be that your account is not allowed to log on remotely, but only on localhost.
At this time, you only need to log in to mysql on the computer of localhost, and change the "host" entry in the "user" table in the "mysql" database to "%" from "localhost"
Mysql> use mysql;
Mysql> update user set host = '%' where user = 'root ';
Mysql> select host, user from user;

2. authorization law:

For example, if you want myuser to use mypassword to connect to the mysql server from any host.
Mysql> grant all privileges on *. * TO 'myuser' @ '%' identified by 'mypassword' with grant option;
If you want to allow the user loginName to connect to the mysql server from the host whose ip address is 192.168.1.6, and use Password as the Password
Mysql> grant all privileges on *. * TO 'loginname' @ '192. 168.1.6 'identified BY 'password' with grant option;
I used the previous sentence.

Mysql> flush rivileges;

3. another method:
Mysql> grant all privileges on *. * TO 'root' @ '%' with grant option;
Mysql> flush privileges;
Mysql> EXIT;

Key points:

I have not implemented 3rd methods, because the first two methods can solve the problem very well.

However, for the table change method, I did not perform the third step. Because an error is reported in step 2. Okay, let me try the authorization method. The authorization method is acceptable. In this way, MySQL Workbench can be used for remote management on my first machine.

However, when I started to operate on my second machine, it was unfortunate that the operation failed. The reason is that I thought that if an Error occurs in the table change method, I can skip it and directly grant the permission. However, this cannot be done. So I modified the table method again and performed step 3. I saw that the second step was actually modified successfully.

In this way, both of my machines can be remotely managed through MySQL Workbench.