MySQL replication not working - no errors

Question

I set both master and slave up by-the-book. I ran the initial LOAD DATA FROM MASTER; on the slave which worked just fine. But when I insert data into the master it is not being copied to the slave at all. I already tried restarting both master and slave mysqld processes, and "slave stop / slave start" on the slave. What's going on?

Master

Config:

server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = pchelp binlog_ignore_db = mysql binlog_ignore_db = test 

mysql> select * from pchelp.test_table;

+----+---------+ | id | sometxt | +----+---------+ | 1 | x | | 2 | x | | 3 | y | | 4 | z | | 5 | p | | 6 | i | +----+---------+ 6 rows in set (0.00 sec) 

mysql> show master status \G

*************************** 1. row *************************** File: mysql-bin.000009 Position: 106 Binlog_Do_DB: pchelp Binlog_Ignore_DB: mysql,test 1 row in set (0.00 sec) 

Slave

Config:

server-id = 2 master-host = hidden.x.xx master-user = replication master-password = hidden master-port = 3308 replicate_do_db = pchelp 

mysql> select * from pchelp.test_table;

+----+---------+ | id | sometxt | +----+---------+ | 1 | x | | 2 | x | | 3 | y | | 4 | z | | 5 | p | | 6 | i | +----+---------+ 6 rows in set (0.00 sec) 

mysql> show slave status \G

*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: hidden.x.xx Master_User: replication Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: pchelp Replicate_Ignore_DB: 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: 106 Relay_Log_Space: 407 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.01 sec) 

Master

insert into pchelp.test_table (id,sometxt) values (7,'q');

Query OK, 1 row affected (0.00 sec) 

mysql> select * from pchelp.test_table;

+----+---------+ | id | sometxt | +----+---------+ | 1 | x | | 2 | x | | 3 | y | | 4 | z | | 5 | p | | 6 | i | | 7 | q | +----+---------+ 7 rows in set (0.01 sec) 

mysql> show master status \G

*************************** 1. row *************************** File: mysql-bin.000009 Position: 106 Binlog_Do_DB: pchelp Binlog_Ignore_DB: mysql,test 1 row in set (0.00 sec) 

Slave (After insert on master)

mysql> select * from pchelp.test_table;

+----+---------+ | id | sometxt | +----+---------+ | 1 | x | | 2 | x | | 3 | y | | 4 | z | | 5 | p | | 6 | i | +----+---------+ 6 rows in set (0.01 sec) 

mysql> show slave status \G

*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: hidden.x.xx Master_User: replication Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: pchelp Replicate_Ignore_DB: 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: 106 Relay_Log_Space: 407 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) 

Anyone have any bright ideas on what could be going wrong? User 'replication' has full permissions (ran this on both master and slave);

grant replication slave on *.* to replication@'%' identified by 'hidden'; GRANT ALL PRIVILEGES ON pchelp.* TO replication; 

And again, the LOAD DATA FROM MASTER; command worked just fine.. I don't get it.

Answer 1

Either binlog_do_db or binlog_ignore_db probably don't work the way you think they do. Here's Baron Schwarz explaining why. My guess is that before you wrote that command, you either wrote USE mysql or USE test. Since those two databases are ignored, any statements you run while using that database, even if they write to another database, are ignored.

One hint that the master didn't even write that INSERT statement to the binary log is in the output from your SHOW MASTER STATUS\G command. Both before and after the INSERT statement:

Position: 106 

The slave also has the same position which explains why it thinks it's working fine:

Read_Master_Log_Pos: 106 

Suggestion: remove the binlog_do_db and binlog_ignore_db configuration options. If you need filtering, do it on the slave(s).