How to Enable MySQL Remote Connection on Your Raspberry Pi

ⓘ Cet article peut avoir été partiellement ou totalement traduit à l'aide d'outils automatiques. Nous nous excusons des erreurs que cela pourrait engendrer.

Today, we will be interested in MySQL, more precisely how to activate the remote connection to the database, it mean from another machine not connected to our Raspberry Pi.
The remote connection can be very useful if you have a server without an internal database.
Indeed, you can choose to separate these two parts in order to lighten your server in terms of query. This is especially what the hosts do, on shared servers.
In addition, opening the connection remotely allows to connect to the MySQL service from another IP (if this one have the authorization) and thus to manage its database without passing by SSH or other services.
For this tutorial we consider that you already have a Raspberry Pi with MySQL server installed.

Creating a dedicated MySQL user

First we will have to connect to the Raspberry Pi and then connect to MySQL in order to create a new user to manage a remote database.
However, we want to warn you, creating a remote connection increases the risk of attacks, so use a complicated username and password and avoid giving a user rights to all your tables (it’s better to use a dedicated user by base). Servers are too often the victim of attack such as brute force.
Here we will create a new user “user” with the password “password”. We will give it rights on the base “example” and no other.
To do this, we need to connect to MySQL as root with the following command:

mysql -u root –p

Your password “root” of MySQL is going to be asked, password that you informed on the installation of MySQL in the Raspberry Pi.
Now that we are connected, we will create the “example” database:

CREATE DATABASE example;

We will use the order “GRANT”, use to define the privileges of a user.
Here, this user will do all the actions of his choice, from any computer, but only on the basis of “example”.

GRANT ALL PRIVILEGES ON example.* TO user@'%' IDENTIFIED BY 'the_password';

This command contains a lot of information that we will analyze. Note that it’s not necessary to have a user already created, if the given one does not exist, it will be created automatically.
literally, you can reads this command like this:
Give all the rights on the “example” database and its tables to the user “user” who can connect from anywhere, with the password “the_password”.

Let’s have a look at this command:

• “example. *” means that we use the database “example”, and “. *” means that this applies to all tables.
• “user” means that the assignment of privileges will be done for the user named “user”
• @’%’ is the IP address of the remote machine to which you are granting access rights. Here “%” is equivalent to saying “all addresses”. A precise address can be used !

Once done, you must tell MySQL to apply the changes made, with the FLUSH command:

flush privileges;

You can now exit MySQL with the command “exit”.

Configure MySQL to accept external connections to Raspberry Pi

Now that the rights have been given, we will have to tell to MySQL that we want to accept external connections to the Raspberry Pi.
To do this, we will edit the MySQL configuration file located in the “/etc/mysql” folder:

sudo nano /etc/mysql/my.cnf

We just have to comment the line bind-address, to comment the line add a ‘#’ to the beginning of it, like this:

#bind-address = 127.0.0.1

By default, MySQL is listening only to local connections (127.0.0.1). Commenting this line makes it possible to delete this security and thus, to recover the external connections!
To apply these changes, restart MySQL with the following line:

/etc/init.d/mysql restart

Modify the MySQL port (optional)

As we said at the beginning of the article, servers activating the remote connection to MySQL are often victims of attacks.

In order to greatly reduce the number of these attacks, we advise you to modify the MySQL port.
By default, MySQL is configured to listen on port 3306, choose a free port, on our side we chose port 8457, port not used by other software or major protocols.
The change is done by modifying the “my.conf” file located in the “/ etc / mysql” folder.

sudo nano /etc/mysql/my.cnf

This file contains two parts, one part intended for the client and the other part for MySQL Server, it’s this second part that interests us.
In the section following the line “[mysqld]” change the line:

port = 3306

Replacing it by

port = 8457

Here we choose the port 8457, but you can choose another one, paying attention to the fact that it isn’t used by another program / protocol.
Finally, restart MySQL to apply the changes:

sudo service mysql restart

Conclusion

Do not forget to open the TCP ports of your box, in order to redirect the stream to your Raspberry Pi. You can visit our tutorial web server raspbian dydns port forwarding which explains perfectly how to proceed.
If you want to mount an efficient database server, choose a good SD card like this. You will find advice on our article about accessories in this page.