Bash script to synchronize remote mysql database with local one..

sync_mysql.sh

#!/bin/bash

start=`date +%s`

LOCAL_USER="root"
LOCAL_PASS="xxxxxx"
REMOTE_HOST="root@xxxxxx.com"

LOCAL_DB="xxxxxx"
REMOTE_DB="xxxxxx"
REMOTE_USER="root"
REMOTE_PASS='xxxxxx'


echo "Clearing local database: $LOCAL_DB"

echo "DROP DATABASE IF EXISTS $LOCAL_DB;" | mysql -u$LOCAL_USER -p$LOCAL_PASS
echo "CREATE DATABASE $LOCAL_DB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;" | mysql -u$LOCAL_USER -p$LOCAL_PASS

# echo "show databases;" | mysql -u$LOCAL_USER -p$LOCAL_PASS

echo "Coping remote database: $REMOTE_DB ($REMOTE_HOST) to local databasse: $LOCAL_DB"

ssh $REMOTE_HOST "mysqldump -u $REMOTE_USER -p$REMOTE_PASS $REMOTE_DB --verbose | bzip2 -c" | bunzip2 -c | mysql -u $LOCAL_USER -p$LOCAL_PASS $LOCAL_DB

# Make localhost adjustments
# echo "UPDATE $LOCAL_DB.core_config_data SET value = 'xxxxxx' WHERE core_config_data.path = 'web/unsecure/base_url';" | mysql -u$LOCAL_USER -p$LOCAL_PASS
# echo "UPDATE $LOCAL_DB.core_config_data SET value = 'xxxxxx' WHERE core_config_data.path = 'web/secure/base_url';" | mysql -u$LOCAL_USER -p$LOCAL_PASS

end=`date +%s`
runtime=$((end-start))

echo "Done. Databases are synchronized in $runtime sec."


exit 0