mysqldump: MySQL Database Backup and Restore Guide

MySQL / MariaDB · 19.04.2026

Basic mysqldump Commands

mysqldump is the standard utility for creating logical backups of MySQL and MariaDB. It produces a SQL file with all commands needed to recreate the database structure and data.

Backup a Single Database

# With compression (recommended)
mysqldump -u root -p --single-transaction --quick mydb | gzip > mydb_$(date +%Y%m%d).sql.gz

Always use --single-transaction for InnoDB tables — creates a consistent snapshot without table locks.

Useful mysqldump Flags

Flag	Description
`--single-transaction`	Consistent InnoDB backup without locks
`--no-data`	Structure only, no data
`--all-databases`	Backup all databases
`--routines`	Include stored procedures

Restore from Dump

mysql -u root -p mydb < mydb_backup.sql
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

Automate with Cron

0 2 * * * root mysqldump -u backup_user -pPass --single-transaction \
    --all-databases | gzip > /backups/mysql/all_$(date +\%Y\%m\%d).sql.gz && \
    find /backups/mysql/ -name "*.sql.gz" -mtime +7 -delete

Never use root for automated backups. Create a dedicated backup user with minimal privileges. Store the password in ~/.my.cnf, not in scripts.