Amazon RDS - Migrating MySql Databases with User Credentials from EC2 to RDS

Question

I have LAMP setup on EC2 instance which hosts a web app. When a new company signs up on the web app, it creates a new mysql database for that company, a new mysql user and assigns the user to the database so that each company has its own separate database.

We are now planning to move to RDS and I need to migrate all the company databases with corresponding mysql user accounts from EC2 to RDS. How can I achieve this? Is this even possible? I know I can migrate the databases via mysqldump but how can I transfer mysql users for the databases?

You can create new MySQL users on RDS just like you can with any other MySQL server. Try looking at the answers to this question: serverfault.com/questions/8860/… — Mark B
The link you posted seems helpful. I will try it and update the answer here. — Haris ur Rehman
Here is the documentation on AWS best practice for migrated an external Mysql instance into RDS. docs.aws.amazon.com/AmazonRDS/latest/UserGuide/… — strongjz

turagittech turagittech · Accepted Answer · 2017-05-15T07:14:42

I found this approach to migrate users from ec2 or other instance to RDS is probably the easiest to move the user accounts. Then use pt-show-grants to obtain the grants from users. Update the where clause as appropriate to exclude anything you don't want to migrate such as the root user which likely already exists in your AWS instance.

SELECT CONCAT('CREATE USER ', '`',u.User,'`', '@','`', u.Host,'`', ' IDENTIFIED BY PASSWORD ','''', u.Password,'''',';' ) FROM mysql.user u WHERE u.is_role = 'N' and u.plugin <> 'unix_socket' and user <> 'root';

Using mysqldump as follows will extract all your table SQL and your data in delimited format to allow parallel database importing The format used in this allows for each database to be dumped to its own folder with a SQL file and a txt file for each table. This is being run from a slave on an EC2 instance as per recommendations from Amazon https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html. We differed in our approach to getting the slave to AWS by using Percona Xtrabackup to migrate a new copy to set up the slave

mysqldump     --tab=/data/mysql/dump/rds/trash    --fields-terminated-by '|-|'     --fields-enclosed-by '"'     --lines-terminated-by 0x0d0a     trash     --master-data=2     --single-transaction     --order-by-primary --routines=0 --triggers=0 --events=0

Amazon RDS - Migrating MySql Databases with User Credentials from EC2 to RDS

1 Answers