Best way to migrate from EC2 mysql to RDS — e.g 2.5Tb in 12 hours

I had been looking for a solution where I can migrate Tbs of data from EC2 -mysql to RDS. and I came up with a way which was obvious tool “mysqldump”. I have tried taking dump and restore, and it took 20+ hours for just taking the dump and another 20+ hours to restore.

So, it’s like 40+ hours for dumps and then start replication where we need to wait for replication data of last 2 days due to high amount of time taking in dump.

and I have decide to use mydumper which was taking just 10–12 hours for the same work.

We can do the same thing either with replication — Where we need to take the dump, restore and start replication with source, and once they are with same data then stop slave and switch the URLs of db.

or

Take some downtime for write operations and take the dump-restore and switch the domain.

I’ve decided to take some downtime(10h) for write operations, and use the mydumper with some tricks and tweaks.

PREREQUISITE:

Create a domain name to point to your database ip and make the changes in your application to start using that domain. e.g mydb.something → 10.0.0.1

Create a RDS and with same mysql version and parameter group. Disable encryption, backup, multi-Az, enhanced monitoring everything.

Make the below changes in parameter group:

Set innodb-flush-log-at-trx-commit = 0

Install mysql to your local, it will help while restoring mysql users.

Install mydumper utility in source (EC2) instance:

wget http://springdale.princeton.edu/data/springdale/6/x86_64/os/Computational/pcre835-8.35-2.sdl6.x86_64.rpm

wget http://mirror.centos.org/centos/6/os/x86_64/Packages/environment-modules-3.2.10-3.el6.x86_64.rpm

rpm -i environment-modules-3.2.10–3.el6.x86_64.rpm

rpm -i pcre835–8.35–2.sdl6.x86_64.rpm

find / -name “libpcre.so.1”
export LD_LIBRARY_PATH=/lib:/usr/lib:/usr/local/lib:/lib64:/usr/local/pcre/8.35/lib64

1. Stop the write operations on Source db (ec2 mysql).

mysql> SET GLOBAL read_only = ON;

2. Take the backup in same server. backup size e.g 170g for 2.5Tb data backup.

time mydumper — user= — password= — outputdir=mydumper-files — rows=50000 — trx-consistency-only — verbose 3 -G -E -R — compress — build-empty-files — threads=32 — compress-protocol — regex ‘^(?!(sys|performance_schema|information_schema|mysql))’ -L mydumper-logs.txt &

Note: This command will take 1.4 hours to take the dump of 2.5Tb data.

3. Run the below commands

cd mydumper-files
zgrep DEFINER *schema*
find . -name “*schema*” | xargs gunzip
find . -name “*schema*” | xargs sed -i -e ‘s/DEFINER=`[A-Za-z0–9_]*`@`localhost`//g’
find . -name “*schema*” | xargs sed -i -e ‘s/SQL SECURITY DEFINER//g’
find . -name “*schema*” | xargs gzip

Note: This will help you to remove things which are not supported in RDS.

4. Start restoring dump to RDS.

time myloader — host=<rds-url> — user=admin — password=<password> — directory=mydumper-files — queries-per-transaction=1000000 — threads=42 — compress-protocol — verbose=3 -e 2> myload-output-logs.txt &

Note: It would take 8.5 hours to restore for 2.5Tb of data.

4. Take the dump of user:

pt-show-grants — host <Sourcehost>— user root — password <password>— ignore root@localhost,’mysql.session’@’localhost’,rdsadmin@localhost,’rdsrepladmin’@’%’ > grants.sql

Take the dump with above command and restore it to your local mysql.

mysql -u<username> -p<password> < grants.sql

Now, login to local mysql: You need to revoke these permission from all users “SUPER, SHUTDOWN, FILE, CREATE TABLESPACE” because only RDS admin can have these permissions and if you restore directly with this permission then there will be error while restoring.

REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM ‘user’@’host’

try removing this for all individual user or the other way is.

open grants.sql file in vim and search the above value and delete with below commands:

:g/REPLICATION CLIENT,/d

:g/SHUTDOWN,/d

:g/REPLICATION SLAVE,/d

:g/FILE,/d

:g/CREATE TABLESPACE,/d

:g/SUPER,/d

5. Restore the Users in RDS:

mysql -h<rds-host> -uadmin -p<password> < grants.sql

6. Point your domain to new RDS URL.

Note: All done within 10 hours of write downtime. This is applicable only if you have huge amount of data more than 2Tb. else you can just take the dump, restore and enable replication.