6

I'm investigating running regular MySQL dumps to an S3 bucket as part of a disaster recovery strategy (partly spurred by the current zero cost of inbound data transfer!). In the event of a disaster, I would want to restore the dump file from S3 to a new RDS instance. Despite extensive searching, I can't find any examples of this. Does anyone know if this is possible?

To give a bit of context, it's a 500GB write-heavy read-light 24x7 MySQL database. I'm planning to use MySQL replication to a slave, which we can then take dumps from to send to S3 in case of a host failure (we use the same hosting provider for the replication master and slave, although they are in different data centres).

6

You can use the AWS CLI tool and STDIN on the CLI ("-") for this:

mysql -u db_user -ppassword database_name < aws s3 cp s3://mysqldump_bucket_name/mysqldump.sql -

The - part of the aws s3 cp command pipes the output to STDOUT which is read by mysql.

2

Currently there's not such feature in RDS, as explained in:

https://forums.aws.amazon.com/message.jspa?messageID=213853

But AWS guys recommend this procedure:

http://aws.amazon.com/articles/2933

1

It worked for me when I tried to backup a mysql server running in my machine to RDS mysql. (my machine was running windows, but it should work for linux mysql dump as well)

mysqldump -u root -p --databases 'mydatabase' > dump.sql

then, just upload to the RDS instance:

mysql -h myrds.com -u myuser -p < dump.sql

1
  • 1
    Thanks for the suggestion, but I'm not sure that really helps. This will push the data from your local machine using your MySQL client to the RDS instance. I need to get the data from an S3 bucket straight into RDS. As I said in the question, the database is pretty big - our zipped dump files are around 25GB, so I can't wait several hours for them to download to a local machine and then transfer up to RDS.
    – Mark B
    Sep 16 '11 at 7:18
0

The way I did this is to run a Fargate task.

Create a Docker container with a shell script containing the pipe statement:

aws s3 cp $DATA_LOCATION - | mysql --host=$MYSQL_HOST --user=$MYSQL_USER --password=$MYSQL_PASS --database=$database

Upload that somewhere AWS can access (eg ECR).

Then create a cluster (if you haven't already got one lying around) and a task definition that runs the container, that has a role that can see the S3 bucket. Also put together a network configuration that can see the RDS instance. (I did this all in Pulumi rather than AWS CLI, hence no examples in this section.)

Then put that all together and run the task with this call:

aws ecs run-task --task-definition=$TASK_ARN --cluster=$CLUSTER_ARN --network-configuration=$TASK_NETWORK --launch-type="FARGATE"

Then clean up the cluster, task definition and role if you had to create one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.