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:
-
https://stackoverflow.com/questions/7182791/daemon-software-to-update-mysql-database-in-background (similar question, but their domain – bidding needs a different solution from mine, answers here appear to be based around bidding amount, my domain isn't so much about content of the database entries, just that the latest needs to be backed up)
-
https://stackoverflow.com/questions/6471879/is-there-a-way-to-listen-for-a-database-event-and-update-a-page-in-real-time (more about keeping the UI up to date to reflect the database (nice question, will use it sometime) but mine is about backup)
-
https://stackoverflow.com/questions/5221164/need-advice-on-developing-real-time-system-using-php-mysql (nice broad question that would cover my question but theirs is again a slightly different domain concerned with technologies I am not using: OpenID, JQuery, PHP frameworks such as CodeIgnitor)
-
https://stackoverflow.com/questions/4200515/how-do-i-do-realtime-database-polling-in-mysql-php (for my problem, probably don't want to poll for changes as this has similar limitations to cron in that data is only as up to date as last poll)
-
Also looked at MySQL documentation, but could not find anything for real time replication, only batch based solutions. (Willing to stand corrected though if others can suggest to me otherwise)
Best 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.