0

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.

PLui
  • 121
  • 4

1 Answers1

0

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)

NickW
  • 10,263
  • 1
  • 20
  • 27
  • 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 03 '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 03 '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 03 '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 03 '13 at 19:29