WordPress, the content management system that does not require any introduction. But here is the fact, There are 313,050 million sites built on WordPress. And it has a 59.7% of market share in content management. With WordPress, you can host files and database on different servers/machines very quickly as it uses MySQL as a database management system. In this guide, we will configure WordPress with an external database in Ubuntu 16.04 servers.
First of all, What do we mean when we say WordPress with external database? It means that we are going to host WordPress files on one server(Application server). And we are going to host our WordPress database on another server(Database server).
Our goal is to execute MySQL queries on database-server and process and display data on application-server. There are many advantages of hosting application and a database on separate servers. Especially if your WordPress application is growing at a reasonable rate. Here are some benefits you get with this setup.
- Let’s say your application-server requires more computing power than your database-server. In this case, you can only upgrade your application-server and keep your database-server the same. This way, you can upgrade and downgrade servers based on the computing power they need.
- In the case of downtime, you can quickly know if it’s an issue on a database server or an application server. If you want high availability set up for your database, Follow this guide to create a master-slave replication in MySQL. With replication, you can keep your site up and running even if the main MySQL server is down.
- In case of monitoring, you can monitor the load and memory on the application server. While you can monitor queries per second, slow queries, read/write queries, and much more on the database server.
I just have mentioned three advantages here, but there are many more that you will experience. So, Let’s get started with the tutorial.
Configure WordPress with external database
What will you need to follow this guide? Well, you will need two Ubuntu 16.04 servers. Both the servers must be clean with only the operating system installed. And a root access to both the servers as we will install packages and update configuration files. Here are the IP addresses and hostnames we will use in this guide.
- Application server: application-server (1.2.3.4)
- Database server: database-server (4.5.6.7)
First of all, we will configure our application server. It is not going to be a typical LAMP server setup, because we do not have to configure MySQL on our application server. So, let’s get started.
CONFIGURE APPLICATION SERVER
On the application server, we will install Apache to serve the HTTP requests and PHP to interpret PHP code requested by Apache. And we are going to use the MySQL database hosted on the database server, so we do not have to install MySQL on this server.
Execute the following commands on the application server.
root@application-server:~# apt-get update
root@application-server:~# apt-get upgrade -y
root@application-server:~# apt-get install apache2 -y
root@application-server:~# apt-get install php7.0 php7.0-curl php7.0-mysql php7.0-mbstring php7.0-dom php7.0-gd -y
root@application-server:~# apt-get install libapache2-mod-php7.0
root@application-server:~# apt-get install mysql-client -y
The first four commands will update repositories, upgrade packages, install Apache, and install PHP7.0 along with the PHP libraries required for WordPress. And the last one will install MySQL client that we can use to test the remote connection with our database server.
To verify if Apache and PHP are installed and configured correctly, execute the following commands and then access the public IP address of the application server in the browser.
root@application-server:~# rm /var/www/html/index.html
root@application-server:~# echo “<?php phpinfo(); ?>” >> /var/www/html/index.php
On the browser, you should see a page with PHP configuration information just like the following image.
If you can see this page on the public IP address of your application server, you have successfully configured the application server. Now, we can move on to the configuration of our database server.
CONFIGURE DATABASE SERVER
Database server configuration is also easy. It is because we have to install MySQL and update the MySQL configuration file to allow remote access. Execute the following command to install MySQL on your database server.
root@database-server:~# apt-get update
root@database-server:~# apt-get upgrade -y
root@database-server:~# apt-get install mysql-server mysql-client -y
root@database-server:~# mysql_secure_installation
While executing the third command, you have to enter the MySQL root password. And while running the fourth command, You have to answer a few questions. Answer it like this.
- Enable Validate Password Plugin? No
- Change the password for root? No
- Remove anonymous users? Yes
- Disallow root login remotely? Yes
- Remove test database and access to it? Yes
- Reload privilege table now? Yes
Once done, try to log in to your MySQL server using the following command.
root@database-server:~# mysql -uroot -p
If you can log into your MySQL server, you have successfully installed MySQL on the server. Now, it is time to update the MySQL configuration file to allow remote connections. Execute the following command to open the MySQL configuration file in edit mode.
root@database-server:~# nano /etc/mysql/mysql.conf.d/mysqld.cnf
In the file, find a line containing bind-address. You have to uncomment this line and replace the default value with the IP address of your database server. The updated line should look like the following.
bind-address = 4.5.6.7
Do not forget to replace the example IP address with yours. Once done, press CTRL+X followed by Y followed by Enter to save the file. Now, restart the MySQL server using the following command to apply the new configuration.
root@database-server:~# service mysql restart
All the hard work is now over. Now, we can create a database for our WordPress application on the database server.
CREATE DATABASE FOR WORDPRESS ON DATABASE SERVER
First of all, log in to your MySQL server using the following command.
root@database-server:~# mysql -uroot -p;
Enter the password you have set while installing the MySQL server. Once you are in, run the following queries in MySQL to create a database and a user having access from our application server.
mysql> CREATE DATABASE wordpress;
mysql> CREATE USER ‘wordpressUser‘@’1.2.3.4‘ IDENTIFIED BY ‘qawsedrf123‘;
mysql> GRANT ALL PRIVILEGES ON wordpress.* TO ‘wordpressUser‘@’1.2.3.4‘;
mysql> FLUSH PRIVILEGES;
Now, if you want to test if the user and database are successfully created, login to your application server and execute the following command to log in to your MySQL server from your application server.
root@application-server:~# mysql -uwordpressUser -p -h4.5.6.7
It will ask you the password of the user we just created on our database server. With the correct password, you should be able to log in to your MySQL server. It is a very crucial step in the process of configuring WordPress with external database.
If you can, Congratulations! The remote connections are working correctly, and you can go to the next step.
DOWNLOAD WORDPRESS ON THE APPLICATION SERVER
In this step, we have to download the WordPress files on our Application server and establish the database connectivity in WordPress.
On the application server, execute the following commands to download the WordPress files and set the permissions correct.
root@application-server:~# cd /var/www/html
root@application-server:/var/www/html# rm -rf *
root@application-server:/var/www/html# wget https://wordpress.org/latest.tar.gz
root@application-server:/var/www/html# tar -xzvf latest.tar.gz
root@application-server:/var/www/html# mv wordpress/* ./
root@application-server:/var/www/html# rm wordpress latest.tar.gz
root@application-server:/var/www/html# chown -R www-data:www-data /var/www/html
In this series of commands, the first command will change the directory to /var/www/html and the second command will remove all the files inside that directory. The third and fourth command will download and extract WordPress files in the directory. The fifth will move WordPress files from the wordpress directory to the current directory. The sixth command will remove the extra directories and files that are no longer required. The last command will set the proper permissions on our /var/www/html directory where our WordPress files are stored.
INSTALL WORDPRESS FROM BROWSER
Finally, the downloading part of our WordPress installation process is done. So, we can now begin WordPress installation from the browser. Open up a new browser tab or window and access the public IP address of your application server.
NOTE: If you have already pointed the domain name to your application server, open the domain name in the browser.
As a result of everything we have done, you will find a language selection screen on your Application server’s public IP or domain. Just like the following image.
Select the language and submit the form. On the next step, you will find a database connectivity information card. Click on the Let’s go button given on the card. In the third step, You will find a form to fill up the database information.
Enter the Database name, username, password and the IP address of your database server in the form. For this example, the correct database configuration should look like the following image.
Do not forget to replace the database name, username, password, and Database host (The IP address of your database server) in the form. Once the information is entered correctly, press the Submit button to submit the form.
If the database information is not correct, you will see a page with an error message. However, If the database information is correct, you will see a page just like the following image.
If you get an error message after submitting the database information, I recommend you to verify the database connectivity using the method I mentioned in the previous step. However, if you see a page like the above-given image, click on the Run the installation button to move to the last step of WordPress installation. It should look like the following image.
Finally, After entering all the required information in the form, Click on the Install WordPress button to install the WordPress site.
Congratulations! You have successfully configured WordPress with the external database. With this setup, All the HTTP requests and PHP interpretation will be done on our application server. And all the database queries will be executed on our database server. It is very good and we have achieved our goal. If you are interested in optimizing your new WordPress installation, I recommend you to configure WordPress Redis object cache.
You might face some issues with database connectivity if you are doing it for the first time. If you are facing issues with Database connectivity, Make sure that the database information is correct and no firewall rule is blocking the requests coming on the database server. If the firewall is on, you can open the port 3306 to allow incoming connections on MySQL.
If you are facing any issue following this guide and need some help, please feel free to contact our support staff.