{"id":2415,"date":"2013-07-24T11:24:33","date_gmt":"2013-07-24T03:24:33","guid":{"rendered":"http:\/\/rmohan.com\/?p=2415"},"modified":"2013-07-24T11:30:04","modified_gmt":"2013-07-24T03:30:04","slug":"backup-mysql-databases-using-amazon-s3","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=2415","title":{"rendered":"BackUp MySQL Databases using Amazon S3"},"content":{"rendered":"

Backup MySQL to Amazon S3<\/p>\n

 <\/p>\n

Yum install s3cmd -y – If you can’t install s3cmd then please follow the instructions<\/p>\n

cd \/etc\/yum.repos.d<\/pre>\n
touch s3cmd.repo<\/pre>\n
nano s3cmd.repo<\/pre>\n
AND COPY THE CODE BELOW –<\/p>\n
# \r\n# Save this file to \/etc\/yum.repos.d on your system\r\n# and run \"yum install s3cmd\"\r\n# \r\n[s3tools]\r\nname=Tools for managing Amazon S3 - Simple Storage Service (RHEL_6)\r\ntype=rpm-md\r\nbaseurl=http:\/\/s3tools.org\/repo\/RHEL_6\/\r\ngpgcheck=1\r\ngpgkey=http:\/\/s3tools.org\/repo\/RHEL_6\/repodata\/repomd.xml.key\r\nenabled=1<\/pre>\n
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.<\/p>\n
s3cmd --configure<\/pre>\n
#!\/bin\/bash\r\nS3_BUCKET=YOUR BUCKET NAME HERE\r\nDATE=`date +%d%m%Y_%H%M`\r\nBACKUP_LOC=\/home\/admin\/user_backups\/$DATE\r\n\r\nmysql_backup(){\r\nmkdir $BACKUP_LOC\r\nmysqldump -uUSERNAME -pPASSWORD DBNAMEHERE > $BACKUP_LOC\/databasename_\r\n$DATE.sql\r\n\r\n\u00a0 \u00a0s3cmd ls s3:\/\/$S3_BUCKET\/database\/$DATE > \/tmp\/log.txt\r\n\u00a0 \u00a0grep -lr \"$DATE\" \/tmp\/log.txt\r\n\u00a0 if [ $? -ne 0 ]\r\n\u00a0 \u00a0 then\r\n\u00a0 \u00a0 mkdir \/tmp\/$DATE\r\n\u00a0 \u00a0 s3cmd put -r \/tmp\/$DATE s3:\/\/$S3_BUCKET\/datbase\/\r\n\r\n\u00a0 \u00a0 s3cmd sync -r $BACKUP_LOC s3:\/\/$S3_BUCKET\/database\/$DATE\/\r\n\r\n\u00a0 else\r\n\u00a0 \u00a0 s3cmd sync -r $BACKUP_LOC s3:\/\/$S3_BUCKET\/database\/$DATE\/\r\n\r\n\u00a0 fi\r\n\r\n}\r\nmysql_backup\r\nexit 0<\/pre>\n
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 \ud83d\ude42<\/p>\n
Sister Document – Restore MySQL from Amazon S3 – read that next
\n1 – Install s3cmd<\/p>\n
this is for Centos 5.6, see http:\/\/s3tools.org\/repositories for other systems like ubuntu etc<\/p>\n
# Install s3cmd
\ncd \/etc\/yum.repos.d\/
\nwget http:\/\/s3tools.org\/repo\/CentOS_5\/s3tools.repo
\nyum install s3cmd
\n# Setup s3cmd
\ns3cmd –configure
\n# You\u2019ll need to enter your AWS access key and secret key here, everything is optional and can be i<\/p>\n
2 – Add your script<\/p>\n
Upload a copy of s3mysqlbackup.sh (it will need some tweaks for your setup), make it executable and test it<\/p>\n
# Add the executable bit
\nchmod +x s3mysqlbackup.sh
\n# Run the script to make sure it’s all tickety boo
\n.\/s3mysqlbackup.sh<\/p>\n
3 – Run it every night with CRON<\/p>\n
Assuming the backup script is stored in \/var\/www\/s3mysqlbackup.sh we need to add a crontask to run it automatically:<\/p>\n
# Edit the crontab
\nenv EDITOR=nano crontab -e
\n# Add the following lines:
\n# Run the database backup script at 3am
\n0 3 * * * bash \/var\/www\/s3mysqlbackup.sh >\/dev\/null 2>&1<\/p>\n
4 – Don’t expose the script!<\/p>\n
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:<\/p>\n
### Deny public access to shell files
\n<Files *.sh>
\nOrder allow,deny
\nDeny from all
\n<\/Files><\/p>\n
s3mysqlbackup.sh
\nShell
\n12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
\n#!\/bin\/bash<\/p>\n
# Based on https:\/\/gist.github.com\/2206527<\/p>\n
# Be pretty
\necho -e ” ”
\necho -e ” . ____ . ______________________________”
\necho -e ” |\/ \\| | |”
\necho -e “[| \\e[1;31m? ?\\e[00m |] | S3 MySQL Backup Script v.0.1 |”
\necho -e ” |___==___| \/ \u00a9 oodavid 2012 |”
\necho -e ” |______________________________|”
\necho -e ” ”<\/p>\n
# Basic variables
\nmysqlpass=”ROOTPASSWORD”
\nbucket=”s3:\/\/bucketname”<\/p>\n
# Timestamp (sortable AND readable)
\nstamp=`date +”%s – %A %d %B %Y @ %H%M”`<\/p>\n
# List all the databases
\ndatabases=`mysql -u root -p$mysqlpass -e “SHOW DATABASES;” | tr -d “| ” | grep -v “\\(Database\\|information_schema\\|performance_schema\\|mysql\\|test\\)”`<\/p>\n
# Feedback
\necho -e “Dumping to \\e[1;32m$bucket\/$stamp\/\\e[00m”<\/p>\n
# Loop the databases
\nfor db in $databases; do<\/p>\n
# Define our filenames
\nfilename=”$stamp – $db.sql.gz”
\ntmpfile=”\/tmp\/$filename”
\nobject=”$bucket\/$stamp\/$filename”<\/p>\n
# Feedback
\necho -e “\\e[1;34m$db\\e[00m”<\/p>\n
# Dump and zip
\necho -e ” creating \\e[0;35m$tmpfile\\e[00m”
\nmysqldump -u root -p$mysqlpass –force –opt –databases “$db” | gzip -c > “$tmpfile”<\/p>\n
# Upload
\necho -e ” uploading…”
\ns3cmd put “$tmpfile” “$object”<\/p>\n
# Delete
\nrm -f “$tmpfile”<\/p>\n
done;<\/p>\n
# Jobs a goodun
\necho -e “\\e[1;32mJobs a goodun\\e[00m”<\/p>\n
 <\/p>\n
 <\/p>\n
!\/bin\/bash<\/div>\n
# Shell script to backup MySql database<\/div>\n
<\/div>\n
# CONFIG – Only edit the below lines to setup the script<\/div>\n
# ===============================<\/div>\n
<\/div>\n
MyUSER=”root” # USERNAME<\/div>\n
MyPASS=”password” # PASSWORD<\/div>\n
MyHOST=”localhost” # Hostname<\/div>\n
<\/div>\n
S3Bucket=”mysql-backup” # S3 Bucket<\/div>\n
<\/div>\n
# DO NOT BACKUP these databases<\/div>\n
IGNORE=”test”<\/div>\n
<\/div>\n
# DO NOT EDIT BELOW THIS LINE UNLESS YOU KNOW WHAT YOU ARE DOING<\/div>\n
# ===============================<\/div>\n
<\/div>\n
# Linux bin paths, change this if it can not be autodetected via which command<\/div>\n
MYSQL=”$(which mysql)”<\/div>\n
MYSQLDUMP=”$(which mysqldump)”<\/div>\n
CHOWN=”$(which chown)”<\/div>\n
CHMOD=”$(which chmod)”<\/div>\n
GZIP=”$(which gzip)”<\/div>\n
<\/div>\n
# Backup Dest directory, change this if you have someother location<\/div>\n
DEST=”\/backup”<\/div>\n
<\/div>\n
# Main directory where backup will be stored<\/div>\n
MBD=”$DEST\/mysql-$(date +”%d-%m-%Y”)”<\/div>\n
<\/div>\n
# Get hostname<\/div>\n
HOST=”$(hostname)”<\/div>\n
<\/div>\n
# Get data in dd-mm-yyyy format<\/div>\n
NOW=”$(date +”%d-%m-%Y”)”<\/div>\n
<\/div>\n
# File to store current backup file<\/div>\n
FILE=””<\/div>\n
<\/div>\n
# Store list of databases<\/div>\n
DBS=””<\/div>\n
<\/div>\n
[ ! -d $MBD ] && mkdir -p $MBD || :<\/div>\n
<\/div>\n
# Only root can access it!<\/div>\n
$CHOWN 0.0 -R $DEST<\/div>\n
$CHMOD 0600 $DEST<\/div>\n
<\/div>\n
# Get all database list first<\/div>\n
<\/div>\n
if [ “$MyPASS” == “” ];<\/div>\n
then<\/div>\n
DBS=”$($MYSQL -u $MyUSER -h $MyHOST -Bse ‘show databases’)”<\/div>\n
else<\/div>\n
DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’)”<\/div>\n
fi<\/div>\n
<\/div>\n
for db in $DBS<\/div>\n
do<\/div>\n
skipdb=-1<\/div>\n
if [ “$IGNORE” != “” ];<\/div>\n
then<\/div>\n
for i in $IGNORE<\/div>\n
do<\/div>\n
[ “$db” == “$i” ] && skipdb=1 || :<\/div>\n
done<\/div>\n
fi<\/div>\n
<\/div>\n
if [ “$skipdb” == “-1” ] ; then<\/div>\n
FILE=”$MBD\/$db.$HOST.$NOW.gz”<\/div>\n
# dump database to file and gzip<\/div>\n
if [ “$MyPASS” == “” ]; then<\/div>\n
$MYSQLDUMP -u $MyUSER -h $MyHOST $db | $GZIP -9 > $FILE<\/div>\n
else<\/div>\n
$MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE<\/div>\n
fi<\/div>\n
fi<\/div>\n
done<\/div>\n
<\/div>\n
# copy mysql backup directory to S3<\/div>\n
s3cmd sync -rv –skip-existing $MBD s3:\/\/$S3Bucket\/<\/div>\n
<\/div>\n
<\/div>\n
\n
u can use the above script in a cron too, so your server is backed up\u00a0regularly. The below cronjob will run the MySQL database backup script everyday at 2am:<\/p>\n
\n
\n
\n
\n\n\n\n
12<\/td>\n\n
# Run everday at 2am<\/div>\n
0 2 * * * \/path\/to\/sql_backup.sh<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Backup MySQL to Amazon S3<\/p>\n
 <\/p>\n
Yum install s3cmd -y – If you can’t install s3cmd then please follow the instructions<\/p>\n
 cd \/etc\/yum.repos.d touch s3cmd.repo nano s3cmd.repo <\/p>\n
AND COPY THE CODE BELOW –<\/p>\n
 # # Save this file to \/etc\/yum.repos.d on your system # and run “yum install s3cmd” # [s3tools] name=Tools for managing Amazon […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[49],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2415"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2415"}],"version-history":[{"count":5,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2415\/revisions"}],"predecessor-version":[{"id":2418,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2415\/revisions\/2418"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2415"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2415"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2415"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}