RDS Automatic Dump and Upload to s3

2022-06-07 344 words 2 minutes

Contents

Another awesome script from yours truly. This will automaticly download all databases from a RDS of your choice. It will then upload the .sql dump to your decided s3 bucket for easy and secure storage.

Dependencies

AWS CLI (With upload access to you s3 bucket)
mysqldump

1 Setup the environment

First we need to create a little directory where we can work from and run the script. This can be anywhere you want on your system, just make sure that you meet the dependencies for this project.
```
mkdir rdsAutoBackup
cd rdsAutoBackup
```

Inside this directory we will create a file .my.cnf where we store all of our sensitive information about our SQL server.

vim .my.cnf

.my.cnf

[mysqldump]
 host=CHANGE_THIS_TO_YOUR_RDS_URL
 user=CHANGE_THIS_TO_THE_ADMIN_OF_THE_RDS
 password=PASSWORD_FOR_THE_RDS_ADMIN
 column-statistics=0

Warning

Make sure that you use a user which is allowed to dump every database on the sever

Save the file
For security we can also make it only readable for the current user by doing
```
chmod 600 .my.cnf
```

We also need to make a file where we store all of the databases we want to dump. This file can be called whatever you want and can contain all or some of the databases from your server.
```
vim databases.txt
```
databases.txt
```
database1
database2
database3
database4
```

Also we have to make a directory for the dumps
```
mkdir dumps
```

2 The script

Now it’s finaly time for the actual script:
```
vim dumpAllDatabasesSeparate.sh
```

dumpAllDatabasesSeparate.sh

#!/bin/bash

echo Please enter the URL for the s3 bucket you want to sync to:
read s3

while IFS= read -r line; do
	mysqldump --defaults-file=.my.cnf --set-gtid-purged=OFF $line > dumps/$line\_$(date '+%d%m%y-%H%M').sql
	aws s3 mv dumps/* $s3
done <databases.txt

Info

If you don’t want it to move to s3 and just want a local dump, then here is a simplified version:

#!/bin/bash

while IFS= read -r line; do
	mysqldump --defaults-file=.my.cnf --set-gtid-purged=OFF $line > dumps/$line\_$(date '+%d%m%y-%H%M').sql
done <databases

Save the file and make it executable
```
chmod +x dumpAllDatabasesSeparate.sh
```
That’s it! Just run the script and paste in the location of your s3 bucket where you want to store the data and watch the magic work for you.