Saturday, March 23, 2013

Mysql backup shell script

This is a shell script that allows you to backup your MySql databases and mail a compressed file to you. Of course if the backup becomes huge, you can better set the mail option off.
In the script you can give the database(s) you want to back up in an array. The script iterates the array and select the tables to back up.

In the given folder there are 4 sub folders for back up rotation. Every day is the new back up dumped in folder 01. 

Here below the script:


       

  #!/bin/bash

  # modify the destination directory to suit your environment
  export DB_BACKUP="/var/www/backup"

  # rotation ######################
  # remove day 4
  rm -rf $DB_BACKUP/04
  # moves day 3 to day 4
  mv $DB_BACKUP/03 $DB_BACKUP/04
  # moves day 2 to day 3
  mv $DB_BACKUP/02 $DB_BACKUP/03
  # moves day 1 to day 2
  mv $DB_BACKUP/01 $DB_BACKUP/02
  # makes dir 01 for day 1
  mkdir $DB_BACKUP/01
  # end rotation commands #########

  # define array of the database to bak up
  array=( db1 db2 )

  # loop through the array (each database to bak up)
  for db in "${array[@]}"
  do

    # loop through the tables of the database
   for i in `echo "show tables" | mysql -u UserNameHere -pYourPasswordHere $db | grep -v Tables_in_`;
   do
     # dumps the sql of the table to a compressed file with a database name prefix to hold the tables together
      mysqldump --add-drop-table --allow-keywords -q -a -c -u UserNameHere -pYourPasswordHere $db $i | gzip -9 > $DB_BACKUP/01/${db}_$i.sql.gz
   done

  done

  # makes a single compressed file of the backups
  tar -cvzf $DB_BACKUP/db_backup.tar.gz $DB_BACKUP

  # sends the file to your emailbox (you have to have sharutils and mailutils installed)
  uuencode  $DB_BACKUP/db_backup.tar.gz db_backup.tar.gz | mailx you@yourdomain.com

       
 

No comments:

Post a Comment