Consistent mysqldump of mixed InnoDB and MyISAM tables from RDS instance

Question

I have MySQL db on dedicated server. I would like it to be an external master for an RDS instance. And I want to make backups of RDS. (Currently we have mix of both InnoDB and MyISAM but we are slowly transitioning to full InnoDB).

I did flush tables with read lock on our master(on dedicated server) and took a dump. Restored that dump on a RDS instance and configured external master. The RDS replication caught up. I am trying to create second slave in RDS from the first slave we have up to date with master. This is where I run into trouble. I can not get consistent copy to run replication. I tried doing snapshot and building replication from the restored snapshot - but replication gets errors about duplicate entires and foreign constraints. If I skip some errors - replications runs for few minutes and errors again. The tables in the error are not MyISAM.

I tried doing dump. Tried locking tables. Flushing tables.

Because you can not do flush tables with read lock on RDS - I tried doing what is said in this AWS article: (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.BackingUpAndRestoringAmazonRDSInstances.html).

Because the errors are not connected to MyISAM tables I though it is not a problem, but still tried by:

use my_db;
flush tables [list of MyISAM tables];
lock each MyISAM table for read: `lock table <MyISAM table> read`

But the problem is still the same.

Database is around 100G so I dump it table by table with shell one liner.

Any ideas what and where I am doing wrong ?

Answer 1

Last month, I answered this question : Synchronizing a RDS instance with another RDS instance

From researching and answering that post, I recently learned that you can set read_only off on a MySQL RDS Slave. What can this mean for you ? A very unique opportunity. What opportunity ?

CONVERT ALL YOUR MyISAM TABLES TO InnoDB !!!

Once you have made the RDS Slave writable by setting read_only to 0, you can change the storage engine of all the tables in the RDS Slave to InnoDB. Don't worry, it is possible for a Master to have MyISAM tables and the Slave to have InnoDB. I have suggested Master and Slave having different storage engines before (Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?)

Once you have converted all that tables in the RDS Slave to InnoDB, you make read replicas of that new Slave without the MyISAM headaches of backing up data.

NOTE : Here is a sample script to convert all your MyISAM tables to InnoDB

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
CONVERT_SCRIPT=/tmp/ConvertMyISAMToInnoDB.sql
echo "SET SQL_LOG_BIN = 0;" > ${CONVERT_SCRIPT}
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${CONVERT_SCRIPT}
less ${CONVERT_SCRIPT}

GIVE IT A TRY !!!

Answer 2

Not sure if you just want to create a read replica clicks away from you?

If not then I guess you need to restore from a dbsnapshot.

You can:

• stop slave.
• note show slave status.
• create db snapshot.
• start slave on first slave.
• use dbsnapshot to create new rds instance.
• use co-ordinates to replicate from external master.

Also if not and you're looking to create slave of RDS slave then, there is an old update blog which says it's possible.

Hope this helps.