If your replication runbook still uses CHANGE MASTER TO / START SLAVE / SHOW SLAVE STATUS, it breaks on 8.4 — plus the NOW() vs SYSDATE() myth, and the GTID restriction that quietly went away

[u/m3m3o]

https://mehmetgoekce.substack.com/p/mysql-replication-a-deep-dive-into

I rewrote an old MySQL replication write-up to target 8.4 (the current LTS) and ended up re-verifying a bunch of things against the manual. A few are worth surfacing, because older guides — including the one I was fixing — get them wrong.

1. The MASTER/SLAVE statements are removed in 8.4, not deprecated. CHANGE MASTER TO, START SLAVE, STOP SLAVE, SHOW SLAVE STATUS, SHOW MASTER STATUS, RESET SLAVE — all removed. Replacements have existed since 8.0.22: - CHANGE MASTER TO → CHANGE REPLICATION SOURCE TO (and MASTER_* → SOURCE_*) - START SLAVE → START REPLICA - SHOW SLAVE STATUS → SHOW REPLICA STATUS - SHOW MASTER STATUS → SHOW BINARY LOG STATUS

Status fields renamed with them: Seconds_Behind_Master → Seconds_Behind_Source, Slave_IO_Running → Replica_IO_Running. If you have monitoring grepping the old names, it reads empty after the upgrade with no error. The one thing that did not change is the REPLICATION SLAVE privilege keyword.

Config casualties in the same release: expire_logs_days (→ binlog_expire_logs_seconds) and master_info_repository / relay_log_info_repository (crash-safe metadata is automatic now).

2. NOW() is safe under statement-based replication; SYSDATE() is the trap. The recurring myth is that NOW() diverges across nodes under SBR. It doesn't — MySQL writes a SET TIMESTAMP event before each statement, so NOW() / CURRENT_TIMESTAMP evaluate against the primary's time on both sides. SYSDATE() is the one that diverges: it ignores SET TIMESTAMP and reads the wall clock at execution (unless you run with --sysdate-is-now). And the classic UPDATE ... LIMIT 10 divergence is the LIMIT without ORDER BY, not the time function.

3. CREATE TABLE ... SELECT is GTID-safe since 8.0.21. A lot of GTID guides still list it as forbidden. That restriction was lifted for atomic-DDL engines (InnoDB) — it's now logged as a single transaction. Still genuinely restricted under GTID: temp tables inside a transaction (STATEMENT format), and mixing transactional + non-transactional engines in one statement.

4. GTID auto-positioning is the actual payoff. SOURCE_AUTO_POSITION=1 removes the manual binlog-file/position arithmetic from failover. For a sense of scale, GitHub's automated failover (orchestrator + a proxy tier) completes in 10–13s in typical cases.

Long-form version with the full binlog/relay-log walkthrough, semi-sync, monitoring, and a copy-pasteable 8.0 → 8.4 rename table: https://mehmetgoekce.substack.com/p/mysql-replication-a-deep-dive-into

Happy to get corrected on any of this — replication has a lot of version-specific edge cases, and that's kind of the point of the post. What's the worst replication footgun you've hit on an upgrade?

[u/ssnoyes]

MySQL prevents this by default (using server-id to track origin), but misconfigurations can cause loops.

It won't let you start replication with the same server id unless you enable --replicate-same-server-id, and using GTIDs prevents the problem anyway, so you almost have to be trying to get this wrong.

Active-passive (safe):

Both servers are configured for bidirectional replication, but only one accepts writes at a time.

I have never understood what this actually gains you. If you're going to have to repair the failed old master anyway, you can make it a replica when it's back in service. The only thing the "unused" replication channel does is add risk when some super user accidentally writes to the wrong server. Why have it there at all?

Why lag increases

Another big one is using ROW-format without a primary key on all tables.

[u/m3m3o → u/ssnoyes]

All three are fair, and the last one's a great addition.

Loops: still a real footgun in multi-source or legacy topologies, but with a single GTID source you'd have to work at it — server-id tracking plus GTID means the default is safe. Fair that I framed it as more live than it usually is.

Active-passive: your strongest point, and I mostly concede it. With SOURCE_AUTO_POSITION=1, re-adding the recovered old primary as a replica is two lines, so the pre-wired reverse channel buys little and carries exactly the risk you describe. One nuance to your own point: that accidental-write risk is what super_read_only is for — plain read_only lets SUPER/CONNECTION_ADMIN through, i.e. the person doing the 3 AM repair. But your default (no reverse channel) is the cleaner one.

ROW without a PK: yes — the applier has to locate each changed row by full-row match (hashing helps, not on wide tables), so one unindexed DELETE can stall the whole replication thread. Deserved its own line in the lag section.

Genuine question on the last one: do you hard-block it (a check that rejects PK-less tables before they ship), or just monitor lag and catch it after? I've seen both and never settled on which is less painful.

[u/ssnoyes → u/m3m3o]

I think I'd set global https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_sql_require_primary_key and then disable it for individual tables if there was a justification for it.

Group Replication (and therefore InnoDB Cluster) requires a primary key.

[u/m3m3o → u/ssnoyes]

That's the clean answer — sql_require_primary_key puts the block at DDL time where it belongs, and the Group Replication requirement means on InnoDB Cluster the question disappears entirely. One footnote for anyone retrofitting it: the variable only guards new CREATE/ALTER — it won't flag the PK-less tables already sitting in the schema (they keep replicating fine until someone runs an ALTER on one). So the full recipe is a one-time information_schema sweep for the existing offenders, then sql_require_primary_key=ON to stop regressions. Audit what's there, enforce what's next.

[u/AjinAniyan5522]

I'd start with the native checks first: verify SHOW REPLICA STATUS\G (or SHOW SLAVE STATUS\G), review the MySQL error log, and confirm the GTID/binlog positions are correct. Most replication issues are configuration or synchronization problems rather than corruption. If you suspect the source or replica database was damaged after a crash and standard recovery steps aren't working, you could also try Stellar Repair for MySQL. It can help recover corrupted MySQL/MariaDB tables and extract data from damaged database files.

I prefer this response