Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

share|improve this question

migrated from stackoverflow.com Jan 24 '12 at 11:39

This question came from our site for professional and enthusiast programmers.

1  
+1 for a very well formatted, presented and researched question. –  JamWaffles Jan 23 '12 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 '12 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 '12 at 11:55
    
I think Replication only is solution left for you. Replicating only particular database which will sole this –  Mahesh Patil Jan 23 '12 at 12:22
1  
Migrating this to our DBA site - it has a better chance of finding the right experts for this particular topic :) –  Nick Craver Jan 24 '12 at 11:38

1 Answer 1

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.

share|improve this answer
    
+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. –  therobyouknow Jan 24 '12 at 17:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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