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 ?