WordPress, DB Replication & Sync

Recently we received an “odd” request from one of our clients. Basically, they wanted to have a CMS based on WordPress (no problem, right?), but with a possibility to replicate the CMS twice and keep them all up to date at the same time (for example, something that is updated on CMS 1 has to be replicated on the other two).

“Mmmmkay, is there a reason for such a specific functionality”, we asked. The answer was — load balancer and multiple backup servers that will host the “same CMS”. Ah, now we’re talking! (: Something out of the ordinary, could be fun, right? And in the end, it was. This is how we solved it.

There is a load balancer that will handle traffic and redirect clients to multiple servers. Each of them will host master replicated CMS and in case of a sudden crash, it will auto-redirect to the first available backup server (kind of neat, right?). What follows is a mini tutorial to provide some guidance on how to setup CMS replication on multiple servers and how to keep WordPress database/uploads folder synced between servers.

Keep in mind that this will be intended for local development purpose and it’s not production ready setup.

We are going to use VirtualBox and Centos 7 (image ready boxes can be found on osboxes.org, w/l use those).

Prerequisites:

1. Three virtual servers with Centos 7

2. Basic understanding of NGINX and MariaDB

3. Basic WordPress knowledge

4. Li’l bit of your time

<! Starting point !>

Let’s hop in! Our structure is as follows:

1. 192.168.0.73 Master server
2. 192.168.0.74 Slave server 1
3. 192.168.0.78 Slave server 2

NGINX SETUP (apply on all three servers):

Login with ssh:

ssh root@192.168.0.73

Before we proceed, do a yum update.

yum update -y

<! NGINX Setup !>

Next step: install NGINX.

yum install nginx

Then start service:

systemctl start nginx

… and if errors occur, probably SELinux

setenforce O
systemctl restart nginx
systemctl enable nginx -> this enables nginx on start

The default nginx document root on CentOS 7 is /usr/share/nginx/html/. The configuration files are found under /etc/nginx directory. Test NGINX on your ip, ex. 192.168.0.73 (you should get a nginx welcome message).

<! Firewall Setup !>

Run the following commands in the terminal to allow HTTP requests through the firewall:

firewall-cmd --permanent --add-service=http
firewall-cmd --reload

<! PHP Setup !>

In this step, we will install PHP 7.2 from EPEL repo. Run these commands:

yum install yum-utils
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
yum install http://rpms.remirepo.net/enterprise/remi-release-7.rpm
yum-config-manager --enable remi-php72

When this is done, lets install PHP 7.2 and some PHP packages:

yum install php72 php72-php-fpm php72-php-mysqlnd php72-php-opcache php72-php-xml php72-php-xmlrpc php72-php-gd php72-php-mbstring php72-php-json

Now test if your php installation works.

php72 -v

Now let’s create PHP symbolic link:

ln -s /usr/bin/php72 /usr/bin/php

Important part! W/l set cgi.fix_pathinfo in php.ini to 0 and save ourselves any possible headaches with exploits that can happen because of this.

Open php.ini with your preferred editor, w/l use nano:

nano /etc/opt/remi/php72/php.ini

Find cgi.fix_pathinfo and set I to 0:

cgi.fix_pathinfo=0

Next step is to edit php-fpm config, we need to setup our user and group, and some configuration for spare servers as well. Open the config file:

nano /etc/php-fpm.d/www.conf

And find this part:

user=apache → change to nginx
group=apache → change to nginx
pm.min_spare_servers = 5 → uncomment if commented
pm.max_spare_servers = 35 → uncomment if commented

Now, lets create our wordpress config for nginx and proper folders.

run nano /etc/nginx/conf.d/wordpress.conf

Paste this inside the newly created file:

server {
     listen 80;
     server_name your-domain.com;
     access_log /usr/share/nginx/your-domain.com/logs/access.log;        #logs folder
     error_log /usr/share/nginx/your-domain.com/logs/error.log;
     location / {
       root /usr/share/nginx/your-domain.com;
       index index.php index.html index.htm;
       if (-f $request_filename) {
         expires 30d;
         break;
       }
       if (!-e $request_filename) {
         rewrite ^(.+)$ /index.php?q=$1 last;
       }
      }
      location ~ .php$ {
           fastcgi_pass   localhost:9000;  # port where FastCGI   processes were spawned
           fastcgi_index  index.php;
           fastcgi_param  SCRIPT_FILENAME   /usr/share/nginx/your-domain.com$fastcgi_script_name;  # same path as above
           fastcgi_param PATH_INFO             $fastcgi_script_name;
           include /etc/nginx/fastcgi_params;
        }
  }

Since we are under local dev, we can create an alias domain for ourselves. Open up hosts file in etc folder:

nano /etc/hosts
127.0.0.1 localhost.localdomain localhost your-domain.com

Alright, now let’s create our project/logs in NGINX directory:

mkdir /usr/share/nginx/your-domain.com
mkdir /usr/share/nginx/your-domain.com/logs

Let’s verify our nginx config:

run nginx -t

Desired output:

nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful

All right, lets boot up our NGINX and php-fpm:

setenforce 0
systemctl restart php-fpm
systemctl enable php-fpm
systemctl restart nginx

If no errors are displayed at this time, we are ready to proceed with MariaDB setup!

<! MariaDB Setup !>

First let’s install MariaDB.

run yum install mariadb-server

Start and enable service:

systemctl start mariadb
systemctl enable mariadb

Check if MariaDB is active and running:

systemctl status mariadb

If status is active, we can proceed with secure installation. Run this command:

mysql_secure_installation

Key points:

1. Remove test database

2. Set up root password

3. Remove anonymous user

4. Disable root remote login

Once this is complete, we login to MariaDB and create a new database and its user.

mysql -u root -p
CREATE DATABASE wordpress;
//create user for database, keep in mind this is not replication user, w/l use separate one
CREATE USER 'wpuser'@'localhost' IDENTIFIED BY 'wppassword';
//grant PRIVILEGES
GRANT ALL PRIVILEGES ON wordpress.* TO 'wpuser'@'localhost';
//check if your database exists
SHOW DATABASES;
//check if user exists
select User, Host from mysql.user;
exit;

Before we start replication process, both master and slaves have to have the same database data! Assuming that you have WordPress website, export your local database and save it for now. Repeat this process on both slave servers (MariaDB setup).

All right, lets config our master database (192.168.0.73). Edit /etc/my.cnf file. Under the [mysqld] section add the following:

log-bin
server_id=1 #unique identifier, prefered for master to be 1
replicate-do-db=wordpress #database name for replication
bind-address=192.168.0.73 #master server IP

Restart MariaDB and login to database afterwards.

systemctl restart mariadb
mysql -u root -p

In the next step we will create replication user with proper permissions and find our master position and log bin (very important for slave setup).

MariaDB [(none)]> CREATE USER 'slave'@'localhost' IDENTIFIED BY 'SlavePassword';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'SlavePassword' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; //make sure to lock it
MariaDB [(none)]> SHOW MASTER STATUS; //this will open up database entry that shows collumn "file and "position", save somewhere both entries

Exit database and import dump sql of your project:

mysql -u root -p wordpress < your_file.sql //path to your .sql dump

Login back to MariaDB, unlock tables and exit:

mysql -u root -p
MariaDB [(none)]> UNLOCK TABLES;
MariaDB [(none)]> exit;

You can copy the exact same .sql dump to slave server, w/l import it there as well!

Now, update system tables:

mysql_upgrade -u root -p

Firewall service:

# firewall-cmd –add-service=mysql
# firewall-cmd --add-service=mysql --permanent
# firewall-cmd --reload

Next step, transfer your WordPress site to the new location. You can do it via scp or for instance use Winscp.

Transfer location is: /usr/share/nginx/your-domain.com.

A few tips:

1. Check if uploads folder has the right permissions

2. Change WordPress folder ownership chown -R nginx:nginx /usr/share/nginx/your-domain.com/ (if you remember, we setup in our php-fpm config, user/group to nginx!)

3. Setup proper home/site url, database connection in wp-config.php

4. Your site should work now.

<! Slave Server !>

All right, this is it, configuration on Master server is done, now we need to set up our Slave servers! Login to your slave server, ex.1:

ssh root@192.168.0.74

From now on, we will assume that nginx/mariadb/php and php fpm are installed on both slave servers!

Let’s connect to MariaDB and execute a few commands:

mysql -u root -p

Create user and grant permissions!

MariaDB [(none)]> CREATE DATABASE wordpress;
MariaDB [(none)]> GRANT ALL PRIVILEGES ON wordpress.* TO 'slave'@'localhost' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;

Import .sql dump from master server!

mysql -u root -p wordpress < your_file.sql //path to your .sql dump

Edit /etc/my.cnf file. Under the [mysqld] section, add following:

log-bin
server_id=2 //slave ID
replicate-do-db=wordpress //database name for replication
bind-address=192.168.0.74 //slave server IP

Restart MariaDB and upgrade system tables:

systemctl restart mariadb
mysql_upgrade -u root -p

Last step is allowing replication. Login back to mysql and execute this command:

CHANGE MASTER TO
MASTER_HOST='192.168.0.73', //master server ip
MASTER_USER='slave',
MASTER_PASSWORD='SlavePassword',
MASTER_PORT=3306,
MASTER_LOG_FILE='master-bin.000028', //this is shown in "SHOW MASRER STATUS" on master server
MASTER_LOG_POS=245, //this is shown in "SHOW MASRER STATUS" on master server
MASTER_CONNECT_RETRY=10,
MASTER_USE_GTID=current_pos;

Start slave and check the status:

SLAVE START;
SHOW SLAVE STATUS\G;

If no errors are shown in status, replication is set.

Replication should work now if you change data on the master server. Keep in mind that all data should be changed only on master database since it will replicate on all slave servers. For instance, when you create a new post it will be created on all slave servers as well. That’s the point (: . Repeat this process on the third slave server, don’t forget to change server ip in my.cnf file and its ID, it should be unique.

Now, we did handle database sync between our master and slaves, but one problem still remains: the uploads folder. We need to sync our images/files across all servers. There is a handy package called Lsyncd, so let’s set it up! Since this is for dev purpose, w/l use ssh keys and login via root. Its recommended that for production you have a separate user with limited access. Also, this package is needed only on master server since w/l sync all data from master server only!

<! Lsyncd Setup !>

Assuming the following:

Master Server’s IP = 192.168.0.73
Slave Server’s 1 IP = 192.168.0.74
Directory to be Sync = /usr/share/nginx/your-domain.com/wp-content/uploads/

Generate ssh key and copy to slave server:

ssh-keygen
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.74 //slave server IP

Let’s install some packages:

yum install lua lua-devel pkgconfig gcc asciidoc
rpm -iUvh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm
yum install lsyncd

Let’s copy config file:

cp /usr/share/doc/lsyncd-2.1.5/examples/lrsync.lua /etc/lsyncd.conf //version can be different *2.1.5*!

Edit the config:

settings {
logfile = "/var/log/lsyncd/lsyncd.log",
statusFile = "/var/log/lsyncd/lsyncd-status.log",
statusInterval = 20
}
sync {
default.rsync,
source="/usr/share/nginx/your-domain.com/wp-content/uploads/",
target="192.168.0.74:/usr/share/nginx/your-domain2.com/wp-content/uploads/", //slave source
rsync = {
compress = true,
acls = true,
verbose = true,
rsh = "/usr/bin/ssh -p 22 -o StrictHostKeyChecking=no" }
}

Keep in mind that you can have as many sync {} rules as you need!

Start and enable service:

systemctl start lsyncd
systemctl enable lsyncd
ln -s '/usr/lib/systemd/system/lsyncd.service' '/etc/systemd/system/multi-user.target.wants/lsyncd.service'

You can preview log with tail -f /var/log/lsyncd.log

If you now upload a new image on master server (media directory in WordPress), the sync should pop up in your log file and the data is synced across all servers that are defined in sync rules. Quite handy, right?

At this point you should have a working Master→Slave→Slave setup. Both database and uploads folder are synced on any change made on master CMS. Keep in mind that changes have to be made only on Master CMS since it will be responsible for data distribution.

So in the end, as a result of this “unusual” request from our client, we created for ourselves a working blueprint for any future projects. On websites and web apps that are expected to have “higher” traffic, it’s an excellent solution for providing a smooth experience for your visitors. On the other hand, in production env you will probably have dedicated servers for both CMS and databases. This should help you a bit with possible struggles when deploying your apps on closed networks that use multiple servers structure and backups of backups*. Ah, the DMZ ):

What about the load balancer, you might ask? That’s a subject for a whole new article, so stay tuned for more info. (:

Also, you can find more details about LEMP configuration for centos 7 on the excellent DigitalOcean Guidelines. Once again, keep in mind that when you deploy something like this in production, you should spend more time on strengthening your configuration and handle firewall rules a bit more seriously.

@guidelines sources DigitalOcean

Aleksandar Luketić , Senior WordPress Developers at euroart93 agency.