3

Summary

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.

Background

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:

8
  • 1
    +1 for a very well formatted, presented and researched question.
    – JamWaffles
    Jan 23, 2012 at 11:20
  • +1 Thank you @JamWaffles ! and I will continue to research as well as hoping to receive some answers here.
    – therobyouknow
    Jan 23, 2012 at 11:23
  • 2
    Have you considered [Replication][1] or [Clustering][2]? [1]: dev.mysql.com/doc/refman/5.0/en/replication.html [2]: dev.mysql.com/doc/refman/5.0/en/mysql-cluster.html
    – perissf
    Jan 23, 2012 at 11:55
  • I think Replication only is solution left for you. Replicating only particular database which will sole this Jan 23, 2012 at 12:22
  • 1
    Migrating this to our DBA site - it has a better chance of finding the right experts for this particular topic :) Jan 24, 2012 at 11:38

1 Answer 1

2

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.

1
  • +1 @Aaron Brown thanks, I would agree. I am in the process of researching further into the detail... I will try to report back here when I have more info. Jan 24, 2012 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.