Parsing remote connection to manage MYSQL databases on other machines _ MySQL

Resolve remote connection to manage MYSQL database bitsCN.com on other machines

In the development process, you sometimes need to remotely connect to and manage the MYSQL database on another machine. in the implementation process, you may encounter a series of problems, now, we use remote access to MYSQL data installed on Ubuntu as an example (the default port is Port 3306) to describe the configuration steps and the problems encountered in each step and the corresponding solutions:
Remote connection management MYSQL,Generally, there are three steps: (A is the master machine and B is the remote machine. (MYSQL is installed on B and A accesses 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 listening for local IP127.0.0.1, but also listening for other IP addresses, and restart the Mysql service to make the configuration take effect.

3. verify that the MYSQL configuration port in A is consistent with that in B (both 3306 and ?), If they are consistent, use mysql-h B's ip address-u wow -- port = 3306-p to remotely connect.

Next, we will describe each step in detail:
1. create a dedicated remote connection user wow in the connected MYSQL;
A special user is created because the default root account and other accounts are only used for localhost connection during the initial installation of MYSQL. On a remote machine, even if you log on with the same user name and password, the connection will fail. For example, you can log on to Mysql on the machine 192.168.11.12 with the root account on the remote machine 192.168.83.56:
Mysql-h 192.168.11.12-u root-p. In this case, you need to log on to the host 192.168.11.12, go to the mysql database in mysql to view the user table, and confirm the specific root permission, especially whether the host is localhost, your corresponding IP address, or %.

Solution:
A, (create A special remote user root or wow in MYSQL on 192.168.11.12). There are two ways to create A user and grant permissions:
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, change the "host" item in the "user" table in the "mysql" database, and change "localhost" to "% ". "%" Means that all hosts can be accessed.
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 use mypassword to connect to the mysql server from any host.
Grant all privileges on *. * TO 'wow' @ '% 'identified BY 'mypassword' with grant option;
If you want to allow the user wow to connect to the mysql server of 192.168.11.12 from the host whose ip address is 192.168.83.56, 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 statement indicates that the permission data is reloaded from the grant table of the mysql database. Because MySQL puts all permissions in the cache, it needs to be reloaded after modification.
B. Can it be used after the GRANT authorization method is used to create the user wow and GRANT permissions? No? Let's verify it. Mysql-h 192.168.11.12-u root-p on the remote machine 192.168.83.56 is not allowed to log on normally. Error 2003:
ERROR 2003 (HY000): Can't connect to MySQL server on '192. 168.11.12 '(192)
Why? Is the account not successfully created? Permission setting problems, or network configuration firewall problems? Other mysql configurationsNot solved?
First, eliminate the account and permission issues: On the localhost (192.168.11.12) machine, try to use the wow user to enter the database. Mysql-u wow-p mypassword, error 1045:
ERROR 1045 (28000): Access denied for user 'wow' @ 'localhost' (using password: YES ),
Why? How can I create a new user that cannot log on to MYSQL on the local machine? is it true that wow is not created successfully? Check the user table again. wow does already exist. After a tangle, I finally found the cause. it turned out that the anonymous account was not deleted during mysql installation and configuration, and 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. The wow user can log on to the machine normally. it seems that the account wow is correct. then try the remote connection again:
Mysql-h 192.168.11.12-u root-p. Error 2003 is also reported:
ERROR 2003 (HY000): Can't connect to MySQL server on '1970. 168.11.12 '(192 ),
Check the error code to troubleshoot the problem:
[Mysql @ vvmvcs0 ~] $ Perror111
OS error code 111: Connection refused
Is it a network problem? Ping xxx. xxx. xxx.12. you can PING the host. It seems that we have to check the remote connection configuration in mysql configuration file my. cnf, which is our second step ..

2. modify the configuration file my. cnf of the connected MYSQL so that this MYSQL not only supports listening for local IP127.0.0.1, but also listening for other IP addresses, and restart the Mysql service to make the configuration take effect.
There are two main configuration items: skip_networking or bind_address. to cancel the local listening, comment out the configuration in my. cnf:
Under normal circumstances, mysql only listens to Port 3306 on the IP address 127.0.0.1, and denies access from other IP addresses (which can be viewed through netstat ). To cancel a local listener, you need to modify the my. cnf file:
Sudo vim/etc/mysql/my. cnf
// Locate the following content and comment out
Bind-address = 127.0.0.1
Then you need to restart mysql (you can restart mysql later ). Sudo mysql stop
Now let's try again. on the remote machine 192.168.83.56, run the following command to log on to the database 192.168.11.12: mysql-h 192.168.11.12-u root-p. Or 2003 error. Why? Oh, the problem is whether mysql port configuration is uniform!

3. verify that the MYSQL configuration port in A is consistent with that in B (both 3306 and ?), If they are consistent, use mysql-h B's ip address-u wow -- port = 3306-p to remotely connect.
The mysql listening port on 192.168.83.56 is configured with 3306 instead of 3308, and the port of 192.168.11.12 is the default 3306, which does not match, if you only use mysql-h 192.168.11.12-u root-p to log on to 192.168.83.56, you can access port 3308 of 192.168.11.12. We also need to specify the correct port number for accessing mysql: mysql-h 192.168.11.12-u wow -- port = 3306-p.

BitsCN.com