Best strategies to have a backup of an RDS MySQL database

Question

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?

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

• Dec 11, 2012 : MySQL on RDS, data transfer from one instance to another as a production job
• Jul 25, 2012 : Scaling Percona datacenters: setup and replication

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.