9th Dec 2009

Synchronise two SilverStripe CMS instances

This script allows you to operate two distinct, fully functional SilverStripe instances and have the content of one synchronised with the other. When running a content management system in a corporate environment it is useful to have an internal 'development' site and a public 'production' site. SilverStripe has a couple of caching options, namely StaticPublisher and StaticExporter, but these generate static HTML files that cannot be easily modified by content editors.

This approach allows the development and production SilverStripe servers to be easily synchronised, but in between times, content editors are free to make different changes at each end. This is useful when internally the content of the website is undergoing significant change, but during this time the production website content must be 'maintained'.
i.e. You are not forced to 'freeze' your production website, or push internal changes out before they have been properly vetted.

The script copies the local SilverStripe MySQL database (sans page revisions) to the production site and synchronises the assets/Uploads directory.
Note: Page revisions are not sent to the production site because this takes a significant amount of time and bandwidth. Considering these revisions are stored on the internal development server, storing them in both locations is not necessary.

A flow diagram of the actions that take place during this synchronisation process is provided below.

Click to enlarge

This script assumes that SilverStripe's StaticPublisher caching mechanism is enabled on both local and remote sites, otherwise the sync process will fail.

Requirements

The script requires SSH, MySQL and RSync, awk, and grep on both servers to function correctly.
For the email notifications to work, either sendmail or postfix will need to be running locally so that the mail command can deliver notifications.
For the script run without SSH prompting for passwords, key-based authentication between the two servers will need to be configured. (The key should not have a password.)
The local MySQL user needs to be able to access two databases:
- Read-only access to the local SilverStripe database.
- All permissions to an empty database where the script can make a copy of the SilverStripe database and strip the page revisions from.

Configuration

The sssync.sh script pulls configuration information from a supplied config file. Below is an example configuration file that lists the various options that should be tuned to your environment.

config

# The local directory where the SilverStripe website is installed
Local_SilverStripe_Directory /var/www

# The local temp directory which the script has write access to
Local_Temp_Directory /tmp

# The local MySQL user (must have write permissions to temp database)
Local_MySQL_User localuser

# The local MySQL password
Local_MySQL_Password localpassword

# The local MySQL hostname
Local_MySQL_Host localhost

# The local MySQL port
Local_MySQL_Port 3306

# The local (primary) MySQL database
Local_MySQL_Database silverstripe

# The local temporary database used to store a revisionless version of the site
Local_MySQL_TempDatabase silverstripe_tmp

# The local user who owns the cache files
Local_User www

# The local group who owns the cache files
Local_Group www

# The remote SSH username
Remote_SSH_User remoteuser

# The remote SSH hostname
Remote_SSH_Host remote.host.name

# The remote SSH port
Remote_SSH_Port 22

# The remote directory where the SilverStripe website is installed
Remote_SilverStripe_Directory /var/www

# The remote directory where backups of the website and database are stored
Remote_Backup_Directory /var/backup/silverstripe

# The remote MySQL username
Remote_MySQL_User remoteuser

# The remote MySQL password
Remote_MySQL_Password remotepassword

# The remote MySQL hostname
Remote_MySQL_Host localhost

# The remote MySQL port
Remote_MySQL_Port 3306

# The remote MySQL database
Remote_MySQL_Database silverstripe

# The remote user who owns the cache files
Remote_User www

# The remote group who owns the cache files
Remote_Group www

# The email address(es) of recipients for sssync email
Recipient_Email_Address notify@user

# The sssync from email address
From_Email_Address sssync@domain.com

# The SMTP server (assumes the Heirloom Mailx utility is used)
SMTP_Server smtp.server.com

The sssync.sh script

The sssync.sh script performs all the described synchronisation functions. Copy and paste the following into a file on your local server named sssync.sh. Make sure you mark it as executable (chmod 777).

sssync.sh (this file can be downloaded from here)

#!/bin/sh
#
#   This program is free software: you can redistribute it and/or modify
#   it under the terms of the GNU General Public License as published by
#   the Free Software Foundation, either version 3 of the License, or
#   (at your option) any later version.
#
#   This program is distributed in the hope that it will be useful,
#   but WITHOUT ANY WARRANTY; without even the implied warranty of
#   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
#   GNU General Public License for more details.
#
#   A copy of the GNU General Public License is available at
#   <http://www.gnu.org/licenses/>.
#
#
###########################################################
#           sssync - SilverStripe Site Sync               #
###########################################################
#
# Author: David Harrison
# Date: 9 December 2009
#
# This script synchronises a remote SilverStripe installation
# with a local copy. It is assumed that SilverStripe's caching
# mechanism is enabled.
# For SSH authentication to occur without a password prompt,
# SSH keys should be generated to allow password-less login.
#
# ---------------------------------------------------------

#############################################
# Variables pulled from the supplied config #
#############################################

# Local website directory
localSSDir=`awk '/^Local_SilverStripe_Directory/{print $2}' $1`
# Local temp directory
tempDir=`awk '/^Local_Temp_Directory/{print $2}' $1`
# Local MySQL configuration
localMySQLUser=`awk '/^Local_MySQL_User/{print $2}' $1`
localMySQLPassword=`awk '/^Local_MySQL_Password/{print $2}' $1`
localMySQLHost=`awk '/^Local_MySQL_Host/{print $2}' $1`
localMySQLPort=`awk '/^Local_MySQL_Port/{print $2}' $1`
localMySQLDatabase=`awk '/^Local_MySQL_Database/{print $2}' $1`
localMySQLTempDatabase=`awk '/^Local_MySQL_TempDatabase/{print $2}' $1`

# Remote SSH configuration
remoteSSHUser=`awk '/^Remote_SSH_User/{print $2}' $1`
remoteSSHHost=`awk '/^Remote_SSH_Host/{print $2}' $1`
remoteSSHPort=`awk '/^Remote_SSH_Port/{print $2}' $1`
# Remote directories
remoteSSDir=`awk '/^Remote_SilverStripe_Directory/{print $2}' $1`
remoteBackupDir=`awk '/^Remote_Backup_Directory/{print $2}' $1`
# Remote MySQL configuration
remoteMySQLUser=`awk '/^Remote_MySQL_User/{print $2}' $1`
remoteMySQLPassword=`awk '/^Remote_MySQL_Password/{print $2}' $1`
remoteMySQLHost=`awk '/^Remote_MySQL_Host/{print $2}' $1`
remoteMySQLPort=`awk '/^Remote_MySQL_Port/{print $2}' $1`
remoteMySQLDatabase=`awk '/^Remote_MySQL_Database/{print $2}' $1`
# The email options - email requires sendmail or postfix running locally
emailRecipient=`awk '/^Recipient_Email_Address/{print $2}' $1`
fromAddress=`awk '/^From_Email_Address/{print $2}' $1`
smtpServer=`awk '/^SMTP_Server/{print $2}' $1`

# The sendEmail function delivers an email notification.
# This function assumes the Heerloom mailx utility is installed on the system.
# It takes the following parameters:
# 1- Subject
# 2- Message
sendEmail() {
   echo "Sending email to $emailRecipient:"
   echo " Subject - ${1}"
   echo " Message - ${2}"
   echo ${2} | mail -s "${1}" -S "smtp=$smtpServer" -r $fromAddress $emailRecipient
}

# The buildStripVersionsSQL function constructs a temporary SQL file that contains
# commands for removing the revisions from the temp database.
#
# Note: If you have custom page types include the relevant SQL statements below
buildStripVersionsSQL() {
   echo "DELETE FROM ErrorPage_versions;" > ${tempDir}/sssync.sql
   echo "DELETE FROM GhostPage_versions;" >> ${tempDir}/sssync.sql
   echo "DELETE FROM RedirectorPage_versions;" >> ${tempDir}/sssync.sql
   echo "DELETE FROM SiteTree_versions;" >> ${tempDir}/sssync.sql
   echo "DELETE FROM VirtualPage_versions;" >> ${tempDir}/sssync.sql
}

# The cleanTemp function removes the temporary error file.
cleanTemp() {
   rm ${tempDir}/sssync.err
}

# The rollBackChanges function restores the file and database backup of the remote website.
rollBackChanges() {
   echo "Rolling back the remote file changes"
   ssh ${remoteSSHUser}@${remoteSSHHost} -p ${remoteSSHPort} \
       "tar -xzf ${remoteBackupDir}/html.tgz -C ${remoteSSDir}"
   echo "Rolling back the remote database changes"
   ssh ${remoteSSHUser}@${remoteSSHHost} -p ${remoteSSHPort} \
       "mysql -u ${remoteMySQLUser} -h ${remoteMySQLHost} -p${remoteMySQLPassword} \
       -P ${remoteMySQLPort} \
       ${remoteMySQLDatabase} < ${remoteBackupDir}/backup.sql"
}

echo
echo "--------------------------------------------"
echo "| SilverStripe Sync process initiated   |"
echo "--------------------------------------------"
echo
echo "Local SilverStripe directory: ${localSSDir}"
echo "Temporary directory: ${tempDir}"
echo "--------------------------------------------"
echo

logger "Initiating sssync script..."

echo "Rebuilding the local SilverStripe cache"
cd ${localSSDir}
sapphire/sake dev/buildcache flush=1 > ${tempDir}/sssync.err 2>&1
chown -R $localUser:$localGroup cache
localCacheRebuilt=`tail ${tempDir}/sssync.err | grep "== Done! =="`
cleanTemp

if [ "${localCacheRebuilt}" != "== Done! ==" ]
then
   echo
   echo "** Error rebuilding the SilverStripe cache - Exiting **"
   echo

   sendEmail "Error rebuilding local SilverStripe cache"\
             "There was an error rebuilding the SilverStripe cache. \
             The sync process was not undertaken."
   exit
fi

#############################################
# Create a local, revisionless SS database #
#############################################

echo "Creating a temporary, revisionless database"

mysqldump -C -u ${localMySQLUser} -p${localMySQLPassword} -h ${localMySQLHost} -P ${localMySQLPort} ${localMySQLDatabase} | \
    mysql -u ${localMySQLUser} -p${localMySQLPassword} -h ${localMySQLHost} -P ${localMySQLPort} ${localMySQLTempDatabase} > ${tempDir}/sssync.err 2>&1

# Create the SQL file to pass to the temp database
buildStripVersionsSQL

# Stip the versions from the temp database
mysql -u ${localMySQLUser} -p${localMySQLPassword} -h ${localMySQLHost} -P ${localMySQLPort} ${localMySQLTempDatabase} \
    < ${tempDir}/sssync.sql > ${tempDir}/sssync.err 2>&1

# Remove the temporary SQL file
rm ${tempDir}/sssync.sql

localDBCreated=$(cat ${tempDir}/sssync.err)
cleanTemp

if [ "${localDBCreated}" != "" ]
then
   echo
   echo "** Error creating a revisionless database - Exiting **"
   echo
   sendEmail "Error creating revisionless database"\
       "There was an error creating a revisionless version of the local database. \
      The sync process was not undertaken."
   exit
fi

#############################################
# Before performing the sync, make a backup #
#############################################

echo "Moving HTML backup ${remoteBackupDir}/html.tgz to ${remoteBackupDir}/html.tgz.old"
ssh ${remoteSSHUser}@${remoteSSHHost} -p ${remoteSSHPort} \
    "mv ${remoteBackupDir}/html.tgz ${remoteBackupDir}/html.tgz.old"
echo "Moving SQL backup ${remoteBackupDir}/backup.sql to ${remoteBackupDir}/backup.sql.old"
ssh ${remoteSSHUser}@${remoteSSHHost} -p ${remoteSSHPort} \
    "mv ${remoteBackupDir}/backup.sql ${remoteBackupDir}/backup.sql.old"

echo "Creating backup of the remote website"
remoteBackupMade=`ssh ${remoteSSHUser}@${remoteSSHHost} -p ${remoteSSHPort} \
    "tar -czf ${remoteBackupDir}/html.tgz -C ${remoteSSDir} ."`

echo "Creating backup of the remote database"
remoteBackupMade="${remoteBackupMade}`ssh ${remoteSSHUser}@${remoteSSHHost} -p ${remoteSSHPort} \
    "mysqldump -u ${remoteMySQLUser} -h ${remoteMySQLHost} -P ${remoteMySQLPort} -p${remoteMySQLPassword} \
    ${remoteMySQLDatabase} > ${remoteBackupDir}/backup.sql"`"

if [ "${remoteBackupMade}" != "" ]
then
   echo
   echo "** Error creating remote backup - Exiting **"
   echo
   echo "Moving the old remote backups into place"
   ssh ${remoteSSHUser}@${remoteSSHHost} -p ${remoteSSHPort} \
       "mv ${remoteBackupDir}/html.tgz.old ${remoteBackupDir}/html.tgz"
   ssh ${remoteSSHUser}@${remoteSSHHost} -p ${remoteSSHPort} \
       "mv ${remoteBackupDir}/backup.sql.old ${remoteBackupDir}/backup.sql"
   sendEmail "Error creating remote backup"\
       "There was an error creating a backup of the remote website files or database. \
       The sync process was not undertaken."
   exit
fi

# Variable to hold sync failure flag
syncFailure="false"

##############################################
# Perform the synchronisation of file assets #
##############################################

echo "Synchronising remote website assets/Uploads directory with local copy"
remoteFileSync=`rsync -aqz --delete -e "ssh -p ${remoteSSHPort}" ${localSSDir}/assets/Uploads/ \
    ${remoteSSHUser}@${remoteSSHHost}:${remoteSSDir}/assets/Uploads/`

if [ "${remoteFileSync}" != "" ]
then
   syncFailure="true"
   echo
   echo "** Error synchronising website assets/Uploads - Rolling back changes **"
   echo

   sendEmail "Error synchronising website assets/Uploads"\
       "There was an error synchronising the remote website's asset directory. \
       The sync process was rolled back."
fi

##############################################
# Synchronise the local and remote databases #
##############################################

echo "Synchronising the remote database with the local (temp) database"

mysqldump -C -u ${localMySQLUser} -p${localMySQLPassword} -h ${localMySQLHost} \
    -P ${localMySQLPort} ${localMySQLTempDatabase} | \
    ssh ${remoteSSHUser}@${remoteSSHHost} -p ${remoteSSHPort} \
    "mysql -u ${remoteMySQLUser} -h ${remoteMySQLHost} -p${remoteMySQLPassword} \
    -P ${remoteMySQLPort} ${remoteMySQLDatabase}" > ${tempDir}/sssync.err 2>&1

remoteMySQLSync=$(cat ${tempDir}/sssync.err)
cleanTemp

if [ "${remoteMySQLSync}" != "" ]
then
   syncFailure="true"
   echo
   echo "** Error synchronising the MySQL databases - Rolling back changes **"
   echo

   sendEmail "Error synchronising the MySQL databases"\
       "There was an error synchronising the two MySQL databases. \
       The sync process was rolled back."
fi

if [ "${syncFailure}" == "true" ]
then
   echo
   echo "** Error synchronising the SilverStripe site - Rolling back & exiting **"
   echo

   # Roll back the file and database changes
   rollBackChanges

   sendEmail "Error synchronising the remote website"\
       "There was an error performing the synchronisation process. \
       The sync process was rolled back."
   exit
fi

##############################################
# Rebuild the remote SilverStripe web cache #
##############################################

echo "Rebuilding the remote SilverStripe cache"
ssh ${remoteSSHUser}@${remoteSSHHost} -p ${remoteSSHPort} \
    "cd ${remoteSSDir}; sapphire/sake dev/buildcache flush=1" > ${tempDir}/sssync.err 2>&1
ssh ${remoteSSHUser}@${remoteSSHHost} -p ${remoteSSHPort} \
    "chown -R $remoteUser:$remoteGroup ${remoteSSDir}/cache"
remoteCacheRebuilt=`tail ${tempDir}/sssync.err | grep "== Done! =="`
cleanTemp

if [ "${remoteCacheRebuilt}" != "== Done! ==" ]
then
   echo
   echo "** Error rebuilding the remote SilverStripe cache - Rolling back changes **"
   echo

   # Roll back the file and database changes
   rollBackChanges

   sendEmail "Error rebuilding the remote SilverStripe cache"\
             "There was an error rebuilding the remote SilverStripe cache."
   exit
fi

sendEmail "SilverStripe was successfully synchronised"\
             "Congratulations, the remote website was synchronised without any issues."

logger "sssync script completed"

echo
echo "------------------------------------------------"
echo "SilverStripe was successfully synchronised"
echo "=============================="
echo

Running the script

Assuming your configuration file is in the same directory as the sssync.sh script, run the sync process with the following command:

./sssync.sh config

Assuming the requirements have been met and sync process takes place without error, the following output should be generated:

--------------------------------------------
| SilverStripe Sync process initiated |
--------------------------------------------

Local SilverStripe directory: /var/www
Temporary directory: /var/backup/sssync
------------------------------------------

Rebuilding the local SilverStripe cache
Creating a temporary, revisionless database
Moving HTML backup /var/backup/silverstripe/html.tgz to /var/backup/silverstripe/html.tgz.old
Moving SQL backup /var/backup/silverstripe/backup.sql to /var/backup/silverstripe/backup.sql.old
Creating backup of the remote website
Creating backup of the remote database
Synchronising remote website assets/Uploads directory with local copy
Synchronising the remote database with the local (temp) database
Rebuilding the remote SilverStripe cache
Sending email to recipient@user.com:
Subject - SilverStripe was successfully synchronised
Message - Congratulations, the remote website was synchronised without any issues.

------------------------------------------------
SilverStripe was successfully synchronised
==============================

It is possible to have multiple configuration files and store them in a different directory to the sssync.sh script. For example:

./ssync.sh /etc/sssync/production
./ssync.sh /etc/sssync/testing

The above commands will execute the sync process using the "production" and "testing" configuration files stored in the /etc/sssync directory.

Handling error pages

The sssync.sh script only synchronises the assets/Uploads directory as this is where file and image uploads are stored by default. SilverStripe error pages are stored in the root of the assets directory which is not synchronised. If an error page is changed, make sure it is republished using the SilverStripe admin interface.

stressfree