Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Can you connect from the master to the slave using: mysql -h123.123.123.123 -ureplication -phidden –  krugger Nov 13 '12 at 1:22

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.