Full Database Backup
mysqldump -u root -p mydb > mydb_full.sql
Single Table Backup
mysqldump -u root -p mydb users orders > mydb_partial.sql
Schema Only (No Data)
mysqldump -u root -p --no-data mydb > schema.sql
Data Only (No Schema)
mysqldump -u root -p --no-create-info mydb > data.sql
WHERE Clause (Partial Data)
# Backup only active users
mysqldump -u root -p mydb users --where="status='active'" > active_users.sql
# Backup recent orders
mysqldump -u root -p mydb orders --where="created_at > '2026-01-01'" > recent_orders.sql
All Databases
mysqldump -u root -p --all-databases > all_databases.sql
With Stored Procedures and Events
mysqldump -u root -p --routines --events --triggers mydb > mydb_complete.sql
Compressed Backup
# Backup and compress in one step
mysqldump -u root -p mydb | gzip > mydb_$(date +%Y%m%d).sql.gz
# Restore from compressed backup
gunzip < mydb_20260225.sql.gz | mysql -u root -p mydb
Important Options
| Option | Purpose |
|---|
--single-transaction | Consistent InnoDB backup without locking |
--quick | Write rows one at a time (saves memory) |
--lock-tables | Lock tables during dump (MyISAM) |
--add-drop-table | Add DROP TABLE before CREATE (default) |
--no-tablespaces | Suppress tablespace info |
--hex-blob | Dump binary data as hex |
Restore
# Restore full backup
mysql -u root -p mydb < mydb_full.sql
# Restore to a new database
mysql -u root -p -e "CREATE DATABASE mydb_restore"
mysql -u root -p mydb_restore < mydb_full.sql