We have a master - slave setup with ROW based replication. We are seeing huge delay's on the salve even though there is no activity running wither on master or slave.

When we looked in, we observed the SQL thread looks like hanged. It has been in "Reading event from the relay log" state since last 3 hours or more.

baleaf:(none)> show processlist ;
+--------+-------------+-----------+------+---------+-------+----------------------------------+-----    -------------+
| Id     | User        | Host      | db   | Command | Time  | State                            |        Info             |
+--------+-------------+-----------+------+---------+-------+----------------------------------+-----    -------------+
| 217159 | system user |           | NULL | Connect |  1039 | Waiting for master to send event | NULL             |
| 217160 | system user |           | NULL | Connect |  10045 | Reading event from the relay log | NULL             |
+--------+-------------+-----------+------+---------+-------+----------------------------------+-----    -------------+
4 rows in set (0.00 sec)

baleaf:blackarrow_dw> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.001403
          Read_Master_Log_Pos: 95601911
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 12757514
        Relay_Master_Log_File: binlog.001403
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                   Last_Errno: 0
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 32820355
              Relay_Log_Space: 75539220
              Until_Condition: None
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: 7720
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
               Last_SQL_Errno: 0
             Master_Server_Id: 1
1 row in set (0.00 sec)

Shall some one please have a look into this ASAP.

  • We tried to find what the query being executed during this time. But the thread is always showing "Reading event from the relay log" state only which means it is ready to execute the even that it has read.... so we stuck up.
    – Uday
    Mar 18, 2013 at 2:05

4 Answers 4


You may need to reset the relay logs. PLEASE DON'T USE RESET SLAVE;

You need to reset the relay logs in such a way that it picks up from the last SQL statement it executed.

Please run the the following:


If Seconds_Behind_Master stays at zero, CONGRATULATIONS !!!

If Seconds_Behind_Master goes back to 7720 immediately, check the date and time set in the OS on the Master against the the date and time set in the OS on the Slave.

Give it a Try !!!

  • You are the best Ronlando!
    – usef_ksa
    Sep 5, 2013 at 16:44
  • Why should RESET SLAVE not be used? This pages, redips.net/mysql/replication-slave-relay-log-corrupted, seems like a similar solution but recommends to reset. The solution here hasn't worked for me and my systems have the same OS time/date. Jan 14, 2020 at 14:56
  • @user3783243 The above method I gave has one tiny advantage: It keeps the host, port, username and password intact. Depending on the version of MySQL, doing RESET SLAVE; might forget host, port, username and password as well. Doing CHANGE MASTER TO will erase the relay logs and forgot the position anyway. Jan 14, 2020 at 15:23
  • @user3783243 Please also note the last paragraph from the link you gave: Just to note that “reset slave” will delete master.info, relay-log.info and all the relay log files, so it’s not needed to clean leftovers in /var/lib/mysql directory.. Guess what's in master.info ??? The host, port, username, and password. When master.info goes, they go as well. So the CHANGE MASTER TO in that link in incomplete. It needs to have the full syntax CHANGER_MASTER TO host,port,username,password,logfile,logpos Jan 14, 2020 at 15:36

Look into the master bin log to see which query is logged on position 32820355 of file binlog.001403

mysqlbinlog --start-position=32820355 binlog.001403

see if it helps to find what is causing the sql thread to hang.


Even this question is too old but as there can be one more scenario so I am posting this answer here so that some can get help from here.

Suppose there are bulk DML (insert/update/delete) statements associated with single pointer (normally one statement should be associated with any possition in binlog but due to some faulty script/app by developer I got huge multiple statements associated with single position in binlog file), then if you execute show slave status command then you will get replication fine even slave_io_running=Yes and slave_sql_running=yes but you will get second_behind_master continue in incrementing mode and even exec_master_log_pos will not change, which should continue increment in normal scenario.

Reason of this behaviour is due to mysql first execute all sql statements associated with single pointer and then move to next log position as here statements are in bulk on single pointer, so we are not seeing position movement here for a long time.

You can verify it by below steps-

  1. read binary log (on master server) file at the position where exec_master_log_pos is stuck, here it is 260127695. (below command is to read row based replication), here you will get multiple sql statements associated with this position.

    mysqlbinlog --start-position=260127695 mysql-bin.000189 --base64-output=decode-rows -v | less

  2. read relay log file (on slave server) at the position where reley_log_pos is stuck, here it is 260127858. (below command is to read row based replication), here you will get multiple sql statements associated with this position.

    mysqlbinlog relay-bin.000002 --start-position=260127858 --base64-output=decode-rows -v | less

  3. You can check if tables in your db is updating or not by below command and you will get that your tables are continue updating even your replication problem is from last 2 hours.

    ls -lt /var/lib/mysql/mydb | head -15

by this command you will get recent 15 updated tables file.

  1. Now you can check if in these tables data is changing (insert/update/delete) or not.

Finally your slave will catch your master as these statements will completely execute on slave.

@Rolando: May you light up that in which case so many multiple statements associated with single log position. It will be really very helpful for better understanding with replication.


"Queueing master event to the relay log" for a long time means the IO thread has a statement from the master that it wants to write to the relay log, but can't access that file for some reason. Check for file system level issues - full disk, or file locked by some external process like a virus scan or backup (use lsof to hunt for these).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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