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 theslave show status
in terms of the master log file (in your case yes) and the position (yes)