DEV Community

Cover image for How to Back Up and Restore MySQL Using mysqldump
DbVisualizer
DbVisualizer

Posted on

How to Back Up and Restore MySQL Using mysqldump

#sql #mysql #backup

If you need to back up or move your MySQL data quickly, mysqldump is the tool for the job. It exports databases, tables, or just the schema into SQL scripts that can be restored anywhere.

This guide shows how to use it with common examples and must-know options.

Core Commands

Backup full database: 

mysqldump -u admin company > company_backup.sql
Enter fullscreen mode Exit fullscreen mode

Restore from SQL file: 

mysql -u admin new_company < company_backup.sql
Enter fullscreen mode Exit fullscreen mode

Export only data: 

mysqldump -u admin company --no-create-info > data.sql
Enter fullscreen mode Exit fullscreen mode

Export only schema: 

mysqldump -u admin company --no-data > schema.sql
Enter fullscreen mode Exit fullscreen mode

Specific tables: 

mysqldump -u admin company --tables orders products > tables.sql
Enter fullscreen mode Exit fullscreen mode

Useful Flags

  • -routines: Includes stored procedures
  • -events: Exports event scheduler definitions
  • -lock-tables: Locks during dump (default for MyISAM)
  • -single-transaction: Faster, consistent backups for InnoDB
  • -result-file: Use instead of > for better encoding on Windows

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 \bin\mysqldump.exe
Replace 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 one of the most important tools in a MySQL user's toolkit. It’s ideal for quick dumps, full backups, or schema exports. With the right flags, you can ensure fast, consistent results every time.

Prefer GUI? DbVisualizer lets you export entire databases in just a few clicks—perfect for those who want power without the CLI.

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

Top comments (0)