Skip to content

Instantly share code, notes, and snippets.

@InTheScript
Forked from jeop10/readme.md
Created March 14, 2020 20:27
Show Gist options
  • Save InTheScript/1e4aabc2e062e485d1a173f36a54c9db to your computer and use it in GitHub Desktop.
Save InTheScript/1e4aabc2e062e485d1a173f36a54c9db to your computer and use it in GitHub Desktop.
Backup MySQL to Amazon S3 using AWS CLI

Backup MySQL to Amazon S3 using AWS CLI

Based on this awesome gist

This is a simple way to backup your MySQL tables to Amazon S3 for a nightly backup - this is all to be done on your server :-) ** Using the AWS CLI (aws command) **

1 - Install aws-cli more info on the official repo

# Install aws-cli
pip install awscli

2 - Add your script

Upload a copy of s3mysqlbackup.sh (it will need some tweaks for your setup), make it executable and test it

# Add the executable bit
chmod +x s3mysqlbackup.sh
# Run the script to make sure it's all tickety boo
./s3mysqlbackup.sh

3 - Run it every night with CRON

Assuming the backup script is stored in /var/s3mysqlbackup.sh we need to add a crontask to run it automatically:

# Edit the crontab
env EDITOR=nano crontab -e
    # Add the following lines:
    # Run the database backup script at 3am
    0 3 * * * bash /var/s3mysqlbackup.sh >/dev/null 2>&1
    # In order to store a log of the upload use this
    0 3 * * * bash /var/s3mysqlbackup.sh >> /var/db-backup.log 2>&1 

4 - Examples:

This a example of the foler crated in the S3 bucket that you specify:

1497712981 - Saturday 17 June 2017 @ 1523

Inside that folder you will find your backups with a filename like this:

1497712981 - Saturday 17 June 2017 @ 1523 - database_name.sql.gz

5 - Troubleshooting

Remeber to check the path in which the aws-cli was installed the script assumes that it is intalled in

/usr/local/bin/aws

Also please remember to update the basic variables in the script:

  • mysqlpass
  • bucket
#!/bin/bash
# Based on https://gist.github.com/oodavid/2206527
# Be pretty
echo -e " "
echo -e " . ____ . ______________________________"
echo -e " |/ \| | |"
echo -e "[| \e[1;31m♥ ♥\e[00m |] | S3 MySQL Backup Script v.0.1.1 |"
echo -e " |___==___| / © oodavid 2012 |"
echo -e " |______________________________|"
echo -e " "
# Basic variables
mysqlpass="ROOTPASSWORD"
bucket="s3://bucketname"
# Timestamp (sortable AND readable)
stamp=`date +"%s - %A %d %B %Y @ %H%M"`
# List all the databases
databases=`mysql -u root -p$mysqlpass -e "SHOW DATABASES;" | tr -d "| " | grep -v "\(Database\|information_schema\|performance_schema\|mysql\|test\)"`
# Feedback
echo -e "Dumping to \e[1;32m$bucket/$stamp/\e[00m"
# Loop the databases
for db in $databases; do
# Define our filenames
filename="$stamp - $db.sql.gz"
tmpfile="/tmp/$filename"
object="$bucket/$stamp/$filename"
# Feedback
echo -e "\e[1;34m$db\e[00m"
# Dump and zip
echo -e " creating \e[0;35m$tmpfile\e[00m"
mysqldump -u root -p$mysqlpass --force --opt --databases "$db" | gzip -c > "$tmpfile"
# Upload
echo -e " uploading..."
/usr/local/bin/aws s3 cp "$tmpfile" "$object"
# Delete
rm -f "$tmpfile"
done;
# Jobs a goodun
echo -e "\e[1;32mJobs a goodun\e[00m"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment