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.
Exporting a MySQL database is essentially creating a text file that looks like this:
Database design ( tables, columns, schema etc.)
Data are stored in tables
Indexes, Triggers and Stored Procedures
This .sql file is to be used to restore or copy the DB to another system.
Most common tool for export MySQL databases is mysqldump.
Windows: Command Prompt or PowerShell Linux/Mac: The Terminal
Use the following syntax please:
mysqldump -u username -p database_name > backup.sqlFor example:
mysqldump -u root -p my_database > my_database_backup.sqlRun 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 .
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.sqlThis 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.gzThis reduces file size and makes storage and transfer a lot easier, especially for cloud backups or server migrations.
You can also improve your export by adding useful flags:
mysqldump -u root -p --routines --triggers --single-transaction my_database > backup.sqlWhat these options do, are:
--single-transaction → Consistent backups without table locking
--routines → Functions and stored procedures
--triggers → Includes the triggers of the table
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.sqlAccess Refused
check username and password are correct
Database permission check
Export empty or incomplete
Validate DB name
Check tables are in place and available
Command not recognized
MySQL is not installed or not in PATH
Always export at times of low traffic for large databases.
Store your backups in multiple places
Compression for large datasets
Schedule exports with cron jobs or scheduled tasks
Restore your .sql files frequently
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.