db-migrate.sh

#!/bin/bash
# Database migration script for Sakura to Any

SRC_IS_SSH_USE=1
SRC_SSH_SAKURA_USER="example"
SRC_SSH_HOST="${SRC_SSH_SAKURA_USER}.sakura.ne.jp"
SRC_SSH_USERNAME="${SRC_SSH_SAKURA_USER}"
SRC_SSH_PASSWORD="foobar"

SRC_DB_HOST="mysql0000.db.sakura.ne.jp"
SRC_DB_USERNAME="example"
SRC_DB_PASSWORD="foobar"

DST_DB_HOST=""
DST_DB_USERNAME=""
DST_DB_PASSWORD=""

# ----


function print_help () {
	
	echo "$0 DB_NAME [DB_NAME ...]"
	echo -e "\nDB_NAME\n  Database name (multiple supported)"
	
}


function db_dump () {
		
	db_name=$1
	dump_filename="dump_${db_name}.sql"
	echo "    * ${db_name}"

	echo "        * Database dumping on remote..."
	export DB_MIGRATE_ASKP="enable"
	export SSH_ASKPASS="$(pwd)/$0"
	export DISPLAY=dummy:0
	setsid ssh -o StrictHostKeyChecking=no -x "${SRC_SSH_USERNAME}@${SRC_SSH_HOST}" "mysqldump -h ${SRC_DB_HOST} -u ${SRC_DB_USERNAME} -p${SRC_DB_PASSWORD} -r ${dump_filename} --default-character-set=binary --single-transaction --quote-names ${db_name}"
	if [ $? -ne 0 ]; then
		echo "        * Error!"
		exit -1
	fi
	
	echo "        * Downloading to local..."
	setsid scp "${SRC_SSH_USERNAME}@${SRC_SSH_HOST}:${dump_filename}" "./${dump_filename}"
	if [ $? -ne 0 ]; then
		echo "        * Error!"
		exit -1
	fi

	if [ ! -f "${dump_filename}" ]; then
		echo "        * Error!"
	fi

	echo "        * Done"

}


function db_import () {

        db_name=$1
        dump_filename="dump_${db_name}.sql"
        echo "    * ${db_name}"

        echo "        * Importing to ${DST_DB_HOST}"

        mysql -h "${DST_DB_HOST}" -u "${DST_DB_USERNAME}" "-p${DST_DB_PASSWORD}" "${db_name}" < "${dump_filename}"
        if [ $? -ne 0 ]; then
                echo "        * Error!"
                exit -1
        fi
	
}


# ----

if [ $# -le 0 ]; then
	print_help
	exit 0
fi	

if [ "$1" = "ASKPASS" -o "${DB_MIGRATE_ASKP}" = "enable" ]; then
	cat <<< "${SRC_SSH_PASSWORD}"
	exit 0
fi

echo -e "Database migration script\n"

if [ $SRC_IS_SSH_USE -eq 1 ]; then
	echo -e "SSH access enabled\n"
	echo "Source database:"
	echo "* Host: ${SRC_DB_HOST} via ${SRC_SSH_USERNAME}@${SRC_SSH_HOST}"
else
	echo -e "SSH access disabled\n"
	echo "Source database:"
	echo "* Host ${SRC_DB_HOST}"
fi

echo "* Username: ${SRC_DB_USERNAME}"

echo -e "* Target database names:"
for db_name in $*
do
	echo "    * ${db_name}"
done

echo -e "\nOkay? (yes/no)"
read ANS
if [ "$ANS" != "yes" ]; then
	echo "Canceled"
	exit 0
fi

echo -e "\nDumping..."
for db_name in $*
do
	db_dump "${db_name}"
done

if [ "${DST_DB_HOST}" = "" ]; then
	echo -e "\nDestination database was not configured. Done."
	exit 0
fi

echo -e "\nDatabase dump was completed. Would you import to ${DST_DB_HOST}? (yes/no)"

read ANS
if [ "$ANS" != "yes" ]; then
	echo "Canceled"
	exit 0
fi

echo -e "\nImporting..."
for db_name in $*
do
        db_import "${db_name}"
done

echo -e "\nDone."

exit 0