BackUp MySQL Databases using Amazon S3

Backup MySQL to Amazon S3

 

Yum install s3cmd -y – If you can’t install s3cmd then please follow the instructions

cd /etc/yum.repos.d

touch s3cmd.repo

nano s3cmd.repo

AND COPY THE CODE BELOW –

# 
# Save this file to /etc/yum.repos.d on your system
# and run "yum install s3cmd"
# 
[s3tools]
name=Tools for managing Amazon S3 - Simple Storage Service (RHEL_6)
type=rpm-md
baseurl=http://s3tools.org/repo/RHEL_6/
gpgcheck=1
gpgkey=http://s3tools.org/repo/RHEL_6/repodata/repomd.xml.key
enabled=1

Try Above YUM Command again – This time it will install S3CMD, Once installation is completed try running the following command – It will configure your S3CMD using your AMAZON KEY AND Access ID.

s3cmd --configure

#!/bin/bash
S3_BUCKET=YOUR BUCKET NAME HERE
DATE=`date +%d%m%Y_%H%M`
BACKUP_LOC=/home/admin/user_backups/$DATE

mysql_backup(){
mkdir $BACKUP_LOC
mysqldump -uUSERNAME -pPASSWORD DBNAMEHERE > $BACKUP_LOC/databasename_
$DATE.sql

   s3cmd ls s3://$S3_BUCKET/database/$DATE > /tmp/log.txt
   grep -lr "$DATE" /tmp/log.txt
  if [ $? -ne 0 ]
    then
    mkdir /tmp/$DATE
    s3cmd put -r /tmp/$DATE s3://$S3_BUCKET/datbase/

    s3cmd sync -r $BACKUP_LOC s3://$S3_BUCKET/database/$DATE/

  else
    s3cmd sync -r $BACKUP_LOC s3://$S3_BUCKET/database/$DATE/

  fi

}
mysql_backup
exit 0

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
1 – Install s3cmd

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 i

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/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

4 – Don’t expose the script!

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>

s3mysqlbackup.sh
Shell
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
#!/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”