Resolve the MYSQL database on the remote connection management machine

In the development process, sometimes need to remote connections and manage other machines MYSQL database, encountered in the process of implementation of a series of problems, now with remote access to my own installed on Ubuntu MYSQL data as an example for the default port (port 3306), suggests that 1 configuration steps and each step 1 under the encountered problems and corresponding solutions:
Remote connection management MYSQL, in general, has three steps :(A for the main operating machine, B for the remote machine (MYSQL installed on B, A for access to B)
1. Create a dedicated remote connection user wow in the connected MYSQL;

2. Modify the configuration file my.cnf of the connected MYSQL so that this MYSQL not only supports local IP127.0.0.1 listening, but also supports other IP listening, and restart the Mysql service to make the configuration effective.

3. Verify whether the configured port of MYSQL in A is the same as 1 in B (both 3306?). , if 1 is connected, via mysql-h B ip-u wow --port= 3306-p.

Below, each step is described in detail 1:
1. Create a dedicated remote connection user wow in the connected MYSQL;
The reason for creating a dedicated user is that when MYSQL was initially installed, the default root account and other accounts were limited to localhost connections. Even if you log in with the same username and password on the remote machine, the connection will not work. For example, if you log on to Mysql on the remote machine 192.168.83.56 using the root account on the machine 192.168.11.12:
mysql-h 192.168.11.12-u root-p cannot log in normally. At this point, you need to log on to the machine 192.168.11.12, enter the mysql database in mysql to view the user table, and confirm the specific root permission, especially if its host is localhost or your corresponding IP or %.

Solutions:
A, (create dedicated remote user root or wow in MYSQL on machine 192.168.11.12), there are two ways to create users and grant permissions:
1) change the table method. It may be that your account is not allowed to log in remotely, only on localhost. At this point, just log in to mysql from the localhost computer, change the "host" entry in the "user" table in the "mysql" database, and change the "localhost" to "%". "%" means that all host are accessible.
mysql -u root -p vmware 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 wow to connect to the mysql server from any host using mypassword.
GRANT ALL PRIVILEGES ON *.* TO 'wow'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
If you want to only allow user wow to connect from the host of ip 192.168.83.56 to the mysql server of 192.168.11.12 and use mypassword as the password
GRANT ALL PRIVILEGES ON *.* TO 'wow'@'192.168.83.56' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES
The second sentence means reloading the permission data from the grant table of the mysql database. Because MySQL puts all the permissions in cache, it needs to be reloaded after the changes are made.
B, is it ok to use wow after using GRANT authorization method to create the user wow and grant permissions? No? So we can easily verify 1. On the remote machine 192.168.83.56, mysql-h 192.168.11.12-u root-p cannot log in normally. Error reported in 2003:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.11.12' (111)
What's the reason? Is the account not set up successfully? Permission setting problem, or network configuration firewall problem? Or have other configuration issues with mysql not been resolved?
Remove the account and permissions first: in the localhost(192.168.11.12) machine, try using the wow user to access the database. mysql-u wow p mypassword, error of reporting 1045:
ERROR 1045 (28000): Access denied for user 'wow'@'localhost' (using password: YES),
Why? How can you create a new user who can't log on to MYSQL on the local machine? Is it true that user wow was not created successfully? If you look at the user table, wow already has it. After a long struggle, I finally found out the reason. It turned out that the anonymous account was not deleted during the installation and configuration of mysql. The anonymous account was deleted:
mysql -u root -p
mysql > use mysql
mysql > delete from user where User= ";
mysql > quit;
Try mysql-u wow-p mypassword again. wow users can log in normally on the machine. It seems that the account wow is ok.
mysql-h 192.168.11.12-u root-p is unable to log in normally. Return the error of 2003:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.11.12' (111),
Check 1 for errorCode to troubleshoot problems:
[mysql@vvmvcs0 ~]$ perror 111
OS error code 111: Connection refused
Is that a network problem? ping xxx. xxx. xxx. 12, can PING tong, the machine is no problem. This brings us to our second step, which is to look at the configuration file mysql, my.cnf, for remote connections.

2. Modify the configuration file my.cnf of the connected MYSQL so that this MYSQL not only supports local IP127.0.0.1 listening, but also supports other IP listening, and restart the Mysql service for the configuration to take effect.
There are mainly two configuration items: skip_networking or bind_address. In order to unlisten locally, you need to comment out the configuration for these two items in my.cnf:
Normally, port 3306 occupied by mysql is only listening on IP 127.0.0.1, denying access to other IP (available through netstat). To cancel local listening, you need to modify my.cnf:
sudo vim /etc/mysql/my.cnf
// find the following and comment it
bind-address = 127.0.0.1
You then need to restart mysql (which can be restarted last). sudo mysql stop
Now let's try 1 again. On the remote machine 192.168.83.56, use the following command to log in to the database of 192.168.11.12 under 1: mysql-h 192.168.11.12-u root-p. It was 2003 again. What's the reason? Oh, it's a question of whether the mysql port configuration is unified or not!

3. Verify whether the configured port of MYSQL in A is the same as that in B (both 3306?). , if 1 is connected, via mysql-h B ip-u wow --port= 3306-p.
The mysql listener port on 192.168.83.56 is configured to be 3308 instead of 3306, while the port of 192.168.11.12 is configured to be 3306 by default, and the 2 do not match. If you log in only on 192.168.83.56 using mysql-u root-u root-p, you will be able to access port 3308 of 192.168.11.12, which of course cannot be accessed. We also need to specify the correct port number of the mysql to be accessed: mysql-h 192.168.11.12-u wow --port= 3306-p.