Raspberry Pi 2: Configuring it as a Complete WordPress Web Server – Part 5 – Syncing and Scripts

This article is a continuation from this post. Previously, we have provisioned our RasPi 2 and installed BIND, Apache, PHP and MySQL on it. We also transferred DNS zones from our Windows DNS system to the RasPi.

Before we proceed, let me explain the topology I have come up with. This also serves for a reason to the madness explored in this post.

• I have a pre-existing Windows system that used to run my blogs. I now have a RasPi 2 device that we are in the process of configuring. The two are connected to the Internet over an Ethernet router that connects to my ISP over a 100 Mbps connection.
• The RasPi system will take over serving the previously Windows-hosted WordPress websites to the Internet. However, I want it to do only that. I continue to run a copy of the same on the Windows system as well.
• My router has a simple feature where I can edit the internal IP address of where I want the requests to be routed. I can do this separately for DNS and Web requests, using both IP address and port.
• “Failover” from one to the other is designed to be simple — I will use the router feature to flip the IP address from one system to the other depending on where I want the DNS lookup or HTTP request to land.
• There is a beautiful script provided on this StackExchange DBA thread for creating MySQL Master/Slave replication. Among all the various tutorials I found on the Internet, this was the one that worked the most painlessly. But though everything said it was configured correctly, I found data did not move from one system to the other. So I abandoned trying to replicate (not just that, I actually needed to go a step further to get Master/Master replication working because data would need to be on both systems to flip back).

With all the above in mind and the fact that I still needed to somehow keep the WordPress files (PHP scripts and content files) up to date on both systems, I decided to write my own scripts. These would fire from the RasPi system using CRON (the Linux version of “scheduled tasks”) and from the Windows system using Task Scheduler fired PowerShell scripts.

In this article, I will walk you through the Linux scripts for this. I have two scripts – one to sync the WordPress files and the other to sync the MySQL databases. The two are run from the main crontab. Here we go!

I am using WinSCP and its editor here extensively.

Folders

In /etc folder, create a new folder called “cron.sync” (/etc/cron.sync). Here’s where our sync scripts will live. Also, in /var/log folder, create a folder called “wordpress.sync” (/var/log/wordpress.sync). Our sync scripts will log to this folder when run via cron.

We will be loading the files via a share from Windows (see immediately below), we need a place to mount this share on RasPi. So create the folders under “mnt” called “windows/wordpress”.

# mkdir -p /etc/cron.sync
# mkdir -p /var/log/wordpress.sync
# mkdir -p /mnt/windows/wordpress

The “-p” will ensure the mkdir creates the folders only if they do not already exist. mkdir will also create the entire folder structure (so don’t worry about creating “/mnt/windows” first).

On the Windows system, share out the folders that contain the WordPress websites (I have all of them rooted under a single folder, so this is easy). Ensure that “Everyone” has READ access to this share. Also ensure the MySQL port (3306) is open.

Key Pre-requisite

A very critical pre-requisite to all this succeeding is that in your WordPress / MySQL configuration on the Windows system, you have configured the connection to “localhost”, with the default port (3306). If this is the case, in your “wp-config.php” file for every WordPress site you are migrating, the following entry should exist:

define( 'DB_HOST', 'localhost' );

Script to Sync WordPress Files

Navigate to the /etc/cron.sync folder, right-click, New > File and name it as “sync.wordpress.disk_files.sh“. WinSCP will directly open the file for editing. The file is not created on disk yet, so hit the floppy icon (CTRL+S) to save it now.

This script is going to copy the WordPress files from the Windows system to the local RasPi system. It will additionally add the required virtual hosts to Apache and restart Apache. In this script, I am going to handle the workflow as follows:

1. We check if the Windows system is online. We do this by executing a PING and checking if there was at least one result. If PING failed, we abort the run.
2. I have previously shared out the WordPress sites directory on the Windows machine. I check if this share is mounted on RasPi at a designated location. If not, I mount it — if it fails, again I abort.
3. Create the folder structure where the sites will be locally hosted, copy the files over using “rsync” (it lets me copy over only updated files).
4. I check Apache to see if the virtual host exists for each website and if not, create and enable it.
5. Finally, Apache is restarted.

Here is the full script. Substitute the items in bold with information specific to your setup.

#!/bin/bash

# This file synchronizes disk files of WordPress blogs
# We only sync from the Windows machine (source) to the RasPi (destination)

echo "Checking if Windows system is online..."
ping -c 1 192.168.1.10 | grep icmp* | wc -l &> /dev/null
if [ $? -ne 0 ]
then
	echo "Windows system is not online. Aborting Sync."
else 
	echo "Checking if folders are mounted..."
	mount | grep "/mnt/windows/wordpress" &> /dev/null
	if [ $? -ne 0 ] 
	then
		mount | grep "/mnt/windows/wordpress"
		if [ $? -eq 0 ] 
		then
			echo "Unable to mount WordPress share. Aborting!"
			exit 1
		fi
	fi

	echo "Creating directories if they do not exist..."
	mkdir -p /web/foo

	echo "Syncing [foo]..."
	rsync -ra /mnt/windows/wordpress/foo/* /web/foo

	# repeat the echo / rsync for each site to be sync'ed

	echo "Setting WordPress permissions (Apache2 gets write permissions on folder!)..."
	chown -R www-data:root /web

	echo "Creating websites in Apache if missing..."
	
	APACHE_VHOSTFILE=/etc/apache2/sites-available/foo.local.conf
	APACHE_VHOSTNAME="foo.local"
	APACHE_VHOSTDIR="/web/foo"

	ls ${APACHE_VHOSTFILE} &> /dev/null
	if [ $? -ne 0 ]
	then
		echo "Creating ${APACHE_VHOSTNAME}..."
		echo "<VirtualHost 192.168.1.20:80>" >> ${APACHE_VHOSTFILE}
		echo "DocumentRoot \"${APACHE_VHOSTDIR}\"" >> ${APACHE_VHOSTFILE}
		echo "ServerName \"${APACHE_VHOSTNAME}\"" >> ${APACHE_VHOSTFILE}
		echo "<Directory \"${APACHE_VHOSTDIR}\">" >> ${APACHE_VHOSTFILE}
		echo " " >> ${APACHE_VHOSTFILE}
		echo "</Directory>" >> ${APACHE_VHOSTFILE}
		echo "</VirtualHost>" >> ${APACHE_VHOSTFILE}

		ln -s 
        /etc/apache2/sites-available/foo.local.conf /etc/apache2/sites-enabled/foo.local.conf
	fi

	# repeat from APACHE_VHOSTFILE to the fi line for each site...

	service apache2 restart
fi

echo "Sync Run completed."

If you know the Apache options and want to add additional options to the Apache virtual host directives, add the same in the echo lines in the if block.

Save the file and close the editor. Back in WinSCP, press CTRL+R to refresh the directory listing (it should now show the new file). Right-click the file, select Properties. Check ON all the “R” and “X” boxes and click OK.

Test the Script

Log on using PuTTY,

# cd /etc/cron.sync

# ./sync.wordpress.disk_files.sh

The script should run and show you the relevant output. Note this will actually cause your files to be synced and the necessary Apache configuration done and restarted. You should be able to open a browser, point it to the website and see it running (don’t forget to either flip your router’s HTTP or the internal DNSs. A record for the site to point to the RasPi first). You can examine that you get the new header value as well using the F12 developer tools of the browser. Note though that most likely, your site will complain with a large “Error connecting to the database“. This is because we have not yet done the MySQL part (coming up right below)!

Script to Sync MySQL Databases

Back in WinSCP, in the “/etc/cron.sync” folder, right-click New > File and name it as “sync.wordpress.mysql.sh“. WinSCP will directly open the file for editing. The file is not created on disk yet, so hit the floppy icon (CTRL+S) to save it now.

This script will connect to the MySQL engine on the Windows system, create a dump of the databases (and users) we are interested in and load it into the local system. Compared to the script for the files, this is a much simpler script.

However, before we write the script itself, we should create another file. Create a file in the same location (/etc/cron.sync) called “MYSQLUSER”. In this file, enter the following:

[client]
user=root
password=P@ssw0rd

[mysqldump]
user=root
password=P@ssw0rd

We will be using this in our sync script to provide credentials to connect. I am using the MySQL “root” user (not the OS user.. this is the database user, of the same name). “root” as you may know has full administration privileges on the MySQL system.

NOTE: If you are following this series exactly, then this password is whatever you entered during the installation in the previous post.

#!/bin/bash

# This file synchronizes MySQL databases of WordPress blogs
# We only sync from the Windows machine (source) to the RasPi (destination)

echo "Creating sync folder if it does not exist..."
mkdir -p /var/local/sync.wordpress.mysql
rm -f /var/local/sync.wordpress.mysql/from-windows.sql

MYSQL_SYNCFILE=/var/local/sync.wordpress.mysql/from-windows.sql

echo "GRANT ALL PRIVILEGES ON *.* TO 'wordpress_user'@'%' 
      IDENTIFIED BY 'P@ssw0rd';" >> ${MYSQL_SYNCFILE}

echo "Connecting to mysql on Windows to get a dump..."
mysqldump --defaults-file=/etc/cron.sync/MYSQLUSER --host=192.168.1.10 
          --protocol=tcp --port=3306 \
--default-character-set=utf8 --add-drop-database --add-drop-table 
       --skip-triggers --skip-routines --single-transaction --flush-privileges \
--databases foo >> ${MYSQL_SYNCFILE}

echo "Importing databases from Windows..."
mysql --defaults-file=/etc/cron.sync/MYSQLUSER < ${MYSQL_SYNCFILE}

echo "Cleaning up..."
rm -f ${MYSQL_SYNCFILE}

echo "Sync Run Completed."

There are four things (in bold above) that you need to change —

• wordpress_user – this should be whatever account name you set up in Windows for WordPress to connect
• P@ssw0rd – replace with the password for that wordpress_user account
• 192.168.1.10 – replace with the IP address of the Windows MySQL server
• foo – replace with the name(s) of all the wordpress databases you are copying over. You do not need to copy over the “mysql” and “INFORMATION_SCHEMA” databases. Multiple databases should be separated with a space (e.g.: “–databases foo bar kumbaya“).

NOTE: While you can very well create different users with different passwords for WordPress on both systems, I recommend you keep them the same. Otherwise, you will need to edit the file sync script above to include a way to replace the username and password in the WordPress wp-config.php file for every site.

The above script ensures that all the required commands will be dumped into a single file (/var/local/sync.wordpress.mysql/from-windows.sql) and executed in one block. Note that the script will actually cause databases and tables to be created and a full dump executed. So this script can also be used to set up the databases for the first time.

Testing the Script

From PuTTY, run:

# /etc/cron.sync/sync.wordpress.mysql.sh

The databases and the WordPress user will be created for the first time. You can check by logging on to the local (RasPi) MySQL and doing a “show databases;”.

At this point, both the websites and MySQL are in sync. This time, when you fire up your web browser and point it at your website, it should work fine.

You should however, log on to each website’s wp-admin portal, go to the Settings > Permalinks page (append “/wp-admin/options-permalink.php” to the main website’s URL) and click Save. This will update the permalink settings and persist to the local “.htaccess” file.

If the new site does not work, restart Apache once and refresh:

# service apache2 restart

Create CRONTAB Jobs

One final thing remains — that is to add the scripts we just created to CRON so that they can run as jobs. Note that if you do the traditional “crontab -e”, it will be added to the crontab of root or pi or whatever other user you are using. Instead, we will add it to the main system’s crontab.

To do this, from WinSCP, navigate to “/etc” and find the file called “crontab”. Double-click to open it. Under the existing entries, find a blank line and enter the following:

#### Crons installed by us ...

# Sync job for WordPress MySQL Databases
# Runs at X:10 every hour
10 * * * * root /etc/cron.sync/sync.wordpress.mysql.sh >> 
             /var/log/wordpress.sync/mysql.log 2>&1

# Sync job for WordPress Files
# Runs at X:20 every hour (10 minutes behind the database sync)
20 * * * * root /etc/cron.sync/sync.wordpress.disk_files.sh >> 
            /var/log/wordpress.sync/files.log 2>&1

Save and exit the WinSCP editor.

What we configured it to do is to run the file-sync at 10 minutes after every hour (1:10, 2:10, 3:10, …) and the MySQL sync at 20 minutes after the hour (1:20, 2:20, 3:20, ..). They will run every day and under the credentials of the “root” user. This is why I did not give any special permissions to the “wordpress.sync” folder we created earlier above. The jobs will log all the messages we wrote out with the “echo” statements to “mysql.log” and “files.log” files respectively, in the /var/log/wordpress.sync folder and also output to syslog (because of the 2>&1 at the end).

Now you are all set and this walkthrough is complete.