How to mysqldump From RDS

Using meta as the example, we have an EC2 web server that connects to an RDS MySQL instance.  In order to get a dump from RDS, one easy way is to use local port forwarding.

1. Open an ssh connection to the web server.  Use the -L flag to create a local port forward connected to the database port on the RDS server.  In this example, I already have an “rdata” shortcut in my .ssh/config that takes care of the ec2 hostname, user, and ssh key.

   ssh rdata -L 1234:reflexions-meta-production.cyunnom7vlkw.us-east-1.rds.amazonaws.com:3306

2. In a separate (local) tab, do the mysqldump using the local port you just set up.

   mysqldump -h localhost -P 1234 --single-transaction -umeta -p meta | pv | gzip > meta-$(date +"%Y-%m-%d").sql.gz

   (You can also add –default-character-set=utf8mb4 if the server is using the mysql latin1 default.)

That’s it!  Thanks to Greg for the solution!