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.

So, for various good reasons, we're maintaining a mySQL database using a MyISAM engine on Amazon RDS. I've just taken over this application, and I'm concerned we're not getting good backups, since snapshots aren't reliable.

I've had an idea. I propose to

  • periodically create a read replica
  • elevate it to be a separate instance
  • and do a mysqldump from that replica.

Does this make sense?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

MyISAM tables are not guaranteed to be consistent with disk and, effectively, Amazon recommends to manually do the equivalent to a FLUSH TABLES WITH READ LOCK (you cannot do that in RDS, so you have to lock and flush each MyISAM table individually). So you are right in the fact that backups may not be doing correctly on your setup.

Creating a backup from a slave is a very common way of doing backups, because in your case, you do not care about blocking or affecting the performance of the slaves. But you are not really solving the problem, because MyISAM is not supported for read replicas, as it basically uses the same snapshoting mechanism, potentially inconsistent with non transactional databases.

If you really need hot & consistent backups, you need InnoDB. Otherwise, lock, flush & snapshot.

If you want to guarantee the consistency of a backup, though, setting a replica and seeing that it works is one of the typical easy ways to partially do it. So creating a replica and leaving it on for a while without replication breaking may mean that at least it has all the records and tables you used in your latest transactions (with no guarantee overall).

share|improve this answer
    
Well, these folks haven't had a useful backup since (at least) June so anything is better than nothing at all. –  Charlie Martin Nov 20 '13 at 21:28
    
Yes, definitely: backups partially guaranteed is better than no backups at all. –  jynus Nov 21 '13 at 10:25

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.