Let's start with a sample display of
SHOW SLAVE STATUS\G
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
1 row in set (0.00 sec)
As long as
Slave_IO_Running is Yes, MySQL Replication has the needed connectivity. According to your question, when MySQL loses connectivity and reconnects, will it lose data? In a perfect world, I would say no as long as the relay log (
Relay_Log_File) is written in a clean format. Once in a long while, when the network intermittency is significantly long, the relay log may get corrupted.
There is one sure fire way to correct this (please follow along):
Take note of these two values from the
SHOW SLAVE STATUS\G
- Relay_Master_Log_File : This represents the binary log on the Master whose entry was last executed on the Slave
- Exec_Master_Log_Pos : This represents the position of binary log on the Master whose entry was last executed on the Slave
To properly connect to a safe place on Master, do the following
Relay_Master_Log_File and use the value : mysql-bin.000262
Exec_Master_Log_Pos and use the value : 803779735
Once you have those values, then run these commands using those values:
CHANGE MASTER TO master_log_file='mysql-bin.000262',master_log_pos=803779735;
CHANGE MASTER TO command will erase all relay logs on the Slave and start with a fresh relay log.
This is most likely the best way because you can only be sure about the last binary log entry from the Master that successfully reached the Slave and was executed on the Slave. Any entries from the Master's binary log that was written to a current relay log but was not closed properly or correctly written due to an intermittency problem with the IO Thread could result in a malformed relay log.
The only way this method would not work is if the binary log on the Master is itself corrupt. I have seen this happen when a DB Server crashed leaving the last binary log not closed properly. Running mysqlbinlog against the Master's last binary log had shown a difference between the filesize and last legal position. Trying to reestablish replication from a Master's corrupt binary log is a headache. You must then fanagle replication to work by running
RESET MASTER on the Master, connecting replication to the first binary log on the Master, and perform due diligence of syncing the Slave using pt-table-checksum and pt-table-sync.
UPDATE 2012-02-17 08:13 EDT
@vinny : If you are thinking of creating an additional Slave, here are other posts I have made in the past on this and similar subjects:
I hope they help.