Does the Amazon RDS backup/snapshot service lock tables?

Posted on

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 :

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: 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

Launch Process

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *