Reading the log positions of a snapshot consistently

MySQL 8.0.11 introduced a new performance schema table named log_status, which provides consistent information about MySQL server instance log positions from replication and transactional storage engines.

Used in conjunction with other MySQL 8.0 feature (the backup lock), this new feature will allow backup tools to take backup with minimal impact in DML throughput, but ensuring consistent snapshot with respect to GTIDs, replication, binary logs and transactional storage engine logs.

Backup with minimal impact in DML throughput

MySQL 8.0.3 introduced a new type of backup lock that permits DML during an on-line backup while preventing operations that could result in an inconsistent snapshot:

LOCK INSTANCE FOR BACKUP prevents files from being created, renamed, or removed. REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE, and account management statements are blocked. … Operations that modify InnoDB files that are not recorded in the InnoDB redo log are also blocked.

When the server instance is locked for backup, no automatic purge of binary log files and relay log files takes place as it would result in files being removed. But as an exception to the rules quoted above, new binary log files and relay log files can be created when the server instance is locked for backup (or else this would block DMLs at some point in time during the backup operation).

So far, so good! Server does not remove files… I can start copying all server files to my backup… But what about consistency? How could I known that the InnoDB LSN being copied is consistent with the binary log file being copied? How could I ensure that once restored, my replica server will continue to replicate from the exact same point as it was when I backed it up?

In order to provide server instance log positions in a consistent way to backup tools MySQL 8.0.11 introduced a new performance schema table named log_status. Querying this table (which required BACKUP_ADMIN privilege) will make the server instance to “freeze” for a very short time but enough to collect a consistent view of:
– GTID_EXECUTED;
– Current binary log file name and position;
– Each replication channel relay log file name and position (from receiver thread point of view);
– InnoDB LSNs.

Note: Even though server already provided all the information in the new table by other means, this table access guarantee that the generated data refer to the same snapshot.

Here is an example in a master server:

mysqld.1>SELECT * FROM performance_schema.log_status\G
*************************** 1. row ***************************
    SERVER_UUID: 51d92186-4251-11e8-ab97-0010e0734796
          LOCAL: {"gtid_executed": "51d92186-4251-11e8-ab97-0010e0734796:1-15", "binary_log_file": "master-bin.000002", "binary_log_position": 9463076}
    REPLICATION: {"channels": []}
STORAGE_ENGINES: {"InnoDB": {"LSN": 30503823, "LSN_checkpoint": 30503823}}
1 row in set (0,01 sec)

Here is an example in a multi source replica server:

mysqld.2>SELECT * FROM performance_schema.log_status\G
*************************** 1. row ***************************
    SERVER_UUID: 51d5fe39-4251-11e8-ab34-0010e0734796
          LOCAL: {"gtid_executed": "51d92186-4251-11e8-ab97-0010e0734796:1-19", "binary_log_file": "slave-bin.000001", "binary_log_position": 9794017}
    REPLICATION: {"channels": [{"channel_name": "", "relay_log_file": "slave-relay-bin.000004", "relay_log_position": 9463292}, {"channel_name": "ch1", "relay_log_file": "slave-relay-bin-ch1.000002", "relay_log_position": 23796}]}
STORAGE_ENGINES: {"InnoDB": {"LSN": 30595231, "LSN_checkpoint": 30595231}}
1 row in set (0,00 sec)

After querying this table, the backup tool should know exactly up to which point to copy from the binary log files, the relay log files and the InnoDB redo logs. Restoring these infrastructures to the logged positions shall put a server instance in a consistent state according to the moment the log_status performance schema table was queried.

Once finishing copying the logs up to the specified positions, the backup tool can unlock the instance so the automatic purges of binary log files and relay log files can proceed as usual and removing other limitations of the backup lock.