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:

mysql -u user -pPASSWORD DATABASE_NAME < DATABASE_NAME.sql

See also my article:

How to see the process of importing a SQL file?

Did my article help you? How about buying me a cup of coffee as an encouragement? Buy me a coffe.

Leave a comment

Leave a Reply