This script was created over the course of a few days early on when I first began working. The way things had been setup, we had an instance of our CMS application using a pre-production database that periodically needed to be synced to our production database. Prior to my arrival this procedure had been completed manually every few hours.

Before this I had never written any sort of shell script so I had a fun time looking up the commands and figuring out how to write the script and get things working.

DBSYNC Shell Script for Plesk Servers

#!/bin/sh
# Plesk MySQL database synchronization script
# DBSYNC -- uses /usr/bin/mysqldump and /usr/bin/mysql to
# create a SQL dump file of the pre-production database
# and then uses this dump file to update the production
# database. SQL dump file is then compressed using gzip
# and is stored in /home/.bkup/dbbackups.
# Last Updated 01/19/2009 by Omar Ramos
# 1. Modified echos to use tee command to output to the screen 
#     and logfile at the same time.
# 2. Modified script to use /tmp so that the database import 
#     would successfully complete even if the backup partition 
#     is filled up.
# 3. Generalized script so that it could be used by others 
#     looking to implement similar functionality on their
#     Plesk servers.

EXPORT_DB="your_pre_production_database_name"
IMPORT_DB="your_production_database_name"
# Gets the date in YYYY-MM-DD format
DATE=`/bin/date +%Y%m%d`
# Shortcut to get the current process ID
PID=$
# Change to your backup directory location
EXPORT_DIR="/your/backup/directory"
# Change the mydb portion to something relevant for your situation
TMP_FILE_NAME="/tmp/$DATE-mydb-$PID.sql"
EXPORT_FILE_NAME="$EXPORT_DIR/$DATE-mydb-$PID.sql"
# Change to your log directory location
LOGFILE=/your/log/directory/DBSYNC.log

# The portions below that use the cat command pull in the Plesk admin
# password dynamically so that it can be used for the script.

#DB Dump Export File
/usr/bin/mysqldump --user=admin --password=`/bin/cat /etc/psa/.psa.shadow` $EXPORT_DB > $TMP_FILE_NAME
echo "Dumping database to $TMP_FILE_NAME from $EXPORT_DB ..." | tee -a $LOGFILE

#DB Dump File Import Database Location
/usr/bin/mysql --user=admin --password=`/bin/cat /etc/psa/.psa.shadow` -D $IMPORT_DB < $TMP_FILE_NAME
echo "Importing $TMP_FILE_NAME to $IMPORT_DB ..." | tee -a $LOGFILE

#gzip DB Dump File and Store in /your/backup/directory
mv $TMP_FILE_NAME $EXPORT_FILE_NAME
echo "Moving $TMP_FILE_NAME to $EXPORT_FILE_NAME ..." | tee -a $LOGFILE
gzip $EXPORT_FILE_NAME
echo "Gzipping $EXPORT_FILE_NAME ..." | tee -a $LOGFILE

#Piped to DBSYNC.log located in /your/log/directory when run
echo "DBSYNC Backup and Sync Performed on `date`" | tee -a $LOGFILE