Slave status = no errors, but no replication process on Master

[Clive le Roux]

Hi,

MySQL Version: 5.1.39-community-log

I have a number of slaves all with the "report-host = xxxx" variable set. The problem I have just experienced happened when we had a network blip on a remote site. The slave displayed that all was fine, however a "show processlist" on the master displayed no replication for that slave, so it seems the slave replication process got into a "hung" status. After running "stop slave;start slave" on the slave server it came right and then displayed that it was 32102 seconds behind the master.

Has anyone else had an issue like this before, or any idea what happened/how to stop it from happening again?

N.B. anything with xxxxx below is just the names being blanked out (company doesn't want names or ips to be shown)

On the slave the "show slave status\G" shows no errors:
=======================================================
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: XXX.XXX.XXX.XXX
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000982
Read_Master_Log_Pos: 1005229731
Relay_Log_File: mysql-relay-bin.000041
Relay_Log_Pos: 1005229876
Relay_Master_Log_File: mysql-bin.000982
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error:
Exec_Master_Log_Pos: 1005229731
Relay_Log_Space: 1005230074
Until_Condition: None
Seconds_Behind_Master: 0

On the master "show processlist" (There should be another slave listed):
========================================================================
+----------+-------------+-----------------------+------+-------------+--------+----------------------------------------------------------------+------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-------------+-----------------------+------+-------------+--------+----------------------------------------------------------------+------+
| 42113686 | replication | xxxxx.xxx.co.nz:55740 | NULL | Binlog Dump | 234934 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 43310258 | replication | xxxxx.xx.co.nz:46564 | NULL | Binlog Dump | 69916 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
.
.
.


On the slave after "stop slave; start slave;":
==============================================
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: XXX.XXX.XXX.XXX
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000982
Read_Master_Log_Pos: 1009839731
Relay_Log_File: mysql-relay-bin.000041
Relay_Log_Pos: 1005229876
Relay_Master_Log_File: mysql-bin.000982
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error:
Exec_Master_Log_Pos: 1009839731
Relay_Log_Space: 1009840024
Until_Condition: None
Seconds_Behind_Master: 32102

On the master AFTER slave restart:
==================================
+----------+-------------+-----------------------+------+-------------+--------+----------------------------------------------------------------+------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-------------+-----------------------+------+-------------+--------+----------------------------------------------------------------+------+
| 42113686 | replication | xxxxx.xxx.co.nz:55740 | NULL | Binlog Dump | 234934 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 43310258 | replication | xxxxx.xx.co.nz:46564 | NULL | Binlog Dump | 69916 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 43851002 | replication | xxxxx.xx.co.nz:35812 | NULL | Binlog Dump | 252 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |


Thanks
Clive