Install Wordpress with remote database on Debian

Overview

In the following post we’ll cover the scenario where we’re going to install Wordpress with remote database on Debian. In other words, we’ll have an environment were we will have two separate servers, one which will have only Wordpress installation(meaning it will host the website only) and the other will have only the database(act only as a database server).

There are many beneficial reasons why this kind of environment is setup - one being the ease of website maintenance and database maintenance, performance increase(meaning the wordpress since it’s not dependent on one server, it will take much less resources).

But the downside is - since we have two servers now, the server maintenance will increase.

This setup will be configured on two Debian servers(both in the same network/same LAN).

The stack will be LEMP - Nginx web server, PHP and MariaDB server.

Database server configuration

We’ll focus first on the database server:

1. Install the MariaDB server

sudo apt install mariadb-server

Run the mysql_secure_installation

sudo mysql_secure_installation

2. Edit the database config file and change the bind-address parameter and enter the private address of your database server:

The file to edit:

sudo /etc/mysql/mariadb.conf.d/50-server.cnf

Line to edit - bind-address = 127.0.0.1

bind-address = 192.168.56.252

Save and exit, restart and enable the mariadb.service.

sudo systemctl restart mariadb.service

sudo systemctl enabled mariadb.service

To find out the IP address of your database server run the - ip a command.

3. Create the database, the user and set privileges(info in the commands are used as an example)

sudo mysql -u root -p
CREATE DATABASE wptest;
GRANT ALL ON wptest.\* TO 'wpadmin'@'192.168.56.251' IDENTIFIED BY 'StrongPassword';
FLUSH PRIVILEGES;
exit

On the database it’s necessary to create a remote user, aka the user account that our Wordpress installation will use to connect on the database. It’s necessary that you need to know in front the IP address of the Wordpress server and specify it/bind it to the remote user account.

Firewall configuration

If you have a running firewall on your database server, then it’s necessary to open the port the 3306 in order to accept the connection from the web server and to access the database. Just to be on the safe side, it’s good to configure the rules to accept only the connections only from our web server.

UFW firewall config

sudo ufw allow from 192.168.56.251 to any port 3306

IPTABLES config

iptables -I INPUT -p tcp -s 192.168.56.251 --dport 3306 -j ACCEPT

Web server configuration

1. Install Nginx web server with other necessary php libraries to run Wordpress and to communicate with the database server and mysql/mariadb client.

sudo apt install nginx php-cli php-fpm php-mysql php-json php-opcache php-mbstring php-xml php-gd php-curl

sudo apt install mariadb-client

We can test the connection to the database server if we run this command from our web server(essentially, logging in remotely to the databas):

sudo mysql -u remote-admin -h 192.168.56.252 -p

2. Install Wordpress, configure it and configure Nginx. Here we’ll now configure only the part of Wordpress where we need to add the address of our remote database server. If you need te detailed steps how to install Wordpress and Configure Nginx, you can find the instructions on this post.

cd /var/www/html/wordpress/

sudo mv wp-config-sample.php wp-config.php

sudo nano wp-config.php

At the database line, replace the localhost with the IP address of your database server. Refer to the picture bellow as example:

3. Create the Nginx conf file and remove the default one:

sudo nano /etc/nginx/sites-available/wordpress.conf

sudo ln -s /etc/nginx/sites-available/wordpress.conf /etc/nginx/sites-enabled/

The Nginx conf you can use, you can locate on the same post we mentioned earlier.

sudo rm /etc/nginx/sites-available/default

sudo rm /etc/nignx/sites-enabled/default

After this configuration you should be able now to run Wordpress, access it’s remote database and finish the Wordpress installation.

And now you should have a running Wordpress site with separate web and database server. You can stop here if you’re satisfied with the setup.These were the necessary steps how to install Wordpress with remote database, and by this point, everything should be up and running. Otherwise continue on the next and final chapter how to secure and encrypt the database connection and traffic between the web and database server and to make the setup more secure.

Secure and encrypt the database connection with an SSL(Optional but recommended)

It’s also a good security practice and additional layer of security to secure and the encrypt the traffic between the web and database server. The reason why is that if not secure and encrypted, and IF somehow an attacker breaks into your private subnet(the private LAN network where are the servers located) or the web server, the attacker then can intercept and sniff the traffic(monitor it and get data) which can result to breaching and hacking your website and the servers.

Database server certificate

1. On both servers(web and database), make a temporary directory for storing the certificates(example ssl or certs) in your home directory for an example or somewhere easy for you to store them - as an example, here we’ll make the certs directory on our home directory:

cd

mkdir certs

We’ll first start with the database server, so switch to the new certs directory we just created:

cd certs

2. On the database server we’re going to generate the CA key, certificate and the private key. Follow the prompts and answer accordingly when prompted. You can use pictures below as reference. One thing to watch out is for the Common name(which is quite important) prompt. It’s asking for database server name. Here you should add IP address of the server(like we have) or domain name of the server if you have or host name. The info must be matching since we’ll create the server certificates and client keys to match.:

sudo openssl genrsa 4096 > ca-key.pem
sudo openssl req -new -x509 -nodes -days 1825 -key ca-key.pem -out cacert.pem

In the second command, the attribute -days 1825 is referring that the created certiicate will be valid for 5 years(1825 days). You can set the days as you want or need.

3. Next is to create the RSA private key and sign the certificate:

sudo openssl rsa -in server-key.pem -out server-key.pem



sudo openssl req -newkey rsa:4096 -days 1825 -nodes -keyout server-key.pem -out server-req.pem

sudo openssl x509 -req -in server-req.pem -days 1825 -CA cacert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Now we can move the certs to a permanent location:

sudo mkdir /etc/mysql/ssl

sudo mv \*.\* /etc/mysql/ssl

cd /etc/mysql/ssl

Web server certificate

4. Generate the client certificate. The prompts will be the same like for the database server but on the Common Name question, enter the details of the web server where Wodpress is installed(like the web server’s IP address). Write the RSA and sign the certificate:

sudo openssl req -newkey rsa:2048 -days 1825 -nodes -keyout client-key.pem -out client-req.pem

sudo openssl rsa -in client-key.pem -out client-key.pem

sudo openssl x509 -req -in client-req.pem -days 1825 -CA cacert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

SSL configuration for both servers

Database server setup

1. Next step is to configure our servers to use the certifcates we created and enable the SSL connection between the web and database server. First edit the config on the MariaDB server config:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

And find the following lines, un-comment them add rewrite the lines that they are matching the directories and files of our certificates we created:

ssl-ca=/etc/mysql/ssl/cacert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

Save file and exit.

2. Now login on MariDB as root and run the following queries to enable on the server that it will allow connection from the web server only with SSL:

sudo mysql -u root -p
GRANT ALL PRIVILEGES ON wptest.\* TO 'wpadmin'@'192.168.56.251' REQUIRE SSL;
FLUSH PRIVILEGES;
exit

Restart the database server:

sudo systemctl restart mariadb.service

Web server setup

3. While still in the directory /etc/mysql/ssl (or move here), with the scp tool, copy over the client certificates to the web server(webuser and the IP address replace with the actual user name and IP address of your web server):

scp cacert.pem client-cert.pem client-key.pem [email protected]:~/certs

Now switch/login to the web server and move the certs somewhere permanent as well(the same as for the database server):

sudo mkdir /etc/mysql/ssl

cd certs

sudo mv /certs/\*.\* /etc/mysql/ssl

Edit the configuration file for the MariaDB client we installed in the first step. It has the same location as the server has:

sudo nano /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf

And find the same SSL(should be under [mysql] section) line as we did for the database server and have them matching the location and files names of certificates we added in the /etc/mysql/ssl directory:

[mysql]
ssl-ca=/etc/mysql/ssl/cacert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem

Save the file, exit.

You can test if the connection is working with remote login:

mysql -u wpadmin -h 192.168.56.252 -p

4. Last file to edit is again the wp-config.php. We need to there as well the SSL config line:

sudo nano /var/www/html/wordpress/wp-config.php

and add the line before the line with database name:

define( ‘MYSQL_CLIENT_FLAGS’, MYSQLI_CLIENT_SSL );

Refer to the picture:

Restart the Nginx web server, and finish the Wordpress installation from the web browser. It shoud be up and running.

Summary

To summarize this article, we covered the steps how to install Wordpress with remote database. We also covered the process how to secure the connection between the two servers(if you haven’t experienced before the self signed certificates and how to work with, you can skip the step, as said above, it’s not necessary, only a recomendation). The basic process itself on how to install Wordpress with remote database is not that difficult and provides couple of benefits, one being the performance and the maintenance of web server can be now rather easier and reduce the downtimes when maintenance is needed.

Thank you for your time…