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.

Can you connect from the master to the slave using: mysql -h123.123.123.123 -ureplication -phidden — krugger, Nov 13 '12 at 01:22

Ladadadada · Accepted Answer · 2012-11-13T09:06:19.253

3

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).

edited Nov 13 '12 at 09:06

answered Nov 13 '12 at 08:13

Ladadadada

26,337
7
59
90

Ah, that makes sense! Fyi, the article only mentions the explicit-disable config `replicate-wild-ignore-table=X.%` but it seems that the explicit-enable version I was looking for is `replicate-wild-do-table=X.%`. All on the slave side, of course. It's working fine so far, so thank you. But doesn't this mean the master's binlog fills up with alot of un-used data from databases I don't intend to replicate? – natli Nov 13 '12 at 10:38
Yes, the master's binlog will have every write statement in it. I'm not sure whether everything ends up in the slave's binlogs or whether they get shipped across the network at all. I suspect they do. `expire_log_days` is a useful option to look at. – Ladadadada Nov 13 '12 at 10:43

MySQL replication not working - no errors

Master

Slave

Master

Slave (After insert on master)

1 Answers1

Linked