Configure WP 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.
Prerequisite:
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
Go through the steps in the URL to install
https://www.coimbatorewebhosting.com/blog/easy-steps-to-install-and-configure-lamp-in-centos-7/
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 = xxx.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 = xxx.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 [email protected] 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.
You have successfully configured WordPress to use a remote database on a CentOS 7server.
