mysqldump Deep Dive: Backup and Restore MySQL with Precision

mysqldump is a core tool for creating logical MySQL backups from the command line. It generates SQL scripts to replicate the schema and data of your databases, making it useful for version control, recovery, or data migration.

This guide covers syntax, advanced flags, and practical examples to help developers run secure, consistent backups in any environment.

Command Syntax Overview

• Dump a single database:

mysqldump -u admin company > company_backup.sql

• Restore:

mysql -u admin new_company < company_backup.sql

• Dump tables only:

mysqldump -u admin company --tables orders products > orders_products.sql

• Export schema only:

mysqldump -u admin company --no-data > schema.sql

• Export data only:

mysqldump -u admin company --no-create-info > data.sql

Key mysqldump Flags and Features

• -routines: Export stored procedures and functions

• -events: Include scheduled events

• -result-file=dump.sql: Directs output to file

• -lock-tables: Explicit locking for data consistency

• -single-transaction: Better performance for InnoDB

• -tab: Outputs both .sql and .txt for each table

For a comprehensive breakdown, refer to the official docs.

FAQ

Where does the output of mysqldump go?

By default, mysqldump writes to standard output. To save the dump to a file, use redirection like > dump.sql or the --result-file=dump.sql option.

What is the path to mysqldump.exe?

On Windows, it’s typically found under:
C:\Program Files\MySQL\MySQL Server <version>\bin\mysqldump.exe
Replace <version> with your MySQL version.

Can I run mysqldump on a remote server?

Yes. Use --host (or -h) to specify the remote server's address. Ensure remote access is enabled and credentials are correctly provided.

What privileges are needed to use mysqldump?

You need SELECT for tables, SHOW VIEW for views, and TRIGGER for triggers. Extra privileges may be required depending on options used.

Conclusion

mysqldump is a tried-and-true command-line tool that helps developers and administrators safeguard their data. From partial table exports to full schema snapshots, it provides the flexibility needed in modern MySQL workflows.

For teams looking to manage exports visually, DbVisualizer provides export tools, ERD generation, and support for over 50 DBMSs—all in one GUI. Ideal for cross-platform database development and routine backups.

Check out mysqldump: How to Backup and Restore MySQL Databases article for more info.