Not the answer you need?
Register and ask your own question!

Data/rows are missing in the slave server while replication

johnsonmjacobsjohnsonmjacobs ContributorCurrent User Role Beginner
Hi All,

When are using 2 level replication:

MASTER1 (mysql 5.6.16-log) <
> MASTER2 (mysql 5.6.16-log)
> SLAVE1 (Percona 5.6.39-83.1-log)

The SLAVE1 has been setup recently ( a month ago) and after initial replication setup we run checksum between MASTER2 and SLAVE1 and was good.

But later when we compared the the total row count of some core tables between MASTER2 and SLAVE1 there is a mismatch and checksum also show such difference

We identified a huge difference in the total row count (using "select count(*) from table") and some rows are not even synced to SLAVE1. For getting more information I am attaching the my.cnf of SLAVE1.

The purpose of the new slave is to migrate the database to a new server running on Percona and due to this unusual behaviorI am stucked and under time pressure.

Your analysis and feedback on this is very helpful.

Regards, Johnson

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hello there. The team are likely to need some more information from you... if you can get me that then I will see if I can find someone to advise: Anyhow, if you could get this info .. thanks. And also about your environment, any server error logs etc.

    I moved your post to the right place in the forum.
  • johnsonmjacobsjohnsonmjacobs Contributor Current User Role Beginner
    Thank you Lorraine, On all the servers we have log_slave_updates=1
    • are you using pt-table-checksum for your checks?
    Yes, We are using pt-table-checksum in MIXED BinarylLog mode
    • the count(*) might not be telling you exactly what you think it should be (I understand)
    1. I stopped the slave on Master2 (readonly) to prevent updates from Master1
    2. Ensure that the seconds_behind_master is Zero on Master2 and Slave1 to make sure that both Master2 and Slave1 are in sync
    3. Run select count(*) from <table> on both and can see a huge count difference
    4. Also run pt-table-checksum on some selected tables and found data inconsistency
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    OK thank you for that update. I will see if I can find someone to take a look. If there's something clear that you can do then hopefully they can help.

    A little context though: if it's something a bit more specific to a particular case - that can sometimes be more difficult to address through this public forum.
  • johnsonmjacobsjohnsonmjacobs Contributor Current User Role Beginner
    Hi Lorraine,

    I missed to say that we have dynamically changed innodb_flush_log_at_trx_commit = 2 on Master2(Read_only) and Slave1 to minimize the replication lag.

    Also attaching a sample table row count details from all the three servers. (M1, M2 and S1)

    Master1 (Production)
    =======
    mysql> select count(*),max(sys_created_on) from task;
    +
    +
    +
    | count(*) | max(sys_created_on) |
    +
    +
    +
    | 4819832 | 2018-08-27 07:44:57 |
    +
    +
    +
    1 row in set (1.38 sec)


    Master2 (Read_only)
    ========
    mysql> select count(*),max(sys_created_on) from task;
    +
    +
    +
    | count(*) | max(sys_created_on) |
    +
    +
    +
    | 4819832 | 2018-08-27 07:44:57 |
    +
    +
    +
    1 row in set (1.62 sec)

    Slave1
    =======
    mysql> select count(*),max(sys_created_on) from task;
    +
    +
    +
    | count(*) | max(sys_created_on) |
    +
    +
    +
    | 4806350 | 2018-08-27 07:44:57 |
    +

    +
    +
    1 row in set (1.14 sec)


    On slave1 there is a count difference of 13482 rows and all the server's seconds behind master is Zero.

    Regards,
    Johnson
  • johnsonmjacobsjohnsonmjacobs Contributor Current User Role Beginner
    Hi Team,

    Any updates on thsi topic !!

    Regards,
    Johnson
  • johnsonmjacobsjohnsonmjacobs Contributor Current User Role Beginner
    Can someone help me on this.

    Regards,
    Johnson
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.