Recently I had to migrate all 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). Make it executable:

chmod a+x /root/bin/sync_db.bash

Configure

You probably only need to change these 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"

Make sure that the MySQL users have sufficient rights on both sides and you're ready to rock.

Run

Easy, make sure you've read the warning and just:

/root/bin/sync_db.bash          # or whereever you stored it

Tip

During the transfer, I wanted to make sure that no changes could be made to the active database. So: 

• I sett up iptables to block every connection to port 3306 (MySQL), except from the receiving host.

Suggestions?

Did I overlook something? Know better ways? Got improvements?

Post a comment, I'll be happy to update the article with your proposed improvements. 

Stay up to date

You can track my blog articles and comments. You may also find my bookmarks interesting. Or Follow me on Twitter