How to know if you’re updating Slave you should not ?

When replication runs out of sync first question you often ask is if someone could be writing to the slave. Of course there is read_only setting which is good to set in the slave but it is not set always and also users with SUPER privilege bypass it.

Looking into binary log is obvious choice – this is a good reason to have binary log on the slave if you do not need it for anything else. By default MySQL will only write statements which come to the server directly (not via replication thread) so you will know offender at once.

In many cases however log_slave_updates is enabled which makes slave to write all updates to binary logs – the ones executed directly on the host as well as coming through replication thread. There is however a way to know which is which – based on server_id.
Here is snippet from Slave binary log which has updates one on Master and another on Slave directly:

First query has server_id=1 and thus same through the master, another has server_id=101 which is slave id.
So to see if we have any queries ran on the slave directly we can do:

Any query events with local server_id means it is being written directly.

Share this post

Comments (8)

  • Dmitriy Reply

    Also, the read-only setting can only be set at server start. This means that any high-availability scheme will need to edit the my.cnf and restart the slave before switching operations to it, while without the read-only “stop slave; reset master;” is enough.

    April 11, 2010 at 10:21 pm
  • Pieters Reply

    @Dmitriy: read_only is a dynamic variable and can be changed at runtime, a restart is not required.

    April 12, 2010 at 3:41 am
  • bon scott Reply

    Is it possible to grep the queries for a specific server?
    i.e. all queries done on server id 101?

    April 12, 2010 at 4:11 am
  • Jeremy Cole Reply

    Dmitriy: You can use SET GLOBAL read_only=1/0

    April 12, 2010 at 1:08 pm
  • Dmitriy Reply

    I don’t think you can do that in MySQL 5.0.x.

    April 12, 2010 at 1:40 pm
  • miloska Reply

    Dmitriy: with MMM you must start your servers with read_only and MMM will disable/enable this flag for you.

    April 12, 2010 at 1:50 pm
  • Baron Schwartz Reply

    Dmitry, yes, you can in MySQL 5.0. Why don’t you look up the docs, or test it, instead of just posting here without even checking first?

    April 12, 2010 at 2:08 pm
  • peter Reply

    Bon Scott,

    Yes as you have server_id in the binary logs you can look for queries done on any server.

    April 13, 2010 at 5:36 pm

Leave a Reply