I created this migration script in 2010 while I had to transfer a quantity of websites.

README.md

Original post was on my own blog post, but unfortunately written only in french.

What it does:

• Two possible actions
  • import to create a SQL dump file
  • transfer to automate the transfer between the two nodes

Both commands has to be launched from the originating server.

Import

Automatically crate blank configuration file to keep targeted destination server.

Transfer

To launch the transfer from the the originating server.

migratemysql

#!/bin/bash

# ////////////////  Some config  //////////////
TMPDIR="/tmp/"
mysql=`which mysql`
mysqldump=`which mysqldump`

#
#    MigrateMySQL v 1.0
#    by Etienne Lachance and Renoir Boulanger
# 
#    https://renoirboulanger.com/projets/
# 
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
# 
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
# 
#    You should have received a copy of the GNU General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
# 

# ////////////////  Actual script  //////////////
PN=`basename "$0"`  		# program name
VER='1.0'

usage () {
echo "
USAGE INFORMATION

  NAME
       $PN

  DESCRIPTION
       Tool to help transfer a MySQL database from a remote site using ssh and
       mysqldump. You only need one copy of this script, on the server you want 
       to import the databases to.
       
  SYNOPSIS
       $PN transfer
       $PN import

  OPTIONS
       transfer     transfer database dump file from remote host to localhost
       import       import database dump to database 
         
  FILES
       $PN.conf
             Config file saved in the directory that you invoked $PN

  LICENCE
       MigrateMySQL is a software provided under the GNU GENERAL PUBLIC LICENCE
       Copyright (C) 2010 by Etienne Lachance and Renoir Boulanger
       This program comes with ABSOLUTELY NO WARRANTY; for details, see source code.
       This is free software, and you are welcome to redistribute it
       under certain conditions;

                                     https://renoirboulanger.com/
"
    exit 1
}

dump_config_file () {
echo "#
# Generated by $PN version $VER on `date`
#" > ${PWD}/$PN.conf
echo 'SSH_SOURCE="hostname"
SSH_PORT=
SSH_USER="username"
#SSH_PASS=""
ALT_PURGE=""                          # En cas ou le mysqldump utilise a pas de DROP TABLE (default: false)
DB_SOURCE_DBNAME=""
DB_SOURCE_USER=""
DB_SOURCE_PASS=""
DB_SOURCE_HOST=""         	      # e.g. mysql4.local    (defaults: localhost)
DB_DEST_DBNAME=$DB_SOURCE_DBNAME
DB_DEST_USER="root"
DB_DEST_PASS="root"
DB_DEST_HOST=""   # e.g. mysql4.local    (defaults: localhost)
DB_DEST_USERHOST=""	  # e.g. webservers-%    (defaults: localhost)
DB_DEST_USERNAME="root"	              # site username
DB_DEST_USERPW="root"                 # site password
#OPTS_KEEP_COMPRESSED=""          # 1 or 0               (1=true) (not yet implemented)
OPTS_COMPRESSION_PROG="gzip"	  # bzip2,gzip,zip       (only gzip is implemented)
' >> ${PWD}/$PN.conf
echo " * Config file ${PWD}/$PN.conf created, modify it, then run $PN again.";
}

function dump_prepare_sql {
	echo "-- Generated by $PN version $VER on `date`" > ${TMPDIR}/$PN.$DB_DEST_DBNAME.sql.tmp
}

function dump_prepare_createdb {
	echo "CREATE DATABASE $DB_DEST_DBNAME;" >> ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp
}

function dump_prepare_addpriv {
	echo "CREATE USER '$DB_DEST_USERNAME'@'$DB_DEST_USERHOST' IDENTIFIED BY '$DB_DEST_USERPW';" >> ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp
	echo "GRANT USAGE ON * . * TO '$DB_DEST_USERNAME'@'$DB_DEST_USERHOST' IDENTIFIED BY '$DB_DEST_USERPW';" >> ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp
	echo "GRANT ALL PRIVILEGES ON \`$DB_DEST_DBNAME\` . * TO '$DB_DEST_USERNAME'@'$DB_DEST_USERHOST';" >> ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp
	echo "FLUSH PRIVILEGES;" >> ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp
}

function dump_stdout_tellpasswords {
	echo "
Here is the application database credentials.
	
 --------------------------------------------------------
|         for WordPress, copy that code block            |
 --------------------------------------------------------
define('DB_SERVER',   '$DB_DEST_HOST');
define('DB_NAME',     '$DB_DEST_DBNAME');
define('DB_LOGIN',    '$DB_DEST_USERNAME');
define('DB_PASSWORD', '$DB_DEST_USERPW');
 --------------------------------------------------------
"
}
function fatal { echo "FAILED: $1"; exit 1; }

function purge_tables {
	echo -n " * Will purge tables from $DB_DEST_DBNAME ... "
	echo "-- Generated by $PN version $VER on `date`" > ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.purge.tmp

	echo -n 'Generating tmp file ... '
	DROPTABLES=`$mysql -u $DB_DEST_USER -h $DB_DEST_HOST -p"$DB_DEST_PASS" -Bse 'show tables' $DB_DEST_DBNAME |sed 's/^/drop table /g' | sed 's/$/;/g'`
	echo $DROPTABLES >> ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.purge.tmp

	echo -n 'Executing ... '	
	cat ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.purge.tmp | $mysql -h $DB_DEST_HOST -u $DB_DEST_USER -p"$DB_DEST_PASS" $DB_DEST_DBNAME
	
	echo -n 'Deleting tmp file ... '
	rm -rf ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.purge.tmp
	echo "DONE"
}

# ///////////////////// ACTUAL EXECUTION //////////////////////
  echo >&2 "$PN - Migrate MySQL database, $VER by evocaTIo Technological Solutions  http://evo.cat.io/"

if [ ! -z $1 ]; then
  if [ ! -f ${PWD}/$PN.conf ]; then
    echo " * You need to have a configuration file"
    dump_config_file
    exit 1
  fi

  echo -n " * Reading configuration file ${PWD}/$PN.conf ... "
    source ${PWD}/$PN.conf
  echo "DONE"
fi

case "$1" in
	transfer)				# Initiate transfer
	    
	    echo -n " * Checking if needed vars are set ... "
	    	ERROR_MSG=""
		    if [ "$SSH_SOURCE" = "" ]; then ERROR_MSG="$ERROR_MSG NO SSH_SOURCE";fi
			if [ -z "$SSH_USER" ]; then ERROR_MSG="$ERROR_MSG NO SSH_USER";fi
			if [ ! -z "$SSH_PORT" ]; then SSH_PORT_STR="-p $SSH_PORT";fi
			if [ -z "$DB_SOURCE_DBNAME" ]; then ERROR_MSG="$ERROR_MSG NO DB_SOURCE_DBNAME";fi
			if [ -z "$DB_SOURCE_USER" ]; then ERROR_MSG="$ERROR_MSG NO DB_SOURCE_USER";fi
			if [ -z "$DB_SOURCE_PASS" ]; then ERROR_MSG="$ERROR_MSG NO DB_SOURCE_PASS";fi
			if [ -z "$DB_SOURCE_HOST" ]; then DB_SOURCE_HOST="localhost";fi
			if [ -z "$OPTS_KEEP_COMPRESSED" ]; then OPTS_KEEP_COMPRESSED="1";fi
			if [ -z "$OPTS_COMPRESSION_PROG" ]; then OPTS_COMPRESSION_PROG="gzip";fi
			if [ ! -z "$ERROR_MSG" ]; then fatal "$ERROR_MSG";fi
		echo "DONE"
	    
	    echo -n " * Checking if remote database exist ... "
	    	DB_EXIST=0
		    DBS=`ssh $SSH_PORT_STR $SSH_USER@$SSH_SOURCE "$mysql  -h $DB_SOURCE_HOST -u $DB_SOURCE_USER -p\"$DB_SOURCE_PASS\" -Bse 'show databases'| egrep -v 'information_schema|$mysql'"`
			for db in $DBS; do
				if [ "$db" = "$DB_SOURCE_DBNAME" ]; then DB_EXIST=1; fi;
			done
			if [ "$DB_EXIST" = "0"  ]; then fatal "DB_SOURCE_DBNAME : $DB_SOURCE_DBNAME does not exist";fi
	    echo "DONE"
	    
	    echo -n " * Get remote database dump file ... "
		    ssh $SSH_PORT_STR $SSH_USER@$SSH_SOURCE "$mysqldump -u $DB_SOURCE_USER -p"$DB_SOURCE_PASS" --opt $DB_SOURCE_DBNAME > ${TMPDIR}$DB_SOURCE_DBNAME.sql; gzip -f ${TMPDIR}$DB_SOURCE_DBNAME.sql; cat ${TMPDIR}$DB_SOURCE_DBNAME.sql.gz" > $DB_SOURCE_DBNAME.sql.gz
	    echo "DONE"
	    
	    echo " * The local database file is $DB_SOURCE_DBNAME.sql.gz"
	    echo "Finished!"
	    exit 0;;
	    
	    
	import)					# Initiate import
	    echo -n " * Checking if needed vars are set ... "
	    	ERROR_MSG=""
			if [ -z "$DB_DEST_DBNAME" ]; then ERROR_MSG="$ERROR_MSG NO_DEST_DBNAME";fi
			if [ -z "$DB_DEST_USER" ]; then ERROR_MSG="$ERROR_MSG NO_DEST_USER";fi
			if [ -z "$DB_DEST_PASS" ]; then ERROR_MSG="$ERROR_MSG NO_DEST_PASS";fi
			if [ -z "$DB_DEST_HOST" ]; then DB_DEST_HOST="localhost";fi
			if [ -z "$DB_DEST_USERHOST" ]; then DB_DEST_USERHOST="localhost";fi
			if [ -z "$OPTS_COMPRESSION_PROG" ]; then OPTS_COMPRESSION_PROG="gzip";fi
			if [ ! -z "$ERROR_MSG" ]; then fatal "$ERROR_MSG";fi
		echo "DONE"

		if [ -f $DB_SOURCE_DBNAME.sql.gz ]; then
			echo -n " * Uncompressing the database dump (Only gzip is supported) ... "
			gunzip $DB_SOURCE_DBNAME.sql.gz
			echo "DONE"
		fi
		
		if [ ! -f $DB_SOURCE_DBNAME.sql ]; then
			fatal "You should have a database dump $DB_SOURCE_DBNAME.sql" 
		fi

		echo -n " * Checking if the requested database exists ... "
	    	DB_EXIST=0
		    DBS=`$mysql -u $DB_DEST_USER -h $DB_DEST_HOST -p"$DB_DEST_PASS" -Bse 'show databases'| egrep -v 'information_schema|$mysql'`
			for db in $DBS; do
				if [ "$db" = "$DB_DEST_DBNAME" ]; then DB_EXIST=1; fi;
			done
			if [ "$DB_EXIST" = "1"  ]; then 
				echo -n "$DB_DEST_DBNAME exists, overwrite? 'y/n': "
				read DOWEPURGE
			        
				case "$DOWEPURGE" in
				  	'y')
						if [ ! -z $ALT_PURGE ]; then
                            purge_tables
						fi
						OVERRIDEUSERCREATION="true"
						;;
				  	*)
				  		fatal "DB_DEST_DBNAME : $DB_DEST_DBNAME exists. And we did not understand the answer to purge DB.";
				  		;;
				esac
			else
			    echo "DONE"
				echo -n " * Creating database ... "
				dump_prepare_sql
				dump_prepare_createdb
				cat ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp | $mysql -h $DB_DEST_HOST -u $DB_DEST_USER -p"$DB_DEST_PASS" 
				echo "DONE"
			fi

		if [ -z $OVERRIDEUSERCREATION ] ; then
		echo -n " * Creating default user ... "
			dump_prepare_sql
			dump_prepare_addpriv
			cat ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp | $mysql -h $DB_DEST_HOST -u $DB_DEST_USER -p"$DB_DEST_PASS"
		echo "DONE"
		fi
	
		echo -n " * Restoring database ... "
			cat $DB_SOURCE_DBNAME.sql | $mysql -h $DB_DEST_HOST -u $DB_DEST_USER -p"$DB_DEST_PASS" $DB_DEST_DBNAME
		echo "DONE"
	
		dump_stdout_tellpasswords

	    echo "Finished!"

	    exit 0;;
	*)  usage;exit 0;;
esac