Mysql: Export entire database via command line (and import it again)

Sometimes, when migrating servers or copying data to a development environment for example, one needs to take a copy of an entire mysql database. Here is how you do this via the command line on a redhat/centos server:

Please note this is NOT done from with mysql but is just run from the general prompt:

Export mysql database

$ mysqldump -u [username] -p[password] databasename > db_backup_file.sql

If the database file is going to be large you can also compress the output:

$ mysqldump -u [username] -p[password] databasename | gzip > db_backup_file.sql.gz

Import mysql database

So, you have your db_backup_file.sql but now you need to import it into another database somewhere. ¬†Here’s how:

$mysql u -username -p new_database_name < db_backup_file.sql

This should then ask for the password and off you go.  If you gzipped you sql file then you should probably unzip it first:

gunzip db_backup_file.sql.gz



Facebook Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Apply your human brain cells and complete this highly complicated maths problem *