The basic problem stems from the Slave's IO Thread. Sometimes, it has the nasty habit of playing dumb and not properly checking or pinging the master.

MySQL 5.5's semisynchronous replication allows you to have the master time itself out and degrade back to asynchronous replication. Slaves running semisynchronous replication should have a more sensitive IO Thread now.

Concerning your particular situation, you are using MySQL 5.0.24 ??? That's a very old version. There are two bug reports (Bug1 and Bug2) discussing this.

It just dawned on me that you asked this question earlier and DTest answered it. Giving credit where credit is due, the bug reports came from his answer.

It would be nice to to be informed when the Public IP is being disabled from Slave access. That way, you could run STOP SLAVE; on the slave in advance.

You also asked is there a way to come to know the master status from the slave, when this type of behavior occurs.

Here is a typical SHOW SLAVE STATUS\G

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.80.136
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019 <<------
          Read_Master_Log_Pos: 277892198 <<------
               Relay_Log_File: relay-bin.000058
                Relay_Log_Pos: 37535484
        Relay_Master_Log_File: mysql-bin.000019 <<------
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 277892198 <<------
              Relay_Log_Space: 277892637 <<------
              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:
1 row in set (0.00 sec)

Please note the following:

• Master_Log_File : Log file from Master whose latest SQL was last copied to relay logs
• Read_Master_Log_Pos : Log position within Master_Log_File whose latest SQL was last copied to relay logs
• Relay_Master_Log_File : Log file from Master whose SQL was last executed from the relay logs
• Exec_Master_Log_Pos : Log position within Relay_Master_Log_File whose SQL was last executed from the relay logs

If none of these value are moving, that could mean the all SQL is processed or that the slave is currently processing an SQL statement in the SQL thread that came from position Exec_Master_Log_Pos of the Master Log Relay_Master_Log_File.

Now, look at Relay_Log_Space. This number represents the sum total of all filesizes for all relay logs. If Slave_IO_Running is Yes and Relay_Log_Space is not changing, then go check the master by running SHOW MASTER STATUS;. It should like something like this:

mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000019 | 682563021 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

If the Master's (File,Position) of SHOW MASTER STATUS; is beyond the Slave's (Master_Log_File,Read_Master_Log_Pos) of SHOW SLAVE STATUS\G, then the IO Thread on the Slave is dead for intents and purposes, even if Slave_IO_Running is Yes.

The reason for the discrepancy is obvious.

When you run

SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A,
(SELECT COUNT(1) MySQLProcColumns FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='proc') B;

you get

mysql> SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A,
    -> (SELECT COUNT(1) MySQLProcColumns FROM information_schema.columns
    -> WHERE table_schema='mysql' AND table_name='proc') B;
+-----------------------+------------------+
| MySQLVersion          | MySQLProcColumns |
+-----------------------+------------------+
| 5.0.51a-community-log |               16 |
+-----------------------+------------------+
1 row in set (0.54 sec)

mysql>

and

mysql> SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A,
    -> (SELECT COUNT(1) MySQLProcColumns FROM information_schema.columns
    -> WHERE table_schema='mysql' AND table_name='proc') B;
+--------------+------------------+
| MySQLVersion | MySQLProcColumns |
+--------------+------------------+
| 5.5.8-log    |               20 |
+--------------+------------------+
1 row in set (0.01 sec)

mysql>

Here is a quick-and-dirty way to fix mysql.proc. Please pay attention:

STEP 01 : On the Slave DB Server, run STOP SLAVE;

STEP 02 : Install MySQL 5.5.26 on a Staging DB Server

STEP 03 : Copy the following files from that Staging DB Server to the Slave DB Server

• /var/lib/mysql/mysql/proc.frm
• /var/lib/mysql/mysql/proc.MYD
• /var/lib/mysql/mysql/proc.MYI

STEP 04 : On the Slave DB Server, run FLUSH TABLES;

STEP 05 : Dump the Stored Procedures on the Master DB Server

MYSQLDUMP_OPTIONS="--routines --no-data --no-create-info --all-databases"
mysqldump -uroot -p ${MYSQLDUMP_OPTIONS} > StoredProc.sql

STEP 06 : Move StoredProc.sql from Master DB Server to /root on the Slave DB Server

STEP 07 : On the Slave DB Server, load the Stored Procedures

mysql -uroot -p -A < /root/StoredProc.sql

STEP 08 : On the Slave DB Server, run START SLAVE;

You should never encounter this issue any more.

Give it a Try !!!

UPDATE 2013-04-22 12:45 EDT

Please run this query

SELECT COUNT(1) FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

For MySQL 5.0, you should get 37. For MySQL 5.5, you should get 42.

If you get 37 on the MySQL 5.5. server, go ahead with running mysql_upgrade.

If you get 42, run my original answer to fix mysql.proc.