Full complete MySQL database replication? Ideas? What do people do?

Question

Currently I have two Linux servers running MySQL, one sitting on a rack right next to me under a 10 Mbit/s upload pipe (main server) and another some couple of miles away on a 3 Mbit/s upload pipe (mirror).

I want to be able to replicate data on both servers continuously, but have run into several roadblocks. One of them being, under MySQL master/slave configurations, every now and then, some statements drop (!), meaning; some people logging on to the mirror URL don't see data that I know is on the main server and vice versa. Let's say this happens on a meaningful block of data once every month, so I can live with it and assume it's a "lost packet" issue (i.e., god knows, but we'll compensate).

The other most important (and annoying) recurring issue is that, when for some reason we do a major upload or update (or reboot) on one end and have to sever the link, then LOAD DATA FROM MASTER doesn't work and I have to manually dump on one end and upload on the other, quite a task nowadays moving some .5 TB worth of data.

Is there software for this? I know MySQL (the "corporation") offers this as a VERY expensive service (full database replication). What do people out there do? The way it's structured, we run an automatic failover where if one server is not up, then the main URL just resolves to the other server.

Value

Maybe this could be inspiration: http://www.howtoforge.com/loadbalanced_mysql_cluster_debian – Orazio

Answer 1

We at Percona offer free tools to detect discrepancies between master and server, and to get them back in sync by re-applying minimal changes.

• pt-table-checksum
• pt-table-sync

Wyeth

Answer 2

GoldenGate is a very good solution, but probably as expensive as the MySQL replicator.

It basically tails the journal, and applies changes based on what's committed. They support bi-directional replication (a hard task), and replication between heterogenous systems.

Since they work by processing the journal file, they can do large-scale distributed replication without affecting performance on the source machine(s).

Poff

Answer 3

I have never seen dropped statements but there is a bug where network problems could cause relay log corruption. Make sure you dont run mysql without this fix.

Documented in the 5.0.56, 5.1.24, and 6.0.5 changelogs as follows:

   Network timeouts between the master and the slave could result
   in corruption of the relay log.

http://bugs.mysql.com/bug.php?id=26489

Rote