Announcement

Announcement Module
Collapse
No announcement yet.

strange issue in replication mysql 5.5.28

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • strange issue in replication mysql 5.5.28

    i am facing very strange issue in mysql replication, I am using mysql 5.5.28 and statement based replication. all was going well when one of the developer report data inconsistencies on master and slave DB. After investigation i found update from application on database not getting reflected in binary log however updates on mysql console change the binary position.

    On Master
    mysql> select count(*) from ad_details;
    +----------+
    | count(*) |
    +----------+
    | 3520246 |
    +----------+

    mysql> show master status\G
    *************************** 1. row ***************************
    File: mysql-bin.000009
    Position: 154166060
    Binlog_Do_DB: cuser
    Binlog_Ignore_DB: mysql,information_schema

    Now table ad_details gets the updates and count increased however binary position not changed and data not reflected on slave

    mysql> select count(*) from ad_details;
    +----------+
    | count(*) |
    +----------+
    | 3520248 |
    +----------+

    mysql> show master status\G
    *************************** 1. row ***************************
    File: mysql-bin.000009
    Position: 154166060
    Binlog_Do_DB: cuser
    Binlog_Ignore_DB: mysql,information_schema

    On Slave

    mysql> show slave status\G
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: ********
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000009
    Read_Master_Log_Pos: 154166060
    Relay_Log_File: mysqld-relay-bin.000030
    Relay_Log_Pos: 253
    Relay_Master_Log_File: mysql-bin.000009
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB: cuser
    Replicate_Ignore_DB: mysql,information_schema
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 154166060
    Relay_Log_Space: 410
    Until_Condition: None

    also physical size of binary file is same as binary position and increasing proportionally to the binary position

    [root@DB mysql]# ls -l mysql-bin.000009
    -rw-rw---- 1 mysql mysql 154166060 Aug 7 12:06 mysql-bin.000009


    Now I am creating a table manually on mysql console

    mysql> create table test1(id int);
    Query OK, 0 rows affected (0.03 sec)

    mysql> show master status\G
    *************************** 1. row ***************************
    File: mysql-bin.000009
    Position: 154166151
    Binlog_Do_DB: cuser
    Binlog_Ignore_DB: mysql,information_schema

    On Slave

    mysql> show slave status\G
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: ********
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000009
    Read_Master_Log_Pos: 154166151
    Relay_Log_File: mysqld-relay-bin.000030
    Relay_Log_Pos: 344
    Relay_Master_Log_File: mysql-bin.000009
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB: cuser
    Replicate_Ignore_DB: mysql,information_schema
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 154166151
    Relay_Log_Space: 501
    Until_Condition: None

    also size of binary file also increased to the binary position

    [root@DB mysql]# ls -l mysql-bin.000009
    -rw-rw---- 1 mysql mysql 154166151 Aug 7 12:58 mysql-bin.000009

    Any idea what is this happening?
    Last edited by yogesh777; 08-07-2013, 02:29 AM.

  • #2
    Hi,

    binlog-do-db option is quiet dangerous. Because its not doing what it supposed to do. Check below post.
    http://www.mysqlperformanceblog.com/...-is-dangerous/

    So I would suggest you to filter database/tables on slave with replicate-wild-* options rather then from master and check. Thanks.

    Comment


    • #3
      I got your point. How binary position relate to size of binary file size here in this particular case?

      Comment


      • #4
        Hi,

        If you are using replication filters then there is caveat which can cause this issue.
        Let me show with one quick example.

        Code:
        master [localhost] {msandbox} ((none)) > use test
        master [localhost] {msandbox} (test) > insert into t1 values (1),(2);
        Query OK, 2 rows affected (0.03 sec)
        Records: 2  Duplicates: 0  Warnings: 0
        
        master [localhost] {msandbox} (test) > select * from t1;
        +------+
        | id   |
        +------+
        |    1 |
        |    2 |
        +------+
        2 rows in set (0.00 sec)
        
        slave1 [localhost] {msandbox} ((none)) > select * from test.t1;
        +------+
        | id   |
        +------+
        |    1 |
        |    2 |
        +------+
        2 rows in set (0.00 sec)
        
        
        master [localhost] {msandbox} ((none)) > insert into test.t1 values (1),(2);
        Query OK, 2 rows affected (0.06 sec)
        Records: 2  Duplicates: 0  Warnings: 0
        
        master [localhost] {msandbox} ((none)) > select * from test.t1;
        +------+
        | id   |
        +------+
        |    1 |
        |    2 |
        |    1 |
        |    2 |
        +------+
        4 rows in set (0.00 sec)
        
        slave1 [localhost] {msandbox} ((none)) > select * from test.t1;
        +------+
        | id   |
        +------+
        |    1 |
        |    2 |
        +------+
        2 rows in set (0.00 sec)
        You can see that when using database alias it fails to replicate so make sure you select database first to update via "USE db;" command in your app. This behavior is documented in MySQL Manual.

        Comment


        • #5
          Thanks mirfan and niljoshi for clarification. I checked and verified it on my database. I am really surprised to see this is happening. I think to use these options developers should know how and when to use a database and query while writing code.
          If these replication and binary logging options have such problems then why they are available to use and if so then for whats specific requirements we should use these options?

          And any clue about binary position and size of binary file, Today again i check and see binary position and size of binary file is same.

          mysql> show master status\G
          *************************** 1. row ***************************
          File: mysql-bin.000009
          Position: 216476307
          Binlog_Do_DB: cuser
          Binlog_Ignore_DB: mysql,information_schema

          [root@DB mysql]# ls -l mysql-bin.000009
          -rw-rw---- 1 mysql mysql 216476307 Aug 8 10:10 mysql-bin.000009
          Last edited by yogesh777; 08-07-2013, 11:55 PM.

          Comment

          Working...
          X