I configured MySQL replication between a Master and a Slave throught VPN.
Everything works fine, when connection between them is down, and i make some changes to the Master (like insert a row), then when the connection comes back any operation is performed on the Slave.
This is the SLAVE and MASTER status before the connection down problem.
**SLAVE STATUS**
Slave_IO_state
Waiting for master to send event
Master_log_file
mysql-bin.000001
Read_master_log_pos
600
Connect_retry
60
Relay_master_log_file
mysql-bin.000001
Relay_log_file
mysql-relay-bin.000002
Relay_log_pos
824
Slave_IO_running
Yes
Slave_SQL_running
YES
Seconds_behind_master
0
**MASTER STATUS**
File
mysql-bin.000001
Position
600
Then while the connection down i insert a row in a master's table.
The connection comes back and what is see is
**SLAVE STATUS**
Slave_IO_state
Waiting for master to send event
Master_log_file
mysql-bin.000001
Read_master_log_pos
600
Relay_master_log_file
mysql-bin.000001
Relay_log_file
mysql-relay-bin.000002
Relay_log_pos
824
Slave_IO_running
Yes
Slave_SQL_running
YES
Seconds_behind_master
0
**MASTER STATUS**
File
mysql-bin.000001
Position
**887** <-- JUST THIS CHANGED
So i don't see any change in the Slave Table.
And after this, with the connecton up, the replication doesn't work.
To let it work I have to do in the slave:
CHANGE MASTER TO MASTER_LOG_POS=887
But with i still don't see al the changes made on the Master when the connection was down.
UPDATE
After connection down, when it comes back, to get the replication on the slave i have to restart it with:
STOP SLAVE;
START SLAVE;
I am still confused.
Best Answer
The solution was to add slave-net-timeout lover like 10 seconds in the ini conf file of the slave and put master-repl=0 that means infinite retryes. So in this case if the connection goes down the slave will notice it after 10 seconds and it will update the db looking in the log file of the master when it comes back online.