MySQL Replication – How to Detect Slave Writes

The best way would be to enable binary logging on the Slave. Why ?

With binary logging enabled on the Slave, each INSERT, UPDATE, DELETE recorded in the binary logs comes with the server_id where the SQL statement originated.

What if log-slave-updates was enabled ?

If you have log-slave-updates enabled on the Slave, every INSERT, UPDATE, DELETE from the Master comes with the Master's server_id. If any writes were executed on the Slave directly, you will have to separate the commands.

EXAMPLE: suppose the Master's server_id is 10 and Slave's server_id is 20. Now, let's say you have these binary logs on the Slave:

+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 | 1073741824 |
| mysql-bin.000002 |  987741325 |
+------------------+------------+

You can separate the Master's commands from the Slave's commands like this:

mysqlbinlog --server-id=10 mysql-bin.* > Master_Commands.sql
mysqlbinlog --server-id=20 mysql-bin.* > Slave_Commands.sql

Then, you examine Slave_Commands.sql to see what commands were run and when.

What if log-slave-updates was disabled ?

This would be better because the binary logs on the Slave should never increase in size on a Slave unless someone or something ran INSERTs, UPDATEs, DELETEs locally.

EXAMPLE: Let's say you ran RESET MASTER; SHOW BINARY LOGS; on the Slave. For MySQL 5.6, you should see this:

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+

From this point forward, if the binary logs ever change in size, this quicky tells you that someone or something ran INSERTs, UPDATEs, DELETEs locally. Next, you run

mysqlbinlog mysql-bin.* > Local_Commands.sql

Then, you examine Local_Commands.sql to see what commands were run and when.