MySQL GTID replication (async / semi-sync)

Builds GTID-based replication with 1 source + N replicas from installed MySQL servers: replication user, seeding the existing data with the clone plugin or mysqldump, SOURCE_AUTO_POSITION, TLS, optional semi-sync, super_read_only replicas, lag monitoring and a manual failover procedure. The result is a monitored topology that takes writes on the source and can serve reads from the replicas. If you need automatic failover, see InnoDB Cluster; for day-to-day operation and failures of an existing replication setup, see the replication operations guide.

Topology: 1 source + 1..5 replicas

Before you start

• MySQL … is installed and running on all servers at the same minor version (DBAtlas MySQL single-server installation guide), with binlog and GTID enabled. The operating system root user can connect with mysql without a password.
• server_id and server_uuid are different on every server. (If the servers were created by copying/cloning, auto.cnf in the datadir may be identical: delete it and restart the service.)
• The servers can reach each other on port … (…).
• Clocks are synchronized (chrony). Lag measurement and PITR rely on timestamps.
• The replicas have free space equal to the source's data size…, plus room for the dump file….
• The data size on the source and the network speed are known (to estimate the transfer time).
• Strong passwords are ready for the replication user… and the temporary clone user… (at least 8 characters, upper and lower case, digits, special characters; no ' or \). The steps prompt for passwords without echoing them.
• It is known that the application sends writes only to the source and (optionally) reads to the replicas; the replicas will be super_read_only.

Phases of the guide

• Preparation (all servers) — Check server identities · Allow the other nodes in the firewall · Add replication settings (source) · Add replication settings (…) · Restart MySQL
• Source preparation — Create the replication user · Install the semi-sync plugins · Enable semi-sync on the source
• Seeding the replica (clone) — Install the clone plugin · Create the temporary clone user · Clone the source (…)
• Seeding the replica (mysqldump) — Take a consistent dump from the source · Copy the dump to the replicas · Load the dump (…)
• Start replication — Make the replica read-only (…) · Configure the source and start replication (…)
• Verification — List connected replicas on the source · Test write (source) · Read the test write on the replica (…) · Drop the test database
• Lag and status monitoring — Replication status and lag (…)
• Cleanup — Drop the clone user · Delete the dump file
• Manual failover (loss of the source) — Failover: check the candidate's state · Failover: check the candidate's state · Failover: promote the candidate to source · Failover: promote the candidate to source · Failover: point the other replicas to the new source (…) · Failover: point the other replicas to the new source (…) …