Monthly Archives: July 2008

Make backup of MySQL databases

Recently I have bought virtual server for several sites but backups are not free. So I decided to write few scripts that can be run with cronjob to save backups that can be later dowloaded vie ftp manually or even uploaded by another script to some other server.

This time I will show you how to make backups of MySQL databases. The most simple way to make backup of all databases is
mysqldump --all-databases > file.sql
It’s not very convinient if you have several databases ant want to make use of backups not only if you’ll need to restore all of them.

So I wrote simple script that makes backups of databases to different compressed (gziped) files and stores only last n backups.

mysqlbakcup.sh:

#! /bin/sh
#path to config file with database names
dbs="dbs.conf"
#Name of created directory
dir=`date +%Y-%m-%d_%H%M%S/`
#Directories with old backups to leave untouched by cleaner
dirsleave=3

if [ ! -d ${dir} ]
then
  mkdir ${dir}
fi
for i in `cat ${dbs}`
do
  mysqldump ${i} | gzip > ${dir}${i}.sql.gz
done
dirsleft=0
for i in `ls -r`
do
  if [ -d ${i} ]
  then
    dirsleft=`expr ${dirsleft} + 1`
    if [ "${dirsleft}" -gt "${dirsleave}" ]
    then
      rm -r -f ${i}
    fi
  fi
done

That’s it. Now fill dba.conf file.

dbs.conf:

database1

database2

In dbs.conf just write the list of names of databases to backup.

This script leaves last 3 backups and deletes older ones. So you can choose how often to run it. I’m doing it every night.

Tagged , , , , , ,
Follow

Get every new post delivered to your Inbox.