Remote access to MySQL

In general, most servers only allow connections MySQL to be done locally, that is, from the same Cloud Server and not from a server or other device.

In those situations where it is necessary to set up MySQL on a different server, in order not to overload the web frontal, for instance, some configurations must be made in order to connect remotely to the data bases.

Here are the steps to follow to set up this access:

1. Access your server through SSH as a “root” user.

   Additional Information

   You can find more information about the access through SSH to your server on the article:Access to Linux through SSH

2. You have to give remote access privileges to the database that you want to be accesible using the following commands:

    Note

   Remember that before launching the command you have to change:

   • “xxx.xxx.xxx.xxx“,for IP that will have access to the data base
   • “nombre_base_de_datos” for the name of the database
   • “usuario_base_de_datos” for the user that will do the connection

   # mysql -u root -p
   mysql> USE nombre_base_de_datos;
   mysql> update db set Host='xxx.xxx.xxx.xxx' where db=’nombre_base_de_datos‘;
   mysql> update user set Host='xxx.xxx.xxx.xxx' where user=’usuario_base_de_datos‘;

   An alternative to setting up manually the privileges of each database as described, would be to set them up for all the databases.

   Warning

   It can become a security problem to guarantee privileges for all the databases to the root user of the remote server. For most of the cases, it will be better to create a user with fewer privileges and that can access only the necessary database for the connection.

   To set up the privileges in all the databases, it would be necessary to use the following commands:

   mysql> GRANT ALL ON *.* to root@'xxx.xxx.xxx.xxx' IDENTIFIED BY ‘contraseña_de_root‘;
   mysql> FLUSH PRIVILEGES;
   mysql> exit;

3. It may be possible that our server has not set up the MySQL service listening only through the IP 127.0.0.1 (Binding), to check that out,we'll run:

   # netstat -tulpn | grep 'mysql\|mariadb'

   If it answers “0.0.0.0:3306” you can go to he next step. If it answers “127.0.0.1:3306”,it's necessaty to change the MySQL setu. In order to do so, we'll edit the /etc/mysql/my.cnf o /etc/my.cnf file, depending on the Linux distro that we use. We'll comment on the line that sets up the binding. It can be something similar to:

    bind-address = ::ffff:0.0.0.0

   Once commented, we'll reiniciate the MySQL service and we can go on with the next step:

    # service mysqld restart

4. Eventually, what you should do is enable port 3306 in the Iptables. A simple syntax to do it would be:

   # /sbin/iptables -A INPUT -i eth0 -p tcp –destination-port 3306 -j ACCEPT
   # service iptables save

    Nota

   Recuerda sustituir IP.de.tu.servidor por la dirección IP del servidor al que quieras acceder.

   Depending on the MySQL version it is possible that the syntax of the command changes. If the previous command returns an error back, you can try with the following line:

   # /sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
   # service iptables save