Import and export MySQL databases

Below are examples of importing and exporting MySQL databases from a Linux terminal.

Export the database to a file like this:

mysqldump -u user -h localhost -p DATABASE_NAME > DATABASE_NAME.sql

You can export directly to the archive:

mysqldump -u user -h localhost -p DATABASE_NAME | gzip -c > /dir/DATABASE_NAME_`date +%Y-%m-%d`.sql.gz

Export several databases into one file and immediately compress it as follows:

mysqldump -u user -h localhost -p -B DATABASE_NAME1 DATABASE_NAME2 | gzip -c > /dir/DATABASE_NAME_`date +%Y-%m-%d`.sql.gz

You can import the compressed file back into the database like this:

mysql -u user -p DATABASE_NAME < /dir/DATABASE_NAME_`date +%Y-%m-%d`.sql.gz

A non-compressed file is similar:

mysql -u user -p DATABASE_NAME < /dir/DATABASE_NAME_`date +%Y-%m-%d`.sql

The -p option indicates that you must enter the password during the connection to the MySQL server, so that you can not enter it in the command (for security reasons it is not desirable), for example:


Leave a Reply