The shortest outage I ever caused lasted about ninety seconds, and a backup caused it. The nightly logical dump ran FLUSH TABLES WITH READ LOCK at 03:00 sharp. An analytics SELECT had been running for forty seconds. FTWRL queued behind the query, every other statement on the instance queued behind FTWRL, and twelve hundred connections later the application was timing out across the board. I killed the backup and watched the queue drain like a dam opening. The backup tool had done exactly what it was told. FTWRL is simply a stall generator: it waits on everything, and then everything waits on it.
MySQL 8.0 gave backup tooling a much lighter set of primitives, and most of the operational pain people still associate with backups is a hangover from tools and habits that predate them. Here is what FTWRL actually waits on and blocks, what LOCK INSTANCE FOR BACKUP changes, how performance_schema.log_status replaces the SHOW MASTER STATUS dance, how XtraBackup and the Clone plugin use the modern locks, why the read-only-plus-FTWRL ritual does not fix the real problem, and how to catch a backup-induced stall while it is happening.
What does FTWRL wait on — and block?
It waits for every in-flight statement on every table to finish, and while it waits, new statements queue behind its pending lock request. FLUSH TABLES WITH READ LOCK must close all open tables and take the global read lock, so it blocks until every running query completes and every open transaction releases its table metadata locks. The killer detail is the queueing: while FTWRL is waiting, its pending request sits in the metadata lock queue, and new statements that need conflicting locks line up behind it — thread states reading Waiting for global read lock, and once it progresses, Waiting for commit lock for anything trying to commit. Even pure SELECTs pile up, because their lock requests sit behind the pending FTWRL on the hot tables. So one forty-five-second analytics query plus one FTWRL does not cost forty-five seconds of backup delay; it costs forty-five seconds of total instance stall, at whatever connection rate your traffic generates. And lock_wait_timeout defaults to a year, so an FTWRL that cannot get its lock will wait essentially forever, hoarding the queue the whole time.
What did MySQL 8.0 replace it with?
LOCK INSTANCE FOR BACKUP — an instance-level backup lock that freezes structure and non-transactional writes while leaving ordinary InnoDB DML running. It exists precisely for online backup tools: while held, it blocks DDL (creates, alters, drops, renames, truncates) and changes to non-transactional tables, the operations that would make a file-level copy inconsistent, and it permits reads and writes on InnoDB tables because InnoDB's own redo and undo machinery is what makes the copy consistent. The privilege model tightened at the same time: you need BACKUP_ADMIN, a grant made for backup service accounts so they no longer need the keys to the kingdom. UNLOCK INSTANCE releases it, and a disconnected session releases it too — FTWRL always had that same property, and a half-open connection the server has not reaped can hold either lock, so the real upgrade is the scope, not the cleanup: what the backup lock stops is DDL and non-transactional writes, not ordinary InnoDB traffic. The lock is lighter, the privilege is narrower, and the queue it can build is measured in DDL statements rather than in every connection on the instance.
How do you get consistent binlog coordinates without FTWRL?
Read performance_schema.log_status while holding the backup lock — it reports binlog file and position, executed GTIDs, and storage-engine log coordinates in a single row. The old pattern needed FTWRL, or Percona's LOCK BINLOG FOR BACKUP, wrapped around SHOW MASTER STATUS, because the coordinates had to be frozen while you read them. The 8.0 flow is shorter and the freeze is lighter:
-- the 8.0 replacement for FTWRL during physical backups
LOCK INSTANCE FOR BACKUP;
-- consistent coordinates: binlog position, GTIDs, InnoDB LSN
SELECT SERVER_UUID, LOCAL, REPLICATION, STORAGE_ENGINES
FROM performance_schema.log_status;
UNLOCK INSTANCE;
-- if a tool still insists on FTWRL, make it fail fast instead of queueing
SET SESSION lock_wait_timeout = 5;
FLUSH TABLES WITH READ LOCK;The LOCAL column is a JSON document with the server's gtid_executed and binary log file and position; REPLICATION describes the replication channels; STORAGE_ENGINES carries the InnoDB LSN and checkpoint data that ties the copy to a crash-recovery point. A backup tool that takes the backup lock, copies or snapshots, reads log_status, and unlocks has a consistent capture with a lock window measured in seconds. The last statement in that block is my opinionated addition: if you are stuck with a tool that still issues FTWRL, a session-level lock_wait_timeout of a few seconds turns a potential instance-wide queue into a failed command the tool can retry.
How do XtraBackup and the Clone plugin use these locks?
XtraBackup copies InnoDB essentially lock-free by following the redo log, and takes the backup lock only briefly at the end to freeze structure, copy non-InnoDB files, and capture coordinates. XtraBackup 8.0 streams .ibd files while tracking redo, so InnoDB is never locked at all; it takes LOCK INSTANCE FOR BACKUP for the final phase — the MyISAM tables, the server-level files, the log_status read — which is why a modern XtraBackup run's lock window is seconds even on a terabyte-scale instance. Older XtraBackup versions used FTWRL around the non-InnoDB phase, and that era is exactly where the industry muscle memory of backups stall production comes from. The Clone plugin works from the other direction: CLONE INSTANCE builds a consistent snapshot of InnoDB — and only InnoDB, with MyISAM and CSV tables recreated empty on the recipient — while the donor keeps serving reads and writes for the bulk of the copy. Its trade is donor DDL rather than a final lock window: DDL is blocked for the duration of the clone (clone_block_ddl, the default since 8.0.27 and unconditional before it), and disabling that block means a concurrent DDL fails the clone and you retry. The provisioning workflow is covered in the Clone plugin notes. Logical tools are a separate branch of the family tree: mysqldump's --single-transaction gets consistency from a REPEATABLE READ snapshot with no lock at all for InnoDB-only schemas, and the trade-offs against mydumper are in mydumper vs mysqldump. FTWRL remains genuinely necessary only when non-transactional tables must be consistent with the rest of the dump.
Is read-only plus FTWRL still worth doing?
It quiets writers and does nothing about the long SELECT that FTWRL actually waits behind — belt and braces, not a fix. The ritual looks like this: before the backup, SET GLOBAL super_read_only=ON so the instance goes quiet, then FTWRL, then the dump. Setting super_read_only stops new writes, including from privileged accounts, and read_only stops ordinary ones — but neither touches the in-flight sixty-second reporting query, and that query, not the writers, is what FTWRL is waiting on. I have watched teams add the read-only step, see no improvement, and conclude backups are just dangerous, when the actual fix was staring at the processlist. What genuinely shortens the FTWRL window: run it in the quietest traffic window you have; kill SELECTs older than a threshold first, since one old query is the usual blocker; and keep that session-level lock_wait_timeout small so a blocked FTWRL aborts and retries instead of queuing the instance behind it. My position after the ninety-second outage: a backup that cannot obtain FTWRL within five seconds should fail and retry, never wait. An aborted backup is an email. A queued FTWRL is an incident.
How do you catch a backup-induced stall while it is happening?
Look in performance_schema.metadata_locks for a pending GLOBAL or BACKUP LOCK request with a crowd of PENDING requests queued behind it. FTWRL shows up as a GLOBAL lock request in PENDING state; LOCK INSTANCE FOR BACKUP appears as BACKUP LOCK; the victims queue behind with their own pending requests, and processlist states fill up with Waiting for global read lock, Waiting for backup lock, and Waiting for commit lock:
SELECT t.PROCESSLIST_ID,
ml.OBJECT_TYPE,
ml.LOCK_TYPE,
ml.LOCK_STATUS,
t.PROCESSLIST_STATE,
t.PROCESSLIST_INFO
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads t
ON t.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.OBJECT_TYPE IN ('GLOBAL', 'COMMIT', 'BACKUP LOCK');Alert on any thread sitting in those wait states for more than a few seconds, because the queue grows at your connection rate even off-peak — twelve hundred threads in forty seconds, at 03:00, in my case. The general workflow for reading metadata lock queues, including who blocks whom, is in metadata lock diagnosis, and backup locks are one row in that same picture. One detection hint that costs nothing: log your backup tool's lock acquisition time. When LOCK INSTANCE FOR BACKUP or FTWRL takes longer than a couple of seconds to return, that is the leading edge of the stall, and it is visible minutes before the application starts timing out.
Where MonPG stands on MySQL
I build MonPG, so plainly: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. Backup windows are exactly the kind of correlated event the MySQL work is designed to make visible — lock waits spiking, thread states piling into backup-lock queues, replication lag stepping up during the copy — as one timeline instead of three mysteries. The MySQL monitoring (coming soon) page tracks that work as it lands. In the meantime the same approach runs on the PostgreSQL side today, and more of these MySQL field notes live on the blog.