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

I had a deleted some data on the master mysql. Looking at the slave status, it shows this:

mysql> show slave status \G;
*************************** 1. row ***************************
           Slave_IO_State: Waiting for master to send event
              Master_Host: 66.x.y.z
              Master_User: repl
              Master_Port: 53308
            Connect_Retry: 60
          Master_Log_File: mysqllog.000002
      Read_Master_Log_Pos: 20341461
           Relay_Log_File: relay.000004
            Relay_Log_Pos: 252
    Relay_Master_Log_File: mysqllog.000002
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table: test.%
Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 20341461
          Relay_Log_Space: 1904953
          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:
Replicate_Ignore_Server_Ids:
         Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR:No query specified

Notice the big gap between Read_Master_Log_Pos & Relay_Log_Pos. Ran show processlist and one of the threads was this:

    Id: 45
  User: system user
  Host:
    db: NULL
Command: Connect
 Time: 297
 State: Slave has read all relay log; waiting for the slave I/O thread to update it
 Info: NULL

The master says...

mysql> show master status;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| mysqllog.000002 | 20341461 |              |                  |
+-----------------+----------+--------------+------------------+

Why is the slave not executing the SQL statements? I have read about how using binlog_do_db will screw up replication. But this is not the case here.

share|improve this question
    
What did you delete, how did you delete it? –  NickW Apr 3 '13 at 16:39
    
I deleted rows from various table by executing delete from tableA where xyz –  PLui Apr 3 '13 at 16:46
    
It's working fine then. –  NickW Apr 3 '13 at 16:47
    
did you try to delete rows from tables on DB name test ?, "Replicate_Wild_Do_Table: test.%" –  chocripple Apr 3 '13 at 16:57
    
@Rikih Yes, I did. –  PLui Apr 3 '13 at 16:58

1 Answer 1

Why do you think that the positions in the relay log and the relay master log have to match up? There could be different size limitations on the files between the servers, you just need to make sure the master show status matches with the slave show status in terms of the master log file (in your case yes) and the position (yes)

share|improve this answer
    
Reason why I'm asking is because I deleted some of the oldest rows from the master. I ran a select to get the oldest data from both the slave and master and noticed they don't match up. If the replication was working, I would have expected the results from select would match. –  PLui Apr 3 '13 at 16:55
    
Well, it should, but there are many reasons why it might not, replication not working is only one of them. From what you've posted, replication is actually running though. –  NickW Apr 3 '13 at 16:59
    
I see...could you point me to where else I could look to confirm why it's not working? Specifically why the delete didn't work? –  PLui Apr 3 '13 at 17:00
    
With the information you have given, there's currently no way to give an answer. According to your data, the replication is working perfectly fine. As NickW already said, the relay log and the Master Log are 2 different files and it's perfectly normal that they have different positions. Your Slave Thread is at exactly the same position as the master, and it also says Seconds_Behind_Master: 0. To debug this further, I'd suggest to check your MySQL-Binlogs on the master for the delete-statements you issued. –  etagenklo Apr 3 '13 at 19:29

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.