Why mysqldump stops being enough on a growing database
As long as the database fits within tens of gigabytes, a nightly mysqldump looks like a safe solution: one command, a readable text file, simple verification.
The problem is that the copy is taken by a single session and proceeds sequentially — first some tables, then others, and the time grows not linearly but in steps, once it stops fitting into the disk cache.
Sooner or later the backup window closes before the export finishes, and the next scheduled run starts on top of one still running, doubling the load on disk and replication.
The insidiousness is that a backup failure is not always noticeable. The process returns a success code, the file is there, the size is non-zero — the report is green, and the on-call engineer does not go look that the export stopped midway or was taken during active writes.
The error comes to light exactly when database recovery is needed: the import reaches the middle, crashes on a corrupted section, and you have neither an intact copy nor time to sort out which transactions are missing.
The cost of such a scenario is not a failed command, but the absence of a rollback point. Recovery stops being a predictable operation of tens of minutes and turns into manual troubleshooting while the service is down.
MySQL backup on a growing database requires a different mode and verification that the copy actually restores — we break down how this is addressed.
What managed MySQL backup provides
A scheduled dump by itself is not yet protection — what matters is whether the copy is fit for recovery. We configure MySQL so that the snapshot is taken in a consistent mode, the result is verified automatically, and backup status is visible to engineers rather than assumed.
The effect rests on three measurable values: database recovery time, the share of copies that pass an integrity check, and the share of failures we learn about before the customer does.
Predictable recovery time
We determine in advance how long it takes to bring the database up from a copy, and keep that time stable: the recovery point is known, the deployment scenario is rehearsed and repeats without improvisation at the moment of failure.
The runbook is exercised on a separate staging environment — from bringing up the structure to switching the service to the recovered node. The cost of service downtime becomes predictable: the unavailability window is known in advance rather than discovered during the incident.
Eliminated failure class: silent backup failures
A silent failure is when a job completes without an error, but the copy turns out incomplete or empty, and this is discovered at the worst possible moment.
That is why backup verification is built into the process: the dump structure and size are compared against expectations, a test restore is performed, and backup monitoring alerts on a missed run, abnormal duration, or checksum mismatch.
The "lucky — unlucky" failure class becomes a regular alert, and recovery stops being a test of endurance.
mysqldump, physical copy, or replica: what to choose
A logical dump is SQL text: the snapshot can be moved between versions and platforms, a single table can be extracted from it, and it can be read and corrected by hand. The price for this is that recovery proceeds query by query, and on a large database it takes hours.
A physical copy captures data and log files as a consistent whole and comes up quickly, while a replica keeps a permanent duplicate for fault tolerance and reporting.
The choice is determined by what is more critical: portability, bring-up time, or hot replacement. Below are the boundaries of each option.
| Option | When it fits | Limitations |
|---|---|---|
| Logical dump (mysqldump) | Migration, selective recovery, rollback of a single table | Recovery time grows with volume, heavy tables take hours to read, no recovery point between runs |
| Physical copy (volume snapshot) | Large database, need to bring everything up in minutes | Tied to version and platform, a single table cannot be extracted, consistency with the storage engine is required |
| Replica | Hot replacement and offloading the primary server | Does not replace a backup: a table deletion is replicated from the primary, and lag grows when it falls behind |
Limitations of a logical dump on large tables
The dump proceeds as a single read stream: on tables of hundreds of gigabytes, this is hours-long reading that holds the transaction snapshot and prevents cleanup of old row versions. The undo log accumulates, and recovery time grows along with it. Extracting a single table from a dump is also not fast — the file is parsed in full.
When one method is not enough
In practice, the methods are combined: a scheduled dump provides a portable snapshot and selective recovery, a physical snapshot provides fast bring-up of the entire database, and a replica provides fault tolerance and reporting queries.
Then each failure class has its own tool: a corrupted row is treated with a dump, and a failed node is brought up from a snapshot.
Work sequence: from database inventory to runbooks
We structure the work sequence so that the copy is verified before anyone relies on it in an emergency. We start with a backup audit: what is already being exported, by what means, where it is stored, and who knows about the result.
Then comes database inventory, and only after that the choice of scheme — otherwise the schedule covers not what is truly critical.
Database inventory and priorities
- We compile a list of databases: size, table type, growth rate, related services, and owners.
- We mark priorities — revenue-critical, reference, temporary. Each group gets its own retention period and backup frequency.
- We choose a backup scheme: a full dump plus change logs for point-in-time recovery, and a separate export for large tables.
- We configure the backup schedule in off-peak windows, with export to a separate volume, compression, encryption, and rotation according to the retention rule.
- We automate launch: return code, failure notification, logging of every run, and backup file size control.
Restore verification on a staging copy
- We deploy the copy on a separate staging environment: dump import, log application, reconciliation of record counts and checksums for key tables.
- We formalize the runbook: target recovery time, responsible parties, and the on-call procedure for disk failure or data corruption.
After that, the schedule is considered operational — we document it and revisit it when new databases appear.
What your team has after implementation
After implementation, your team has not a set of one-off actions but a reproducible backup loop. Any engineer can bring up a copy according to the instructions without assembling context from chat, and mysqldump runs on schedule and reports failures on its own.
We hand over everything needed for independent operation and a smooth handoff of the task to a new employee.
Backup scripts and configurations
- Launch scripts with return code logging: you can see exactly what failed and at which step.
- Backup configuration: database list, schedule, snapshot depth, consistency parameters.
- Backup retention policy: how many daily, weekly, and monthly sets we keep and when old ones are cleaned up.
- Scheduler jobs with protection against overlapping runs, so two backups of the same database do not run at the same time.
- Automatic checks after a run: archive size, checksum, test restore into a separate schema.
- Infrastructure diagram: where backups are stored, how they are transferred to a standby node, and which services are involved.
Documentation and recovery runbooks
- Backup documentation: what is backed up, in what volume, where it goes, and how long it lives.
- Recovery runbook: step-by-step procedure for bringing up from the archive, with points where an engineer's decision is required.
- Instructions for deploying a copy to another server and to another database server branch.
- Target recovery times and acceptable data loss volume, agreed with the customer.
- Drill log: dates of recovery checks, what worked, and what was fixed afterward.
- On-call cheat sheet: what to do if a failure happens at night, where to look first, and whom to involve.
From practice: restoring an online store database
An online store with a database of hundreds of gigabytes: catalog, orders, stock. The nightly dump was taken with mysqldump on schedule, files were moved to a separate volume, and a successful completion report arrived in chat.
Formally, backup was working — but not a single copy had ever been restored, and this came to light at the worst possible moment.
How it was: a copy existed, recovery was not verified
The dump was taken without the --single-transaction flag: while the utility read tables, writes continued into them, and no one guaranteed data consistency in the output.
Separately, it turned out that some copies were cut off by timeout, and the success report arrived based on launch rather than completion.
A failure on the primary server resulted in six hours of downtime: two went to finding the current file and sorting out errors, and another four to manually reconciling orders, payments, and stock that had diverged between tables.
How it became: scheduled restore on staging
We set up a staging environment where the latest dump is automatically restored once a week: first a clean load into a separate instance, then a control set of queries — reconciliation of row counts for key tables, daily order totals, and foreign key integrity.
A successful restore without discrepancies became the only criterion by which a copy is considered valid, and a failed run raises an alarm before anything breaks in production.
The dump itself was moved to --single-transaction with compression: the artifact size dropped by a factor of three, retention increased, and rollback to the previous slice remains possible.
Database recovery time decreased from six hours to forty minutes, with most of the path following the scenario rehearsed on staging.
mysqldump is already configured, but recovery has not been verified?
Yes, backup works — but a mysqldump that has never been restored is not a backup, it is hope.
The risks of unverified copies almost never lie in the file not being created: it is created, only incomplete — without triggers, stored procedures, and events, or with a table snapshot that diverged during active writes.
This is discovered exactly when the database is needed in full, and there is nothing nearby except a dump of unknown quality.
There is no need to break working backup in the process — dump restore verification is layered on top.
The latest archive is restored into an isolated instance, after which the composition of schema objects, row counts for key tables, and checksums for several large tables are compared.
A discrepancy triggers a notification before the next archive is spoiled, and at the same time the actual recovery time is measured — the figure on which acceptable downtime depends.
The dump itself is taken in consistent mode: --single-transaction for InnoDB and the --routines --triggers --events flags, otherwise recovery formally passes but part of the logic is missing from the schema.
Copies are transferred to a separate site or external storage with encryption and time-based rotation — a copy lying on the same disk as the database will not survive a failure.
The transition plan is built so that nothing is stopped: first we enable daily verification on current archives and observe, then we add off-site export, and only after that do we change the schedule and parameters of the backup itself.
The output is a restorable dump with a known deployment time and a rollback procedure that can be demonstrated and repeated on any copy.
Common technical doubts about MySQL backup
The main doubt is the load from backup: the dump reads all table pages and on a large database occupies the disk for a long time.
The consistent snapshot mode in mysqldump does not block writes to InnoDB, and a pause between batches and reduced process priority remove the peak in disk operations — the copy takes longer to take, but the production database does not degrade in latency.
The second layer of questions is where to store copies, how to rotate them, and how often to run backups.
How to back up without interfering with the production workload?
A consistent snapshot in a single transaction, batch pauses between tables, and low process priority. On a replica, the number of parallel read threads is additionally limited so that its lag does not grow.
How to store and rotate copies?
A full dump once a day, with logs between them for point-in-time recovery. Rotation keeps the last several copies and monthly ones: storage does not grow indefinitely, and rollback to the needed day remains possible.
Can a copy be taken from a replica?
Yes, for loaded databases this is the preferred option: the replica provides the snapshot, and the primary node is not involved. Monitor lag and do not start the dump during the replication window for large batches.
What to do with large tables?
They are exported in separate streams or split by key ranges: one gigantic table in a shared dump stretches the backup window and complicates recovery. On-the-fly stream compression reduces the artifact.
Let's discuss your backup task
To come back with a work estimate and backup plan, we need a short snapshot of your infrastructure: where the databases live, how much data there is, and what is critical to preserve in a failure.
Usually one conversation with an engineer is enough — after that we estimate the scope of work and send a document with the schedule and backup verification procedure.
What is worth preparing for the task discussion:
- List of databases and servers: how many instances we service, where they are located, and whether there are replicas.
- Data volume and nature: total size, proportion of tables of different types, large binary fields.
- Recovery point requirements: how much data is acceptable to lose in a failure and how quickly the database must return to service.
- Backup window: when the load is minimal and whether a full mysqldump can be taken without affecting service responsiveness.
- Backup storage: local disks, a separate server, or object storage, how many copies we keep, and for how long.
- Verification runbook: how often we restore a copy in a test environment and who confirms the result.
- Access and restrictions: service accounts, network rules, and requirements for encrypting exports.
- Current process: what is already configured, what failures have occurred, and whether existing scripts need to be migrated.
- Handoff to support: who on the team accepts the runbook and whether on-call training is needed.
If some data is not at hand — no problem, we will collect it together. After the conversation, we will come back with an estimate, a work plan, and a description of the handoff to support.