Permalink
Switch branches/tags
Nothing to show
Find file
1c943f1 Aug 31, 2014
executable file 129 lines (109 sloc) 5.29 KB
#!/usr/bin/env bash
# Description : Sync DB script for MySQL, Mongo, Postgres.
# Usage : db sync [-t db-(t)ype] [-n db-(n)ame] [-r (r)emote-credentials] [-m sync-(m)ethod] [-h]
# Dependencies : mysql, mongo, postgres, openssh-server, nmap
# Example : db sync -m get -t mysql -n some_db -r deploy@server.com
# Notes : ASSUMES: Always user root, no password for DB access, ssh-key is available at remote host, same DB-name at both ends.
# Source : http://stackoverflow.com/a/15198031/865268 - Setup Temporary SSH
# Source : http://stackoverflow.com/a/16620593/865268 - Copy Mongo
# Source : http://stackoverflow.com/a/1238305/865268 - Copy Postgres
# Source : http://stackoverflow.com/a/16348366/865268 - Progress bar
function exit_script() {
echo $1
ssh -S db-sync-socket -O exit $REMOTE_CREDENTIALS 2> /dev/null
exit 1
}
function print_usage() {
echo "Usage: db-sync [-t db-(t)ype] [-n db-(n)ame] [-r (r)emote-credentials] [-m sync-(m)ethod] [-h]"
exit 0
}
TUNNEL_PORT=12345
SYNC_TYPE="get" # Default Argument. All others are a must.
# Get All Variables
# ------------------------------------------------------------------------------------------------------------
while getopts :m:t:n:r:h OPTION; do
case $OPTION in
t) DB_TYPE=$OPTARG ;;
m) SYNC_TYPE=$OPTARG ;;
n) DB_NAME=$OPTARG ;;
r) REMOTE_CREDENTIALS=$OPTARG ;;
h) print_usage ;;
\?) exit_script "Invalid option -$OPTARG. Please run 'db-sync -h' for help" ;;
:) exit_script "Option -$OPTARG requires an argument. Please run 'db-sync -h' for help" ;;
esac
done
shift $((OPTIND - 1))
# Sanity checks
# ------------------------------------------------------------------------------------------------------------
echo "Validating all variables have value..."
SCRIPT_VARS="DB_TYPE SYNC_TYPE DB_NAME REMOTE_CREDENTIALS"
for SCRIPT_VAR in $SCRIPT_VARS; do
[[ -z ${!SCRIPT_VAR} ]] &&
exit_script "ERROR: Variable $SCRIPT_VAR must have a value."
done
# An easy way to check connectivity without using logins or keys or passwords.
# Need to find a way to get only the server credentials for it to work properly.
echo "Checking remote server is reachable..."
# REMOTE_EXISTS=`nmap $REMOTE_CREDENTIALS -PN -p ssh | grep -o 'open'`
# [[ -z $REMOTE_EXISTS ]] &&
# exit_script "ERROR: Provided remote server is not reachable."
# Check DB Type
# -----------------------------------------------------------------------------------------------------------
# TODO: Better way to find if DB exists for mongo/postgres
# TODO: More DB's? MariaDB? Redis? w00t?
# TODO: Run on more than one possible DB port?
if [[ $DB_TYPE == "mysql" ]]; then
DB_PORT=3306
DB_EXISTS="mysql -uroot -e \"SHOW DATABASES LIKE '$DB_NAME';\""
SYNC_COMMAND="mysqldump -uroot -h127.0.0.1 -P$TUNNEL_PORT $DB_NAME | mysql -uroot -h127.0.0.1 -P$DB_PORT -C $DB_NAME"
elif [[ $DB_TYPE == "mongo" ]]; then
DB_PORT=27017
DB_EXISTS="mongo --eval \"printjson(db.adminCommand('listDatabases'))\" | grep -o $DB_NAME"
SYNC_COMMAND="mongo --host 127.0.0.1:$DB_PORT $DB_NAME --eval \"db.dropDatabase();db.copyDatabase('$DB_NAME','$DB_NAME','127.0.0.1:$TUNNEL_PORT');\""
elif [[ $DB_TYPE == "postgres" ]]; then
DB_PORT=5432
DB_EXISTS="psql -l | grep -o $DB_NAME"
SYNC_COMMAND="pg_dump -C -h127.0.0.1 -p$TUNNEL_PORT -Uroot $DB_NAME | psql -h127.0.0.1 -p$DB_PORT -Uroot $DB_NAME"
else
exit_script "ERROR: Failed to recognize DB type."
fi
# Connect To SSH With Master Control And Check DB's
# ------------------------------------------------------------------------------------------------------------
echo "Connecting through ssh..."
ssh -fqTNM -S db-sync-socket -L $TUNNEL_PORT:127.0.0.1:$DB_PORT $REMOTE_CREDENTIALS
echo "Check local and remote DB's..."
LOCAL_DB_EXISTS=`eval $DB_EXISTS`
REMOTE_DB_EXISTS=`ssh -S db-sync-socket $REMOTE_CREDENTIALS "$DB_EXISTS"`
# Check Sync Type
# ------------------------------------------------------------------------------------------------------------
# TODO: Does any other DB requires creating before syncing?
if [[ $SYNC_TYPE == "get" ]]; then
[[ -z $REMOTE_DB_EXISTS ]] &&
exit_script "ERROR: Can't reach remote DB. Are you sure you typed it correctly?"
[[ -z $LOCAL_DB_EXISTS && $DB_TYPE == "mysql" ]] &&
echo "Can't reach local DB. Creating now..." &&
db-mysql-create $DB_NAME
elif [[ $SYNC_TYPE == "put" ]]; then
# Reverting the ports for a reverse sync. Yay clever method!:D
TUNNEL_PORT=$DB_PORT
DB_PORT=12345
[[ -z $REMOTE_DB_EXISTS && $DB_TYPE == "mysql" ]] &&
echo "Can't reach remote DB. Creating now..." &&
ssh -S db-sync-socket $REMOTE_CREDENTIALS 'bash -s' < db-mysql-create $DB_NAME
[[ -z $LOCAL_DB_EXISTS ]] &&
exit_script "ERROR: Can't reach local DB. Are you sure you typed it correctly?"
else
exit_script "ERROR: Failed to recognize sync type."
fi
# Perform Sync (With Progress Bar)
# ------------------------------------------------------------------------------------------------------------
echo "Syncing DB's..."
while :; do echo -n .; sleep 1; done &
eval $SYNC_COMMAND;
kill $!; trap 'kill $!' SIGTERM && echo
[[ $? != 0 ]] &&
exit_script "ERROR: DB sync failed."
# Shutdown SSH
# ------------------------------------------------------------------------------------------------------------
echo "Disconnecting from ssh..."
ssh -S db-sync-socket -O exit $REMOTE_CREDENTIALS 2> /dev/null