mysqldump from MySQL 5.7 in EC2 to AWS RDS

Question

I am trying to migrate a database from a MySQL 5.7 installed in an EC2 Instance to AWS RDS MySQL 5.7 using this procedure: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.SmallExisting.html

mysqldump -u <local_user> \
--databases <database_name> \
--single-transaction \
--compress \
--order-by-primary  \
-p<local_password> | mysql -u <RDS_user> \
    --port=<port_number> \
    --host=<host_name> \
    -p<RDS_password>

When running the mysqldump command I get the following known warning: "Using a password on the command line interface can be insecure".

I tried to following workaround, but in my case the EC2 DB and the AWS RDS have different users/pswds.

mysql_config_editor set --login-path=local --host=localhost --user=username --password

Suppress warning messages using mysql from within Terminal, but password written in bash script

Any ideas on how to use two different users and passwords to run mysqldump from the command line?

Answer 1

mysql_config_editor set --login-path=local --host=localhost --user=username --password

This will connect to the local mysql engine running on the server where you are logged in.In your case, you must have logged into the ec2 instance.

Now to connect to RDS instance(with different user/password) you can use the below command. You must have port 3306 opened on RDS sec-group with ec2 sec-group as the source.

mysql_config_editor set --login-path=myrdsinstance --host=myrdsinstance.123456789012.us-east-1.rds.amazonaws.com --user=username --password

If you want to create multiple login-path's to the same instance based on user-id, then all you have to do is change the login-path id's accordingly.

For example:

mysql_config_editor set --login-path=myrdsinstance-user1                    
   --host=myrdsinstance.123456789012.us-east-1.rds.amazonaws.com --user=user1 --password
mysql_config_editor set --login-path=myrdsinstance-user2
    --host=myrdsinstance.123456789012.us-east-1.rds.amazonaws.com --user=user2 --password