Home / server / Questions / 310520
Accepted
Arie Skliarouk
Asked: 2011-09-13 07:40:32 +0800 CST

mysql replication over long distance - many tcp/ip reconnects

  • 772

We are doing offsite mysql replication over long distance and limited bandwidth - latency of about 200ms and about 70KB/s (max).

The replication works sporadically, transferring several tens of kilobytes and then reconnecting.

I have traced the problem to bad tcp/ip behavior of the mysqld daemon on the client - when a single tcp/ip packet is lost, the whole ACK/retransmit sequence can not recover the connection and at some point server issues FIN.

It is notable that scp from the same server works properly (even with packets lost that happens occasionally).

Mysql client from the client machine to the server also works properly and occasional packet loss does not break the connection.

Both server and client have the same version of mysql server:

mysql> SHOW VARIABLES LIKE '%version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  | 
| version                 | 5.0.67-0ubuntu6-log | 
| version_comment         | (Ubuntu)            | 
| version_compile_machine | x86_64              | 
| version_compile_os      | debian-linux-gnu    | 
+-------------------------+---------------------+
5rows in set (0.21 sec)

Might this caused by packets loss that happens when TCP/IP attempts to raise TCP/IP window beyond what is allowed by the connection?

mysql replication

1 Answers

  1. Best Answer

    I ended up with using ssh tunnel (and adjusting replication settings a bit) - works perfectly:

    ssh -L 3307:localhost:3307 root@the-replication-master-server

    Added bonus is that the replication stream is encrypted.

    • 1