Quickly tell who is writing to a MySQL replica

Many of us have been there in the past, you get an alert telling you that replication has stopped because of an error, you dig in to it to find that you’re getting an error for an update event that is trying to update a non-existent row, or a duplicate key error because the row ID for some INSERT already exists.

Even with the server set to read only (and not using the new super_read_only variable from MySQL 5.7.8), these problems can still happen – how many of you have seen over-zealous ops trying to “quickly fix” some problem only to royally screw up your data integrity?

The question then becomes – “who or what is making changes on my replica that shouldn’t be?!?”.

The only way to find this out in the past, and still “the conventional wisdom” (I just saw it recommended in a discussion in the MySQL support group) was to turn on the binary log on the replica, make sure log_slave_updates is not enabled, and then see what events turn up in the binary log.

The data within them is like the merged output of “SHOW GLOBAL STATUS LIKE ‘Com_%'” (only by user here for example, or by host/account in the other base summary views), with the regular performance schema statement data (latency, lock time, result info, sorting info, temporary table info etc.).

The replication SQL thread statement activity is also recorded within these tables. Within the base performance schema tables, these will have a user / host of NULL, and in the sys schema these are translated to a generic “background” user (an example from my local sandbox topology where one table was created with one row via replication, shown in the “background” user, and the msandbox user has created newly the sys schema):