How to back up and restore a MySQL database with mysqldump

Create a complete logical backup of a MySQL or MariaDB database and restore it on the same or a different server using mysqldump and the mysql client.

Prerequisites

• MySQL or MariaDB server running
• mysqldump and mysql client installed (bundled with the server package)
• A user with SELECT and LOCK TABLES privileges for backup, and CREATE/INSERT for restore

Step-by-Step: Back Up and Restore with mysqldump

1. Back Up a Single Database with mysqldump

mysqldump -u root -p myapp > myapp_backup.sql

The mysqldump command exports the database schema (CREATE TABLE statements) and data (INSERT statements) to a SQL file. The -p flag prompts for the password.

2. Back Up All Databases with mysqldump

mysqldump -u root -p --all-databases > all_databases.sql

3. Create a Compressed Backup with mysqldump

Pipe the mysqldump output through gzip to reduce backup file size:

mysqldump -u root -p myapp | gzip > myapp_backup.sql.gz

4. Restore a Database from a mysqldump Backup

mysql -u root -p myapp < myapp_backup.sql

The MySQL client reads the SQL file and executes each statement to recreate the schema and data. The target database must exist — create it first with CREATE DATABASE myapp; if needed.

5. Restore a Compressed Backup

gunzip < myapp_backup.sql.gz | mysql -u root -p myapp

How to Verify the Backup Was Successful

Check the backup file size and peek at its contents:

ls -lh myapp_backup.sql
head -20 myapp_backup.sql

The file should start with mysqldump header comments and contain CREATE TABLE and INSERT statements.

Common Issues

mysqldump hangs on large databases— Add --single-transaction for InnoDB tables to avoid locking: mysqldump --single-transaction -u root -p myapp > backup.sql.

Restore fails with "Access denied"— The MySQL user lacks CREATE or INSERT privileges on the target database. Grant privileges with GRANT ALL ON myapp.* TO 'user'@'localhost';.

For automated daily backups, schedule the mysqldump command with crontab. See How to schedule a script with crontab.