How to export database from Amazon RDS MySQL instance to local instance?
Solution 1
Sure.
Take the dump from the remote RDS Server:
mysqldump -h rds.host.name -u remote_user_name -p remote_db > dump.sql
When prompted for password, provide the password for user=remote_user_name (remote server)
Upload it to your local mySql instance:
mysql -u local_user_name -p local_db < dump.sql
Also, if you own an ec2
server in the same region, I'd suggest take a dump there. zip the file and then scp
it to your local machine. Typically, the compressed version of the file would be much smaller and you'd be able to transfer it quicker.
Solution 2
To export db from RDS
mysqldump -h rds.host.name -u remote_user_name -p remote_db > remote_db.sql
When prompted for password, provide the password
To import db on RDS
mysql -h rds.host.name -u remote_user_name -p remote_db < remote_db.sql
When prompted for password, provide the password
Solution 3
Another very easy option is by using the MySql Workbench. In the toolbar select 'Database' and 'Data export'. Select the right options, the target file ... and you're done! Easy does it!
Related videos on Youtube
Srini K
Updated on February 03, 2022Comments
-
Srini K over 2 years
AWS documentation has this page that talks about importing and exporting data from MySQL server, but it's mostly about import. The only thing I see in their documentation is a way to export 5.6 data using replication, which is documented here. I was wondering if there is a simpler way to export data using mysqldump and load in local database. The database that I want to export is not huge, may be 1GB, so size is not a issue.
-
Michael - sqlbot about 9 yearsOr, add the
--compress
option tomysqldump
for faster transfer from a remote server. The resulting output files isn't compressed, but over the wire you can see a significant bandwidth savings and faster transfer. -
nafg almost 9 yearsBut you may have to add some more flags to compensate, like -K so you don't get foreign key issues on import
-
homebrand about 7 yearsIf you can't connect, you may need to add your IP to the security groups. It's in EC2 dashboard > Network and Security > Security Groups
-
JohnW about 3 yearsUnfortunately this can be incredibly slow with even moderately-sized data sets.
-
TJ- over 2 years