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.

I have an AWS RDS MySQL database. I'm not a DBA, so my knowledge is limited.

I wish to design strategies to have zero data loss in case of a failure, in terms of replication or data backup.

I know they are different terms. (And ignoring that a wrong delete statement can delete data in which case replication may not be useful).

All I wish to do is have a zero data loss when there is a database failure. AWS maintains snapshots but that may be few hours old. So there is data loss.

Should I consider setting a DB server outside AWS? Or what? Should I do replication or take regular backup?

Any other strategies that DBAs have in mind?

share|improve this question

1 Answer 1

You may not be aware of this but MySQL RDS allows you to create two things

  • Read Replicas
  • Snapshots

What is the difference between them?

  • A read replica is simply a MySQL Replication Slave that you create from the AWS console. It's just a point and click away. It maintains a continuous backup.
  • A snapshot is simply an instance copy of an AWS MySQL RDS Instance. This is more of a point-in-time copy of the data. This can serve as a physical backup of the instance.
  • CAVEAT #1 : You could combine both methods by taking snapshots of read replicas so as not to disturb the production RDS instance. You can take snapshots of a production instance but it might be a little slower than taking the snapshot of the read replica.
  • CAVEAT #2 : You don't want mysqldumps from RDS because there may be applicable charges for doing so. If you really need access to mysqldumps, switch from RDS to EC2. You just have to be more hands on managing MySQL (Should I increase max_connections in AWS RDS t1-micro for MySQL?) as well as managing the EC2 instance.

Here are some of my past posts on this subject

UPDATE 2013-05-22 16:21 EDT

Replication lag is a part of MySQL Replication because it is asynchronous. Even though you are using MySQL 5.5, you cannot take advantage of semisynchronous replication features (evidently AWS policy).

If you would like virtual synchronous replication, you may want Percona XtraDB Cluster (PXC), an all-InnoDB virtually synchronous ACID-compliant DB Cluster, since replication lag is forbidden by design (Codership's Galera libraries are embedded in the Server Binaries). You will have to stop using RDS and go with EC2.

Check out PXC's Documentation on how to setup PXC in the EC2 environment.

share|improve this answer
    
thanks for the help. However i was reading about RDS replicas. It said there can be a lag . Q: Can I use a Read Replica to enhance database write availability or protect the data on my source DB Instance against failure scenarios? With Read Replicas and MySQL’s native, asynchronous replication, database writes occur on a Read Replica after they have already occurred on the source DB Instance, and this replication “lag” can vary significantly. And incase of AWS Multi AZ it says they are slave machines deployed in different Availability Zone of Same Region. –  user23577 May 22 '13 at 8:26

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.