MySQL native replication on Azure Flexible Server: notes from a rebuild

I recently rebuilt a MySQL 8 replica that had been quietly broken, and then spent a week learning why it broke. The environment was Azure Database for MySQL — Flexible Server, three servers, each in a different Entra tenant, with a ~70 GB database underneath. Names, hosts and identifiers below are placeholders; everything else is exactly what happened.

Most of what follows is not in the replication chapter of the MySQL manual, because most of it comes from what a managed service takes away from you.

The shape of the problem

Production feeds two downstream copies:

                   ┌──(managed migration service)──▶  copy-a
   prod-mysql
   = real prod     └──(native GTID replication)─────▶  replica-b

The managed-migration hop to copy-a replicates schema once and then sits there — useful for a one-time move, not a living copy. The replica-b leg is native GTID replication and is the one this post is about. It used to hang off copy-a — a replica of a replica of prod — which is how it drifted far enough to need a rebuild. It now replicates directly from prod.

One thing worth saying before any of the technical detail: the server names lied. The server with the customer-facing brand in its name was real production; the one with prod in its name was a downstream copy; the subscription named “Production” held the copy, not prod. Before anything destructive I now confirm identity with:

SELECT @@GLOBAL.server_uuid;

and check it against a UUID written down in the runbook. A hostname is a label someone chose in a hurry two years ago. A server_uuid is the server.

Azure takes SUPER away, and that changes the procedure

On Flexible Server nobody gets SUPER — not the admin account, not you. Everything that would normally be a SET GLOBAL becomes a server parameter change through the portal or az, which means a parameter’s scope is no longer your decision and some of them require a restart. Four consequences hit the rebuild directly:

• SET @@GLOBAL.GTID_PURGED in a dump fails with error 1227. mysqldump --set-gtid-purged=ON writes that line, and the restore dies on it.
• START REPLICA is denied, also 1227. The replacement is CALL mysql.az_replication_start;.
• replica_exec_mode is read-only on the 8.0.21 build, so IDEMPOTENT is off the table. replica_skip_errors isn’t exposed either, and sql_slave_skip_counter is incompatible with GTID auto-positioning. Net effect: you cannot skip a replication conflict. Every one gets repaired by hand, at the row level.
• Replication is configured through stored procedures, and there are two of them. Use the GTID one:

CALL mysql.az_replication_change_master_with_gtid(
  'prod-host', 'repl_user', '<password>', 3306, '<ca-cert>');

The other procedure, mysql.az_replication_change_master, takes seven arguments and is file/position based. Passing '' and 0 for the file and position does not silently enable auto-positioning — it starts from the oldest binlog on the source and dies on pre-GTID anonymous transactions.

Point-in-time restore cannot seed a replica

This is the one that cost the most time, so it gets its own section.

The obvious way to seed a 70 GB replica on a managed platform is to let the platform do it: point-in-time restore prod into a new server, then attach that server to prod as a replica. It doesn’t work, and it fails in a way that looks like success.

A PITR-restored server comes up with gtid_mode=OFF and no GTID history. gtid_executed, gtid_purged and mysql.gtid_executed are all empty. Turning GTID on afterwards does not reconstruct the history — it just starts a new one. So the restored server holds prod’s data at a known point in time while having no idea which transactions that point corresponds to. You have a perfect snapshot and no coordinates for it.

If you enable GTID and then set a position with az mysql flexible-server gtid reset, be very careful about which GTID set you use. A seed server that has been running for a while has its own UUID in gtid_executed alongside prod’s. Feed the whole set back and the replica starts life believing it has already applied transactions prod has never heard of. Take only the prod-UUID portion.

The working answer is to dump the real source.

The seeding procedure that worked

mysqldump \
  --single-transaction \
  --set-gtid-purged=ON \
  --routines --events --triggers \
  --databases appdb > appdb.sql

--single-transaction does not block writes on InnoDB — it takes the snapshot inside a REPEATABLE READ transaction, no table locks. Users read and write throughout. What it does cost the source is a sustained sequential read of the whole dataset, so it is still a “run it out of hours” operation, not a free one.

Then read the start position out of the dump before restoring it:

grep -n "SET @@GLOBAL.GTID_PURGED" appdb.sql

Strip that line while streaming the restore, because it will be rejected:

grep -v "SET @@GLOBAL.GTID_PURGED" appdb.sql | mysql -h replica-host -u admin -p

Note grep -v into a pipe, not sed -i. In-place editing of a 45 GB file needs a second 45 GB of free disk you probably don’t have.

Then set the position through the management plane, point replication at prod, and start:

az mysql flexible-server gtid reset \
  --resource-group <rg> --server-name <replica> \
  --gtid-set "<the value from the dump>"

CALL mysql.az_replication_change_master_with_gtid(...);
CALL mysql.az_replication_start;

Two things I’d do differently next time:

Test --source-data=2 first. Because Azure denies RELOAD, --set-gtid-purged=ON reads gtid_executed a moment apart from the snapshot itself. That gap produced a handful of duplicate-key (1062) and missing-row (1032) errors on start-up that had to be repaired by hand. --source-data=2 captures the position atomically with the snapshot.

Verify row counts after the restore. The restore silently lost about 1,500 rows from the one table with a mediumblob column. Whole INSERT statements vanished, the client exited 0, and the error log was empty. It was found by comparing COUNT(*) across all 191 tables, and repaired by re-copying that one table with --skip-extended-insert --max-allowed-packet=1G. A clean exit code is not proof of a complete restore.

When the replica is also written to

This replica is not read-only. The application writes directly into its own log tables on the replica, which means read_only must stay off — turning it on “to make the replica a proper replica” breaks the app. Two structural consequences follow.

First, the shared log tables have to be excluded from replication, or prod’s inserts and the app’s inserts fight over the same AUTO_INCREMENT values and the SQL thread halts on error 1062 constantly:

replicate_wild_ignore_table =
  mysql.%,information_schema.%,performance_schema.%,sys.%,
  appdb.app_logs_%

Second — and this is the trap — that filter is a blacklist. Any new database created on prod stops replication dead with “Unknown database” until someone adds it to the list. That is a permanent piece of coupling between whoever creates schemas on prod and whoever owns the replica.

Because the app writes here, divergence between the two servers can’t be prevented, only detected. There’s a script of 191 exact COUNT(*) statements that gets run against both servers and diffed.

binlog_row_image=MINIMAL and the strict applier

Replication also halted repeatedly on error 1364, “Field doesn’t have a default value”, applying perfectly ordinary inserts.

The cause is a combination: prod runs binlog_row_image=MINIMAL, and the application overrides sql_mode per session. So the binlog row events omit columns that are NOT NULL without a default, and a replica applying them under a strict sql_mode rejects them. The fix in place is a deliberately relaxed sql_mode on the replica:

sql_mode = ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO

Someone will eventually find that setting, decide it looks sloppy, and “fix” it. It is load-bearing. The real fix is binlog_row_image=FULL on prod, which costs binlog volume and needs a maintenance window.

I’ll also correct something I believed at the time: I’d assumed MINIMAL was safe because every table has a primary key. That’s wrong. A primary key covers row identification — which row to update. It says nothing about missing columns in an insert.

Two deadlines on a stalled replica

Here is the part I’d want someone to tell me before I touched a stalled replica.

When the stall was found, the SQL thread was 1.68 million transactions behind, and reading a relay log whose corresponding binlog prod had already purged. Prod keeps five days of binlogs; the stall had gone unnoticed for longer.

Which means: the relay logs on the replica were the only surviving copy of those transactions. Anything that drops them — a reset, a rebuild, re-pointing the replica, a “let’s just start clean” — converts a ten-minute repair into a full 70 GB reseed. So the first command against a stalled replica is not a fix, it’s a comparison:

SHOW REPLICA STATUS\G   -- Relay_Source_Log_File

against SHOW BINARY LOGS; on the source. If your file is still in the source’s list, you have slack. If it isn’t, the relay logs are irreplaceable and every subsequent decision changes.

The second deadline is quieter. relay_log_space_limit was 1 GiB and pegged at it, which stalls the IO thread — it had stopped fetching several binlogs behind where prod was writing. When prod eventually purges the file the IO thread is parked on, it dies with error 1236, and that is also a full reseed. There were about eight binlogs of headroom, roughly four days.

So a stalled replica has a repair window measured by the source’s binlog retention, and a second one measured by relay log space. Both were days, not weeks.

One pleasant surprise: replica_parallel_workers was 0 — single-threaded apply — but measured throughput was about 110 transactions per second, which cleared the 1.67 M backlog in roughly four hours. That was faster than an Azure parameter change plus a restart would have been. Measure the actual apply rate before assuming catch-up needs tuning.

The conflict that will keep coming back

Five days after the rebuild, replication stopped again on error 1062 — and this one is structural rather than a mistake.

There’s a small table that hands out ID ranges for other tables. It has an auto-increment primary key, and both prod and the replica-side app write to it. Most rows are update-in-place counters, which self-heal on catch-up. But one row is DELETE-then-INSERTed on every cycle, so it burns a fresh auto-increment value each time. Eventually both sides allocated the same one. Collision.

The tempting fix is to add that table to the ignore filter alongside the log tables. Don’t. Unlike log tables, this one hands out IDs for real business tables. Freezing its counters on the replica would let the replica-side app issue IDs that collide with prod’s — trading a loud replication error for silent data corruption.

So it gets repaired by hand, and will again:

-- on prod: confirm the id genuinely isn't there
SELECT * FROM id_allocator WHERE id = <n>;

-- on the replica: remove the locally-allocated row
DELETE FROM appdb.id_allocator WHERE id = <n>;

CALL mysql.az_replication_start;

Diff the whole table on both sides first. Rows that differ only in their counter value are just lag, not divergence.

What I’d actually change

The technical findings above are all downstream of one gap: nothing alerted on Replica_SQL_Running going to No. Both incidents were found by a person noticing something else. The second one had been broken for five days — most of the binlog retention window — before anyone looked.

Every hard problem in this post existed only because the easy signal wasn’t being watched. A five-line check on Replica_SQL_Running, Last_Error and Seconds_Behind_Source would have turned a week of forensics into a Tuesday afternoon.

The rest, in short:

• Confirm server identity by server_uuid, never by hostname.
• PITR is a backup, not a replication seed.
• Compare Relay_Source_Log_File against the source’s binlogs before you touch a stalled replica.
• A clean mysqldump exit code is not a complete restore. Count rows.
• On a managed MySQL, assume every conflict must be repaired by hand, and write the repair recipe down the first time you work it out.