MySQL is the second most popular database management system in the world! The reason why it is so popular is, It allows us to scale our databases very easily and effectively. More than that, It’s open source (With name “MariaDB”). In this tutorial, I will show you how you can allow and secure MySQL remote connections on Ubuntu 16.04 machine.
There is only one way to allow remote connections on MySQL. But there are many ways to secure remote connections to your MySQL server. Each has it’s own advantages and complexity. In this case, We will get started with a clean Virtual Machine or Virtual Private Server, at the end of this tutorial, we will have a MySQL server that allows remote access and is protected by a Firewall.
To follow this guide, you will need a clean Ubuntu 16.04 server and root access. We will need root access to the server because we will install MySQL and update the configuration file to allow remote connections.
So, Let’s get started with the actual tutorial.
Install MySQL Server
It just takes few command to install MySQL server on your VM/VPS. Let’s update our repositories and install MySQL server on our machine. To complete this task, execute the commands given below as a root:
sudo apt-get update
sudo apt-get install mysql-server mysql-client -y
sudo mysql_secure_installation
The first command will update all the repositories, the second one will install the actual MySQL server and MySQL client so that we can work on our databases. In the third command, It will ask you few questions, Follow the below given instructions to give answers in yes or no.
Enter password for user root: YOUR_PASSWORD
Press y/Y for Yes, and other key for No: N
Change the password for root ? N
Remove anonymous users? Y
Disallow root login remotely? Y
Remove test database and access to it? Y
Reload privilege tables now? Y
Once done, Our MySQL server is installed and secured locally. Now, we have to enable the remote access by updating our configuration file.
Allow MySQL remote connections
Finally, It’s time to update our MySQL configuration file and bind the MySQL server to the public IP address. To complete this task, execute the following command in the terminal to open the MySQL configuration file for editing.
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Now, You will see a configuration file with many directives. The one directive we are looking for is bind-address. To find a line containing the text we want, Press CTRL+W and write down bind-address and then hit the enter key. You will automatically navigate to a line containing the following text.
bind-address = 127.0.0.1
Replace the local IP address with the Public IPv4 address of your server. For example, Let’s say it is 1.2.3.4. Then, the correct configuration should look like this:
bind-address = 1.2.3.4
After updating the IP address in this directive, press CTRL+X followed by Y and Enter. It will save the file with changes. However, we still have to restart the MySQL server to apply the changes.
To restart the MySQL server, execute the following command.
sudo service mysql restart
Once done, we have to create a MySQL user that has access from the MySQL client from which you want to connect to your MySQL server remotely.
Allow MySQL remote connections to MySQL users
MySQL is pretty secure by default. It is because, MySQL does not only verify username and password to authenticate user. But it also verifies the host of the user. Which means, the IP address of the MySQL client from which a user is trying to connect.
It means, before a user can connect to our MySQL server, we have to set an IP address of a user as host while creating a user. Execute the following command to log in to MySQL command line interface.
mysql -uroot -p;
It will ask you for the root password, Enter the root password you have set so that you can access MySQL command line interface. Once you are in, Execute the following commands to create a user with access from remote host.
CREATE USER ‘USERNAME‘@’REMOTE_IP‘ IDENTIFIED BY ‘PASSWORD‘;
CREATE DATABASE ‘EXAMPLE‘;
GRANT ALL ON EXAMPLE.* TO ‘USERNAME‘@’REMOTE_IP‘;
FLUSH PRIVILEGES;
Do not forget to replace the variables with your own values. You can set any username you want, any password you want, any database name you want and any Remote IP address you want.
We executed these commands to create a user with access from a specific IP address and a strong password. Then, we created an example database and granted all privileges on the example database to our user.
Congratulations! Now, you can access the MySQL server from remote server using the following command only from the remote IP you entered while creating a user. Again, do not forget to replace the IP address with your MySQL server’s IP address.
mysql -uroot -p -hMYSQL_SERVER_IP;
It will ask you for a password. With correct combination of your IP, username and password, you will be able to login.
Secure MySQL remote connections
As we know, MySQL is already a secure database management system. But we also want to secure it using Firewall. Which means, only the request from trusted ip address will reach MySQL. Other requests will be denied as soon as they reach the server.
So, we will use UFW to deny requests coming on the MySQL port (3306) except from the Remote IP we trust. So, every time you create a user with access from different Remote IP, you have to add rule in your UFW firewall configuration.
First of all, we have to allow access on SSH(22) port so that we can access the server via SSH. Then, we have to allow access to MySQL(3306) port to specific IP addresses only. It means, we have to deny all incoming connections first and then allow the connections as required.
Execute the following command to deny all the incoming requests, allow all the outgoing requests and allow requests on SSH(22) port.
sudo ufw default deny incoming
sudo ufw default allow outgoing
sudo ufw allow ssh
Now, we also have to allow incoming requests from REMOTE_IP we entered while creating a user in MySQL. To allow requests from the REMOTE_IP, execute the following command.
sudo ufw insert 1 allow from REMOTE_IP to any port 3306
Do not forget to replace the REMOTE_IP with the IP address from which you want to access the MySQL server. Once done, execute the following command to enable the firewall.
sudo ufw enable
It will show you that the firewall is active and enabled on system startup. It means, our remote MySQL connections are secure now. As our server would not accept the requests except it is sent from a trusted IP address with correct username and password.
Conclusion
This is how you can allow MySQL remote connections run securely. Using this method, you can separate your WordPress host server and a database server so that all the web requests and processing is done on the application server and the database queries are executed on the database server.
You can also perform master-slave replication in MySQL. With replication, you can make your application use two MySQL servers instead of one to read and write data which can divide the load between two database servers. If you are facing any issue following this guide, Please let us know, we are happy to help.