Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I've honestly tried this left and right and still find that my mirror server, set up as a replication slave still lags behind. My app's user base keeps growing and now Ive reached the point where I can't keep "shutting down" to "resync" databases (not even on weekends).

Anyways, my question: Are there any plausible, affordable, alternatives to binlog replication? I have two servers so wouldn't consider buying a third for load balancing just yet, unless its the only option.

Cheers,

/mp

like image 617
mauriciopastrana Avatar asked Nov 07 '08 16:11

mauriciopastrana


People also ask

Can I remove Binlog MySQL?

Can I Remove MySQL Binary Log Yes, as long as the data is replicated to Slave server, it's safe to remove the file. It's recommend only remove MySQL Binary Log older than 1 month. Besides, if Recovery of data is the main concern, it's recommend to archive MySQL Binary Log.

Is MySQL replication reliable?

MySQL replication is reasonably stable, and no less so than other solutions. But there are a variety of failures that can happen, without it being MySQL's fault. Binlogs can develop corrupted packets in transit due to network glitches. MySQL 5.6 introduced binlog checksums to detect this.

How does MySQL Binlog replication work?

Replication works as follows: Whenever the master's database is modified, the change is written to a file, the so-called binary log, or binlog. This is done by the client thread that executed the query that modified the database.

What is the use of Binlog in MySQL?

The binary log is a set of log files that contain information about data modifications made to a MySQL server instance. The log is enabled by starting the server with the --log-bin option. The binary log was introduced in MySQL 3.23.


2 Answers

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.

like image 75
Gary Richardson Avatar answered Sep 29 '22 04:09

Gary Richardson


Have you tried : 1) SET innodb_flush_log_at_trx_commit=0 2) SET sync_binlog=0

Both will help to speed up your Slave with a small level of added risk if you have a server failure.

like image 43
Gary Avatar answered Sep 29 '22 05:09

Gary