The mysqldump utility is a powerful command-line tool used for creating logical backups of MySQL databases. It generates a set of SQL statements that can be executed to recreate the database objects and data. This is essential for database administration, migration, and disaster recovery.

Export a Single MySQL Database

To export a single database to a file, you can use the following command. Remember that including the password directly in the command can expose it in your shell history.

# To dump a database to a file (Note that your password will appear in your command history!):
mysqldump -u<username> -p<password> <database> > db.sql

# To dump a database to a file (prompt for password):
mysqldump -u<username> -p <database> > db.sql

Compress MySQL Database Dump

For larger databases, compressing the dump file can save significant disk space and transfer time. You can pipe the output of mysqldump to gzip.

# To dump a database to a .tgz file (Note that your password will appear in your command history!):
mysqldump -u<username> -p<password> <database> | gzip -9 > db.tgz

# To dump a database to a .tgz file (prompt for password):
mysqldump -u<username> -p <database> | gzip -9 > db.tgz

Export All MySQL Databases

If you need to back up all databases on your MySQL server, use the --all-databases option.

# To dump all databases to a file (Note that your password will appear in your command history!):
mysqldump -u<username> -p<password> --all-databases > all-databases.sql

# To dump all databases to a file (prompt for password):
mysqldump -u<username> -p --all-databases > all-databases.sql

Export Database Structure or Data Only

You can selectively export only the database structure (schema) or only the data, which is useful for specific migration or analysis tasks.

# To export the database structure only:
mysqldump --no-data -u<username> -p <database> > dump_structure.sql

# To export the database data only:
mysqldump --no-create-info -u<username> -p <database> > dump_data.sql

For more detailed information on mysqldump options and usage, refer to the official MySQL documentation.