Does the Amazon RDS backup/snapshot service lock tables?

Question

We have a database that is 100+GB. Most tables use the InnoDB engine.

A regular dump takes 1-2 hours.

If we moved to Amazon RDS, would this backup cause table locks?

Does Amazon use a simple mysqldump?

Answer 1

If there are any MyISAM tables present, as your wording implies, the RDS Documentation instructs you on how to handle it and what to expect

According to the RDS Documentation :

Automated Backups with Unsupported Storage Engines

Amazon RDS automated backups and DB Snapshots are currently supported for only the InnoDB storage engine. Use of these features with other MySQL storage engines, including MyISAM, may lead to unreliable behavior while restoring from backups. Specifically, since storage engines like MyISAM do not support reliable crash recovery, your tables can be corrupted in the event of a crash. For this reason, we encourage you to use the InnoDB storage engine.

If you choose to use MyISAM, you can attempt to manually repair tables that become damaged after a crash by using the REPAIR command ((see: http://dev.mysql.com/doc/refman/5.5/en/repair-table.html). However, as noted in the MySQL documentation, there is a good chance that you will not be able to recover all your data.

If you want to take DB snapshots with MyISAM tables, follow these steps:

Launch Process

1 Stop all activity to your MyISAM tables (that is, close all sessions)

2 Lock and flush each of your MyISAM tables

3 Issue a CreateDBSnapshot API call, or use the RDSCLI rds-create-db-snapshot command. When the snapshot has completed, release the locks and resume activity on the MyISAM tables. These steps force MyISAM to flush data stored in memory to disk thereby ensuring a clean start when you restore from a DB snapshot.

Finally, if you would like to convert existing MyISAM tables to InnoDB tables, you can use alter table command (for example, alter table TABLE_NAME engine=innodb;).

Believe me, large MyISAM tables have no place in Amazon RDS. InnoDB is far more accepted. Please, either convert them to InnoDB or live with doing your own locking and possible table crashes/repairs.