MySQL: Remote can not connect when

<meta charset="utf-8">
Local connection to the MySQL server, can not connect from a remote location if
When this happens, try and test the contents of the following checks, the problem can be identified. Haebomyeon


Remote access error messages that you may experience are as follows.

ERROR 2003 (HY000): Can not connect to MySQL server on '...'
ERROR 1045 (28000): Access denied for user 'user' @ 'host' (using password: ...)

If the connection fails, ERROR 2003 (1) from the start of the checklist below.
If the authentication fails, the ERROR 1045 (7) from the start of the checklist below.



Now that the high priority given the option of run-time * configuration file (my.cnf / my.ini) than with the configuration file, run the option to look closely is important.

(1) skip-networking
mysql server running on the - skip-networking option skip-networking in the configuration file, or if the server will allow only local Unix socket connection. If you set off.


(2) bind-address
127.0.0.1 - bind-address value in the bind-address value or the option of running or is disabled, make sure that the configuration file. Which determine any IP you want to listen and related TCP / IP socket binding. 127.0.0.1, means that it does not allow external connection. Modified to take advantage of, the system has all the IP address 0.0.0.0, or the value specified in the system is assigned a particular IP. Allocated IP address if you have two or more, it is common to specify the appropriate values.


(3) port
Should be used to bind the TCP / IP socket port number. The default value is 3306, but for security reasons, or to run multiple MySQL servers on a single server, if you have changed the so determine this value. The option to run the - port = 3306, port = 3306 as a configuration file to specify.


Determine whether (4) listen
shell> netstat-na | grep 3306
Make sure that through the above command, the port is to LISTEN. If you can not verify, 1-3 to check once again. Must restart the server after you change the settings. (In other cases, the port settings, enter the port number.)


(5) TCP / IP socket connection attempt
mysql.sock file to access mysql client running on the local common.
After checking the above options, the local TCP / IP socket connection try.
shell> mysql-h 127.0.0.1-P 3306-u root-p

to specify localhost to localhost and 127.0.0.1 of the same name, but if you will try to connect to Unix socket.
1-4 once again to check if the local TCP / IP connection failed.


(6) firewall
The local TCP / IP socket connection, which Once a Telnet connection from a remote location should not try.
shell> telnet-h xxx.xxx.xxx.xxx-P 3306

1-5 is normal until, if telnet connection is not possible, on the firewall that is blocking. Firewall attempts to connect to a host of local firewall, access the server's firewall, and routing in firewalls, etc. that lie in its path, can be
- Local firewall should determine whether a particular outgoing packets be restricted.
Make sure the server firewall: MySQL server port that is being used for the firewall option.


(7) user / host combination confirmed
From my mysql database to be used to connect the user name and host address is registered correctly.
mysql> USE mysql;
mysql> SELECT user, host FROM user WHERE user = 'user name';

host is specified as follows.
-% Allow access from all hosts:
- Xxx.xxx.xxx.xxx: allow connections from a specific host
- Xxx.xxx.% Allow access from a certain band of
Allow connections from a specific host-specific name: (skip-name-resolve if not available)

%, As the syntax used MySQL's LIKE you can specify a particular band, or a whole host of
Example) 192.168.0.0/16 => 192.168%


(8) Confirm Password
Make sure that you are using to connect to the user / host / password combination is valid.
mysql> SELECT COUNT (*) FROM user WHERE user = 'user name' AND host = 'host' AND password = PASSWORD ('password')

For your user name if there are multiple records, most of them specify a specific 'value' records first use.
(See: http://dev.mysql.com/doc/refman/5.0/en/connection-access.html)

For example, user1 @% and user1@192.168.0.1 records exist, attempt to access user1 as a user on the host 192.168.0.1 If user1% of the value without using the value of the user1@192.168.0.1 perform authentication, so if multiple records in your user name and permission password set requires more attention.


(9) FLUSH PRIVILEGES
table of values ​​related to the user tables and permissions to add / change if the server with the following syntax is to use the new authentication data.
mysql> FLUSH PRIVILEGES;


(10) skip-name-resolve
skip-name-resolve option is not specified, the MySQL server go through a process to check the IP of the remote host to connect via DNS name. Value for this parameter if the server is installed on the internal network to enable access by domain name resolving of DNS server is installed in the system is not set up correctly, if the delay may be caused, it is generally

Attempts to connect to the host's IP via DNS if the name is obtained by name rather than by IP user table, so in order to avoid confusion it through are specified.


(11) Other special circumstances
MySQL 4.1 from the new password hash function is enabled by default, which, if you are using a legacy client / ODBC connector 4.1 or earlier of the server's secure-auth, old-passwords option, refer to the link below to properly adjust the should be used.
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html

All been a process of 1-10, despite the fact that the 1045 error occurs if the user's password via the following command specifies the hash value of the old way, then carried the connection test looks. Rare, but if there was a single MySQL 5 version of mysql client to use the old-password hashing.
mysql> SET PASSWORD FOR 'user' @ 'host' = OLD_PASSWORD ('password');