MySQL: remote access when it should not

<meta charset="utf-8">
In the well-connected to local MySQL server remotely, If it does not have access.
When this happens, then haebomyeo Inspect the contents of the test if you can see the problem.


When remote access is that you may experience an error message is as follows.

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

If the connection fails, ERROR 2003, the bottom of the check list (1) begins.
If the authentication fails with ERROR 1045, the following checklist of (7) begins.



* Configuration file (my.cnf / my.ini) is given the option of a priority than the execution be very high, with the option to run the configuration file is important to look closely.

(1) skip-networking
mysql server at runtime - skip-networking option, or, with skip-networking in the configuration file, the server allows only local connections are in the Unix socket. Is set to be released.


(2) bind-address
Bind-address value in the configuration file or in the Run option - bind-address 127.0.0.1 or is disabled, make sure the value. This, listen and related TCP / IP socket to the binding of any IP to determine whether or not to. 127.0.0.1 does not allow outside access to the means. This system is assigned a value or specify a particular IP, 0.0.0.0 designated as the system has to be modified to take advantage of all the IP addresses. If you have more than one IP address assigned, it is common to specify the appropriate value.


(3) port
TCP / IP socket is used for binding to determine the port number. The default value is 3306, for security reasons or on a single server to run multiple MySQL servers, so if you change this value to check. Run option - port = 3306, the configuration file is specified as the port = 3306.


(4) listen Check
shell> netstat-na | grep 3306
Through the above command, make sure that that port is being LISTEN. Can not be resolved until 1-3 once again checked. Be sure to change the settings you must restart the server after. (In other cases, the value of the port settings, enter the port number).


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

Although the same name localhost and 127.0.0.1, localhost, if you specify a Unix socket connection is attempted.
In the local TCP / IP connection fails, up to 1-4 once again checked.


(6) Firewall
In the local TCP / IP socket connections are good, if a Telnet connection from a remote location should not see a try.
shell> telnet-h xxx.xxx.xxx.xxx-P 3306

Is normally up to 1-5, Telnet connections not possible in most cases, it is blocking at the firewall. Firewall tries to connect to the host's local firewall, the connection that is the target server's firewall, and routing within the firewall is placed in its path and can be.
- The local firewall: Certain outgoing packets to determine whether this restriction is being.
- Server Firewall: MySQL server uses the port to check for the firewall options.


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

host is specified as follows.
-%: Allow connections from any host
- Xxx.xxx.xxx.xxx: allow access from specific hosts
- Xxx.xxx.%, Such as: allow access from a particular band
- Specific names: allow access from specific hosts (skip-name-resolve if you are using can not be used)

MySQL's LIKE as the syntax used to, a particular band or a whole host of% can be specified.
For example) 192.168.0.0/16 => 192.168.%


(8) Confirm password
Users who are used to connect to / host / password combination is correct.
mysql> SELECT COUNT (*) FROM user WHERE user = 'user name' AND host = 'host' AND password = PASSWORD ('password')

For the name of the user to have multiple records, the most 'specific' first record, will use the specified value.
(See: http://dev.mysql.com/doc/refman/5.0/en/connection-access.html)

For example, user1 @% and when there are records of user1@192.168.0.1, 192.168.0.1 in the user1 user tries to connect to the host when, user1 @% of the value from the values ​​without the use of user1@192.168.0.1 to perform authentication, so that if multiple records with the user name and password to set a permission requires more attention.


(9) FLUSH PRIVILEGES
involved in the user table and permissions add the values ​​of the table / with the following syntax must be changed if the server is to use the new authentication data.
mysql> FLUSH PRIVILEGES;


(10) skip-name-resolve
skip-name-resolve option is not specified, MySQL server, remote access, IP address of the host name resolution via DNS goes through the process. The system's DNS server is installed, if you do not set up correctly, domain name resolving may be caused by a delay in connection, if the server is installed on the internal network to enable this value is common.

It tries to access the host's IP is obtained through DNS names rather than IP address with the name of the user table to see, so it may specify, to avoid confusion is over.


(11) Other special circumstances
New password from MySQL 4.1, by default I'm using a hash function, the legacy client pre-4.1 / ODBC connector to use them if you see the link below the server's secure-auth, old-passwords option is properly adjusted, and the should be used.
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html

1-10 of the course, despite all geochyeoteumedo, 1045, if an error occurs, the following command via the user's password, specify the old value of the hash, then see and do a connection test. Although uncommon, MySQL 5 version of the mysql client versus the old-password hashing if you were using.
mysql> SET PASSWORD FOR 'user' @ 'host' = OLD_PASSWORD ('password');