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…
