MySQL replication to slave having VPN connection to Master

Question

I am setting up an environment in which the Master and Slave are on different networks at different physical locations (both run on MySQL 5.5.20). I have set up a VPN connection from slave's network to Master's network to accomplish replication.

But I observed over a few days that sometimes the VPN connection is lost. I was wondering, if the slave loses connection to the master, will it result in lost data for replication?

And when the connection is re-established, will replication start from the point it stopped or from the point where the Master's pointer is at that moment ?

Answer 1

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:

• https://dba.stackexchange.com/a/1409/877
• https://dba.stackexchange.com/a/2734/877
• https://dba.stackexchange.com/a/12352/877
• https://serverfault.com/questions/257394/create-a-mysql-slave-from-another-slave-but-point-it-at-the-master/257426#257426

I hope they help.

Answer 2

--master-connect-retry=seconds Default value is 60

The number of seconds that the slave thread sleeps before trying to reconnect to the master in case the master goes down or the connection is lost. The value in the master.info file takes precedence if it can be read. If not set, the default is 60. NOTE: These retries are not invoked until the slave reaches the --slave-net-timeout.

You should increase this value to higher than default as you are using over VPN and there will be connection lost frequently.

make these changes in Slave server(s):

stop slave;
change master to .. 
(
<Give master info here>
....
master_connect_retry=300;
...
)
start slave;

• Also, as long as only if there is only network problem your replication set up will work smoothly once slave reconnects and reads binlog file of master server.

You can check status of slave by:

Show slave status;

If this slave I/O shows "Waiting for master to send event" that means slave is able to connect to master and get data. I hope this helps to you :)