Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

DZone's Guide to

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

· Database Zone
Free Resource

Traditional relational databases weren’t designed for today’s customers. Learn about the world’s first NoSQL Engagement Database purpose-built for the new era of customer experience.

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:

   1.
# at 666
2.
#100411 19:24:01 server id 1 end_log_pos 725 Query thread_id=68 exec_time=1 error_code=0
3.
SET TIMESTAMP=1271039041/*!*/;
4.
BEGIN
5.
/*!*/;
6.
# at 725
7.
#100411 19:24:01 server id 1 end_log_pos 819 Query thread_id=68 exec_time=0 error_code=0
8.
SET TIMESTAMP=1271039041/*!*/;
9.
update tmp set c1=5 where id=10
10.
/*!*/;
11.
# at 819
12.
#100411 19:24:01 server id 1 end_log_pos 846 Xid = 27
13.
COMMIT/*!*/;
14.
# at 846
15.
#100411 19:24:45 server id 101 end_log_pos 914 Query thread_id=5 exec_time=0 error_code=0
16.
SET TIMESTAMP=1271039085/*!*/;
17.
BEGIN
18.
/*!*/;
19.
# at 914
20.
#100411 19:24:45 server id 101 end_log_pos 1007 Query thread_id=5 exec_time=0 error_code=0
21.
SET TIMESTAMP=1271039085/*!*/;
22.
update tmp set c1=6 where id=8
23.
/*!*/;
24.
# at 1007
25.
#100411 19:24:45 server id 101 end_log_pos 1034 Xid = 37

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:

   1.
[root@centos data]# mysqlbinlog mysql-bin.000002 | grep "server id 101" | grep Query
2.
#100411 19:11:22 server id 101 end_log_pos 360 Query thread_id=4 exec_time=0 error_code=0
3.
#100411 19:11:22 server id 101 end_log_pos 453 Query thread_id=4 exec_time=0 error_code=0
4.
#100411 19:24:45 server id 101 end_log_pos 914 Query thread_id=5 exec_time=0 error_code=0
5.
#100411 19:24:45 server id 101 end_log_pos 1007 Query thread_id=5 exec_time=0 error_code=0

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

Learn how the world’s first NoSQL Engagement Database delivers unparalleled performance at any scale for customer experience innovation that never ends.

Topics:

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}