Backup MySQL to Amazon S3
#!/bin/bash
# Based on https://gist.github.com/2206527
# Be pretty
echo -e " "
echo -e " . ____ . ______________________________"
echo -e " |/ \| | |"
echo -e "[| \e[1;31m♥ ♥\e[00m |] | S3 MySQL Backup Script v.0.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..."
s3cmd put "$tmpfile" "$object"
# Delete
rm -f "$tmpfile"
done;
# Jobs a goodun
echo -e "\e[1;32mJobs a goodun\e[00m"
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 :-)
Sister Document - Restore MySQL from Amazon S3 - read that next
this is for Centos 5.6, see http://s3tools.org/repositories for other systems like ubuntu etc
# Install s3cmd
cd /etc/yum.repos.d/
wget http://s3tools.org/repo/CentOS_5/s3tools.repo
yum install s3cmd
# Setup s3cmd
s3cmd --configure
# You’ll need to enter your AWS access key and secret key here, everything is optional and can be ignored :-)
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
Assuming the backup script is stored in /var/www/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/www/s3mysqlbackup.sh >/dev/null 2>&1
If for some reason you put this script in a public folder (not sure why you would do this), you should add the following to your .htaccess or httpd.conf file to prevent public access to the files:
### Deny public access to shell files
<Files *.sh>
Order allow,deny
Deny from all
</Files>