mysqldump has been the standard MySQL backup tool for decades — simple, reliable, and available on essentially every MySQL installation without extra setup.
# Back up a single database mysqldump -u root -p --single-transaction mydb > mydb-backup.sql # Back up all databases mysqldump -u root -p --all-databases > full-backup.sql # Compress on the fly to save space mysqldump -u root -p mydb | gzip > mydb-backup.sql.gz # Restore from a backup mysql -u root -p mydb < mydb-backup.sql
–single-transaction Matters
Without it, mysqldump against InnoDB tables locks them during the backup, blocking writes on a live production database. --single-transaction takes a consistent snapshot without locking, letting the backup run against a live database safely.
# A simple nightly backup cron entry with rotation 0 2 * * * mysqldump -u backup_user -p'pass' mydb | gzip > /backups/mydb-$(date +\%F).sql.gz