I recently took over service of a server that had been through a few different system administrators in just 3 years. This left the server a bit messy for lack of a better term, as each administrator had done stuff “his” way and not documented anything. On top of that security was mostly through obscurity and basic administrator tasks had been either ignored or completed with one eye closed.

Take backup of the databases, in fact it was the only thing with backup and only locally too. The previous administrator had thought it optimal to just backup 7 days of data and then rotate it based on weekday.

My goal was:

• Each database in separate file
• Optimal compression
• Date separation of backups
• A clear and readable log file
• Simple time recording per event

So without further delay:

#!/usr/local/bin/bash

cd /backup

echo -n "Backup started: " > backup.log
/bin/date >> backup.log
echo "" >> backup.log

DBUSER="--user=username"
DBPASS="--password=password"
DBOPTIONS="--opt --hex-blob --force"
DBLOGIN="$DBUSER $DBPASS"

dblist=`echo show databases\; | /usr/local/bin/mysql $DBLOGIN | /usr/bin/tail -n +2`

for dbname in $dblist
do (
	echo "Backing up $dbname " >> backup.log
	echo " $(/bin/date +%H:%M:%S) - Dump cycle" >> backup.log
	/usr/local/bin/mysqldump $DBOPTIONS $DBLOGIN $dbname > ${dbname} 2>> backup.log
	echo " $(/bin/date +%H:%M:%S) - Compression Cycle" >> backup.log
	/usr/local/bin/p7zip ${dbname} > /dev/null 2>&1
	echo " $(/bin/date +%H:%M:%S) - $dbname finished!" >> backup.log
	echo "" >> backup.log
)
done

echo "Moving compressed files into $(date +%Y%m%d)" >> backup.log
echo "" >> backup.log
mkdir $(/bin/date +%Y%m%d)
mv *7z $(/bin/date +%Y%m%d)

echo -n "Backup ended: " >> backup.log
/bin/date >> backup.log
mv backup.log $(/bin/date +%Y%m%d)

Obviously you need to change the paths to match your setup and either install p7zip or change it to suit your needs. I went with p7zip as it was 30-35% better than the usual suspects (gzip or bzip) in terms of compression at almost same speed.

Log file example output:

Backup started: Thu Oct  7 20:10:51 CEST 2010

Backing up information_schema
 20:10:51 - Dump cycle
 20:10:51 - Compression Cycle
 20:10:51 - information_schema finished!

Backing up db1
 20:10:51 - Dump cycle
 20:12:04 - Compression Cycle
 20:13:21 - db1 finished!

Backing up db2
 20:13:21 - Dump cycle
 20:14:24 - Compression Cycle
 20:15:48 - db2 finished!

Backing up db3
 20:15:48 - Dump cycle
 20:16:28 - Compression Cycle
 20:17:13 - db3 finished!

Moving compressed files

Backup ended: Thu Oct  7 20:17:15 CEST 2010