How to Use mysqldump for MySQL Database Backup

Use mysqldump to export your MySQL database by running mysqldump -u [user] -p [database_name] > backup.sql. This command creates a backup file with SQL statements to recreate your database.

Syntax

The basic syntax of mysqldump is:

• mysqldump: The command to export the database.
• -u [user]: Your MySQL username.
• -p: Prompts for your password securely.
• [database_name]: The name of the database you want to export.
• > [filename].sql: Redirects the output to a file.

bash

mysqldump -u [user] -p [database_name] > [filename].sql

Example

This example exports a database named shopdb using user admin. It creates a file called shopdb_backup.sql with all the SQL commands to recreate the database.

bash

mysqldump -u admin -p shopdb > shopdb_backup.sql

Output

Enter password: 
# After entering the password, the file 'shopdb_backup.sql' is created with the database dump.

Common Pitfalls

Common mistakes when using mysqldump include:

• Not specifying the -p flag and expecting a password prompt.
• Forgetting to redirect output to a file, which prints SQL to the terminal.
• Using incorrect database names or user credentials.
• Not having proper permissions to access the database.

Always check your command and credentials before running.

bash

Wrong: mysqldump -u admin shopdb
Right: mysqldump -u admin -p shopdb > shopdb_backup.sql

Quick Reference

Option	Description
-u [user]	MySQL username
-p	Prompt for password
[database_name]	Name of the database to export
> [file].sql	Redirect output to a file
--all-databases	Export all databases
--single-transaction	Dump with consistent snapshot for InnoDB tables

Key Takeaways

Use mysqldump with -u and -p to securely export your database.

Always redirect output to a file to save the backup.

Check your database name and credentials before running the command.

Use --all-databases to export all databases at once.

Use --single-transaction for consistent backups of InnoDB tables.