Nasser Heidari

2010-02-08

Mysql Backup Script

Filed under: Miscellaneous — Nasser Heidari @ 20:49

mysql_backup.sh :
——————————————————————————
#!/bin/sh
DATESTAMP=$(date +%F)
DIR=/files/MYSQL/backup/
DB_USER=backup
DB_PASS=’backup’
HOSTS=/files/MYSQL/HOST_LIST

# remove backups older than $DAYS_KEEP
DAYS_KEEP=30
find $DIR -mtime +$DAYS_KEEP -exec rm -f {} \; 2> /dev/null

# create backups securely
umask 006

for HOST in $(egrep ^[^#] $HOSTS | cut -f 1);
do
MYSQLDUMP=”/usr/bin/mysqldump –lock-tables=false -u $DB_USER -p”$DB_PASS” -h$HOST”
HOSTNAME=$(grep $HOST $HOSTS | cut -f2)
if [ ! -d $DIR$HOSTNAME ]; then
mkdir $DIR$HOSTNAME
fi
# list MySQL databases and dump each
DB_LIST=$(mysql -h $HOST -u $DB_USER -p”$DB_PASS” -Bs -e’show databases;’ | egrep -v “information_schema|test” | xargs )

for DB in $DB_LIST;
do
FILENAME=$DIR$HOSTNAME/$DB-$DATESTAMP.sql.gz
$MYSQLDUMP $DB | gzip > $FILENAME
done
done
——————————————————————————
HOST_LIST:
########################################################
# GRANT Select,USAGE ON *.* to backup@’backup_server’ IDENTIFIED BY ‘backup’;
# FLUSH PRIVILEGES;
########################################################
#ip name
192.168.2.26 srv1
192.168.2.25 mailserver
192.168.2.11 radius
192.168.11.127 cacti

Advertisements

1 Comment »

  1. Great script! Good blog and good posts!

    Comment by Alberto Permuy — 2010-02-10 @ 08:04


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: