Resolve Remote connections Manage Mysql databases on other machines _mysql

In the development process, sometimes need to remotely connect and manage the MySQL database on other machines, in the implementation of the process will encounter a series of problems, now remotely access my own installed on Ubuntu MySQL data as an example (port is the default port 3306), Explain the configuration steps and the problems encountered in each step and the corresponding solutions:
Remote connection management MySQL, in general, there are three steps: (a main operation of the machine, b for the remote machine (MySQL installed on B, accessed by a B)
1, create a dedicated remote connection user in the connected MySQL wow;

2, modify the connected MySQL configuration file my.cnf, so that this MySQL not only support the local IP127.0.0.1 monitoring, but also support other IP monitoring, and restart the MySQL service, so that the configuration takes effect.

3, verify that the MySQL configuration port in a is consistent with B (all 3306?) ), if consistent, through Mysql-h B's Ip-u wow--port=3306-p, to connect remotely.

Here's a detailed description of each step:
First, create a dedicated remote connection user in the connected MySQL wow;
The reason to create a dedicated user is because MySQL was initially installed, and the default root account and other accounts are limited to localhost connections. On the remote machine, even if you log in with the same username and password, you will not be able to connect properly. For example, you log on to MySQL on the 192.168.11.12 machine using the root account on the remote machine 192.168.83.56:
Mysql-h 192.168.11.12-u Root-p, is unable to log in normally. At this point, need to log into the 192.168.11.12 machine, MySQL database into MySQL to view the user table, confirm the specific root permissions, especially its host is localhost or your corresponding IP, or%.

Workaround:
A, (create a dedicated remote user root or Wow in MySQL in the 192.168.11.12 machine), there are two ways to create a user and give permission:
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, "localhost" changed to "%". "%" means that all host can access it.
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, you want WOW to use MyPassword to connect to a MySQL server from any host.
Grant all privileges in *.* to ' wow ' @ '% ' identified by ' MyPassword ' with Grant OPTION;
If you want to allow only the user Wow from IP for 192.168.83.56 host to connect to the 192.168.11.12 MySQL server and use MyPassword as the password
Grant all privileges in *.* 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 in the MySQL database. Because MySQL puts all the permissions in the cache, it needs to be reloaded after the changes are done.
B, is it possible to use the grant authorization method after creating the user Wow and giving permission? Can't you? We can simply verify that. Mysql-h 192.168.11.12-u root-p on the remote machine 192.168.83.56 first, is unable to log on normally. Report 2003 of the Mistake:
ERROR 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.11.12 ' (111)
What's the reason? Is the account not established successfully? Permissions settings issues, or network configuration firewall and so on? or other configuration issues with MySQLno solution?
First rule out the problem of the account and permissions: In the localhost (192.168.11.12) machine, try to enter the database with WOW user. Mysql-u Wow-p MyPassword, newspaper 1045 's fault:
ERROR 1045 (28000): Access denied for user ' wow ' @ ' localhost ' (using Password:yes),
Why, then? How to create a new user on this machine can not log on MySQL, is it true that the user Wow did not create success? And then look at the user table, WOW does already have AH. A tangle, and finally found the reason, the original is installed in the process of MySQL did not delete anonymous account, delete anonymous account:
Mysql-u root-p
Mysql>use MySQL
Mysql>delete from user where user= ';
mysql>quit;
Try again mysql-u wow-p mypassword,wow user can log on in the machine normally, it appears that the account wow is no problem, then try the remote connection again:
Mysql-h 192.168.11.12-u Root-p, is unable to log on normally. Also reported 2003 's fault:
ERROR 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.11.12 ' (111),
Check the ErrorCode to troubleshoot the problem:
[Mysql@vvmvcs0 ~]$ perror 111
OS error code 111:connection refused
Is that a problem with the Internet? Ping xxx.xxx.xxx.12, you can ping, the machine is no problem. It seems to look at the configuration of the remote connection in MySQL's profile my.cnf, which goes into our second step.

2, modify the connected MySQL profile my.cnf so that this MySQL not only supports local IP127.0.0.1 listening, but also supports other IP listening, and restarts the MySQL service to make the configuration effective.
is primarily two configuration items: Skip_networking or bind_address, in order to cancel local listening, you need to comment out the configuration of these two items in my.cnf:
Normally, MySQL uses 3306 ports only to listen on IP 127.0.0.1, denying access to other IP (which can be viewed through netstat). Canceling local listening requires modifying my.cnf files:
            sudo vim/etc/mysql/ MY.CNF
           //Find the following, and comment
             bind-address = 127.0.0.1
             will then need to reboot MySQL (and reboot later). sudo mysql stop
Now we try again, on the remote machine 192.168.83.56 the following command to login to the 192.168.11.12 database: Mysql-h 192.168.11.12-u root -P, Still unable to log on properly. It's still 2003 wrong. And what is the reason? Oh, is the MySQL port configuration is unified issue!

3, verify that the MySQL configuration port in a is consistent with B (all 3306?). ), if consistent, through Mysql-h B's Ip-u wow--port=3306-p, to connect remotely. The MySQL listening port on the
192.168.83.56 is not configured with 3306, is 3308, And the 192.168.11.12 port is the default of 3306, the two do not match, if only on the 192.168.83.56 with Mysql-h 192.168.11.12-u root -p to log in, is to access the 192.168.11.12 3308 port, of course, can not access the. We also need to specify the correct port number for access to MySQL: mysql-h 192.168.11.12-u wow--port=3306-p.