Basic mysqldump Commands

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

Backing Up a Single Database

# Basic backup
mysqldump -u root -p mydb > mydb_backup.sql

# With compression (recommended)
mysqldump -u root -p mydb | gzip > mydb_$(date +%Y%m%d_%H%M%S).sql.gz

# Without locking tables (for InnoDB)
mysqldump -u root -p --single-transaction --quick mydb | gzip > mydb.sql.gz

Always use --single-transaction for InnoDB databases — it creates a consistent snapshot without locking tables. Without this flag the site may "freeze" during the backup.

Useful mysqldump Flags

Flag	Description
`--single-transaction`	Consistent InnoDB backup without locks
`--quick`	Read rows one at a time (saves RAM)
`--no-data`	Structure only, no data
`--no-create-info`	Data only, no CREATE TABLE
`--all-databases`	Back up all databases
`--routines`	Include stored procedures
`--triggers`	Include triggers (ON by default)

Backing Up All Databases

mysqldump -u root -p --all-databases --single-transaction \
    --routines --triggers | gzip > all_databases_$(date +%Y%m%d).sql.gz

Restoring from a Dump

# Restoring a plain dump
mysql -u root -p mydb < mydb_backup.sql

# Restoring a compressed dump
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

# Or with zcat
zcat mydb_backup.sql.gz | mysql -u root -p mydb

Automating with cron

sudo nano /etc/cron.d/mysql-backup

# Backup every day at 2:00, keep for 7 days
0 2 * * * root mysqldump -u backup_user -pPassword --single-transaction \
    --all-databases | gzip > /backups/mysql/all_$(date +\%Y\%m\%d).sql.gz && \
    find /backups/mysql/ -name "*.sql.gz" -mtime +7 -delete

Creating a Backup-Only User

mysql -u root -p -e "
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongBackupPass';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;"

Do not use root for automated backups — create a dedicated user with minimal privileges. Keep the password in ~/.my.cnf, not in your scripts.

Password in ~/.my.cnf (no password prompt)

cat ~/.my.cnf
[mysqldump]
user=backup_user
password=StrongBackupPass

chmod 600 ~/.my.cnf
# Now you can omit -p:
mysqldump mydb > backup.sql

Common mysqldump Errors

Error	Cause	Fix
Got error: 1044: Access denied for user to database	The user lacks the SELECT privilege on that specific database, even though GRANT was issued elsewhere	Grant access explicitly: `GRANT SELECT, LOCK TABLES ON mydb.* TO 'backup_user'@'localhost';`
Error 2013: Lost connection to MySQL server during query	Dumping a large table exceeded wait_timeout, or the --quick flag wasn't used	Add `--quick` and raise `wait_timeout` in my.cnf for the backup session
Warning: A partial dump from a server that has GTIDs...	GTID is enabled on the server, and a dump without an explicit mode can break replication when restored on another server	Add `--set-gtid-purged=OFF` if the dump goes anywhere other than a replica of the same cluster
ERROR 1146 (42S02): Table doesn't exist during restore	The dump was taken with --no-data, or the dump process was interrupted midway	Check the dump for completeness and recreate it without --no-data if both structure and data are needed

Comparing MySQL Backup Methods

Method	Backup type	Table locking	Best for
mysqldump	Logical (SQL file)	None with --single-transaction for InnoDB	Small and medium databases, portability across versions
Percona XtraBackup	Physical (data files)	None, fully hot backup	Large databases where restore speed matters
Binlog + periodic snapshot	Incremental, point-in-time	None	Restoring state at the exact second before a failure

When mysqldump Is Not a Good Fit

The database weighs hundreds of gigabytes — a logical dump and its restore can take hours, while a physical backup (XtraBackup) is far faster
Recovery is needed for an arbitrary second before an incident — that requires binary logs, not a once-a-day dump
MyISAM tables under constant active writes — even with flags, the lock at dump time is noticeable to users
The dump is taken from a heavily loaded replication master — it's better to dump from a replica to avoid adding extra load

For databases where a logical dump no longer fits the time budget, see "Hot MySQL Backup with Percona XtraBackup", and for recovery to an exact moment in time, see "MySQL binlog: point-in-time recovery step by step".

Verifying the Result

Don't trust a backup that has never been test-restored:

# The dump should not be empty or cut off midway
tail -5 mydb_backup.sql
# The last line is usually "-- Dump completed on ..."

# The table count in the dump should match the source database
grep -c "^CREATE TABLE" mydb_backup.sql
mysql -u root -p -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mydb';"

# Trial restore into a temporary database
mysql -u root -p -e "CREATE DATABASE mydb_restore_test;"
mysql -u root -p mydb_restore_test < mydb_backup.sql
mysql -u root -p -e "DROP DATABASE mydb_restore_test;"

If the site being backed up runs WordPress, it's worth cross-checking the dedicated checklist in "WordPress Backup: UpdraftPlus and Manual Methods" — it also covers files, not just the database.