Tranfer all MySQL databases to another server

I have managed migrate all of my MySQL databases from one server to another. This was a one time only operation, so setting up replication wasn't an option. And restoring a backup on the new server involved more downtime than strictly necessary. I figured the most ideal way would be to dump the active MySQL directly into the new MySQL instance. And that's what I did.


Features
* This often beats setting up replication if you want a quick & dirty solution.

* Extremely fast because it injects the MySQL dump directly into the new MySQL database, hence it does not use additional diskspace or disk IO.

* InnoDB proof.

* By putting everything in one script we can instantly create snapshots with recent data. This allows you to focus on other issues that might pop up during a database migration.

* And yes, you could schedule this script in the crontab and sync to a standby server for emergency failover. But consider replication or clustering instead.

Warning
Be careful, this script will drop any existing database on the receiving side it encounters. USE AT OWN RISK! This is heavy material and you could seriously mess up your system if you don't know what you're doing.


The Script
#!/bin/bash
# where your mysql commands are
CMD_MYSQL="/usr/bin/mysql"
CMD_MYSQLDUMP="/usr/bin/mysqldump"

# login credentials of the submitting side
DB_USER_FROM="root"
DB_PASS_FROM="passxxx"
DB_HOST_FROM="source.example.com"

# login credentials of the receiving side
DB_USER_TO="root"
DB_PASS_TO="passxxx"
DB_HOST_TO="destination.example.com"

# index all databases on the local (submitting) side
echo "database sync"
DATABASES=`echo "SHOW DATABASES;" | ${CMD_MYSQL} -p${DB_PASS_FROM} -u ${DB_USER_FROM} -h ${DB_HOST_FROM}`

# loop over all the databases
for DATABASE in $DATABASES; do
# skip non-databases
if [ "${DATABASE}" != "Database" ] && [ "${DATABASE}" != "information_schema" ]; then
echo "transmitting ${DATABASE}"

# create database on remote (receiving) side if it doesn't exist
echo "CREATE DATABASE IF NOT EXISTS ${DATABASE}" | ${CMD_MYSQL} -p${DB_PASS_TO} -u ${DB_USER_TO} -h ${DB_HOST_TO}

# dump the current database and pipe it directly to the remote (receiving) side to inject it
${CMD_MYSQLDUMP} -Q -B --create-options --delayed-insert --complete-insert --quote-names --add-drop-table -p${DB_PASS_FROM} -u${DB_USER_FROM} -h${DB_HOST_FROM} ${DATABASE} | ${CMD_MYSQL} -p${DB_PASS_TO} -u ${DB_USER_TO} -h ${DB_HOST_TO} ${DATABASE}
fi
done


Save this script somewhere (I usually take the /root/bin dir for this) and Make it executable:


Configure
You need to change following variables to suit your environment:

DB_USER_FROM="root"
DB_PASS_FROM="passxxx"
DB_HOST_FROM="source.example.com"

DB_USER_TO="root"
DB_PASS_TO="passxxx"
DB_HOST_TO="destination।example.com"]

Now run the script (be sure read the warning above).