In this tutorial, we will learn how to enable remote connections on a MySQL / MariaDB database. It’s common to see the web server and the database server on the same server: it works well, and it’s a way to save resources as only one machine is needed. Depending on your environment, and especially if it is a distributed infrastructure, chances are that the two roles are independent: the Web server on the one hand, the database server on the other.
By default, MySQL (or MariaDB) only accepts local connections since it listens on the “127.0.0.1” interface corresponding to the local address. Because of this, if it’s a remote web server that needs to be connecting to the database for the web application to work, that’s a problem. This implies a specific configuration of MySQL in order to authorize remote connections: this is what we will see.
For this tutorial, I use a Debian 11 machine with MariaDB but the same procedure applies with MySQL (by adapting the paths).
II. MySQL: modify the listening IP address
First, we need to configure the instance to listen on an IP address other than “127.0.0.1”. For MariaDB, the file to modify is the following:
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
While for MySQL, it will be this file:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
In this file, you will find the following line:
bind-address = 127.0.0.1
You have to replace this IP address with your server’s IP address which will be used by the web server to contact the database server. If you specify “0.0.0.0”, then the MySQL server will listen on all its interfaces. In this example, I am indicating the IP address of my MySQL / MariaDB server, namely “192.168.100.14”.
bind-address = 192.168.100.14
Here is an example :
Save the file and restart the MySQL service:
sudo systemctl restart mysql
The first step is complete!
III. MySQL: change listening port
By default, MySQL listens on port “3306”. This is known information that can be easily found in official documentation. To hide this service which will now be exposed in a limited way, we can modify the listening port. It is not an obligation, but it is recommended.
For MariaDB, you will need to modify this file:
sudo nano /etc/mysql/mariadb.cnf
For MySQL, you will need to modify this file:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
The “port” direction is used to specify the port number you wish to use. For example, to use port “33061”, we specify:
port = 33061
Then save the file and restart the MySQL service again. Be careful, if there are active connections on port 3306, they will be cut and they will no longer work!
IV. MySQL: allow remote connections in the firewall
If your Linux machine’s firewall is active and configured, it should block remote streams to your MySQL server, whether on port 3306 or another port. We need to configure the local firewall, either via IPtables, UFW or FirewallD (depending on the system).
Using UFW, here’s how to allow incoming connections on port “33061” only for the remote host with IP address “192.168.100.51”. This is the IP address of my web server.
sudo ufw allow from 192.168.100.51 to any port 33061
The message “Rule added” is returned in the console. Alright, the stream should be allowed.
V. MySQL: grant remote access to the database
We still have one last authorization to give, within the database itself, for a specific user. Logically, you must use a dedicated database for your application and associate a dedicated user with it.
From the MySQL server, you must connect to the instance with the root account to make changes to the configuration:
mysql -u root -p
Enter the root account password. Here you are, you are connected: the MySQL / MariaDB prompt is displayed on the screen.
If the MySQL database does not exist, it will be necessary to create it first:
CREATE DATABASE 'db_itconnect';
If the web application database already exists, rights must be added to it with the GRANT command. To allow remote connections from IP address”192.168.100.51” for the user “flo“with password”MonSuperMotDePasseMySQL“on the database”db_itconnect“, we will use this command:
GRANT ALL PRIVILEGES ON db_itconnect.* TO 'flo'@'192.168.100.51' IDENTIFIED BY 'MonSuperMotDePasseMySQL' WITH GRANT OPTION;
Note: In the previous command, using “%” instead of the IP address allows to indicate “all hosts“.
Then, we update the privileges before exiting:
FLUSH PRIVILEGES; EXIT;
That’s it, there’s one last step: test the remote connection to our MySQL / MariaDB database!
VI. MySQL: test the remote connection
From web server with IP address”192.168.100.51“, it is time to make a connection attempt. Logically, according to our configuration, this is the only IP address authorized to connect remotely to the database server. For this connection, we will have to specify the username, the IP address of the host i.e. the MySQL server, and the port number if it has been changed.
Which give :
mysql -u flo -p -h 192.168.100.14 -P 33061
Here is some information about the command above:
- -u : username
- -p : ask to enter the password (once the command has been executed)
- -h : IP address or host name of the MySQL server
- -P : port number, if not specified the port number 3306 is used implicitly
It’s done, the remote connection works! Now all that remains is to test within your application! When specifying the MySQL server, remember to specify the number if it is not the default, using the following syntax: 192.168.100.51:33061.
Thanks to this tutorial, you are able to configure your MySQL or MariaDB server to allow a remote machine to connect to a database!
Base de données,BDD,Configuration,Mysql,serveur,
#enable #remote #connections