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
Master_Host: 10.48.20.253
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000262
Read_Master_Log_Pos: 803779735
Relay_Log_File: relay-bin.067799
Relay_Log_Pos: 402744
Relay_Master_Log_File: mysql-bin.000262
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: 803779735
Relay_Log_Space: 402598
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
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
Select the Relay_Master_Log_File and use the value : mysql-bin.000262
Select the Exec_Master_Log_Pos and use the value : 803779735
Once you have those values, then run these commands using those values:
STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000262',master_log_pos=803779735;
START SLAVE;
The 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.
Using replicate-wild-do-table=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:
The replicate-* options are restrictive by their presence. The *-do-* options seem to be telling the server what to "do," but in fact they are telling the server what to only do.
The complete absence of any replicate-* configuration variables means "replicate everything."
In the simplest case, when there are no --replicate-* options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html
That, I think, is the point you are needing. It holds true for all MySQL 5.x.
Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ..., and then START SLAVE, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.
If you started out with identical data sets, everything should behave exactly as you would expect it to.
What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions. Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."
When you use the --replicate-* options, the document cited above also offers this tip:
it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options
You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.
Best Answer
I addressed something like back on June 14, 2012 : Configured MySQL replication but its not working
The basic problem may be the queries you are using
If you have a query that looks like this
You could be potentially blocking queries of this nature
Queries like this:
should work if you have
replicate-do-db=db1andReplicate-wild-do-table=db.%To be sure, check out the replication filtering rules to make sure your queries are allowing your current settings to work on the Slave.
According to the replication filtering rules
Perhaps you need to get rid of one of those rules on the Slave and restart mysql on the Slave.