mysqldump is more than twenty years old, ships with every MySQL installation, and remains the most widely used database backup tool on the planet. It is also dangerously easy to use badly. The difference between a mysqldump command that quietly locks your production tables during business hours and one that takes a consistent, non-blocking backup comes down to a handful of flags that the documentation explains tersely and most tutorials skip entirely.
This guide explains the flags that actually matter for a production MySQL backup, what each one does under the hood, when it can hurt you, and how they combine into a command you can trust on a cron schedule. If you have ever copied a mysqldump one-liner from Stack Overflow into a backup script without being sure what --single-transaction really does, this is for you.
The baseline: what mysqldump does without flags
Run bare, mysqldump mydb > backup.sql connects to the server, reads every table, and writes CREATE and INSERT statements to a file. Two default behaviors cause production pain.
First, locking. By default mysqldump acquires table-level read locks (it implies --lock-tables for the duration of each table's dump) to get a consistent view. On a busy database, that means writes queue behind your backup. Customers notice.
Second, memory buffering. mysqldump retrieves each table's full result set into client memory before writing it out. For a 20 GB table, that is a 20 GB memory spike on whatever machine runs the dump, which is frequently the database server itself. Backups that crash with out-of-memory errors at 3 AM trace back to this default more often than anything else.
The three flags in this article's title exist to fix exactly these problems.
--single-transaction: consistency without locks
This is the single most important flag for anyone running InnoDB, which since MySQL 5.5 is everyone by default. When you pass --single-transaction, mysqldump starts a transaction with REPEATABLE READ isolation and takes a consistent snapshot before dumping. Thanks to InnoDB's MVCC (multi-version concurrency control), the dump then sees the database exactly as it existed at that instant, no matter how many writes happen while the dump runs, and it takes no table locks at all.
The practical effect: you can run a full backup of a busy production database in the middle of the day and your application will not notice. This is the property that makes hourly backup schedules viable, and hourly backups are what turn a data incident from a lost day into a lost hour.
The caveats that actually matter
It only protects InnoDB tables. MyISAM and MEMORY tables are dumped without snapshot consistency. If a legacy MyISAM table lurks in your schema (check with SELECT table_name FROM information_schema.tables WHERE engine != 'InnoDB' AND table_schema = 'mydb'), its rows can be inconsistent with the rest of the dump.
DDL breaks the snapshot. If an ALTER TABLE, CREATE TABLE, or TRUNCATE runs while the dump is in progress, the snapshot's view of the affected table can become invalid and the dump can fail or produce wrong results. The operational rule: do not deploy schema migrations during your backup window, or better, time risky migrations right after a backup completes so you have a fresh restore point, a practice that matters double when AI-drafted migrations are shipping with less review.
Long dumps hold a long transaction open. InnoDB must retain old row versions for the duration, which adds undo log pressure on very large, very busy servers. For most databases this is a non-issue; for multi-hundred-GB monsters it is one reason to consider physical backup tools alongside logical dumps.
--quick: stream rows, don't buffer them
--quick changes how mysqldump retrieves data: instead of pulling each table's entire result set into memory and then writing it, it fetches and writes one row at a time. Memory use stays flat regardless of table size, which is the difference between a backup that runs reliably on your 4 GB app server and one that gets OOM-killed the week your biggest table doubles.
Here is the good news: --quick is included in --opt, which is on by default in modern mysqldump. So why list it explicitly in every recipe? Two reasons. Some scripts disable the opt group (--skip-opt) for portability reasons and silently lose it, and being explicit means your backup script's intent survives the next engineer's refactor. There is no downside: on small tables the difference is unmeasurable, and on large tables it is the flag keeping the process alive.
--flush-logs: rotate the binary log at backup time
This one is about point-in-time recovery, the capability gap between "restore to last night's backup" and "restore to 14:32, right before the bad UPDATE ran."
MySQL's binary log records every change to the database. If you have binlogs enabled (log_bin, the default on MySQL 8), you can take last night's dump, restore it, then replay binlog events up to any moment with mysqlbinlog. --flush-logs tells the server to close the current binary log file and start a new one at the instant the dump begins. That alignment means recovery is clean: restore the dump, then replay binlog files from the rotation point forward to your target time, without hunting through a log file that spans the backup boundary.
Two notes. Use it together with --single-transaction so the rotation and the snapshot happen at a coherent moment, and add --source-data=2 (formerly --master-data=2) to write the exact binlog file and position into the dump as a comment, so your recovery procedure knows precisely where to start replaying. Without binlogs enabled, --flush-logs does nothing useful, and your recovery granularity equals your backup frequency, which is the strongest argument for backing up hourly instead of nightly.
The supporting cast: flags most teams should also use
--routines --triggers --events: stored procedures, functions, and scheduled events are not all dumped by default (--triggers is, --routines and --events are not). Teams discover this during a restore when application code starts erroring on missing procedures. If you use any of these features, dump them.
--set-gtid-purged=OFF: on GTID-enabled servers, the default dump embeds GTID state that causes errors when restoring into an existing server or a replica. For routine backups intended for flexible restore, OFF is usually what you want; leave the default when cloning replicas.
--no-tablespaces: avoids needing the PROCESS privilege on MySQL 8, letting your backup user stay minimal. Speaking of which: run backups as a dedicated user with SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, and (for binlog coordinates) REPLICATION CLIENT, never as root.
--hex-blob: encodes binary columns as hex, immunizing your dump against charset mangling in transit. Cheap insurance if you store any binary data.
Putting it together: a production-grade backup command
mysqldump \
--single-transaction \
--quick \
--flush-logs \
--source-data=2 \
--routines --triggers --events \
--hex-blob \
--set-gtid-purged=OFF \
--no-tablespaces \
-h 127.0.0.1 -u backup_user -p"$MYSQL_PW" \
myapp_production \
| gzip \
| rclone rcat s3:my-backups/myapp/hourly/myapp-$(date +%Y%m%d-%H00).sql.gzConsistent and non-blocking, memory-safe, binlog-aligned for point-in-time recovery, complete with routines and triggers, and streamed compressed to off-site object storage without ever filling local disk. Pipe through gzip costs CPU but typically shrinks dumps 5 to 10x.
mysqldump vs physical backups: when flags are not enough
Everything above tunes a logical backup: SQL statements that recreate your data. For most databases under a few hundred gigabytes, a well-flagged mysqldump is the right primary tool, because logical dumps are portable across versions and hosts, human-inspectable, and restorable at table granularity.
Past that scale, restore time becomes the constraint. Replaying hundreds of gigabytes of INSERT statements and rebuilding indexes can take many hours, and that is when physical backup tools enter the picture: Percona XtraBackup copies InnoDB data files directly with much faster restores, and cloud volume snapshots capture the whole disk in minutes. The mature pattern is both: snapshots or XtraBackup for full-instance disaster recovery, and frequent mysqldump checkpoints for the everyday case of restoring one table or one database to a recent moment without touching the rest of production. The flags in this guide are what make the mysqldump half of that pattern safe to run every hour.
How this fails silently, and what to do about it
A correct command is half the job. The mysqldump failure modes that actually destroy weekends are operational. Exit codes get swallowed by pipes, so check PIPESTATUS, not just the last command's status. A server upgrade changes a default and the dump starts erroring while cron mails the error to a mailbox nobody reads. The dump succeeds but is suspiciously small because a database was renamed. The disk fills. The credential rotates.
Defenses, in order of value: verify the dump's size after every run and alert if it deviates wildly from the previous one; send a heartbeat ping to a monitor only after a verified successful upload, so silence triggers an alert; restore a backup to a scratch server quarterly and check row counts. A backup that has never been restored is a rumor.
This operational layer (scheduling, verification, rotation across hourly/daily/weekly/monthly tiers, off-site shipping, alerting in Slack when something breaks) is precisely what Ottomatik's automated MySQL backups manage for you, using these same mysqldump flags under the hood. Your team keeps the battle-tested tooling and loses the babysitting; at $79/month it costs less than the first hour of the first incident it prevents. The first backup takes about three minutes to set up.
Frequently asked questions
Does mysqldump lock tables?
By default, yes, table-level read locks during each table's dump. With --single-transaction on InnoDB tables, no: the dump reads from an MVCC snapshot and concurrent writes proceed normally. This is why the flag is non-negotiable for production use.
How long does mysqldump take?
Rough rule of thumb: 10 to 60 GB per hour depending on row sizes, CPU, and whether you compress in the pipe. The bigger question is restore time, typically 2 to 5x longer than the dump. Time both on your own data, because the day you need the answer is the wrong day to measure it.
Can mysqldump back up a single table?
Yes: mysqldump --single-transaction mydb orders > orders.sql dumps just the orders table, and you can list several tables after the database name. This is handy for ad hoc safety copies right before a risky data fix, though for routine protection a full-database schedule is what saves you, because incidents rarely announce in advance which table they will hit.
Should I back up each database separately or all at once?
Separately, one file per database. Per-database dumps restore independently, parallelize naturally, and keep a corruption or failure in one dump from taking your whole backup with it. Skip --all-databases for routine backups except on tiny servers; among other things it entangles the mysql system schema with your application data.
Where should backup files be stored?
Off the database server, full stop, and ideally in object storage under a different cloud account with its own credentials: S3, DigitalOcean Spaces, Backblaze B2, Azure Blob, or your own Nextcloud all work well. Follow the 3-2-1 shape (three copies, two media, one off-site), enable versioning or object lock so backups cannot be silently overwritten, and treat the storage credential with write-only permissions so a compromised app server cannot read or delete your history.
How often should I run mysqldump in production?
Hourly is the modern default for active applications, with tiered retention (24 hourly, 7 daily, 4 weekly, 12 monthly) keeping storage costs trivial. If hourly sounds aggressive, calculate what a full day of lost writes would cost your business and the question usually answers itself.
Summary
--single-transaction buys you consistent, lock-free dumps of InnoDB tables and is non-negotiable for production. --quick keeps memory flat so big tables cannot kill the dump, explicit even though modern defaults include it. --flush-logs with --source-data=2 aligns binary logs to your backup boundary, unlocking restore-to-the-minute recovery. Add routines, triggers, events, hex-blob, and a least-privilege backup user, stream the result off-site, and then, the part that separates real backup strategies from lucky ones, verify, alert, and rehearse the restore.
