How to export database from Amazon RDS MySQL instance to local instance?

Question

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.

Answer 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.

Answer 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

Answer 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!

Answer 4

The best way to export data from RDS is create new EC2 instance to connect and dump mysql.

1. Create new EC2 Linux2 instance
2. Connect SSH

3. Install Docker

   • Update the installed packages and package cache on your instance.

     sudo yum update -y

   • Install the most recent Docker Community Edition package.
   • Amazon Linux 2.

     sudo amazon-linux-extras install docker

   • Amazon Linux.

     sudo yum install docker

   • Start the Docker service.

     sudo service docker start

   • Add the ec2-user to the docker group so you can execute Docker commands without using sudo.

     sudo usermod -a -G docker ec2-user

   • Log out and log back in again to pick up the new docker group permissions. You can accomplish this by closing your current SSH terminal window and reconnecting to your instance in a new one. Your new SSH session will have the appropriate docker group permissions.

   • Verify that the ec2-user can run Docker commands without sudo.

     docker info

4. Run mysql container

   docker run -it --network some-network --rm mysql mysql -h some-mysql -u example-user -p

5. Run dump sql

   mysqldump -h host -u use_name -P 3306 -p --databases db_name | gzip > db_name.gz

6. Copy file from container to host

   docker cp container_id:/home /home/ec2-user/sql_backup