Nov 9, 2022 2 min read

Backup and restore MySQL databases using the mysqldump command

Backup and restore MySQL databases using the mysqldump command
Photo by Rubaitul Azad / Unsplash
Table of Contents

mysqldump command can only generate output in CSV, XML, and delimited text.

mysqldump user requires privilege.

To make a backup of an entire database

mysqldump db_name > backup-file.sql

To load the dump file back into the server

mysql db_name < backup-file.sql

Another way to reload the dump file

mysql -e "source /path-to-backup/backup-file.sql" db_name

mysqldump is also very useful for populating databases by copying data from one MySQL server to another

mysqldump --opt db_name | mysql --host=remote_host -C db_name

You can dump several databases with one command

mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

To dump all databases, use the --all-databases option

mysqldump --all-databases > all_databases.sql

Dumping Stored Programs

Several options control how mysqldump handles stored programs (stored procedures and functions, triggers, and events):

Dumping Table Definitions and Content Separately

The --no-data the option tells mysqldump not to dump table data, resulting in the dump file containing only statements to create the tables.

The --no-create-info the option tells mysqldump to suppress CREATE statements from the output, so that the dump file contains only table data.

mysqldump --no-data test > dump-defs.sql
mysqldump --no-create-info test > dump-data.sql
For a definition-only dump, add the --routines and --events options to also include stored routine and event definitions
mysqldump --no-data --routines --events test > dump-defs.sql

Note: Issue in Powershell while mysqldump

A dump made using PowerShell on Windows with output redirection creates a file that has UTF-16 encoding:

mysqldump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set (see Impermissible Client Character Sets), so the dump file cannot be loaded correctly. To work around this issue, use the --result-file option, which creates the output in ASCII format:

mysqldump [options] --result-file=dump.sql

For large-scale backup and restore, a physical backup is more appropriate, to copy the data files in their original format so that they can be restored quickly.
Great! You’ve successfully signed up.
Welcome back! You've successfully signed in.
You've successfully subscribed to BugFix.
Your link has expired.
Success! Check your email for magic link to sign-in.
Success! Your billing info has been updated.
Your billing was not updated.