aws_db_sync.sh

#!/bin/bash
#
# Refresh data in AWS RDS database
# 
#  Add to cron:	aws_db_sync.sh &> aws_db_sync.log
#   or
#  Call by:		aws_db_sync.sh &> aws_db_sync.log &
#  				    tail -f aws_db_sync.log

if [ ! -r ~/.my.cnf ]; then
	echo "Please create ~/.my.cnf"
	exit
fi

SRC_SUFFIX=--defaults-group-suffix=local
DST_SUFFIX=--defaults-group-suffix=amazon
SRC_DB_NAME=DATABASE_NAME
DST_DB_NAME=DATABASE_NAME
DST_HOST_NAME=DATABASE.SERVER.REGION.rds.amazonaws.com

if [ "$1" = "COPY-TEMP" ]; then
	SRC_DB_NAME=TMP_DATABASE_NAME
	DST_DB_NAME=TMP_DATABASE_NAME
fi

SQLCHECK=$(mysql $SRC_SUFFIX -sNe "select 1;")
if [ "$SQLCHECK" != "1" ]; then
	echo "Error accessing _source_ MySQL database or wrong host"
	echo " $SQLCHECK"
	exit
fi

SQLCHECK=$(mysql $DST_SUFFIX -sNe "select 1;")
if [ "${SQLCHECK:0:3}" != "1" ]; then
	echo "Error accessing _destination_ MySQL database or wrong host"
	echo " $SQLCHECK"
	exit
fi

date
echo
echo "AWS DB Export/Import"
echo "FROM $SRC_SUFFIX $SRC_DB_NAME"
echo "TO   $DST_SUFFIX $DST_DB_NAME"

# For each table, except the temp tables (begining with TMP)
# - export the table from the source
# - import it into the destination
for table in $(mysql $SRC_SUFFIX -sNe "SHOW FULL TABLES IN $SRC_DB_NAME WHERE Table_Type = 'BASE TABLE'"); do
	if [ ${table:0:4} != "BASE" ] && [ ${table:0:5} != "TABLE" ]; then
		# not a temprary backup table
		if [ ${table:0:3} != "TMP" ]; then
			DATENOW=$(date +"%T")
			echo " $DATENOW -- DROPPING   $table"
			mysql $DST_SUFFIX --host=$DST_HOST_NAME --port=3306 -sNe "USE $DST_DB_NAME; SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS $table;"
			
			DATENOW=$(date +"%T")
			echo " $DATENOW -- REFRESHING $table"
			mysqldump $SRC_SUFFIX $SRC_DB_NAME $table --single-transaction --compress --order-by-primary | \
				mysql $DST_SUFFIX -D $DST_DB_NAME --host=$DST_HOST_NAME --port=3306
		else
			DATENOW=$(date +"%T")
			echo " $DATENOW -- SKIPPING   $table"
		fi
	fi
done

# For the views
# - export the view from the source database to a sql file
# - change the view definer to the export/import user in the sql file
# - define the view in the source database from the sql file
for table in $(mysql $SRC_SUFFIX -sNe "SHOW FULL TABLES IN $SRC_DB_NAME WHERE Table_Type = 'VIEW'"); do
	if [ ${table:0:4} != "VIEW" ]; then
		DATENOW=$(date +"%T")
		echo " $DATENOW -- DROPPING   $table"
		mysql $DST_SUFFIX --host=$DST_HOST_NAME --port=3306 -sNe "USE $DST_DB_NAME; SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS $table;"
		
		DATENOW=$(date +"%T")
		echo " $DATENOW -- EXPORTING  $table"
		mysqldump $SRC_SUFFIX $SRC_DB_NAME $table --single-transaction --compress --order-by-primary > /tmp/tmp.sql
		
		DATENOW=$(date +"%T")
		echo " $DATENOW -- MODIFYING  $table"
		sed -i 's/`VIEW_OWNER`@`%`/`SYNC_ACCOUNT`@`%`/g' /tmp/tmp.sql

		DATENOW=$(date +"%T")
		echo " $DATENOW -- IMPORTING  $table"
		mysql $DST_SUFFIX -D $DST_DB_NAME --host=$DST_HOST_NAME --port=3306 < /tmp/tmp.sql
	fi
done

# remove the temporary sql file
if [ -f "/tmp/tmp.sql" ]; then
	rm -f /tmp/tmp.sql
	exit
fi

echo "Export/Import complete."
echo
date