I am running a MySQL instance within a Docker container using a named volume for persistence. I need to implement a reliable backup strategy that ensures data integrity without significant downtime. Essentially the database receives one update every 24 hours at the same time as part of a metrics analysis framework.
I want to to essentially take a “snapshot” of the volume containing the database (or indeed the database itself I’m not fussy) before the daily edits take place to make sure nothing is corrupted and I can roll back to a stable version should there be a needD
Technical Conflict:
I am evaluating two methods but have concerns regarding the atomic nature of the backup:
- Logical Dump (mysqldump):
docker exec mysql-container mysqldump --single-transaction --all-databases > backup.sql
Concern: While --single-transaction works for InnoDB, does this ensure a consistent state if the metrics ingestion starts while the dump is still streaming?
- Physical Tarball (Filesystem Level):
docker run --rm --volumes-from mysql-container -v $(pwd):/backup ubuntu tar cvf /backup/backup.tar /var/lib/mysql
Concern: This is a “hot copy.” Even with FLUSH TABLES WITH READ LOCK, I am concerned about the OS-level file handles and InnoDB’s redo logs being in an inconsistent state during the tar process.
Questions:
-
In a standalone Docker environment, what is the technically correct way to invoke a READ LOCK from a host-side script that persists long enough for a secondary container to finish a tar of the shared volume?
-
Are there documented race conditions when using docker exec for mysqldump alongside high-volume INSERT operations in a containerized environment?
-
How can I verify the integrity of a physical volume backup without performing a full container restart/restore cycle?