Replication: A Second Server, and the read_only Gap Nobody Mentions

Setting up replication is one CHANGE REPLICATION SOURCE TO statement. Trusting it is a separate question — and the first real attempt below fails before it even connects.

Connecting a replica: the error a tutorial’s copy-paste skips

-- on the replica, first attempt
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='172.17.0.3', SOURCE_PORT=3306,
  SOURCE_USER='repl', SOURCE_PASSWORD='replpass',
  SOURCE_LOG_FILE='binlog.000002', SOURCE_LOG_POS=13192;
START REPLICA;

SHOW REPLICA STATUS\G

Replica_IO_Running: Connecting
Last_IO_Error: Error connecting to source 'repl@172.17.0.3:3306'. This was attempt 1/10,
with a delay of 60 seconds between attempts. Message: Authentication plugin
'caching_sha2_password' reported error: Authentication requires secure connection.

The replication user authenticates with caching_sha2_password — MySQL 8’s default plugin — which refuses to complete a login over a plain connection without either TLS or the source’s public key. The fix is one more option on the same statement:

STOP REPLICA;
CHANGE REPLICATION SOURCE TO GET_SOURCE_PUBLIC_KEY=1;
START REPLICA;

SHOW REPLICA STATUS\G

Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Last_IO_Error:
Last_SQL_Error:

Both threads running and no error text — the IO thread pulls binary log events from the primary, the SQL thread applies them, and each is reported separately because either one can fail independently of the other.

A real write, replicated, confirmed by polling — not assumed

-- on the primary
CREATE DATABASE repldemo;
CREATE TABLE repldemo.events (id INT PRIMARY KEY AUTO_INCREMENT, note VARCHAR(100));
INSERT INTO repldemo.events (note) VALUES ('first write on primary');

-- on the replica, checked in a short retry loop rather than a fixed sleep
SELECT COUNT(*) FROM repldemo.events;

Source_Host: 172.17.0.3
Seconds_Behind_Source: 0

id  note
1   first write on primary

Replication lag on a local, idle link is small enough that a fixed wait is guesswork — polling until the row count matches what was written on the primary is the same principle as checking Seconds_Behind_Source in production: confirm the actual state instead of trusting a timer.

The gap: read_only doesn’t mean what it sounds like

-- on the replica, started with --read-only=ON
INSERT INTO repldemo.events (note) VALUES ('should be rejected');
SELECT * FROM repldemo.events;

id  note
1   first write on primary
2   second write, timed
3   should be rejected

No error — the insert succeeded. read_only only restricts accounts without the SUPER privilege, and root has SUPER by default, so the setting that’s supposed to make this server a safe read replica did nothing for the account most likely to run a stray write.

SET GLOBAL super_read_only = ON;
INSERT INTO repldemo.events (note) VALUES ('should really be rejected now');

ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option
so it cannot execute this statement

super_read_only closes the gap — it blocks SUPER accounts too, which is the setting that actually makes a replica non-writable rather than just discouraging non-privileged clients.

Practice

-- on the primary
INSERT INTO repldemo.events (note) VALUES ('third write, after super_read_only');

-- on the replica, polled
SELECT * FROM repldemo.events;

id  note
1   first write on primary
2   second write, timed
3   third write, after super_read_only

SHOW REPLICA STATUS\G

Seconds_Behind_Source: 0
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates

Next: backup and recovery — turning repldemo (and the rest of this database) into something that survives a mistake, and proving the restore actually works rather than trusting that it would.