Transfer MySQL databases between AWS RDS instances.

README.md

Transfer MySQL databases between AWS RDS instances

The situation

• Two AWS RDS MySQL databases - moving databases from source to destination.
• Both databases are not publicly accessible, only via an EC2 instance(s) - e.g. you have setup your security groups.
• Can SSH to a target EC2 instance (but of course).

What it does

• Sets up two SSH port forwards on local machine - one to source database, another to target.
• Via calls to transferDatabase():
  • Drops and recreates each given database.
  • mysqldump of source database which is piped into mysql client connection to destination.
• Drops SSH port fowards.
• Done!

Note: MySQL users and permissions are not copied.

script.sh

#!/bin/bash -e

SSH_SERVER_NAME="ec2.server.address.com"
SSH_SERVER_USER="ec2user"
SSH_SERVER_PORT="22"
SSH_KEY_FILE="$HOME/.ssh/my.id_rsa"

MYSQL_PORT="3306"
MYSQL_ADDRESS_SOURCE="db-name-source.ap-southeast-1.rds.amazonaws.com"
MYSQL_ADDRESS_DEST="db-name-destination.ap-southeast-1.rds.amazonaws.com"
MYSQL_LOCAL_IP_ADDR="127.0.0.1"
MYSQL_LOCAL_PORT_SOURCE="7100"
MYSQL_LOCAL_PORT_DEST="7102"

MYSQL_ROOT_USERNAME="mysql-rootuser"
MYSQL_ROOT_PASSWORD="" # must populate


function portForwardCreate {

	echo "Creating SSH port forward $1:$2"

	ssh -fN \
		-L $1:$2:$MYSQL_PORT \
		-i $SSH_KEY_FILE \
		-p $SSH_SERVER_PORT $SSH_SERVER_USER@$SSH_SERVER_NAME
}

function portForwardDestroy {

	echo "Destroy SSH port forward on $1"
	sshForwardPID=$(eval "ps x | grep 'ssh -fN -L $1:' | head -n1" | awk '{print $1}')
	kill $sshForwardPID
}

function transferDatabase {

	databaseName="$1"
	echo "Transferring: $databaseName"

	# create fresh database on destination MySQL server
	mysql \
		--host $MYSQL_LOCAL_IP_ADDR --port $MYSQL_LOCAL_PORT_DEST \
		--user $MYSQL_ROOT_USERNAME --password=$MYSQL_ROOT_PASSWORD \
		--execute "DROP DATABASE IF EXISTS $databaseName;CREATE DATABASE $databaseName;"

	# dump contents of source database and pipe into destination
	mysqldump \
		--host $MYSQL_LOCAL_IP_ADDR --port $MYSQL_LOCAL_PORT_SOURCE \
		--user $MYSQL_ROOT_USERNAME --password=$MYSQL_ROOT_PASSWORD \
		--single-transaction $databaseName | \
			mysql \
				--host $MYSQL_LOCAL_IP_ADDR --port $MYSQL_LOCAL_PORT_DEST \
				--user $MYSQL_ROOT_USERNAME --password=$MYSQL_ROOT_PASSWORD \
				--database $databaseName
}


# ensure MYSQL_ROOT_PASSWORD is populated
if [[ -z $MYSQL_ROOT_PASSWORD ]]; then
	echo "Error: must populate MYSQL_ROOT_PASSWORD" >&2
	exit 1
fi

# create port forwards
portForwardCreate "$MYSQL_LOCAL_PORT_SOURCE" "$MYSQL_ADDRESS_SOURCE"
portForwardCreate "$MYSQL_LOCAL_PORT_DEST" "$MYSQL_ADDRESS_DEST"


# now transfer databases
#transferDatabase "first"
#transferDatabase "second"
#transferDatabase "third"


echo "Listing destination databases"
mysql \
	--host $MYSQL_LOCAL_IP_ADDR --port $MYSQL_LOCAL_PORT_DEST \
	--user $MYSQL_ROOT_USERNAME --password=$MYSQL_ROOT_PASSWORD \
	--execute "SHOW DATABASES;"


# destroy port forwards
portForwardDestroy "$MYSQL_LOCAL_PORT_SOURCE"
portForwardDestroy "$MYSQL_LOCAL_PORT_DEST"

wanted to ask if you've done any sort of benchmarking. How long does it take for say 100M or 1G of data-set. It depends on type of disk and other DB resources but it can give a rough idea