Reply to comment

There have been countless times where I have a local snapshot of a site I'm developing, and want to push the database either out to the remote site, or pull it in to my local sandbox.

This script allows you to synchronize a remote MySQL database to/from your local MySQL database using SSH.

Invoke the script without any arguments to read the usage. (Or read below!)

# Copyright (c) 2010 Ruben Rodriguez (cha...@therealcha0s.net)
#
# Permission is hereby granted, free of charge, to any person
# obtaining a copy of this software and associated documentation
# files (the "Software"), to deal in the Software without
# restriction, including without limitation the rights to use,
# copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the
# Software is furnished to do so, subject to the following
# conditions:
#
# The above copyright notice and this permission notice shall be
# included in all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
# EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
# OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
# NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
# HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
# WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
# OTHER DEALINGS IN THE SOFTWARE.

if test -z $1; then
	echo "Usage: db-sync [OPTIONS] DIRECTION HOST DB_NAME [DB_USER] [REMOTE_DB_NAME]"
	echo "[REMOTE_DB_USER]"
	echo
	echo "Bidrectionally synchronize a MySQL database with your local MySQL"
	echo "database."
	echo
	echo "Options:"
	echo "-v, --verbose  Verbose output."
	echo "--remote-args  Arguments to pass to the remote mysql server."
	echo "--local-args   Arguments to pass to the local mysql server."
	echo
	echo "DIRECTION can be 'in' or 'out'. 'in' synchronizes the local database with the"
	echo "remote database, and out synchronizes the remote database with the local"
	echo "database."
	echo
	echo "If DB_USER is not specified, it is copied from DB_NAME."
	echo "If REMOTE_DB_NAME is not specified, it is copied from DB_NAME."
	echo "If REMOTE_DB_USER is not specified, it is copied from REMOTE_DB_NAME."
	echo
	exit
fi

while [ 1 ]; do
	
	case "$1" in
		"-v"|"--verbose")
			verbose=1
			shift
		;;
		"--local-args")
			shift
			
			local_args=$1
			shift
		;;
		"--remote-args")
			shift
			
			remote_args=$1
			shift
		;;
		*)
			break
		;;
	esac	
done

direction=$1
shift

host=$1
shift

db=$1
shift

user=$1
shift
if test -z "$user"; then user=$db; fi

rdb=$1
shift
if test -z "$rdb"; then rdb=$db; fi

ruser=$1
shift
if test -z "$ruser"; then ruser=$rdb; fi

# Make sure if the program dies while the echo is locked (to prevent echo'ing
# sensitive data) that we'll release it.
trap 'stty echo' INT TERM EXIT

stty -echo
stty echonl
	
	echo -n Input the local db password:\ 
	read -r pass
	
	echo -n Input the remote db password:\ 
	read -r rpass
	
stty echo

echo
if [ "$direction" = "out" ]; then
	echo Synchronizing database "$ruser@$host/$rdb from $user@localhost/$db"
	if [ "$verbose" = "1" ]; then
		echo mysqldump $local_args -u $user -p[...] $db \| ssh -C $host "cat | mysql $remote_args -u $ruser -p[...] $rdb"
	fi
	
	mysqldump $local_args -u $user -p"$pass" $db | ssh -C $host "cat | mysql $remote_args -u $ruser -p\'$rpass\' $rdb"
else
	echo Synchronizing database "$user@localhost/$db from $ruser@$host/$rdb"
	if [ "$verbose" = "1" ]; then
		echo ssh -C $host "mysqldump $remote_args -u $ruser -p[...] $rdb" \| mysql $local_args -u $user -p[...] $db
	fi
	
	ssh -C $host "mysqldump $remote_args -u $ruser -p'$rpass' $rdb" | mysql $local_args -u $user -p"$pass" $db
fi