Using mysqldump and gzip to move a database to another server

When your database is large enough that using phpMyAdmin is not feasible, you can use mysqldump to move a database to a new server.


This looks like a reasonable tutorial, which came up with a google search:


http://www.mydigitallife.info/2007/07/21/how-to-backup-and-restore-export-and-import-mysql-databases-tutorial/


Essentially all you do is:

# mysqldump dbname > whatever.sql
-- or --
# mysqldump --all-databases > mysqldump.sql

If you import the "all databases" file, it should be into a clean mysql installation. Do it like this:
# mysql < mysqldump.sql

To import a single DB dump it's just about as simple as:
# mysql
# create database dbname;
# mysql dbname < dbname.sql

after you send it to the new server.

If the database is over .5 GB, I would use gzip to compress the output file before moving it to the new server:

Make the SQL dump file:
mysqldump -u username -ppassword database_name > dump.sql | gzip > outputfile.sql.gz


Then on your new server:
gunzip < outputfile.sql.gz | mysql -u username -ppassword database_name < dump.sql