Select Page

It is important to keep regular back ups, like any insurance salesman will tell you

it is better to have it and not need it, than need it and not have it.

On most servers there are in built scripts to do back ups, how you do that will depend on what web hosting panel you are using.

You can also use a script, like the one below, thanks to:

https://gist.github.com/tleish/1c6e788c84f59200446b

vi /root/mysql.sh to create in your root folder

Don’t forget to create a cron job using crontab -e on centos

#!/bin/bash
#=====================#
#TITLE: mysql_backup.sh
#DESCRIPTION: script for automating the daily mysql backups on development computer
#AUTHOR: tleish
#DATE: 2013-12-20
#VERSION: 0.4
#USAGE: ./mysql_backup.sh
#CRON:
# example cron for daily db backup @ 9:15 am
# min hr mday month wday command
# 15 9 * * * /Users/[your user name]/scripts/mysql_backup.sh

# every 4hrs @ quater past the hour

# 15 1,5,9,13,17,21 * * * /root/mysql.sh

#RESTORE FROM BACKUP
#$ gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

#=====================#
# CUSTOM SETTINGS
#=====================#

# directory to put the backup files
BACKUP_DIR=/PATH-TO-BACKUP

# MYSQL Parameters
MYSQL_UNAME=USER
MYSQL_PWORD=PASSWORD

# Don’t backup databases with these names
# Example: starts with mysql (^mysql) or ends with _schema (_schema$)

IGNORE_DB=”(^mysql|_schema$)”

# include mysql and mysqldump binaries for cron bash user
PATH=$PATH:/usr/local/mysql/bin

# Number of days to keep backups
KEEP_BACKUPS_FOR=30 #days

#=====================#
# METHODS
#=====================#

# YYYY-MM-DD
TIMESTAMP=$(date +%F_%T)

#date +%F_%T

function delete_old_backups()
{
echo “Deleting $BACKUP_DIR/*.sql.gz older than $KEEP_BACKUPS_FOR days”
find $BACKUP_DIR -type f -name “*.sql.gz” -mtime +$KEEP_BACKUPS_FOR -exec rm {} \;
}

function mysql_login() {
local mysql_login=”-u $MYSQL_UNAME”
if [ -n “$MYSQL_PWORD” ]; then
local mysql_login+=” -p$MYSQL_PWORD”
fi
echo $mysql_login
}

function database_list() {
local show_databases_sql=”SHOW DATABASES WHERE \`Database\` NOT REGEXP ‘$IGNORE_DB'”
echo $(mysql $(mysql_login) -e “$show_databases_sql”|awk -F ” ” ‘{if (NR!=1) print $1}’)
}

function echo_status(){
printf ‘\r’;
printf ‘ %0.s’ {0..100}
printf ‘\r’;
printf “$1″‘\r’
}

function backup_database(){
backup_file=”$BACKUP_DIR/$TIMESTAMP.$database.sql.gz”
output+=”$database => $backup_file\n”
echo_status “…backing up $count of $total databases: $database”
$(mysqldump $(mysql_login) $database | gzip -9 > $backup_file)
}

function backup_databases(){
local databases=$(database_list)
local total=$(echo $databases | wc -w | xargs)
local output=””
local count=1
for database in $databases; do
backup_database
local count=$((count+1))
done
echo -ne $output | column -t
}

function hr(){
printf ‘=%.0s’ {1..100}
printf “\n”
}

#==============================================================================
# RUN SCRIPT
#==============================================================================
delete_old_backups
hr
backup_databases
hr
printf “All backed up!\n\n”