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?