JulianSchrader.de

MySQL: Moving databases easily with mysqldump

As a follow up to my last post on MySQL, How to allow connections from outside of localhost, I’d like to write down how I recently moved some databases from one server to another (I assume you’re on UNIX).

First of all, you’ll need to use the mysqldump command to dump your database into a file:

mysqldump -u [dbuser] -p -h [database.host.com] -​-opt [dbname] > db_dump.sql

After filling in your values for dbuser, your database host and your database’s name, mysqldump will prompt you for the database user’s password—enter it and you’re almost done exporting your data.

Assuming you have shell access to both servers involved, you can use scp to copy the dump file over to the new machine easily and securely:

scp db_dump.sql sshuser@newhost.com:/directory/on/the/new/server/db_dump.sql

With the database dump file copied over, all there’s left to do is to fill it into the new database! Since the dump file consists of SQL queries, we’ll do the last step like this:

mysql -u [dbuser] -D [dbname] -p < db_dump.sql

That’s it—the user and database names you enter in this step will probably differ from the ones in step #1, but all in all, that’s how you move a MySQL database.

A word of caution though: If you want to move a database for a webproject which requires a lot of user interaction with the database, you’ll want to look for other methods or put your webapp in maintenance mode during the switch—you don’t want your users to lose data they enter on the old server after you dumped the database.