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

strange issue in replication mysql 5.5.28

yogesh777yogesh777 ContributorCurrent User Role Beginner
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

[[email protected] 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

[[email protected] 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?

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

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

    So I would suggest you to filter database/tables on slave with replicate-wild-* options rather then from master and check. Thanks.
  • yogesh777yogesh777 Contributor Current User Role Beginner
    I got your point. How binary position relate to size of binary file size here in this particular case?
  • mirfanmirfan Database Administrator Inactive User Role Beginner
    Hi,

    If you are using replication filters then there is caveat which can cause this issue.
    Let me show with one quick example.
    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.
  • yogesh777yogesh777 Contributor Current User Role Beginner
    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

    [[email protected] mysql]# ls -l mysql-bin.000009
    -rw-rw---- 1 mysql mysql 216476307 Aug 8 10:10 mysql-bin.000009
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.