Network Solution for Windows – Windows system, Linux system, Ubuntu server configuration tip

If you want 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.

• 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.

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.

#!/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

ConfigureYou 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.

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

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

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