How To Export A MySQL Database Using The Command Prompt

1. Easiest Way

For Export:

$ mysqldump -u [username] -p [password] [dbname] > filename.sql

To Import:

$ mysql -u username -p database_name < file.sql

Note: It is better to use the full path of the SQL file ‘file.sql'

And if you wish to zip it at the same time:

To Export:

mysqldump -u [username] -p [password] [db] | gzip > filename.sql.gz

To Import:

$ gunzip filename.sql.gz | mysql -u [user] -p [password] [database]

2. Other Ways

For Export:

$ mysqldump --databases --user=root --password your_db_name > export_into_db.sql

The generated file will be available in the same directory where you had ran this command.

To use gzip (or just add ‘.gz' to the end of the command above):

For Export:

$ mysqldump -u [user] -p [db_name] | gzip > [filename_to_compress.sql.gz]

For Import:

gunzip < [compressed_filename.sql.gz]  | mysql -u [user] -p[password] [databasename]

Note: There is no space between the keyword ‘-p' and your password.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.