How to export SQL file from MySQL using Command Line

Structured Query Language is a computer language that we use to interact with a relational database.SQL is a tool for organizing, managing, and retrieving archived data from a computer database. 

Backups of the database are required whether the project is small or large. If something goes wrong, the database can be easily reverted. This article will teach you how to use the command line to export SQL files from MySQL.

Exporting From MySQL

The best tool for exporting a MySQL database to a sql file is mysqldump.

To use mysqldump, you will need to know the login credentials of an appropriate MySQL user that has the necessary privileges to export the database in question.

With that information in hand, enter the mysqldump command with the appropriate flags and options:

mysqldump -u username -p database_name > output_file_path

The options in use are:

• The -u flag indicates that the MySQL username will follow.
• The -p flag indicates we should be prompted for the password associated with the above username.
• database_name is of course the exact name of the database to export.
• The > symbol is a Unix directive for STDOUT, which allows Unix commands to output the text results of the issued command to another location. In this case, that output location is a file path, specified by output_file_path.

How to export SQL files from MySQL using Command Line

There are different methods to export Databases from MySQL using the command line. We will be using them one by one.

1. Easiest Way

mysqldump -u username -p password dbname > filename.sql

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

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

mysqldump -u username -p password dbname | gzip > filename.sql.gz

2. Other Ways

mysqldump --databases --user=root --password dbname > filename.sql

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

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

mysqldump -u user -p dbname | gzip > filename_to_compress.sql.gz

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

Thank you for reading this article.

Read Also: How to import SQL file to MySQL using Command Line