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?