AutoMySQLbackup

This one worked for me. It is now sitting in /private/etc/periodic/daily/ with other daily scripts being run by cron. Had to add the path to the MYSQLDUMP utility in this line:

PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin

Automatic MySQL Backup


Here is the script:

#!/bin/bash

#

# MySQL Backup Script

# VER. 1.9 – http://sourceforge.net/projects/automysqlbackup/

# Copyright (c) 2002-2003 [email protected]

#

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; either version 2 of the License, or

# (at your option) any later version.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

# GNU General Public License for more details.

#

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

#

#=====================================================================

#=====================================================================

# Set the following variables to your system needs

# (Detailed instructions below variables)

#=====================================================================

# Username to access the MySQL server e.g. dbuser

USERNAME=root

# Username to access the MySQL server e.g. password

PASSWORD=H***** (masked)

# Host name (or IP address) of MySQL server e.g localhost

DBHOST=localhost

# List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3″

# DBNAMES=”DB1 DB2 DB3″

DBNAMES=”mov_type”

# Backup directory location e.g /backups

BACKUPDIR=”/backups”

# Mail backup log? (yes or no)

MAILLOG=no

# Email Address to send log to? ([email protected])

MAILADDR=”[email protected]

# ============================================================

# === ADVANCED OPTIONS ( Read the doc’s below for details )===

#=============================================================

# List of DBBNAMES for Monthly Backups.

MDBNAMES=”mysql $DBNAMES”

# List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in ” quotes)

DBEXCLUDE=””

# Include CREATE DATABASE in backup?

CREATE_DATABASE=yes

# Separate backup directory and file for each DB? (yes or no)

SEPDIR=yes

# Which day do you want weekly backups? (1 to 7 where 1 is Monday)

DOWEEKLY=6

# Command to run before backups (uncomment to use)

#PREBACKUP=”/etc/mysql-backup-pre”

# Command run after backups (uncomment to use)

#POSTBACKUP=”/etc/mysql-backup-post”

#=====================================================================

# Options documantation

#=====================================================================

# Set USERNAME and PASSWORD of a user that has at least SELECT permission

# to ALL databases.

#

# Set the DBHOST option to the server you wish to backup, leave the

# default to backup “this server”.(to backup multiple servers make

# copies of this file and set the options for that server)

#

# Put in the list of DBNAMES(Databases)to be backed up. If you would like

# to backup ALL DBs on the server set DBNAMES=”all”.(if set to “all” then

# any new DBs will automatically be backed up without needing to modify

# this backup script when a new DB is created).

#

# If the DB you want to backup has a space in the name replace the space

# with a % e.g. “data base” will become “data%base”

# NOTE: Spaces in DB names may not work correctly when SEPDIR=no.

#

# You can change the backup storage location from /backups to anything

# you like by using the BACKUPDIR setting..

#

# The MAILLOG and MAILADDR options and pretty self explanitory, use

# these to have the backup log mailed to you at any email address or multiple

# email addresses in a space seperated list.

# (this will require that you are permitted to run the “mail” program

# on your server.)

#

# Finally copy automysqlbackup.sh to anywhere on your server and make sure

# to set executable permission. You can also copy the script to

# /etc/cron.daily to have it execute automatically every night or simply

# place a symlink in /etc/cron.daily to the file if you wish to keep it

# somwhere else.

# NOTE:On Debian copy the file with no extention for it to be run

# by cron e.g just name the file “automysqlbackup”

#

# Thats it..

#

#

# === Advanced options doc’s ===

#

# The list of MDBNAMES is the DB’s to be backed up only monthly. You should

# always include “mysql” in this list to backup your user/password

# information along with any other DBs that you only feel need to

# be backed up monthly. (if using a hosted server then you should

# probably remove “mysql” as your provider will be backing this up)

# NOTE: If DBNAMES=”all” then MDBNAMES has no effect as all DBs will be backed

# up anyway.

#

# If you set DBNAMES=”all” you can configure the option DBEXCLUDE. Other

# wise this option will not be used.

# This option can be used if you want to backup all dbs, but you want

# exclude some of them. (eg. a db is to big).

#

# Set CREATE_DATABASE to “yes” (the default) if you want your SQL-Dump to create

# a database with the same name as the original database when restoring.

# Saying “no” here will allow your to specify the database name you want to

# restore your dump into, making a copy of the database by using the dump

# created with automysqlbackup.

# NOTE: Not used if SEPDIR=no

#

# The SEPDIR option allows you to choose to have all DBs backed up to

# a single file (fast restore of entire server in case of crash) or to

# seperate directories for each DB (each DB can be restored seperately

# in case of single DB corruption or loss).

#

# To set the day of the week that you would like the weekly backup to happen

# set the DOWEEKLY setting, this can be a value from 1 to 7 where 1 is Monday,

# The default is 6 which means that weekly backups are done on a Saturday.

#

# Use PREBACKUP and POSTBACKUP to specify Per and Post backup commands

# or scripts to perform tasks either before or after the backup process.

#

#

#=====================================================================

# Backup Rotation..

#=====================================================================

#

# Daily Backups are rotated weekly..

# Weekly Backups are run by default on Saturday Morning when

# cron.daily scripts are run…Can be changed with DOWEEKLY setting..

# Weekly Backups are rotated on a 5 week cycle..

# Monthly Backups are run on the 1st of the month..

# Monthly Backups are NOT rotated automatically…

# It may be a good idea to copy Monthly backups offline or to another

# server..

#

#=====================================================================

# Please Note!!

#=====================================================================

#

# I take no resposibility for any data loss or corruption when using

# this script..

# This script will not help in the event of a hard drive crash. If a

# copy of the backup has not be stored offline or on another PC..

# You should copy your backups offline regularly for best protection.

#

# Happy backing up…

#

#=====================================================================

# Restoring

#=====================================================================

# Firstly you will need to uncompress the backup file.

# eg.

# ungzip file.gz

#

# Next you will need to use the mysql client to restore the DB from the

# sql file.

# eg.

# mysql –user=username –pass=password –host=dbserver database < /path/file.sql

# or

# mysql –user=username –pass=password –host=dbserver -e "source /path/file.sql" database

#

# NOTE: Make sure you use "” in the above command because

# you are piping the file.sql to mysql and not the other way around.

#

# Lets hope you never have to use this.. 🙂

#

#=====================================================================

# Change Log

#=====================================================================

#

# VER 1.9 – (2004-05-25)

# Small bug fix to handle spaces in LOGFILE path which contains spaces (reported by Thomas von Eyben)

# Updated docs to mention that Log email can be sent to multiple email addresses.

# VER 1.8 – (2004-05-01)

# Added option to make backups restorable to alternate database names

# meaning that a copy of the database can be created (Based on patch by Rene Hoffmann)

# Seperated options into standard and advanced.

# Removed ” from single file dump DBMANES because it caused an error but

# this means that if DB’s have spaces in the name they will not dump when SEPDIR=no.

# Added -p option to mkdir commands to create multiple subdirs without error.

# Added disk usage and location to the bottom of the backup report.

# VER 1.7 – (2004-04-22)

# Fixed an issue where weelky backups would only work correctly if server

# locale was set to English (issue reported by Tom Ingberg)

# used “eval” for “rm” commands to try and resolve rotation issues.

# Changed name of status log so multiple scripts can be run at the same time.

# VER 1.6 – (2004-03-14)

# Added PREBACKUP and POSTBACKUP command functions. (patch by markpustjens)

# Added support for backing up DB’s with Spaces in the name.

# (patch by markpustjens)

# VER 1.5 – (2004-02-24)

# Added the ability to exclude DB’s when the “all” option is used.

# (Patch by kampftitan)

# VER 1.4 – (2004-02-02)

# Project moved to Sourceforge.net

# VER 1.3 – (2003-09-25)

# Added support for backing up “all” databases on the server without

# having to list each one seperately in the configuration.

# Added DB restore instructions.

# VER 1.2 – (2003-03-16)

# Added server name to the backup log so logs from multiple servers

# can be easily identified.

# VER 1.1 – (2003-03-13)

# Small Bug fix in monthly report. (Thanks Stoyanski)

# Added option to email log to any email address. (Inspired by Stoyanski)

# Changed Standard file name to .sh extention.

# Option are set using yes and no rather than 1 or 0.

# VER 1.0 – (2003-01-30)

# Added the ability to have all databases backup to a single dump

# file or seperate directory and file for each database.

# Output is better for log keeping.

# VER 0.6 – (2003-01-22)

# Bug fix for daily directory (Added in VER 0.5) rotation.

# VER 0.5 – (2003-01-20)

# Added “daily” directory for daily backups for neatness (suggestion by Jason)

# Added DBHOST option to allow backing up a remote server (Suggestion by Jason)

# Added “–quote-names” option to mysqldump command.

# Bug fix for handling the last and first of the year week rotation.

# VER 0.4 – (2002-11-06)

# Added the abaility for the script to create its own directory structure.

# VER 0.3 – (2002-10-01)

# Changed Naming of Weekly backups so they will show in order.

# VER 0.2 – (2002-09-27)

# Corrected weekly rotation logic to handle weeks 0 – 10

# VER 0.1 – (2002-09-21)

# Initial Release

#

#=====================================================================

#=====================================================================

#=====================================================================

#

# Should not need to be modified from here down!!

#

#=====================================================================

#=====================================================================

#=====================================================================

#PATH=/usr/local/bin:/usr/bin:/bin

PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin

DATE=`date +%Y-%m-%d` # Datestamp e.g 2002-09-21

DOW=`date +%A` # Day of the week e.g. Monday

DNOW=`date +%u` # Day number of the week 1 to 7 where 1 represents Monday

DOM=`date +%d` # Date of the Month e.g. 27

M=`date +%B` # Month e.g January

W=`date +%V` # Week Number e.g 37

VER=1.9 # Version Number

LOGFILE=/$DBHOST-`date +%N`.log # Logfile Name

OPT=”–quote-names –opt” # OPT string for use with mysqldump ( see man mysqldump )

# Run command before we begin

if [ “$PREBACKUP” ]

then

$PREBACKUP

fi

# Create required directories

if [ ! -e “$BACKUPDIR” ] # Check Backup Directory exists.

then

mkdir -p “$BACKUPDIR”

fi

if [ ! -e “$BACKUPDIR/daily” ] # Check Daily Directory exists.

then

mkdir -p “$BACKUPDIR/daily”

fi

if [ ! -e “$BACKUPDIR/weekly” ] # Check Weekly Directory exists.

then

mkdir -p “$BACKUPDIR/weekly”

fi

if [ ! -e “$BACKUPDIR/monthly” ] # Check Monthly Directory exists.

then

mkdir -p “$BACKUPDIR/monthly”

fi

if [ “$SEPDIR” = “yes” ]; then # Check if CREATE DATABSE should be included in Dump

if [ “$CREATE_DATABASE” = “no” ]; then

OPT=”$OPT –no-create-db”

else

OPT=”$OPT –databases”

fi

else

OPT=”$OPT –databases”

fi

# Hostname for LOG information

if [ “$DBHOST” = “localhost” ]; then

HOST=`hostname`

else

HOST=$DBHOST

fi

# If backing up all DBs on the server

if [ “$DBNAMES” = “all” ]; then

DBNAMES=”`mysql –user=$USERNAME –password=$PASSWORD –host=$DBHOST –batch -N -e “show databases”| sed ‘s/ /%/g’`”

# If DBs are excluded

for exclude in $DBEXCLUDE

do

DBNAMES=`echo $DBNAMES | sed “s/\b$exclude\b//g”`

done

MDBNAMES=$DBNAMES

fi

echo ======================================================================>>”$BACKUPDIR/$LOGFILE”

echo AutoMySQLBackup VER $VER>>”$BACKUPDIR/$LOGFILE”

echo http://sourceforge.net/projects/automysqlbackup/>>”$BACKUPDIR/$LOGFILE”

echo >>”$BACKUPDIR/$LOGFILE”

echo Backup of Database Server – $HOST>>”$BACKUPDIR/$LOGFILE”

echo ======================================================================>>”$BACKUPDIR/$LOGFILE”

# Test is seperate DB backups are required

if [ “$SEPDIR” = “yes” ]; then

echo Backup Start Time `date`>>”$BACKUPDIR/$LOGFILE”

echo ======================================================================>>”$BACKUPDIR/$LOGFILE”

# Monthly Full Backup of all Databases

if [ $DOM = “01” ]; then

for MDB in $MDBNAMES

do

# Prepare $DB for using

MDB=”`echo $MDB | sed ‘s/%/ /g’`”

if [ ! -e “$BACKUPDIR/monthly/$MDB” ] # Check Monthly DB Directory exists.

then

mkdir -p “$BACKUPDIR/monthly/$MDB”

fi

echo Monthly Backup of $MDB…>>”$BACKUPDIR/$LOGFILE”

mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT “$MDB” > “$BACKUPDIR/monthly/$MDB/$DATE.$M.$MDB.sql”

gzip -f “$BACKUPDIR/monthly/$MDB/$DATE.$M.$MDB.sql”

echo>>”$BACKUPDIR/$LOGFILE”

echo Backup Information for $BACKUPDIR/monthly/$MDB/$DATE.$M.$MDB.sql.gz>>”$BACKUPDIR/$LOGFILE”

gzip -l “$BACKUPDIR/monthly/$MDB/$DATE.$M.$MDB.sql.gz” >> “$BACKUPDIR/$LOGFILE”

echo ———————————————————————->>”$BACKUPDIR/$LOGFILE”

done

fi

for DB in $DBNAMES

do

# Prepare $DB for using

DB=”`echo $DB | sed ‘s/%/ /g’`”

# Create Seperate directory for each DB

if [ ! -e “$BACKUPDIR/daily/$DB” ] # Check Daily DB Directory exists.

then

mkdir -p “$BACKUPDIR/daily/$DB”

fi

if [ ! -e “$BACKUPDIR/weekly/$DB” ] # Check Weekly DB Directory exists.

then

mkdir -p “$BACKUPDIR/weekly/$DB”

fi

# Weekly Backup

if [ $DNOW = $DOWEEKLY ]; then

echo Weekly Backup of Database \( $DB \)>>”$BACKUPDIR/$LOGFILE”

echo Rotating 5 weeks Backups…>>”$BACKUPDIR/$LOGFILE”

if [ “$W” -le 05 ];then

REMW=`expr 48 + $W`

elif [ “$W” -lt 15 ];then

REMW=0`expr $W – 5`

else

REMW=`expr $W – 5`

fi

eval rm -fv “$BACKUPDIR/weekly/$DB/week.$REMW.*” >> “$BACKUPDIR/$LOGFILE”

echo>>”$BACKUPDIR/$LOGFILE”

mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT “$DB” > “$BACKUPDIR/weekly/$DB/week.$W.$DATE.sql”

gzip -f “$BACKUPDIR/weekly/$DB/week.$W.$DATE.sql”

echo Backup Information for $BACKUPDIR/weekly/$DB/week.$W.$DATE.sql.gz>>”$BACKUPDIR/$LOGFILE”

gzip -l “$BACKUPDIR/weekly/$DB/week.$W.$DATE.sql.gz” >> “$BACKUPDIR/$LOGFILE”

echo ———————————————————————->>”$BACKUPDIR/$LOGFILE”

# Daily Backup

else

echo Daily Backup of Database \( $DB \)>>”$BACKUPDIR/$LOGFILE”

echo Rotating last weeks Backup…>>”$BACKUPDIR/$LOGFILE”

eval rm -fv “$BACKUPDIR/daily/$DB/*.$DOW.sql.gz” >> “$BACKUPDIR/$LOGFILE”

echo>>”$BACKUPDIR/$LOGFILE”

mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT “$DB” > “$BACKUPDIR/daily/$DB/$DATE.$DOW.sql”

gzip -f “$BACKUPDIR/daily/$DB/$DATE.$DOW.sql”

echo Backup Information for $BACKUPDIR/daily/$DB/$DATE.$DOW.sql.gz>>”$BACKUPDIR/$LOGFILE”

gzip -l “$BACKUPDIR/daily/$DB/$DATE.$DOW.sql.gz” >> “$BACKUPDIR/$LOGFILE”

echo ———————————————————————->>”$BACKUPDIR/$LOGFILE”

fi

done

echo Backup End `date`>>”$BACKUPDIR/$LOGFILE”

echo ======================================================================>>”$BACKUPDIR/$LOGFILE”

else # One backup file for all DBs

echo Backup Start `date`>>”$BACKUPDIR/$LOGFILE”

echo ======================================================================>>”$BACKUPDIR/$LOGFILE”

# Monthly Full Backup of all Databases

if [ $DOM = “01” ]; then

echo Monthly full Backup of \( $MDBNAMES \)…>>”$BACKUPDIR/$LOGFILE”

mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT $MDBNAMES > “$BACKUPDIR/monthly/$DATE.$M.all-databases.sql”

gzip -f “$BACKUPDIR/monthly/$DATE.$M.all-databases.sql”

echo>>”$BACKUPDIR/$LOGFILE”

echo Backup Information for $BACKUPDIR/monthly/$DATE.$M.all-databases.sql.gz>>”$BACKUPDIR/$LOGFILE”

gzip -l “$BACKUPDIR/monthly/$DATE.$M.all-databases.sql.gz” >> “$BACKUPDIR/$LOGFILE”

echo ———————————————————————->>”$BACKUPDIR/$LOGFILE”

fi

# Weekly Backup

if [ $DNOW = $DOWEEKLY ]; then

echo Weekly Backup of Databases \( $DBNAMES \)>>”$BACKUPDIR/$LOGFILE”

echo>>”$BACKUPDIR/$LOGFILE”

echo Rotating 5 weeks Backups…>>”$BACKUPDIR/$LOGFILE”

if [ “$W” -le 05 ];then

REMW=`expr 48 + $W`

elif [ “$W” -lt 15 ];then

REMW=0`expr $W – 5`

else

REMW=`expr $W – 5`

fi

eval rm -fv “$BACKUPDIR/weekly/week.$REMW.*” >> “$BACKUPDIR/$LOGFILE”

echo>>”$BACKUPDIR/$LOGFILE”

mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT $DBNAMES > “$BACKUPDIR/weekly/week.$W.$DATE.sql”

gzip -f “$BACKUPDIR/weekly/week.$W.$DATE.sql”

echo Backup Information for $BACKUPDIR/weekly/week.$W.$DATE.sql.gz>>”$BACKUPDIR/$LOGFILE”

gzip -l “$BACKUPDIR/weekly/week.$W.$DATE.sql.gz” >> “$BACKUPDIR/$LOGFILE”

echo ———————————————————————->>”$BACKUPDIR/$LOGFILE”

# Daily Backup

else

echo Daily Backup of Databases \( $DBNAMES \)>>”$BACKUPDIR/$LOGFILE”

echo>>”$BACKUPDIR/$LOGFILE”

echo Rotating last weeks Backup…>>”$BACKUPDIR/$LOGFILE”

eval rm -fv “$BACKUPDIR/daily/*.$DOW.sql.gz” >> “$BACKUPDIR/$LOGFILE”

echo>>”$BACKUPDIR/$LOGFILE”

mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT $DBNAMES > “$BACKUPDIR/daily/$DATE.$DOW.sql”

gzip -f “$BACKUPDIR/daily/$DATE.$DOW.sql”

echo Backup Information for $BACKUPDIR/daily/$DATE.$DOW.sql.gz>>”$BACKUPDIR/$LOGFILE”

gzip -l “$BACKUPDIR/daily/$DATE.$DOW.sql.gz” >> “$BACKUPDIR/$LOGFILE”

echo ———————————————————————->>”$BACKUPDIR/$LOGFILE”

fi

echo Backup End Time `date`>>”$BACKUPDIR/$LOGFILE”

echo ======================================================================>>”$BACKUPDIR/$LOGFILE”

fi

echo Total disk space used for backup storage..>>”$BACKUPDIR/$LOGFILE”

echo Size – Location>>”$BACKUPDIR/$LOGFILE”

echo `du -hs $BACKUPDIR`>>”$BACKUPDIR/$LOGFILE”

echo>>”$BACKUPDIR/$LOGFILE”

echo ======================================================================>>”$BACKUPDIR/$LOGFILE”

echo If you find AutoMySQLBackup valuable please make a donation at>>”$BACKUPDIR/$LOGFILE”

echo http://sourceforge.net/project/project_donations.php?group_id=101066>>”$BACKUPDIR/$LOGFILE”

echo ======================================================================>>”$BACKUPDIR/$LOGFILE”

if [ “$MAILLOG” = “yes” ]

then

cat “$BACKUPDIR/$LOGFILE” | mail -s “MySQL Backup Log for $HOST – $DATE” $MAILADDR

else

cat “$BACKUPDIR/$LOGFILE”

fi

# Clean up Logfile

eval rm -f “$BACKUPDIR/$LOGFILE”

# Run command when we’re done

if [ “$POSTBACKUP” ]

then

$POSTBACKUP

fi

exit 0

One thought on “AutoMySQLbackup

Leave a Reply

Your email address will not be published. Required fields are marked *