#!/bin/bash
# Shell script to replicate MySql database from REMOTE to LOCAL
# By Niraj Shah

# CONFIG - Only edit the below lines to setup the script
# ===============================

# REMOTE DB SETTINGS
REMOTE_USER="user"         # USERNAME
REMOTE_PASS="password"     # PASSWORD
REMOTE_HOST="mydomain.com" # HOSTNAME / IP
REMOTE_DB="test_db"        # DATABASE NAME

# LOCAL DB SETTINGS
DB_USER="user"             # USERNAME
DB_PASS="pass2"            # PASSWORD
DB_HOST="localhost"        # HOSTNAME / IP
DB_NAME="test_local"       # DATABASE NAME

DUMP_FILE="temp.sql"       # SQL DUMP FILENAME

# DO NOT EDIT BELOW THIS LINE UNLESS YOU KNOW WHAT YOU ARE DOING
# ===============================

# get remote database

if [ "$DB_PASS" == "" ];
then
  mysqldump -h $REMOTE_HOST -u $REMOTE_USER $REMOTE_DB > $DUMP_FILE
else
  mysqldump -h $REMOTE_HOST -u $REMOTE_USER -p$REMOTE_PASS $REMOTE_DB > $DUMP_FILE
fi

# drop all tables

if [ "$DB_PASS" == "" ];
then
  mysqldump -u $DB_USER \
    --add-drop-table --no-data $DB_NAME | \
    grep -e '^DROP \| FOREIGN_KEY_CHECKS' | \
    mysql -u $DB_USER $DB_NAME
else
  mysqldump -u $DB_USER -p$DB_PASS \
    --add-drop-table --no-data $DB_NAME | \
    grep -e '^DROP \| FOREIGN_KEY_CHECKS' | \
    mysql -u $DB_USER -p$DB_PASS $DB_NAME
fi

# restore new database

if [ "$DB_PASS" == "" ];
then
  mysql -u $DB_USER $DB_NAME < $DUMP_FILE
else
  mysql -u $DB_USER -p$DB_PASS $DB_NAME < $DUMP_FILE
fi