Home / server / Questions / 163305
Accepted
Toby
Asked: 2010-07-23 22:52:45 +0800 CST

Fastest way to move multiple databases to a new server

  • 772

I have several small to medium sized mysql databases, say about 40 and I need to migrate them from one whm server with different cpanel accounts to another one with the accounts set up from the previous server and old versions of the databases I am moving already in place.

Could anyone recommend the quickest way of doing this, I was going to manually dump each one and import it across but it seems very time consuming, I would like to cut out my machine as the middle man if possible and automate were I can.

migration mysql

2 Answers

  1. Best Answer

    I do not know much about cPanel, but I know how to transfer a database very fast to another server - if you have access to ssh.

    use mysqldump with the proper parameters and chain it with ssh. so the database is imported while the sorce database still exports. no temporary files are used (except mysql internally ;))

    sourceserver# mysqldump --user=user1 --all-databases | ssh targethost 'mysql --user=user2'

    if you authenticate to the source server with your private key and ssh-agent, you could use the -A option of ssh to connect. So you do not need to care about authorization on target side. But keep in mind:

             Agent forwarding should be enabled with caution.  Users with the
         ability to bypass file permissions on the remote host (for the
         agent's Unix-domain socket) can access the local agent through
         the forwarded connection.  An attacker cannot obtain key material
         from the agent, however they can perform operations on the keys
         that enable them to authenticate using the identities loaded into
         the agent.

    (source: man 1 ssh)

    hope this helps a bit

    • 8

  2. Probably a little too much as @krissi answer works very good, but just in case you have to do it more than once, you can use a script like this:

    #!/bin/bash
# MySQL databases migration script
# Jorge Barnaby (jorge {dot} barnaby {at} gmail)

################################################################################
# Configuration variables

ORIG_USER="origin-username"
ORIG_PASS="origin-password"
ORIG_HOST="origin-server"

DEST_USER="destination-username"
DEST_PASS="destination-password"
DEST_HOST="destination-server"

# Do not backup the following databases
IGNORED_DBS="information_schema"

################################################################################
# Start of the program

# Command that runs on the origin server to extract the databases
MYSQL_ORIG="mysqldump -u $ORIG_USER -h $ORIG_HOST -p$ORIG_PASS --add-drop-database --databases"

# Command that runs on the destination server to popuplate the databases
MYSQL_DEST="mysql -u $DEST_USER -h $DEST_HOST -p$DEST_PASS"

# Get all database list first
DBS="$(mysql -u $ORIG_USER -h $ORIG_HOST -p$ORIG_PASS -Bse 'show databases')"

echo
echo -----------------------------------------------------------
echo `date +"%F %T %Z"` : Starting MySQL Migration script
echo -----------------------------------------------------------
echo
echo -- MySQL Origin Server: $ORIG_HOST
echo -- MySQL Destination Server: $DEST_HOST

for db in $DBS
do
    skipdb=-1
    if [ "$IGNORED_DBS" != "" ];
    then
        for i in $IGNORED_DBS
        do
            [ "$db" == "$i" ] && skipdb=1 || :
        done
    fi

    if [ "$skipdb" == "-1" ];
    then
        echo
        echo -- `date +"%F %T %Z"` : Migrating database $db
        # Command to be executed piping mysqldump on the origin and mysql on the remote
        $MYSQL_ORIG $db | $MYSQL_DEST
        echo -- `date +"%F %T %Z"` : Done
    fi
done

echo
echo -----------------------------------------------------------
echo `date +"%F %T %Z"` : All Done
echo -----------------------------------------------------------

exit 0
    • 2