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?
Best Answer
You may not be aware of this but MySQL RDS allows you to create two things
What is the difference between them?
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 jobJul 25, 2012
: Scaling Percona datacenters: setup and replicationUPDATE 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.