mysqldump from MySQL 5.7 in EC2 to AWS RDS

1
votes

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?

2
mysqlamazon-web-servicesamazon-ec2rds
I believe (so not an answer) that if you just use -p without a password it will ask you twice. First will be for the local DB, second will be for the remote. There's also a config file that you can use to specify host-specific users/passwords (again, I believe that to be the case, so not an answer). – kdgregory
Thanks, but that didn´t work. – Luis
Another "solution" is to just ignore the warning. warning != error. The only thing insecure about the password on the command line is that the password is on the command line, thus in the shell history, like everything on every command line. The alternative is not especially more secure, since the password is still stored locally. – Michael - sqlbot
You are right. I realized the mysqldump command actually completed ok and the RDS database was created even after getting that warning. – Luis

2 Answers

0
votes
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
1
votes

Your piped mysql client can not get access to stdin, as its stdin is actually the stdout stream of the mysqldump command. Since you do not want the passwords to be provided in command-line, try the following method.

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

This will create 2 sections in your config file, named local and remote (you can change names). Now:

mysqldump --login-path=local | mysql --login-path=remote

should work