Fastest way to move multiple databases to a new server
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
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