How to Configure WordPress to use a Remote Database

by

How to Configure WordPress to use a Remote Database

WordPress is the most popular content management system (CMS) based on PHP and MySQL. When your WordPress CMS grows in traffic and you have outgrown your current server resources, a professional solution is to host your database on a separate database server. With this solution, you can optimize your database and your web server independently and both servers can grown on it’s own machine. In this tutorial we will guide you on how to configure WordPress to use a remote database, on a CentOS 7 based VPS.

Requirements

In order to run WordPress on your CentOS 7 VPS and configure it to use a remote database, we need the following requirements pre-installed:

  • A Web VPS on which we will install the WordPress instance.
  • A Database VPS with MariaDB or MySQL installed on it. The database will be hosted on this VPS.

WordPress requires the following:

  • Web server: Apache, Nginx
  • PHP version 7.2 or newer, with JSON support, mbstring, zip and GD2 extensions.
  • MariaDB version 10.0 or greater or MySQL database server version 5.6 or newer

Step 1: Log in via SSH on both servers:

Log in to each VPS via SSH as user root

ssh roo@IP_Address -p Port_number

Step 2: Update all packages

Once you are logged, run the following command on both servers to make sure that all installed RPM packages are up to date

yum -y update

Step 3: Install MariaDB server on the Database VPS

yum -y mariadb mariadb-server

Step 4: Install LAMP stack on a Web VPS

As mentioned in the requirements section of the tutorial, a LAMP stack (Apache, MySQL/MariaDB and PHP) is required to run WordPress on the server. We will start with installing the Apache web server:

yum -y install httpd

After the Apache installation is complete, start the web server and enable it to start upon server boot:

systemctl enable httpd

PHP 5.4 is installed on CentOS 7 by default.

In order to install and use PHP 7.2, we need to install EPEL and REMI repositories:

yum install epel-release

rpm -Uvh http://rpms.remirepo.net/enterprise/remi-release-7.rpm

yum install yum-utils

yum-config-manager --enable remi-php72

yum update

Next, install PHP 7.2 along with the required PHP extensions:

yum -y install php php-cli php-mbstring php-gd php-mysqlnd php-xmlrpc php-xml php-zip php-curl

And finally, complete the LAMP installation by installing MariaDB client package:

yum -y install mariadb mariadb-server

Start the service and set it to start on reboot

systemctl start mariadb
systemctl enable mariadb

To accept remote connections, edit the MariaDB configuration file (/etc/my.cnf.d/server.cnf) and change the following line:

bind-address = 127.0.0.1

with:

bind-address = web_server_IP_address

Do not forget to replace ‘web_server_IP_address’ with the public IP of the web server.

If you want to configure MariaDB to listen on all interfaces on the web VPS, set:

bind-address = 0.0.0.0

Restart MariaDB for the changes to take effect:

systemctl restart mariadb.service

Step 5: Create a MariaDB database for WordPress on the Database VPS

Log in to MariaDB console with the root user account:

# mysql -u root -p

Create a new MariaDB database for WordPress using the following query:

mysql> CREATE DATABASE wpdb;

Create a new MariaDB user for WordPress using the following query:

mysql> CREATE USER 'wpuser'@'localhost';
mysql> CREATE USER 'wpuser'@'database_VPS_IP';

Then execute the following query to add a separate user for WordPress that will interact with the MariaDB database:

mysql> GRANT ALL PRIVILEGES ON wpdb.* to 'wpuser'@'localhost' IDENTIFIED BY '5tr0ng_Pa55w0rd';
mysql> GRANT ALL PRIVILEGES ON wpdb.* to 'wpuser'@'database_VPS_IP' IDENTIFIED BY '5tr0ng_Pa55w0rd';

Do not forget to replace database_VPS_IP with the actual IP address of the database VPS .

Execute the following command to apply the privileges we set:

mysql> FLUSH PRIVILEGES;

Now we can exit the MariaDB session:

mysql> quit

Step 6: Configure the MariaDB server on database VPS to listen on public IP (or all interfaces)

Edit the MariaDB configuration file (/etc/my.cnf.d/server.cnf) and change the following line:

bind-address = 127.0.0.1

with:

bind-address = database_server_IP_address

Or, configure MariaDB to listen on all interfaces on the database VPS:

bind-address = 0.0.0.0

Restart MariaDB for the changes to take effect:

systemctl restart mariadb.service

Step 7: Install WordPress on the Web VPS

Download the latest WordPress version available at https://wordpress.org/download/ and extract it in a directory on your server:

wget https://wordpress.org/latest.zip

unzip -d /var/www/html/ latest.zip

Set proper permissions on WordPress files and directories:

chown apache:apache -R /var/www/html/wordpress/

Rename wp-config-sample.php WordPress configuration file to wp-config.php:

mv /var/www/html/wordpress/wp-config-sample.php /var/www/html/wordpress/wp-config.php

Edit the wp-config.php file and modify the following lines

vi /var/www/html/wordpress/wp-config.php
 
/** The name of the database for WordPress */
define('DB_NAME', 'wpdb');

/** MySQL database username */
define('DB_USER', 'wpuser');

/** MySQL database password */
define('DB_PASSWORD', '5tr0ng_Pa55w0rd');

/** MySQL hostname */
define('DB_HOST', 'database_VPS_IP');

Step 8: Configure Apache to serve WordPress

Now we will have to setup the Apache configuration so it can serve the WordPress directory. Add the contents below in the /etc/httpd/conf.d/wordpress.conf file using vi or your favorite editor:

# vi /etc/httpd/conf.d/wordpress.conf

Add the following lines:

<VirtualHost *:80>
ServerAdmin admin@your-domain.com
DocumentRoot /var/www/html/wordpress
ServerName your-domain.com
ServerAlias www.your-domain.com

Alias /matomo “/var/www/html/wordpress/”
<Directory /var/www/html/wordpress/>
Options +FollowSymlinks
AllowOverride All

</Directory>

ErrorLog /var/log/httpd/wordpress-error_log
CustomLog /var/log/httpd/wordpress-access_log common
</VirtualHost>

Save the changes and restart Apache for the changes to take effect:

systemctl restart httpd

Open http://your-domain.com in your favorite web browser and finish the WordPress installation.

Congratulations. You have successfully configured WordPress to use a remote database on a CentOS 7 server.

Of course you don’t have to configure WordPress to use a remote database on CentOS 7, if you use one of our Managed CentOS VPS Hosting Solutions, in which case you can simply ask our expert Linux admins to set up your WordPress to use a remotely hosted database for you. They are available 24×7 and will take care of your request immediately.

PS. If you liked this post on how to configure WordPress to use a remote database on a CentOS 7 server,  please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.

Leave a Comment