Backup and restore MySQL databases using the mysqldump command

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

mysqldump user requires privilege.

• SELECT privilege for dumped tables.
• SHOW VIEW for dumped views.
• TRIGGER for dumped triggers.
• LOCK TABLES if the --single-transaction option is not used.
• PROCESS if the --no-tablespaces option is not used.

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):

• --events: Dump Event Scheduler events.
• --routines: Dump stored procedures and functions.
• --triggers: Dump triggers for tables.

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