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.
You need a log-bin entry in your master my.cnf file
Per http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
To enable the binary log, start the server with the --log-bin[=base_name] option. If no base_name value is given, the default name is the value of the pid-file option (which by default is the name of host machine) followed by -bin. If the basename is given, the server writes the file in the data directory unless the basename is given with a leading absolute path name to specify a different directory.
After setting the log-bin and restarting the master, you then can run RESET MASTER; and then SHOW MASTER STATUS; to get the correct values for your CHANGE MASTER command for your slave.
Best Answer
I think you what you are describing is a replication topology called a Star Topology
I wrote about that before
Apr 18, 2013
: Single slave - multiple master MySQL replication (Approach #1)Apr 28, 2011
: MySQL in star topology (ServerFault)Apr 26, 2011
: MySQL in star topology (Stack Overflow)In that event, the Central Server must serve as a Slave in MySQL Replication paradigm until all changes in the binary logs of the Master (outside server) are pulled and executed in the Central Server.
If you pull the plug, replication breaks. It will lose its place.
MySQL does have a setting to control this in the CHANGE MASTER TO command. By default, the retry interval for MySQL 5.6 is 86400 seconds. That's 24 hours. Before MySQL 5.6, it was 60 seconds. To change the retry interval simply run,
CAVEAT
Even if you pull the plug, it is possible for replication to lose its place and not reconnect because of incomplete transmission of binlog events. When that happens, you must reset the replication coordinates manually.
Here is how to fix replication manually in this regard:
Step 1:
STOP SLAVE;
Step 2:
SHOW SLAVE STATUS\G
Suppose the display looks like this
Step 4: Record Relay_Master_Log_File and Exec_Master_Log_Pos
In this case,
'mysql-bin.000254'
and858190247
Step 5: Use Replication Coordinates from Step 4
Step 6: Start Replication
Give it a Try !!!