Skip to content
Tutorials Center
My learning

Topics

Lesson 62 of 75 ยท Security and Administration

The Binary Log and Replication

Learn how the MySQL binary log works, set up GTID-based source and replica replication, understand semi-sync and lag, and use replicas for reads safely.

MySQL replication copies changes from one server (the source) to one or more others (replicas). The mechanism is simple: the source writes every change to its binary log, and each replica downloads that log and applies it. That one idea gives you read scaling, hot standbys, safe places to run reports and a base for backups and upgrades. It also has traps, and most of this lesson is about avoiding them.

All server commands here are read-only, because a browser cannot run two MySQL servers. The two live examples are stand-ins: one measures replication lag from timestamps, and one finds the transactions a replica is missing using a GTID-style ledger.

The binary log

The binary log (binlog) is an ordered record of changes made to data. It is separate from the InnoDB redo log, which exists only for crash recovery. Binary logging has been on by default since MySQL 8.0, and the files are named binlog.000001, binlog.000002 and so on. Useful commands:

SQL
SHOW BINARY LOG STATUS;   -- current file and position (8.4 name)
SHOW BINARY LOGS;         -- all files and sizes
SHOW BINLOG EVENTS IN 'binlog.000003' LIMIT 10;

In MySQL 8.4, SHOW BINARY LOG STATUS replaced SHOW MASTER STATUS, and the old master/slave statement names were removed in favor of source and replica.

The format says what is written for each change. It is set with binlog_format:

Format What is logged Notes
ROW The before and after image of each changed row The default and the safest. Deterministic, but large for big updates.
STATEMENT The SQL text Compact, but unsafe for non-deterministic statements such as UUID() or LIMIT without ORDER BY.
MIXED Statements, switching to rows when needed Rarely a good reason to choose it today.

Stay with ROW. Change-data-capture connectors also expect it. Logs are removed automatically after binlog_expire_logs_seconds, which defaults to 30 days.

How a change travels

Source commits The change goes into the binary log.
Replica fetches An I/O thread copies it into a relay log.
Replica applies SQL applier threads replay it on the replica.

Replication is asynchronous by default.

1 / 2

GTIDs: naming every transaction

A global transaction identifier is a name for each committed transaction, written source_uuid:sequence, for example 3E11FA47-71CA-11E1-9E33-C80AA9429562:23. With GTIDs, a replica does not need to be told a file name and position. It just tells the source which GTIDs it already has, and the source sends the rest. This makes failover and rebuilding far less error-prone.

Enable it in my.cnf on every server:

INI
[mysqld]
server_id                = 1        # unique per server
gtid_mode                = ON
enforce_gtid_consistency = ON
log_replica_updates      = ON

enforce_gtid_consistency rejects statements that cannot be logged safely as one transaction, such as creating a temporary table inside a transaction. Every server needs a different server_id.

Setting up a source and a replica

  1. On the source, create a replication account and grant it the replication privilege:
SQL
CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'a-long-random-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
  1. Seed the replica with the source's data. With the Clone plugin, a Shell dump, or a physical backup (see Backup and Restore). The Clone plugin is the fastest option.

  2. On the replica, point it at the source and start the applier:

SQL
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.1',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'a-long-random-password',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL = 1;
START REPLICA;

SOURCE_AUTO_POSITION = 1 tells MySQL to use GTIDs. Check health with:

SQL
SHOW REPLICA STATUS\G

Look for Replica_IO_Running: Yes and Replica_SQL_Running: Yes, an empty Last_Error, and Seconds_Behind_Source. Make the replica read-only so that nobody writes to it by mistake:

SQL
SET PERSIST super_read_only = ON;

A replica that accepts writes will diverge from its source, and then replication breaks or, worse, quietly continues with different data.

The REPLICATION SLAVE privilege name is a historical leftover. It still works in 8.4. Roles and other tools talk about "replication" in more modern words, but the grant has not been renamed.

Replication lag

Because replication is asynchronous, a replica is always a little behind. Lag appears when the source produces changes faster than the replica applies them, usually because of one huge transaction, a slow disk, or a single long-running statement. A replica can apply independent transactions in parallel with:

INI
replica_parallel_workers       = 8
replica_preserve_commit_order  = ON

Seconds_Behind_Source is a rough guide. It can read zero while the I/O thread is stalled, and it jumps in big steps. For precise monitoring, write a timestamp into a heartbeat table on the source every second and compare it on the replica, or use the replication tables in performance_schema.

Here is the idea as a live example. Each row has the time the source committed it and the time the replica finished applying it:

SQL Stand-in: measure replication lag from timestamps

Press Run (or Ctrl + Enter) to see the result here.

Rows 3 and 4 show a burst of lag (7 and 8 seconds) that then recovers by row 5. MySQL's own functions are TIMESTAMPDIFF(SECOND, a, b) and UNIX_TIMESTAMP(); the stand-in uses SQLite's strftime. Strings, Dates and Math Functions shows the MySQL spellings.

Semi-synchronous replication

By default, the source commits and answers the client without waiting for any replica. If the source dies right then, the last transactions may exist only on the dead machine. Semi-synchronous replication makes the source wait until at least one replica has received (not applied) the transaction in its relay log:

SQL
INSTALL PLUGIN rpl_semi_sync_source  SONAME 'semisync_source.so';
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET PERSIST rpl_semi_sync_source_enabled  = ON;   -- on the source
SET PERSIST rpl_semi_sync_replica_enabled = ON;   -- on the replica

If no replica answers within rpl_semi_sync_source_timeout (10 seconds by default), the source falls back to asynchronous mode. It reduces data loss, but does not remove it, and it adds a network round trip to every commit.

Read scaling and its traps

Sending SELECT statements to replicas is the usual first scale-out. It works well for dashboards and pages that tolerate slightly old data. Watch for these problems:

  • Read-your-writes: a user saves a profile, the next request reads from a lagging replica, and sees the old value. Fix it by reading from the source for a short time after a write, or by waiting for a GTID with WAIT_FOR_EXECUTED_GTID_SET().
  • Writes do not scale. Every replica applies every write, so replicas add read capacity only.
  • Transactions on replicas: do not mix reads from replicas and writes in one transaction.
  • Hidden lag: alert on lag, and take a replica out of rotation when it is too far behind.

Failover basics

When the source fails, you promote a replica. With GTIDs the steps are:

  1. Stop traffic or fence off the old source so that it cannot accept writes if it comes back.
  2. Pick the replica with the most complete GTID set, and wait until it has applied everything it received.
  3. Run STOP REPLICA; RESET REPLICA ALL; and turn off super_read_only on it.
  4. Point the other replicas at the new source with CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION = 1.
  5. Switch application traffic, using a proxy or DNS change.

Doing this by hand at 3 a.m. is error-prone. High Availability shows tools that automate it. This second example models how a replica finds what it is missing:

SQL Stand-in: which transactions is a replica missing?

Press Run (or Ctrl + Enter) to see the result here.

It returns sequences 4 and 5. Real GTID sets are compact ranges such as uuid:1-3, and GTID_SUBTRACT(source_set, replica_set) computes the same difference.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which binary log format is the default and the safest choice?

    ROW logs the changed rows, so replay is deterministic. STATEMENT can misbehave with non-deterministic functions.
  2. 2. What does SOURCE_AUTO_POSITION = 1 do?

    With GTID auto-positioning, the replica reports which transactions it already has and the source sends the rest.
  3. 3. A user updates a profile and immediately sees old data on the next page. What is the most likely cause?

    Asynchronous replication means a replica can be a little behind, so reads right after a write may show old values.
  4. 4. What does semi-synchronous replication guarantee?

    It waits for a replica to receive the event in its relay log, not to apply it, and it falls back to asynchronous mode after a timeout.

Summary

  • The source writes changes to the binary log, and replicas copy them into a relay log and apply them. The default is asynchronous.
  • Keep binlog_format=ROW, give every server a unique server_id, and enable GTIDs.
  • Set up a replica with CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION = 1, START REPLICA, and super_read_only = ON.
  • Monitor lag with a heartbeat, use parallel workers, and design the application for stale reads.
  • Semi-sync narrows the data-loss window, and failover is far easier with GTIDs, ideally automated.

Next: High Availability: Group Replication, InnoDB Cluster and Managed Services.