How To Setup And Configure A Remote MySQL Database Server

A Remote MySQL Database Server is a database server that is located in a different location from your production server. Using a different server to house your databases can have some great security benefits. In a standard cPanel environment, the database server is located on the same server as the website. Should a website on the cPanel server be compromised the database for that website would also be compromised. By using a Remove MySQL Database Server you can protect against this. We’re going to set up a remote MySQL database server to house a test environment data.

Before you proceed with this you must think it over. Your database server must be reliable. If this server is ever offline so will your website so ideally, you need to have some sort of high availability setup. High availability is beyond the scope of this article but for our setup, we are using an NVMe VPS with Debian 10 installed. You can use any Operating System and follow this guide. The only difference in commands is when installing MySQL.

Once you have completed the setup. Don’t forget to Secure Your Remote MYSQL Server.

Install MySQL Server

On your database server first, update the server then install the MySQL setup packages

# Update server
apt update

# Install Gnupg
apt install gnupg

# Install lsb-release
apt install lsb-release

The page at https://dev.mysql.com/downloads/repo/apt/ lists the latest MySQL version available. Open this page and click the download button. On the next page right-click the link “No thanks, just start my download” and select “Copy Link Address” Inside your MySQL server issue the following commands

#Move to temp
cd /tmp

# The link address from the MySQL page
wget https://dev.mysql.com/get/mysql-apt-config_0.8.19-1_all.deb

#List Directory
ls

#Install MySQL Setup
dpkg -i mysql-apt-config*

On-screen now is the MySQL setup. You want the latest version of MySQL so press enter, select the latest version from the top list and press enter again. On the menu page select ok to proceed.

Now you are back at the console. Issue the update command to get the correct MySQL files and finally, install MySQL.

# Update 
apt update

# Install MySQL
apt install mysql-community-server

Configure Remote MySQL Server

On-screen now you should see a message asking for a MySQL root password. Enter password. On the next page, you will see a message about the MySQL Authentication Plugin. If you are going to be importing databases to this server think carefully. If you enable this option and your passwords do not meet the new security policy you will have issues importing databases. We’re creating a new database for this guide so we are going to enable this option. If you’re going to be importing databases, do not install this feature.

Once installed, check the MySQL service is online.

<code>systemctl status mysql.service</code>

MySQL should now be installed, configured and online. Next, secure the MySQL install

mysql_secure_installation

Go through the pages answering Y to all of the options. This will also disable root logins but in our security tweaks later we will be using a network firewall to disallow traffic anyway. Next, open up the MySQl conf file;

nano /etc/mysql/mysql.conf.d/mysqld.cnf

In this file, we are going to allow remote connections to our MySQL database server. Get the IPv4 of your server and add it to the file like in the example below. We have added the port section and the bind-address section. Ensure you use your server’s main IPv4 address.

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
port            = 3306
bind-address    = 188.165.4.114

Save and close the file and restart the MySQL service.

systemctl restart mysql

Create a Remote MySQL Database and User

So far we have installed MySQL and secured it. We have also configured MySQL to list on the server’s IPv4 address. Now we need to create a database and user that is allowed to connect remotely. Login to MySQL

mysql -u root -p mysql

Create Remote Database

Create the database. Our database is going to be named delta.

mysql> CREATE DATABASE delta;

Create a Remote MySQL User

Now you have a database it’s time to create a user. We must create a user for localhost and % then assign that user to all databases.

CREATE USER 'db-user'@'localhost' IDENTIFIED BY 'db-pass';
CREATE USER 'db-user'@'%' IDENTIFIED BY 'db-pass';

You run both commands. The only difference in the commands is ‘localhost’ and ‘%’ the user and the password should be the same in both commands.

Allow Database Privileges

Now assign that user to all databases. You can also just specify the database name but our server doesn’t have any other databases on it.

GRANT ALL ON *.* TO 'db-user'@'localhost';
GRANT ALL ON *.* TO 'db-user'@'%';
flush privileges;

Allow Remote Connections From IP

You need to allow connections to your database server. So, where you have your production site, get the IP of this website and all that IP to your database. Replace xx.xx.xx.xx with your websites IP and the database name and user.

update db set Host='xx.xx.xx.xx' where Db='db-name';
update user set Host='xx.xx.xx.xx' where user='db-user';

At this point, you could import an existing MySQL database to your server. You should now be ready to use your remote MySQL server. To test our setup we have uploaded a copy of WordPress. We’re going to install it using the database we just created on our Remote MYSQL Server. Let’s give it a try.

Note the database host is the IP of the Remote MYSQL Server.

So we have now completed the setup of our Remote MySQL Database server, set up and configured a user and allowed remote connections to our database server. We have installed WordPress on our production site and during the installation used the MySQL credentials from our remote database server.

You should also configure a firewall on your server to protect your data. When using an F2H instance you can set your network firewall policy to block all incoming connections except from your production website’s IP. At the very least, block all connections and just open your MySQL port which will be 3306.