Before migrating a MySQL database to AWS Relational Database Service, be sure to understand the impact of that move if any tables in your MySQL database utilize the MyISAM engine. AWS repeats this warning in many places:
Amazon RDS automated backups and DB snapshots are currently supported for all DB engines. For the MySQL DB engine, only the InnoDB storage engine is supported; 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.
Use this query to list the MyISAM tables in your database. As the root user, run the following query:
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'MyISAM'; |
ALTER TABLE table_name ENGINE=innodb, ALGORITHM=COPY; |
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.
AWS outlines the following process to endeavour to get good snapshots, if your MySQL RDS DB has MyISAM tables.
Stop all activity to your MyISAM tables (that is, close all sessions). You can close all sessions by calling the mysql.rds_kill command for each process that is returned from a MySQL SHOW FULL PROCESSLIST command.
The lock-and-snap.rb script in https://github.com/CU-CommunityApps/docker-mysql-util attempts to follow this procedure to lock-and-flush MyISAM tables before creating and RDS snapshot.
We are in the process of understanding this process in greater detail. Be aware that your database may have MyISAM tables in the internal schemas MySQL uses for operations. Initial trials suggest that flushing-and-locking those tables along with your own tables will block the snapshot from beginning (or at least completing). Snapshot creation times seem nominal if only your own MyISAM tables are flushed-and-locked, leaving out the internal MyISAM tables. Read Replicas
|