HowToCodePage

Replication, backup and operational pitfalls

Binary logs, replicas, restorable dumps, and the failure modes that only appear after a few months in production.

Binary logging and replication

A replica replays the primary's binary log. With row-based logging (the 8.0 default) the log records the rows that changed, which makes replication deterministic even when a statement uses NOW() or a non-deterministic function.

# primary: /etc/mysql/my.cnf
[mysqld]
server-id            = 1
log_bin              = mysql-bin
binlog_format        = ROW
binlog_expire_logs_seconds = 604800   # keep 7 days of point-in-time recovery
gtid_mode            = ON
enforce_gtid_consistency = ON
-- on the replica
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.10',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'secret',
  SOURCE_AUTO_POSITION = 1;          -- derive position from GTIDs

START REPLICA;
SHOW REPLICA STATUS\G
  • Each server needs a unique server-id, including replicas that are themselves promoted later.
  • MySQL 8 uses SOURCE and REPLICA terminology; the older MASTER/SLAVE names were deprecated in 8.0.23 and removed in 8.4.
  • Replication is asynchronous by default: a committed transaction on the primary can be absent on a replica. Tune rpl_semi_sync_source or accept the window.
  • Watch Seconds_Behind_SOURCE, but treat it as a rough signal — a replica can be far behind while reporting zero.

Backups that actually restore

# logical dump: consistent without locking the whole server
mysqldump --single-transaction --routines --triggers --events \
  --source-data=2 --set-gtid-purged=OFF app > app-$(date +%F).sql

# restore and verify
mysql app_restore_check < app-2026-09-18.sql
mysql app_restore_check -e "CHECKSUM TABLE orders EXTENDED;"
MethodGood forWatch out for
mysqldumpSmall to medium databases, portable SQL, logical repairSlow restore on large data; --single-transaction only helps InnoDB
Physical copy (XtraBackup)Large databases, fast restoreVersion and page-size specific
Snapshot at the volume layerVery large datasetsCrash-consistent only if the filesystem and InnoDB flush are coordinated
Binary log replayPoint-in-time recovery after a dumpOnly as long as the logs are retained

A backup you have never restored is a hypothesis, not a backup. Keep the restore step in a script, run it on a schedule, and compare a checksum or row count against the source.

Operational pitfalls

  • Long-running transactions hold a read view open, which blocks purge of old row versions and lets undo grow — a temporary reporting query can inflate disk usage for hours.
  • DDL causes an implicit commit: wrapping ALTER TABLE in a transaction does not make it rollback-safe.
  • Some ALTER TABLE operations still copy the table. Check with ALTER TABLE orders ADD COLUMN note TEXT, ALGORITHM=INPLACE, LOCK=NONE; and have a plan if it errors.
  • Random primary keys (UUIDv4) scatter inserts across the B-tree and cause page splits; use an auto-increment key or a time-ordered UUID.
  • Timestamps depend on the session time zone while DATETIME does not: store UTC in DATETIME unless you have a reason not to.
  • Connection churn is expensive. Pool connections in the application rather than opening one per request.
⚠️
Run SELECT * FROM performance_schema.data_lock_waits and check innodb_row_lock_time when latency spikes without a load change — lock contention usually shows up as slowness long before it shows up as an error.

FAQ

Can I read from a replica?
Yes, and it is a good way to move reporting load off the primary — but expect replication lag. For read-your-own-writes, read from the primary for a short window after an update, or route by session.
How do I recover from a bad DELETE?
Restore the last backup into a scratch database, then replay binary logs up to just before the statement, and copy the affected rows back. Practise this before you need it.

Indexes and reading EXPLAIN Installation, databases, users and engines

Last refreshed 2026-09-18.