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
# 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
# The link address from the MySQL page
#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.
# 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
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;
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.
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 = 126.96.36.199
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'@'%';
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.