Consistent Backups with Transactions, Locks, Replication Coordinates, and Large Datasets

Learning outcomes

ServiceHub can stay online while a backup runs, but “online” and “consistent” are not synonyms. A consistent backup must represent a state that could have existed at one logical point, even while users are committing other transactions. This lesson connects InnoDB consistent reads, metadata changes, backup locks, coordinates, and large-data parallelism to that requirement.

Transaction consistency is a boundary, not a pause button

Under InnoDB's REPEATABLE READ model, a transaction can maintain a consistent read view while other sessions commit changes. mysqldump --single-transaction uses this property: it starts a transaction before reading table data. New committed rows after the snapshot begins are not part of that snapshot. This is exactly what we want—a self-consistent historical state rather than a mixture of “before” and “after” rows.

That mechanism depends on transactional semantics. A MyISAM or MEMORY table can change while the dump reads it and is not protected by the same snapshot. This course's recovery schema uses InnoDB deliberately.

Two-session observation: DML can continue

-- Run on a disposable local MySQL 8.4 LTS instance.CREATE DATABASE IF NOT EXISTS servicehub_recovery_lab  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;CREATE TABLE IF NOT EXISTS servicehub_recovery_lab.sites (  site_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  site_code VARCHAR(20) NOT NULL UNIQUE,  site_name VARCHAR(120) NOT NULL) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS servicehub_recovery_lab.work_orders (  work_order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  site_id BIGINT UNSIGNED NOT NULL,  status ENUM('OPEN','IN_PROGRESS','DONE','CANCELLED') NOT NULL DEFAULT 'OPEN',  summary VARCHAR(180) NOT NULL,  opened_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  closed_at TIMESTAMP(6) NULL,  CONSTRAINT fk_recovery_work_order_site    FOREIGN KEY (site_id) REFERENCES servicehub_recovery_lab.sites(site_id)) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS servicehub_recovery_lab.recovery_markers (  marker_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  marker_name VARCHAR(80) NOT NULL UNIQUE,  marker_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  note VARCHAR(255) NOT NULL) ENGINE=InnoDB;INSERT IGNORE INTO servicehub_recovery_lab.sites(site_code,site_name)VALUES ('BAKU-01','Baku Central Service Site'),       ('BAKU-02','Baku East Service Site');INSERT IGNORE INTO servicehub_recovery_lab.recovery_markers(marker_name,note)VALUES ('CH13_BASELINE','Known recovery invariant before Chapter 13 drills');SELECT COUNT(*) AS sites FROM servicehub_recovery_lab.sites;SELECT COUNT(*) AS markers FROM servicehub_recovery_lab.recovery_markers;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;START TRANSACTION WITH CONSISTENT SNAPSHOT;SELECT COUNT(*) AS count_at_snapshotFROM servicehub_recovery_lab.work_orders;-- Leave Session A open. Do not COMMIT yet.

INSERT INTO servicehub_recovery_lab.work_orders(site_id,status,summary)SELECT site_id,'OPEN','Post-snapshot leak inspection'FROM servicehub_recovery_lab.sites WHERE site_code='BAKU-01';COMMIT;SELECT COUNT(*) AS count_in_new_sessionFROM servicehub_recovery_lab.work_orders;

SELECT COUNT(*) AS count_still_at_snapshotFROM servicehub_recovery_lab.work_orders;COMMIT;SELECT COUNT(*) AS count_after_commitFROM servicehub_recovery_lab.work_orders;

Session A should keep its old count until it commits, then see the newer committed state. This is the mental model behind online logical backup of InnoDB: foreground writers need not be stopped simply because a consistent reader exists.

Why concurrent DDL is more dangerous

ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, and related metadata changes do not behave like ordinary row-versioned DML. Oracle's mysqldump documentation explicitly warns that issuing such DDL on dumped tables while a --single-transaction dump is running can make the dump incorrect or cause its SELECT to fail. A production backup window therefore needs a schema-change policy even if application DML remains online.

Capture recovery coordinates at the backup boundary

Point-in-time recovery requires knowing where the full backup ends in the binary-log stream. The current source-side statement is SHOW BINARY LOG STATUS. It reports the active binary-log file and position plus the executed GTID set when GTIDs are in use. The statement requires REPLICATION CLIENT (or legacy broad administrative privilege, which this course does not recommend).

SHOW GLOBAL VARIABLES WHERE Variable_name IN  ('log_bin','binlog_format','gtid_mode','enforce_gtid_consistency');SHOW BINARY LOG STATUS;SELECT @@GLOBAL.gtid_executed AS gtid_executed,       @@GLOBAL.gtid_purged AS gtid_purged;

If log_bin=OFF, you can still make a full logical backup, but you cannot recover post-backup changes from a binary-log stream that does not exist. That changes the achievable RPO. Do not enable binary logging casually on a valuable server merely for this lesson; use a disposable instance or plan the restart/configuration change through normal operations.

mysqldump coordinates: informative, not a magic restore button

mysqldump -h 127.0.0.1 -P 3306 -u root -p \  --single-transaction \  --source-data=2 \  --set-gtid-purged=OFF \  --databases servicehub_recovery_lab \  > servicehub_consistent_with_coords.sql# Inspect the header for the commented CHANGE REPLICATION SOURCE TO line.# Linux/macOS:grep -n "CHANGE REPLICATION SOURCE" servicehub_consistent_with_coords.sql | head# PowerShell:Select-String -Path .\servicehub_consistent_with_coords.sql -Pattern "CHANGE REPLICATION SOURCE"

--source-data=2 records source binary-log coordinates as a comment, useful as recovery metadata without making the restore execute replication configuration automatically. --set-gtid-purged=OFF is intentional for this small partial-schema learning artifact: by default, a GTID-enabled source can embed the source server's global gtid_executed set, including transactions outside the dumped schema. GTID provisioning deserves an explicit topology decision rather than a hidden side effect.

MySQL Shell: how a multithreaded dump becomes consistent

With consistent:true (the default), MySQL Shell's dump utilities coordinate a short global/table lock as available, start each worker transaction at REPEATABLE READ with START TRANSACTION WITH CONSISTENT SNAPSHOT, then use LOCK INSTANCE FOR BACKUP when the account has BACKUP_ADMIN. This prevents schema-changing operations that could invalidate the dump while allowing normal DML to continue. If the needed privilege is unavailable, the utility performs extra consistency checks and can stop or report an error when it cannot establish safety.

// MySQL Shell JavaScript modeutil.dumpSchemas(  ["servicehub_recovery_lab"],  "servicehub_consistent_shell",  {    consistent: true,    threads: 4,    checksum: true,    showProgress: true  });

Large data: bounded parallelism and observable cost

For large tables, logical backup competes with the application for CPU, buffer-pool pages, storage bandwidth, and network bandwidth. MySQL Shell chunks tables by default and uses multiple threads. That can reduce wall-clock time, but excessive parallelism can raise foreground latency or destabilize storage queues. The correct thread count is therefore an operational experiment.

SHOW GLOBAL STATUS WHERE Variable_name IN  ('Threads_running','Bytes_sent','Innodb_buffer_pool_reads',   'Innodb_data_reads','Innodb_data_writes');SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAITFROM performance_schema.events_waits_summary_global_by_event_nameWHERE EVENT_NAME LIKE 'wait/io/file/innodb/%'ORDER BY SUM_TIMER_WAIT DESCLIMIT 10;

These counters are context, not a benchmark score. Pair them with OS disk latency/throughput, CPU, application p95/p99 latency, and backup progress. The lesson deliberately does not claim a universal “safe” concurrency.

Failure drill: detect a consistency prerequisite before production

Use SHOW TABLE STATUS or INFORMATION_SCHEMA.TABLES to inventory engines before relying on --single-transaction. If a schema contains a nontransactional table that must be captured atomically with InnoDB data, a different locking or application-quiesce strategy may be required.

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA='servicehub_recovery_lab'ORDER BY TABLE_NAME;

The safe correction is not “ignore the warning.” Either migrate the object to an appropriate transactional engine, accept/document a weaker consistency contract, or choose a method that coordinates the nontransactional object. Backups are contracts, so exceptions must be explicit.

Summary and bridge to Lesson 3

A backup now has a consistent transactional boundary and explicit recovery coordinates. The next lesson studies the stream those coordinates point into: the binary log, including its formats, GTID identity, retention window, and the failure that occurs when required recovery logs have already been purged.

Authoritative references

• MySQL 8.4 — mysqldump --single-transaction and source-data options
• MySQL 8.4 — SHOW BINARY LOG STATUS
• MySQL Shell 8.4 — Dump utility consistency options
• MySQL 8.4 — Performance Schema