MySQL has some special features when making a connection from a remote client that if we do not know can complicate access to a MySQL database from a different machine that hosts the database.
With other databases such as Oracle or SQL Server, once that no firewall or anything like that prevents us from the client machine access to the server, using normally data acces from a database user we can 'enter'.
With MySQL, although access to the port, usually 3306, is open, the database can be configured to shut out external connections, and the result is the same as if the port was closed by a firewall:
telnet mysql.dataprix.es 3306 Trying 188.166.233.199... telnet: connect to address 188.166.233.199: Connection refused telnet: Unable to connect to remote host
If you get this result should consult the file /etc/my.cnf, and checking for bind-address variable or skip-networking.
If skip-networking is and is not discussed, edit the file and delete it, or make a comment to have no effect and allow external connections:
#skip-networking
If you are bind-address = 127.0.0.1 or bind-address = localhost also need to edit and change the value for the external IP from which you want to connect (only allowed one) or 0.0.0.0 to leave passing all, and then filter them by other means (firewall or security at level access control)
bind-address=0.0.0.0
With this, after restarting the database, and the previous telnet should accept our external connection at 3306 port.
MySQL also has a security mechanism for access control that takes into account not only the user and password, IP or domain of the machine from which the connection is made. From the same server (localhost) you can usually connect, but if coming from a client or external server make sure that the combination IP / domain + user has permission to access the database.
If we have stood the test of telnet and try to connect to a user / password correctly we obtain an error message like this:
Access denied for user 'myuser'@'55.66.77.88' (using password: YES)
It's about connecting to the database with the administrator user, by logging into the ssh server, or with phpMyAdmin, for example, and the database 'mysql', see the value of 'Host' field for the 'User' with which it tries to connect.
If the value is 'localhost', this user can only connect from the same server, so the remote connection is rejected. If the value is '%' could connect from any machine, and would have to find another reason for the error message.
This value can be modified by the domain or the ip value of the machine from which you want to connect, and you can use wildcards such as % to allow ip ranges.
To view the different options, Chapter Access Control, Stage 1: Connection Verification of the MySQL reference manual explains in detail.
For example, to grant access and privileges to 'myuser' from the ip '55.66.77.88':
GRANT ALL PRIVILEGES ON db.* to myuser@'55.66.77.88' IDENTIFIED BY 'password'; flush privileges;
