Is there a generic framework/design pattern/library for real time replication of MySQL database onto other servers?

Posted on

Question :


I need to backup in real time updates/inserts to a MySQL database. Cron/batch jobs appear to be unsuitable for this particular requirement due to the time interval meaning that the data would only be as recent as the last cron job date/time.


I have three LAMP (Linux, Apache, MySQL, PHP/Perl) servers on three locations each issuing small amounts of data regularly requested by client applications on other machines. Each server logs the data that it has issued. Each server holds configuration data that determines the kind of data issued to the clients. Both the logs and the configuration data are to be stored in a mysql database on the server.

Considered solution – servers backup each other in real time, cron jobs not suitable it seems

I am considering backup strategies for each server. Cron job based backups at certain intervals are unsuitable, owing to the necessity for the logs to be current in the backup, containing the very last entry before the server failed. For example, a cron job say every 4 hours would have data that is 4 hours old and not data since then.

So the strategy I am considering is to use the servers to backup themselves, sending their log data to each other, each log entry identifying which server it was from, as well as date stamp. This strategy seems bespoke and specific to this application because the code and data structures are specific. So I wondered:

Is there a design pattern or library or even a framework that does real time MySQL replication?

Answered questions I have already considered on Stack Overflow are:

Answer :

Perhaps I am misreading this, but I am having difficulty understanding how your requirement is any different from built-in MySQL replication.

With MySQL replication, the master database writes changes to a binary log file. The slaves connect to the master, read the binary logs, write them to their local relay log, and play the statements back.

Please understand that realtime copy and backup are not the same thing. A real-time slave can help you recover from certain types of problems, but cannot resolve data-corruption issues (oops, I dropped that table!). For that, you need point-in-time backups. With a backup and a copy of the binary logs, you can recover from almost any type of failure by replaying the binary logs up to the point where the problem occurred.

Leave a Reply

Your email address will not be published. Required fields are marked *