EmergencyEMERGENCY? Get 24/7 Help Now!

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


Posted on:

|

By:


PREVIOUS POST
NEXT POST
Share Button

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 Button
PREVIOUS POST
NEXT POST


Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.



Tags:

Categories:
Insight for DBAs, MySQL


Comments

Leave a Reply

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.

Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.

No, thank you. Please do not ask me again.