Easy MySQL Database Migration

Tonight I’m going to put down something I learned just yesterday. How to easily migrate MySQL Databases across servers via the command line. Now I’m a beginner Linux Admin as my focus is on PHP Development but ever since I got myself a dedicated box at Rackspace I’ve had to learn a few tricks. Over the past month I have learned a host of them due to migrating from one Rackspace box to another. My biggest challenge was moving some of my MySQL Databases. A couple of them were quite large and PHPMyAdmin was just not up to the challenge. So after some searching and one phone call to the excellent Rackspace support team I had my solution and I am going to share this tidbit with you all.

The solution is something that any MySQL guru probably already knows but I think beginners and amateurs in command line techniques will benefit. It consist of three commands. They are as follows:

mysqldump --database --opt [DB NAME] > [DST FILE NAME].sql
scp [DST FILE NAME].sql [RMT BOX UN]@[RMT BOX IP]:[RMT BOX DST DIR]
mysql [DB NAME] < [TRANSFERRED FILE NAME].sql

The first and last commands are mysql commands and the middle one is a Linux command. What these three commands basically do when used in this sequence is:

1. Dump a DB to a SQL file
2. Transfer the created SQL file to another machine. (scp = Secure copy)
3. Fill a DB with the contents of a SQL file.

When you run the scp command you will be prompted for the password associated with the username you are trying to connect with. Now scp only works on the same network but you can get around this by downloading the sql file via FTP and then uploading it to the destination machine.

You will also need to create the DB before running the last command.

Now this is pretty straight forward and easy to accomplish and now that I have this knowledge I am able to move databases in less then a minute! This made my last night of migrating painless. Also it will make things easy when I need to synce the servers DB’s with my own local machine for development or bug testing.

Here are links to the documentation for each command if you want to delve deeper into any of the available options.

mysqldump
scp
MySQL – The official website

All questions and comments are welcome!

Thanks,
– Miah

Advertisement