mySQL-Backup

mySQL-Backup is a backup script for mySQL databases.

Mit dem script ist es moeglich per cronjob einen automatischen Backup der SQL-databases zu erstellen. Die max. Anzahl der Backups ist einstellbar, nach erreichen von max Backup wird das aelteste Backup geloescht.

mySQL-Backup is a backup script for mySQL databases. With this script you can backup your mySQL-databases automatically with a cronjob. You can configure it for your needs. The max. Number of backups can be adjusted, after reaching a maximum, the oldest backup will be deleted.

Jetzt herunterladen!

# #
# checked / tested with:
#
# CentOS6.2, mysql Ver 14.14 Distrib 5.1.61, bash 4.1.2
#
#
# dump:
# /usr/bin/mysqldump --all-databases > alldbs.sql
#
# restore:
# /usr/bin/mysql -u<username> -p<password> -h localhost < alldbs.sql
#


# -------------- start configuration --------------#

# tempdir
TDR=/tmp/sql

# destination(backup) dir
DTR=/my_backupdir/goes/here

# how many dumps/DB --> oldest backup will be deleted
MXDP=8

# mySQL
#
# mysql root user
#
MSQLR=<place root username here>

# mysql root password
#
MSQLPW=<place (mxsql-)root password here>

# mysql hostname
#
MSQLHT=localhost

# mysql databases --> delimited with spaces
#
MSQLDBS=(database1 database2 database3)

# ------------- end configuration ----------#
#
#
#-------------- start script --------------------#


TERM=linux
export TERM


clear
echo ""

# gzip
GZ=`which gzip`

# mysqldump
MYDP=`which mysqldump`

# mysql
MYSL=`which mysql`

# date + time
crdt=$(date +%Y_%m_%d_%H_%M)

# check if dest. dir exists
#
if [ ! -d $DTR ]; then
echo "Destination/backup dir does not exist ! --> check configuration"
echo "Program terminated"
exit 
fi


# check if mySQL Database exists
#
for b in "${MSQLDBS[@]}"
do
# echo "testing database $b"
RESULT=`$MYSL -u$MSQLR -p$MSQLPW -h $MSQLHT --skip-column-names -e "SHOW DATABASES LIKE '$b'"`

if [ ! "$RESULT" == "$b" ]; then
echo "db $b does not exist ! --> check configuration"
echo "Program terminated"
exit
fi 
done

# create tempdir if not exist
if [ ! -d $TDR ]; then
mkdir -p $TDR
fi


# dump databases

echo ""
echo "Dump mySQL databases"
echo ""
echo ""

for a in "${MSQLDBS[@]}"
do
echo "saving database $a"
DF=$TDR/$a-$crdt.sql
$MYDP -u$MSQLR -p$MSQLPW -h $MSQLHT --database $a > $DF
$GZ $DF 
done

echo " "

# copy dumps to destination / backupdir
#
echo "copy files to backupdir"
cp $TDR/*.gz $DTR/
echo ""

# delete tempfiles
cd $TDR
rm -f *.gz

# check the number of backups/database
#
# change to dest. dir
cd $DTR

# count / delete files
#

for c in "${MSQLDBS[@]}"
do
FILES=($(ls -t $c*.gz))
FCNT=${#FILES[@]}
if [ $FCNT > $MXDP ]; then
# Database $c max limit reached
# delete oldest file(s)
for ((d=$MXDP; d<$FCNT; d++));
do
rm -f ${FILES[$d]} 
done 
fi
done