MySQL ring replication behind NAT

Question

I am considering switching from Firebird where I'm using a hand-built replication to MySQL and its integrated replication solution. I have 4 departments with sporadic network connections. Each one has to have its copy of the database, and has to be able to update database, so I decided that using ring replication method (A->B, B->C, C->D, D->A) would be the best (correct me if I'm wrong!).

However, while 3 of the departments have a public connection to the outside world, one is behind NAT and I do not have any chance to forward ports - so basically, I can only connect to outside world, but cannot accept incoming connections. Is there any way to set up ring type replication with this limitation?

Answer 1

You can use a reverse tunnel to get access to the host behind the NAT. If the host behind the NAT is host D and host C is accessible from the internet with a name of hostc.com for example, the reverse tunnel command would be:

ssh -nNT -o TCPKeepAlive=yes -R <remote port>:localhost:<local port> [email protected]

<remote port> can be any unprivileged port. If your set <remote port> to 8022, and <local port> to 22, then doing the command:

ssh localhost -p 8022

on host C would allow you to connect to port 22 (ssh) on host D.

In the same way you can tunnel any local port (web, mysql) to any remote port. The tricky part is keeping the connection up (-o TCPKeepAlive=yes should help there) and reconnecting automatically, some bash script that pings periodically could work or a cron job that kills the ssh process and fires it up again.

As for the efficiency of a ring style replication setup, I wouldn't have the slightest idea :(