Simple script for mirroring a remote mysql database on local.

clone-remote-db.sh

#!/bin/bash

REMOTE_NAME='name'
REMOTE_PASS='pass'
REMOTE_HOST='host'
REMOTE_DB='db'

LOCAL_NAME='name'
LOCAL_PASS='pass'

DB_NAME="remote-db-clone-$(date +"%Y-%m-%d")"
DB_FILE_PATH="/tmp/remote-db-clone.sql"

# Dump db from remote
mysqldump -u $REMOTE_NAME -p$REMOTE_PASS -h $REMOTE_HOST $REMOTE_DB > $DB_FILE_PATH
echo "Dumped {$REMOTE_DB} from {$REMOTE_HOST}."

# Create new db on local
mysql -u $LOCAL_NAME -p$LOCAL_PASS -e 'CREATE DATABASE IF NOT EXISTS `'"$DB_NAME"'` CHARACTER SET utf8 COLLATE utf8_general_ci'
echo "New db {$DB_NAME} created."

# Import dumped remote into new local db
mysql -u $LOCAL_NAME -p$LOCAL_PASS $DB_NAME < $DB_FILE_PATH
echo "Import done."