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

I note that during the export dump tables are locked, which can affect the applications that use them. To prevent the tables from blocking, you need to add the key “—single-transaction”, for example:

mysqldump --single-transaction -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

You can unpack it like this:

gzip -d file.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?

Leave a comment

Leave a Reply

Discover more from IT Blog

Subscribe now to keep reading and get access to the full archive.

Continue reading