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.
Best Answer
Either
binlog_do_dborbinlog_ignore_dbprobably 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 wroteUSE mysqlorUSE 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
INSERTstatement to the binary log is in the output from yourSHOW MASTER STATUS\Gcommand. Both before and after theINSERTstatement:The slave also has the same position which explains why it thinks it's working fine:
Suggestion: remove the
binlog_do_dbandbinlog_ignore_dbconfiguration options. If you need filtering, do it on the slave(s).