MySQL Slave Not Replicating

MySQLmysql-replication

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.

Best Answer

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)

Related Topic