Now you know how to back up your files and code to Amazon S3, but what about databases? Luckily, help is at hand. The Linux s3cmd script can easily be used to back up your MySQL databases. To make things easier, I’ve created a script that you can use to back up all your databases into a folder, which is then synched with your S3 bucket.
The below script can be used via command line with a bit of configuration. All you need to do is set the MyUSER, MyPASS, MyHOST and S3Bucket variables at the top of the script and you can start backing up your databases.
| #!/bin/bash | |
| # Shell script to backup MySql database | |
| # CONFIG - Only edit the below lines to setup the script | |
| # =============================== | |
| MyUSER="root" # USERNAME | |
| MyPASS="password" # PASSWORD | |
| MyHOST="localhost" # Hostname | |
| S3Bucket="mysql-backup" # S3 Bucket | |
| # DO NOT BACKUP these databases | |
| IGNORE="test" | |
| # DO NOT EDIT BELOW THIS LINE UNLESS YOU KNOW WHAT YOU ARE DOING | |
| # =============================== | |
| # Linux bin paths, change this if it can not be autodetected via which command | |
| MYSQL="$(which mysql)" | |
| MYSQLDUMP="$(which mysqldump)" | |
| CHOWN="$(which chown)" | |
| CHMOD="$(which chmod)" | |
| GZIP="$(which gzip)" | |
| # Backup Dest directory, change this if you have someother location | |
| DEST="/backup" | |
| # Main directory where backup will be stored | |
| MBD="$DEST/mysql-$(date +"%d-%m-%Y_%T")" | |
| # Get hostname | |
| HOST="$(hostname)" | |
| # Get data in dd-mm-yyyy format | |
| NOW="$(date +"%d-%m-%Y")" | |
| # File to store current backup file | |
| FILE="" | |
| # Store list of databases | |
| DBS="" | |
| [ ! -d $MBD ] && mkdir -p $MBD || : | |
| # Only root can access it! | |
| $CHOWN 0.0 -R $DEST | |
| $CHMOD 0600 $DEST | |
| # Get all database list first | |
| if [ "$MyPASS" == "" ]; | |
| then | |
| DBS="$($MYSQL -u $MyUSER -h $MyHOST -Bse 'show databases')" | |
| else | |
| DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')" | |
| fi | |
| for db in $DBS | |
| do | |
| skipdb=-1 | |
| if [ "$IGNORE" != "" ]; | |
| then | |
| for i in $IGNORE | |
| do | |
| [ "$db" == "$i" ] && skipdb=1 || : | |
| done | |
| fi | |
| if [ "$skipdb" == "-1" ] ; then | |
| FILE="$MBD/$db.$HOST.$NOW.gz" | |
| # dump database to file and gzip | |
| if [ "$MyPASS" == "" ]; then | |
| $MYSQLDUMP -u $MyUSER -h $MyHOST $db | $GZIP -9 > $FILE | |
| else | |
| $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE | |
| fi | |
| fi | |
| done | |
| # copy mysql backup directory to S3 | |
| s3cmd sync -rv --skip-existing $MBD s3://$S3Bucket/ |
by You can use the above script in a cron too, so your server is backed up regularly. The below cronjob will run the MySQL database backup script everyday at 2am:
| # Run everday at 2am | |
| 0 2 * * * /path/to/sql_backup.sh |
Once the script has completed, you’ll see a folder on your S3 Bucket with the date of the backup. If you run the script multiple times in a day, the latest version of your database will be synched to the same folder.
Excellent script! Works like a charm, thanks very much for sharing!
Hi, thanks for this script, it looks like just what I need. Can you tell me where is the correct / safe place to store the script on the server?
I usually put the script in the home directory, e.g.
~/or/root. The script works best with root access, so it can access all the databases to back them up. But in general, anywhere other than your web root folder should be safe.Thank you! Best guide out of 10 I came across online on backing up MySql to S3.
Nice one please tell me how to take dump of rds database without using any linux machine directly on s3
You need a server to take a dump of a database. You cannot run code on S3, only static content.