DZone
Database Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Database Zone > Checking if a Slave Has Applied a Transaction From the Master

Checking if a Slave Has Applied a Transaction From the Master

This quick tutorial will make sure your MySQL slaves are applying transactions from their masters.

Marcelo Altmann user avatar by
Marcelo Altmann
·
Nov. 10, 16 · Database Zone · Tutorial
Like (1)
Save
Tweet
3.30K Views

Join the DZone community and get the full member experience.

Join For Free

In this blog post, we will discuss how we can verify if an application transaction executed on the master has been applied to the slaves.

In summary, is a good practice to alleviate the load on the master by doing reads on slaves. It is acceptable in most of the cases to just connect on slaves and issue selects. But there are some cases we need to ensure that the data we just applied on our master has been applied on the slaves before we query it.

One way to do this is using a built-in function called MASTER_POS_WAIT. This function receives a binary log name and position. It will block the query until the slave applies transactions up to that point, or timeout. Here is one example of how to use it:

-- insert our data on master
master [localhost] {msandbox} (test) > INSERT INTO test VALUES ();
Query OK, 1 row affected (0.00 sec)
-- get the binlog file and position from master
master [localhost] {msandbox} (test) > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 1591 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
-- connect on slave and run MASTER_POS_WAIT passing the binlog name and position
slave [localhost] {msandbox} ((none)) > SELECT NOW(); SELECT MASTER_POS_WAIT('mysql-bin.000005', 1591); SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2016-10-20 18:24:24 |
+---------------------+
1 row in set (0.00 sec)
-- it will wait until the slave apply up to that point
+-------------------------------------------+
| MASTER_POS_WAIT('mysql-bin.000005', 1591) |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
1 row in set (3.82 sec)
+---------------------+
| NOW() |
+---------------------+
| 2016-10-20 18:24:28 |
+---------------------+
1 row in set (0.00 sec)


Blocking the connection until the slave is in sync with the coordinate passed as a parameter on MASTER_POS_WAIT might not be affordable to all applications, however.

As an alternative, MySQL 5.6+ makes use of relay_log_info_repository configuration. If we set this variable to TABLE, MySQL stores the slave status information in the  slave_relay_log_info table under mysql database. We must configure the sync_relay_log_info variable, and set to 1 in case we use non-transactional tables such as MyISAM. It forces  slave_relay_log_info  to sync after each statement. So edit my.cnf on slaves:

relay_log_info_repository=TABLE
sync_relay_log_info=1


Now we can query slave_relay_log_info directly to see if the slave we are connected to already applied the transaction we need:

master [localhost] {msandbox} (test) > INSERT INTO test VALUES (NULL);
Query OK, 1 row affected (0.00 sec)
master [localhost] {msandbox} (test) > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 366 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > SELECT COUNT(*) FROM mysql.slave_relay_log_info WHERE (Master_log_name > 'mysql-bin.000003') OR ( Master_log_name = 'mysql-bin.000003' AND Master_log_pos >= '366' );
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)


Conclusion

You can use relay_log_info_repository as a replacement for  MASTER_POS_WAIT to check if a slave has applied a particular transaction. Since it won’t block your thread (in case the slave is not in sync) you will be able to either abort the operation or disconnect and move to the next slave.

master Database

Published at DZone with permission of Marcelo Altmann. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Data Statistics and Analysis With Java and Python
  • 3 Best Tools to Implement Kubernetes Observability
  • Spring, IoC Containers, and Static Code: Design Principles
  • Applying Domain-Driven Design Principles to Microservice Architectures

Comments

Database Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo