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