CODEWITHSUNDEEP
mysqlamazon-web-servicesamazon-ec2rds
Luis
Luis

Reputation: 625

mysqldump from MySQL 5.7 in EC2 to AWS RDS

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?

Upvotes: 1

Views: 1061

Answers (2)

Madhukar Mohanraju
Madhukar Mohanraju

Reputation: 2863

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

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80639

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

Upvotes: 1

Related Questions