How do I export a .sql file via the command-line?

Exporting a .sql file through the command-line is one of the best ways to backup or migrate a database from one server to another. It is fast, scriptable and works robustly across environments. This is the reason why it is so popular among developers and sysadmins.

In this tutorial we will export a MySQL database to a .sql file using simple command line tools.

What does exporting a .sql file mean?

Exporting a MySQL database is essentially creating a text file that looks like this:

This .sql file is to be used to restore or copy the DB to another system.

Method 1: Export using mysqldump (Most Common Method)

Most common tool for export MySQL databases is mysqldump.

Step 1: Open the Command-Line Interface

Windows: Command Prompt or PowerShell Linux/Mac: The Terminal

Step 2 — Run the export command

Use the following syntax please:

mysqldump -u username -p database_name > backup.sql

For example:

mysqldump -u root -p my_database > my_database_backup.sql

Run this command and you will be prompted for your password. When you run it, mysql will create a. sql file in your current directory with the export of the whole database .

Method 2. Export Specific Tables Only

If you do not want to export the whole database you can export specific tables:

mysqldump -u root -p my_database table1 table2 > partial_backup.sql

This is handy if you only need a subset of a database for testing or migration purposes.

For large databases you can zip the exportfile:

mysqldump -u root -p my_database | gzip > backup.sql.gz

This reduces file size and makes storage and transfer a lot easier, especially for cloud backups or server migrations.

Method 4: Export with additional options

You can also improve your export by adding useful flags:

mysqldump -u root -p --routines --triggers --single-transaction my_database > backup.sql

What these options do, are:

Where the Export File is Saved

The .sql file is saved in the directory from which you issue the command, by default. You can also specify a full path:

mysqldump -u root -p my_database > /home/user/backups/my_database.sql

Troubleshooting and Solutions

Access Refused

Export empty or incomplete

Command not recognized

Exporting Databases – Best Practices

  1. Always export at times of low traffic for large databases.

  2. Store your backups in multiple places

  3. Compression for large datasets

  4. Schedule exports with cron jobs or scheduled tasks

  5. Restore your .sql files frequently

Conclusion

Exporting a .sql file from the command line is a simple but powerful skill, helping you preserve your data and allowing for smooth migrations. Tools like mysqldump make it easy to make full or partial backups, and compress and store them securely.

Once you have the command, exporting databases is a quick and reliable part of your workflow.