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

mysql replication step by step in linux (6)

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.

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).

Adding memory to the slave would probably help. We went from 32 to 128 megs and the lagging more or less went away. But its neither cheap nor will it be enough in all situations.

Buying a third server will probably not help that much though, you will most likely just get another lagging slave.

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.

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.

Binlog MySQL Replication is a “Bag of Hurt”. Are there any good alternatives?

Your master executes in parallel and your slave executes in serial. If your master can process 1.5 hours of inserts/updates/executes in 1 real hour, your slave will fall behind.

If you can't find ways to improve the write performance on your slave (more memory, faster disks, remove unnecessary indexes), you've hit a limitation in your applications architecture. Eventually you will hit a point that you can't execute the changes in real time as fast as your master can execute them in parallel.

A lot of big sites shard their databases: consider splitting your master+slave into multiple master+slave clusters. Then split your customer base across these clusters. When a slave starts falling behind, it's time to add another cluster.

It's not cheap, but unless you can find a way to make binlog replication execute statements in parallel you probably won't find a better way of doing it.

Update (2017): MySQL now support parallel slave worker threads. There are still many variables that will cause a slave to fall behind, but slaves no longer need to write in serial order. Choosing to preserve the commit order of parallel slave threads is an important option to look at if the exact state of the slave at any point in time is critical.

MySQL replication for fallback scenario

Binary log is definitely the way to go. However, you should be aware that with MySQL you can't just flip back and forth between servers like that.

One server will be the master and the other will be the slave. You write/read to the master, but can only read from the slave server. If you ever write to the slave, they'll be out of sync and there's no easy way to get them to sync up again (basically, you have to swap them so the master is the new slave, but this is a tedious manual process).

If you need true hot-swappable backup databases you might have to go to a system other than MySQL. If all you want is a read-only live backup that you can use instantly in the worst-case scenario (master is permanently destroyed), Binary Log will suit you just fine.