How to import MYSQL Dump into Amazon RDS

Question

I just logged into my EC2 via Putty on Windows and I got into my RDS instance and into one database I had created. Then I try to import a SQL dump from my machine with the following code which results into an error.

    mysql> source C:\Users\guru\Downloads\latest.sql;
    ERROR:
    Unknown command '\U'.
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'source C:\Users' at line 1
    ERROR:
    Unknown command '\D'.
    ERROR:
    Unknown command '\l'.
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uru\Downloads\latest.sql' at line 1

The file exists in my Windows machine. Please help me resolve the error.

Answer 1

As datasage mentioned, you would probably be better off working from your EC2 -> RDS instances. If you really want to work from your local machine, though:

• Install MySQL for Windows: http://dev.mysql.com/downloads/mysql/

• Add the parent directory of mysql.exe (the mysql command line tool) to your path environment variable.

Try the following in the command prompt. You could use powershell also, but you would need to wrap this statement in cmd /c "mysql etc..." because powershell handles redirection a little differently.

mysql -u myUser --password=myPass -h rdsEndpoint myDB < C:\Users\guru\Downloads\latest.sql

This procedure would also be useful to know if you have need for a Windows EC2 instance.

As an aside: extracting data from RDS to your local machine can get pricey, especially as your database grows. If you're just doing this as a backup solution, then you may want to look into Snapshots or automated backups. If you are doing this to replicate your RDS environment you could also transfer your data directly from RDS to your EC2 instances, which is free if both instances are in the same availability zone.

Answer 2

I found a very simple and user friendly solution: Downloaded SQLyog https://www.webyog.com and fed in my ssh details for my ec2 and my MYSQL log in details. I ended up accessing a my database using this GUI tool that has all the options for importing and exporting data as easy as on localhost/phpmyadmin