Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

We're an online store and backups are made daily. The problem is that if our database is lose, we lose all database changes/orders that were made on our site that day. We're tossing around the idea of setting up a database instance on Amazon and saving a copy of all customer's order information there as well so if our database went down we'd have that in place as a backup and not actually lose any customer information.

This seems like a decent solution, but are there better options available for syncing databases? One thing I don't like about this is that it requires adding extra queries to ensure the databases are synced.

The final factor is that we're moving from our own server to Amazon's cloud hosting for all of our web sites in the next few months. Is having a separate database instance worth it in the long run, would we still need it when we're hosting with Amazon?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

One thing you can do is setup replication and take regular backup. Also enable binlog on the backup sever and save the binlogs too. So suppose someone ran a drop database command on your server then you can recover using the backup and replay the binlogs till just before the drop database command.

share|improve this answer
    
Seems like a good setup, but it would still require code modification executing the queries on both servers, right? –  Webnet Dec 9 '10 at 15:21
1  
It wouldn't require any code modification. Mysql support replication. It is also a standard practise which is used for scaling mysql dev.mysql.com/doc/refman/5.0/en/replication.html –  Sameer Dec 9 '10 at 15:27
    
It says "requires server restart". Does that mean I can just restart MySQL or the whole server has to be restarted? –  Webnet Dec 9 '10 at 16:41
    
Just restart the mysql server. In Linux there are very few times when you need to restart the server. A kernel upgrade would be one them. –  Sameer Dec 9 '10 at 19:52

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.