AWS RDS migration

0
votes

The current setup is the following one:

  • MySQL RDS Master InstanceDB with some MyISAM tables in some of its DBs (in default VPC)
  • MySQL RDS Read Replica (in default VPC)
  • New custom VPC with private subnets created and ready, so that I can perform the migration to this new custom VPC

1) Stop the traffic towards the RDS DB from the web/app servers

2) Create a manual snapshot of the RDS InstanceDB following this procedure: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomatedBackups.html#Overview.BackupDeviceRestrictions If you want to take a snapshot of your MyISAM tables prior to restoring, follow these steps:

2.1) 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 the SHOW FULL PROCESSLIST command.

What about the processes in charge if the replication to the ReadReplica? enter image description here

2.2) Lock and flush each of your MyISAM tables. For example, the following commands lock and flush two tables named myisam_table1 and myisam_table2:

mysql> FLUSH TABLES myisam_table, myisam_table2 WITH READ LOCK;

2.3) Take a manual snapshot

2.4) When the snapshot has completed, release the locks and resume activity on the MyISAM tables. You can release the locks on your tables using the following command:

mysql> UNLOCK 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.

3) Restore from the snapshot and create a new DBInstance in the new custom VPC

4) Alter (user databases) tables to change to InnoDB

ALTER TABLE table_name ENGINE=InnoDB;

5) Modify the web/app code (new servers in the custom VPC) to point to the new RDS DBInstance

Any thoughts? Has someone performed a similar procedure? Should I stop the ReadReplica beforehand?

Thanks.

1
mysqlamazon-web-servicesamazon-rdsamazon-vpc
Have you tried simply creating a new RDS replica of this instance in the new VPC, and then promoting the new replica to be a standalone master? RDS replicas do not need any connectivity to each other via your networking -- they have their own -- so I'm not sure why this wouldn't work. – Michael - sqlbot
Good option! I´ll test it – Luis
not possible... when you select the subnetGroup of the new custom VPC when creating the ReadReplica, it gives an error like this: "The DB instance and EC2 security group are in different VPCs. The DB instance is in vpc-xxxx and the EC2 security group is in vpc-yyyy (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterCombination;" – Luis

1 Answers

0
votes

In case someone faced the same issue, I followed the procedure above and it worked perfectly. Just be careful if you assign a Parameter Group to your new RDS and you want to make the change to InnoDB. Make sure the Parameter Group allows that.