strange issue in replication mysql 5.5.28

MySQL & MariaDB Other MySQL® Questions
1

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?

2

Hi,

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

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

3

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

4

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.

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