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).
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 ?