Troubleshoot MySQL database connectivity issues

Set MySQL remote connection root permissions

In the remote connection to MySQL should have encountered, the root user can not connect to the remote MySQL, only local connection, foreign refused to connect. You need to establish a database account that allows remote logins so that you can operate the database remotely. By default, the MySQL database's system database MySQL system table user rights within users only provide localhost native login, need to change permissions to implement remote connection MySQL database.

Confirm by the following methods:

Root#mysql-h localhost-uroot-p
Enter Password: ******
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 4 to server Version:4.0.20a-debug
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer.
mysql> use MySQL; (This DB holds various configuration information for MySQL)
Database changed
Mysql> Select Host, user, password from user; (view user's permissions)
+-----------+------+-------------------------------------------+
| Host | user | password |
+-----------+------+-------------------------------------------+
| localhost | Root | *4acfe3202a5ff5cf467898fc58aab1d615029441 |
| 127.0.0.1 | Root | *4acfe3202a5ff5cf467898fc58aab1d615029441 |
| localhost | | |
+-----------+------+-------------------------------------------+
4 rows in Set (0.01 sec)
As you can see, you can only access it as a host of localhost.

Workaround:

Mysql> Grant All privileges on * * to ' root ' @ '% ' identified by ' kdm001 ' with Grant option;
(% means all external machines, if a certain machine is specified, the% is changed to the corresponding machine name; ' root ' means the username to be used.)
mysql> flush Privileges; (Run this sentence to take effect, or restart MySQL)
Query OK, 0 rows affected (0.03 sec)
Check again:
Mysql> Select Host, user, password from user;
+-----------+------+-------------------------------------------+
| Host | user | password |
+-----------+------+-------------------------------------------+
| localhost | Root | *4acfe3202a5ff5cf467898fc58aab1d615029441 |
| 127.0.0.1 | Root | *4acfe3202a5ff5cf467898fc58aab1d615029441 |
| localhost | | |
| % | Root | *4acfe3202a5ff5cf467898fc58aab1d615029441 |
+-----------+------+-------------------------------------------+
4 rows in Set (0.01 sec)
It can be seen that a new user has been added, quit, try the effect .... You can now successfully log in ...

If this is not resolved:

1) Make sure the MySQL service is started: service MySQL start
2) Try to ping the destination IP on the command line at the end of the installed SQLyog to ensure that it can ping the
3) Check the MySQL user name and permissions on the server that needs to be connected to ensure that the user name and password entered by SQLyog are correct
4) View user permissions on the server side that needs to be connected: show grants; If only grants [email protected] Line:
Reset Password mysql> set password for ' root ' @ ' 172.16.124.1 ' =password (' coship ');
mysql> flush Privileges;
5) Remember to turn off VMware's firewall
6) Open the/etc/my.cnf file, bind-address = ******** The front plus #, comment out
Add two more sentences:
Skip-external-locking
Skip-name-resolve
7) A very important step!!! Restart Mysql:service mysql Restart

MySQL Database remote Access Setup method

1. Modify localhost
Change the "host" entry in the "User" table in the "MySQL" database, changing from "localhost" to "%"
Mysql>use MySQL;
Mysql>update User Set host = '% ' where user = ' root ';
Mysql>select host, user from user;
Mysql>flush privileges;

2. Designated authorization
Connect to the MySQL server from any host using Myuser/mypassword:
GRANT all privileges on * * to ' myuser ' @ ' percent ' identified by ' MyPassword ' with GRANT OPTION;

Use Myuser/mypassword to connect to the MySQL server from a host with IP 192.168.225.166:
GRANT all privileges on * * to ' myuser ' @ ' 192.168.225.166 ' identified by ' MyPassword ' with GRANT OPTION;

3. Pan-Authorized
Mysql-h Localhost-u Root
Mysql>grant all privileges on * * to ' root ' @ '% ' with GRANT OPTION; Give any host access to the data as root
Mysql>flush privileges;

troubleshoot problems with MySQL not being able to connect remotely

1, MySQL port is correct
View port occupancy through NETSTAT-NTLP, typically 3306 for the lower end. The use of the tool to connect to MySQL is to use the port. For example, My admin\my Query browser\mysql front and so on.

2. Check whether the user rights are correct
The user table of the MySQL library has two records: host is localhost and% (for security,% can be replaced with IP you need to connect externally).

3. See if the skip-networking has been/etc/mysql/my.cnf
Need to be dropped
Error: Error 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.51.112 ' (111)

sudo gedit/etc/mysql/my.cnf
#skip-external-locking
#bind-address = 127.0.0.1
Skip-name-resolve

4, check whether the iptables is stopped, does not close the case, can not connect
By: Service Iptables stop is temporarily closed.
Error: Error 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.51.112 ' (113)