A simple bash script to sync a local database with a remote one (for development purpose)

sync_database.sh

#!/bin/bash

# configure script parameters here


USERNAME=username # username for ssh/scp connection
SERVER=123.123.123.123 # server ip for ssh/scp connection
PORT=22 # ssh port
ADDITIONAL_SSH_PARAMS="-i mykeys.pem" # additional ssh parameters to append to ssh and scp commands
REMOTE_DATABASE_NAME=exampledb # name of the database on the remote server (used with dump script and as sql remote filename)
PATH_TO_DB=/path/to/file # path to remote file
REMOTE_USERNAME=user # remote username
REMOTE_PASSWORD=password # remote password
TARGET_DATABASE=local_exampledb # local target database name
LOCAL_USERNAME=root # local username
LOCAL_PASSWORD=password # local password
REMOVE_REMOTE=YES # remove remote sql dump file
REMOVE_LOCAL=NO # remove local sql dump file





# do not touch below

color_output() {
  echo -e "\033[$2m$1\033[0m"
}


color_output "Dumping database on remote server..." 37

ssh -p $PORT $ADDITIONAL_SSH_PARAMS $USERNAME@$SERVER "mysqldump -u $REMOTE_USERNAME -p'$REMOTE_PASSWORD' $REMOTE_DATABASE_NAME > $PATH_TO_DB/$REMOTE_DATABASE_NAME.sql"

color_output "Done." 32

color_output "Downloading database..." 37

scp -P $PORT $ADDITIONAL_SSH_PARAMS $USERNAME@$SERVER:$PATH_TO_DB/$REMOTE_DATABASE_NAME.sql .

color_output "Done." 32

if [ $REMOVE_REMOTE == "YES" ]; then
    color_output "Removing remote backup file..." 37

    ssh -p $PORT $ADDITIONAL_SSH_PARAMS $USERNAME@$SERVER rm $PATH_TO_DB/$REMOTE_DATABASE_NAME.sql

    color_output "Done." 32
fi

color_output "Reloading local database..." 37

color_output "- Dropping old" 37
mysql -u $LOCAL_USERNAME -p$LOCAL_PASSWORD -e 'DROP DATABASE `'$TARGET_DATABASE'`'

color_output "- Creating new" 37
mysql -u $LOCAL_USERNAME -p$LOCAL_PASSWORD -e 'CREATE DATABASE `'$TARGET_DATABASE'` CHARACTER SET utf8 COLLATE utf8_unicode_ci'

color_output "- Loading new database dump" 37
mysql -u $LOCAL_USERNAME -p$LOCAL_PASSWORD --database $TARGET_DATABASE < $REMOTE_DATABASE_NAME.sql

color_output "Done." 32

if [ $REMOVE_LOCAL == "YES" ]; then
    color_output "Removing local copy..." 37

    rm $REMOTE_DATABASE_NAME.sql

    color_output "Done." 32
fi

color_output "All tasks finished." 32