cPanel – Database-only Migration

Sometimes you may come across some situations where you need to migrate all the databases in the server but not the account files. This may happen if you have accounts in one server and databases in other server. The cpanel script “pkgacct” may not help you with this kind of setup. But the following set of script will help you to complete all database migration from one DB server to another DB server.

1) First create a new directory to store the database backups you are about to take.
mkdir /home/sqlbackups
Otherwise your backups will be stored under your Current Working Directory. It may cause confusions if you already have some backup files in your CWD.  I would suggest you to execute this command in both Source and Destination DB server.

2) Now time to take a backup of all the databases in your server with the following one liner:
for I in $(mysql -e ‘show databases’ -s –skip-column-names); do mysqldump $I > /home/sqlbackups/$I.sql; done
The above script will create a backup sql files of your databases and store it under /home/sqlbackups directory.

3) Now time to copy the DB backups over to the destination server using SCP command:
scp /home/sqlbackups/*.sql root@destinationServerIP:/home/sqlbackups
This will copy the Database backups over to the destiantion server and store them under “/home/sqlbackups” directory in the destination server.

scp -P Portnumber /home/sqlbackups/*.sql root@destinationServerIP:/home/sqlbackups
The above command can be used if the destination server is using a different SSH port.

4) Now you need to create a list of Databases in the source server and send it over to the Destination server:
mysql -e ‘show databases’ -s –skip-column-names >> sql.txt
This command will generate the DB list.

scp sql.txt root@destinationServerIP:/home/mysqlbackups
This command will send the sql.txt file over to destination server under the directory “/home/mysqlbackups/”

scp -P Portnumber root@destinationServerIP:/home/mysqlbackups
The above command can be used if the destination server is using a different SSH port.

5) Now time to restore the all the DB backups at the destination server with the following one liner.
for I in $(cat /home/mysqlbackups/sql.txt); do mysql $I < /home/sqlbackups/$I.sql; done

Once the process is completed and you have migrated the databases only to your new DB server. Have a Great Day!!!!

Advertisements

One thought on “cPanel – Database-only Migration”

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s