How to export an SQL file using the command line in MySQL

In this Answer, we’ll walk through the steps to export an SQL file from a MySQL database using the command line. This process is commonly used for creating backups or transferring database data between systems.

Prerequisites

Before we get started, make sure we have the following:

• A MySQL server installed on our system

• MySQL command-line client (usually included with MySQL installation)

• Appropriate access permissions to the database we want to export

Process

Following is the process to export an SQL file using the command line in MySQL:

Step 1: Open a terminal

First, open a terminal or command prompt on our system. This is where we’ll enter the necessary commands.

Step 2: See all the databases in MySQL (optional)

We can log into the MySQL server to look for the available databases from which we can export the desired database. Replace [username] with our MySQL username, root:

Note: After running this command, we’ll be prompted to enter our MySQL password.
The default [username] is root and the default password is empty. Since the default password is empty, we can also drop the -p tag in the above command.

Step 3: Export the database

This step is the crux of this whole process. We can use the mysqldump command to export the database to an SQL file. Replace [output_file.sql] with the desired name for our SQL export file:

Note: We’ll be prompted to enter our MySQL password once more. We can also drop the -p tag in the above command, if we haven't changed the default empty password.

Step 4: Verify the export

After running the mysqldump command, our database will be exported to the specified SQL file. We can verify this by listing the files in our current directory:

Note: We should see the [output_file.sql] in the list of files.

Example

Type in the commands from the steps 2–4 one-by-one.

Note: If you don’t have a database to test this export command on, you can use mysql database as the database to be exported into output_file.sql.

We should see a newly created output_file.sql in our current directory.

Conclusion

We’ve successfully exported an SQL file from a MySQL database using the command line. This SQL file can be used for backup purposes, migration, or sharing data with others. Remember to keep your exported SQL files secure, as they might contain sensitive data.