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.
Question 1
Does the DML operations committed by db2 during the replication process gets included in its own bin-log?
Answer to Question 1
Yes it will, provided you have this in /etc/my.cnf on both db1 and db2
[mysqld]
log-slave-updates
If you do not have this, add it and restart mysql
Question 2
Would the resulting bin-log in db2 be exactly the same with the bin-log of db1, to the letter?
Answer to Question 2
Yes. Make sure the clocks on both DB servers are synchronized
Question 3
What happens to the entries in db2 relay-log once they are committed to the database during the replication process, are they discarded? What role does the relay-log info log has in this?
Answer to Question 3
In MySQL Replication, the IO Thread of a Slave will read its Master's bin-log entries and store them in a FIFO queue. For each relay log in a slave, when every entry in the currently processed relay is executed it is rotated out and discarded. If relay logs are piling up, this quickly indicates that the SQL thread died because of any SQL error. Just do SHOW SLAVE STATUS\G to find out what stopped the SQL thread. The IO Thread would conitnue collecting completed SQL statements from its Master.
Question 4
How does db1 know where in the bin-log of db2 (somehow dependent on the answer of Question 2), it will start the replication process?
Answer to Question 4
When you do SHOW SLAVE STATUS\G, look for the following lines:
- Master_Log_File : The latest binary log whose most recently command was copied to the Slave
- Read_Master_Log_Pos : The latest position of the latest binary log whose most recently command was copied to the Slave
- Relay_Master_Log_File : The latest binary log whose most recently command was executed on the Slave
- Exec_Master_Log_Pos : The latest binary log whose most recently command was executed on the Slave
- Relay_Log_Space : The sum total (in bytes) of all relay logs. By default, each relay log is the default size of a binary log (1G). If Relay_Log_Space starts to significant exceed 1G, this indicates one of two things:
- SQL thread died due to SQL Error
- SQL thread is busy with a long-running query
Question 4.1
If you enable log-slave-updates on both databases i.e. dB1 & dB2, then that would mean all items from the binary log of dB1, which was successfully replicated by dB2 will be written into dB2's binary log and vice-versa. Would not this result to some sort of infinite circular replication or duplications of entries on both databases, if it's possible at all, considering the possible key-collision issues that would arise? What I'm trying to say is, How would dB1 know once it checks on the binary log of dB2 that, "I should not replicate those entries in there because they all just came from me"?
Answer to Question 4.1
You must have log-slave-updates available on both DB servers in order to have an audit trail that the SQL executed on on DB server made it to the other. If you don't, you would have to do your due diligence to compare the data explicitly. Such ways would include:
- Running CHECKSUM TABLE on every table you have in both DB servers to compare their contents.
- Using pt-table-checksum, which is an automated version of running CHECKSUM TABLE between Master and one or more Slaves
You need not worry about infinite circular replication unless you are dealing with more that two masters. There have been rare times when someone with, let's say four Masters, removes one of the four servers from circular rep cluster. Let's suppose the the server_id is 13. It is remotely, but still, possible for binary log entries whose server_id belongs to the server that removed to be inside the relay logs on other servers. Only in such a scenario would you worry about infinite circular replication.
To circumvent such situations, MySQL 5.5 has a new option for the CHANGE MASTER TO command called IGNORE_SERVER_IDS. You would do the following to repair things on all the remaining servers:
STOP SLAVE;
CHANGE MASTER TO IGNORE_SERVER_IDS = (13);
START SLAVE;
In fact, here is what the MySQL Documentation says on this:
IGNORE_SERVER_IDS was added in MySQL 5.5. This option takes a comma-separated list of 0 or more server IDs. Events originating from
the corresponding servers are ignored, with the exception of log
rotation and deletion events, which are still recorded in the relay
log.
In circular replication, the originating server normally acts as the terminator of its own events, so that they are not applied more
than once. Thus, this option is useful in circular replication when
one of the servers in the circle is removed. Suppose that you have a
circular replication setup with 4 servers, having server IDs 1, 2, 3,
and 4, and server 3 fails. When bridging the gap by starting
replication from server 2 to server 4, you can include
IGNORE_SERVER_IDS = (3) in the CHANGE MASTER TO statement that you
issue on server 4 to tell it to use server 2 as its master instead of
server 3. Doing so causes it to ignore and not to propagate any
statements that originated with the server that is no longer in use.
Question 5
On INSERT queries on the master, what form of the query is written into the binary log? Is it the 'raw' form of the query, or the one which already has the auto-generated value of the auto-increment key?
Answer to Question 5
Whichever form is presented. Here is what I mean: The raw form would usually not include the auto_increment column expressed explicitly. On the other hand, it you import a mysqldump into a DB server with binary logging, the rows being inserted would explicitly be given. Either version of INSERT would be allowed execution in mysqld. In like fashion, either version of INSERT would be recorded AS IS...
Best Answer
Conflict-free replication isn't supported in multi-source replication...
You should look at cluster multi-master setup, but, I don't know a multi-master cluster with lazy updates (async-replication).