Restoring backup to AWS RDS and setting replication from a specific master_log_pos

[revh]

I have a 300GB MySQL DB that’s running in a data center. I am planning to migrate to AWS RDS MySQL, and was wondering what the best way to sync that data over was, in order to avoid a 2-3 hour backup window on the night of go-live.

so what i’m thinking of was:
#1 Take a backup of the DB
#2 Restore that backup on to AWS RDS MySQL
#3 Setup replication between the local DB and RDS to start from a specific master_log_pos value

Is this scenario something that XtraBackup can help with (assuming it’s even doable) ? and if so, how do I do it ?

Thanks in advance.

[scott.nemes]

Hi revh;

That’s the basic idea, though you’ll probably have to use mysqldump vs. xtrabackup. There are a lot of good guides out there on this (it’s a fairly common activity), so I would start with the below and then do some extra Googling if you need some additional information:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html

https://www.percona.com/blog/2014/07/28/what-i-learned-while-migrating-a-customer-mysql-installation-to-amazon-rds/

https://engineering.gosquared.com/migrating-mysql-to-amazon-rds

-Scott

[revh]

awesome, thanks for the reply Scott. I’ll read up on the links you sent.

[revh]

After reading the above 3 links, it looks like the 3rd link (https://engineering.gosquared.com/mi…-to-amazon-rds) is the only way to sync the data over without taking at least one multi-hour downtime …or am I reading that wrong ?

[scott.nemes]

Hi revh;

The first link from Amazon is the most in-depth, and should allow minimal downtime. As long as the new DB you setup in RDS is a slave of your current live database, then downtime should be minimal, as you just re-point your application to RDS when you are ready to make the switch. So the only downtime would be however long it takes to do the switch (usually means either stopping your app, or setting the live database to read only, then doing the switch, so you do not lose any data). I should say this process is not specific to RDS; this is what you would do when switching to any other database (local or in the cloud).

-Scott