Automatic MySQL Backup<\/a><\/p>\n
\nHere is the script:<\/p>\n
#!\/bin\/bash<\/p>\n
#<\/p>\n
# MySQL Backup Script<\/p>\n
# VER. 1.9 – http:\/\/sourceforge.net\/projects\/automysqlbackup\/<\/p>\n
# Copyright (c) 2002-2003 wipe_out@lycos.co.uk<\/p>\n
#<\/p>\n
# This program is free software; you can redistribute it and\/or modify<\/p>\n
# it under the terms of the GNU General Public License as published by<\/p>\n
# the Free Software Foundation; either version 2 of the License, or<\/p>\n
# (at your option) any later version.<\/p>\n
#<\/p>\n
# This program is distributed in the hope that it will be useful,<\/p>\n
# but WITHOUT ANY WARRANTY; without even the implied warranty of<\/p>\n
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the<\/p>\n
# GNU General Public License for more details.<\/p>\n
#<\/p>\n
# You should have received a copy of the GNU General Public License<\/p>\n
# along with this program; if not, write to the Free Software<\/p>\n
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA<\/p>\n
#<\/p>\n
#=====================================================================<\/p>\n
#=====================================================================<\/p>\n
# Set the following variables to your system needs<\/p>\n
# (Detailed instructions below variables)<\/p>\n
#=====================================================================<\/p>\n
# Username to access the MySQL server e.g. dbuser<\/p>\n
USERNAME=root<\/p>\n
# Username to access the MySQL server e.g. password<\/p>\n
PASSWORD=H***** (masked)<\/p>\n
# Host name (or IP address) of MySQL server e.g localhost<\/p>\n
DBHOST=localhost<\/p>\n
# List of DBNAMES for Daily\/Weekly Backup e.g. “DB1 DB2 DB3″<\/p>\n
# DBNAMES=”DB1 DB2 DB3″<\/p>\n
DBNAMES=”mov_type”<\/p>\n
# Backup directory location e.g \/backups<\/p>\n
BACKUPDIR=”\/backups”<\/p>\n
# Mail backup log? (yes or no)<\/p>\n
MAILLOG=no<\/p>\n
# Email Address to send log to? (user@domain.com)<\/p>\n
MAILADDR=”my@mail.com”<\/p>\n
# ============================================================<\/p>\n
# === ADVANCED OPTIONS ( Read the doc’s below for details )===<\/p>\n
#=============================================================<\/p>\n
# List of DBBNAMES for Monthly Backups.<\/p>\n
MDBNAMES=”mysql $DBNAMES”<\/p>\n
# List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in ” quotes)<\/p>\n
DBEXCLUDE=””<\/p>\n
# Include CREATE DATABASE in backup?<\/p>\n
CREATE_DATABASE=yes<\/p>\n
# Separate backup directory and file for each DB? (yes or no)<\/p>\n
SEPDIR=yes<\/p>\n
# Which day do you want weekly backups? (1 to 7 where 1 is Monday)<\/p>\n
DOWEEKLY=6<\/p>\n
# Command to run before backups (uncomment to use)<\/p>\n
#PREBACKUP=”\/etc\/mysql-backup-pre”<\/p>\n
# Command run after backups (uncomment to use)<\/p>\n
#POSTBACKUP=”\/etc\/mysql-backup-post”<\/p>\n
#=====================================================================<\/p>\n
# Options documantation<\/p>\n
#=====================================================================<\/p>\n
# Set USERNAME and PASSWORD of a user that has at least SELECT permission<\/p>\n
# to ALL databases.<\/p>\n
#<\/p>\n
# Set the DBHOST option to the server you wish to backup, leave the<\/p>\n
# default to backup “this server”.(to backup multiple servers make<\/p>\n
# copies of this file and set the options for that server)<\/p>\n
#<\/p>\n
# Put in the list of DBNAMES(Databases)to be backed up. If you would like<\/p>\n
# to backup ALL DBs on the server set DBNAMES=”all”.(if set to “all” then<\/p>\n
# any new DBs will automatically be backed up without needing to modify<\/p>\n
# this backup script when a new DB is created).<\/p>\n
#<\/p>\n
# If the DB you want to backup has a space in the name replace the space<\/p>\n
# with a % e.g. “data base” will become “data%base”<\/p>\n
# NOTE: Spaces in DB names may not work correctly when SEPDIR=no.<\/p>\n
#<\/p>\n
# You can change the backup storage location from \/backups to anything<\/p>\n
# you like by using the BACKUPDIR setting..<\/p>\n
#<\/p>\n
# The MAILLOG and MAILADDR options and pretty self explanitory, use<\/p>\n
# these to have the backup log mailed to you at any email address or multiple<\/p>\n
# email addresses in a space seperated list.<\/p>\n
# (this will require that you are permitted to run the “mail” program<\/p>\n
# on your server.)<\/p>\n
#<\/p>\n
# Finally copy automysqlbackup.sh to anywhere on your server and make sure<\/p>\n
# to set executable permission. You can also copy the script to<\/p>\n
# \/etc\/cron.daily to have it execute automatically every night or simply<\/p>\n
# place a symlink in \/etc\/cron.daily to the file if you wish to keep it<\/p>\n
# somwhere else.<\/p>\n
# NOTE:On Debian copy the file with no extention for it to be run<\/p>\n
# by cron e.g just name the file “automysqlbackup”<\/p>\n
#<\/p>\n
# Thats it..<\/p>\n
#<\/p>\n
#<\/p>\n
# === Advanced options doc’s ===<\/p>\n
#<\/p>\n
# The list of MDBNAMES is the DB’s to be backed up only monthly. You should<\/p>\n
# always include “mysql” in this list to backup your user\/password<\/p>\n
# information along with any other DBs that you only feel need to<\/p>\n
# be backed up monthly. (if using a hosted server then you should<\/p>\n
# probably remove “mysql” as your provider will be backing this up)<\/p>\n
# NOTE: If DBNAMES=”all” then MDBNAMES has no effect as all DBs will be backed<\/p>\n
# up anyway.<\/p>\n
#<\/p>\n
# If you set DBNAMES=”all” you can configure the option DBEXCLUDE. Other<\/p>\n
# wise this option will not be used.<\/p>\n
# This option can be used if you want to backup all dbs, but you want<\/p>\n
# exclude some of them. (eg. a db is to big).<\/p>\n
#<\/p>\n
# Set CREATE_DATABASE to “yes” (the default) if you want your SQL-Dump to create<\/p>\n
# a database with the same name as the original database when restoring.<\/p>\n
# Saying “no” here will allow your to specify the database name you want to<\/p>\n
# restore your dump into, making a copy of the database by using the dump<\/p>\n
# created with automysqlbackup.<\/p>\n
# NOTE: Not used if SEPDIR=no<\/p>\n
#<\/p>\n
# The SEPDIR option allows you to choose to have all DBs backed up to<\/p>\n
# a single file (fast restore of entire server in case of crash) or to<\/p>\n
# seperate directories for each DB (each DB can be restored seperately<\/p>\n
# in case of single DB corruption or loss).<\/p>\n
#<\/p>\n
# To set the day of the week that you would like the weekly backup to happen<\/p>\n
# set the DOWEEKLY setting, this can be a value from 1 to 7 where 1 is Monday,<\/p>\n
# The default is 6 which means that weekly backups are done on a Saturday.<\/p>\n
#<\/p>\n
# Use PREBACKUP and POSTBACKUP to specify Per and Post backup commands<\/p>\n
# or scripts to perform tasks either before or after the backup process.<\/p>\n
#<\/p>\n
#<\/p>\n
#=====================================================================<\/p>\n
# Backup Rotation..<\/p>\n
#=====================================================================<\/p>\n
#<\/p>\n
# Daily Backups are rotated weekly..<\/p>\n
# Weekly Backups are run by default on Saturday Morning when<\/p>\n
# cron.daily scripts are run…Can be changed with DOWEEKLY setting..<\/p>\n
# Weekly Backups are rotated on a 5 week cycle..<\/p>\n
# Monthly Backups are run on the 1st of the month..<\/p>\n
# Monthly Backups are NOT rotated automatically…<\/p>\n
# It may be a good idea to copy Monthly backups offline or to another<\/p>\n
# server..<\/p>\n
#<\/p>\n
#=====================================================================<\/p>\n
# Please Note!!<\/p>\n
#=====================================================================<\/p>\n
#<\/p>\n
# I take no resposibility for any data loss or corruption when using<\/p>\n
# this script..<\/p>\n
# This script will not help in the event of a hard drive crash. If a<\/p>\n
# copy of the backup has not be stored offline or on another PC..<\/p>\n
# You should copy your backups offline regularly for best protection.<\/p>\n
#<\/p>\n
# Happy backing up…<\/p>\n
#<\/p>\n
#=====================================================================<\/p>\n
# Restoring<\/p>\n
#=====================================================================<\/p>\n
# Firstly you will need to uncompress the backup file.<\/p>\n
# eg.<\/p>\n
# ungzip file.gz<\/p>\n
#<\/p>\n
# Next you will need to use the mysql client to restore the DB from the<\/p>\n
# sql file.<\/p>\n
# eg.<\/p>\n
# mysql –user=username –pass=password –host=dbserver database < \/path\/file.sql<\/p>\n
# or<\/p>\n
# mysql –user=username –pass=password –host=dbserver -e "source \/path\/file.sql" database<\/p>\n
#<\/p>\n
# NOTE: Make sure you use "” in the above command because<\/p>\n
# you are piping the file.sql to mysql and not the other way around.<\/p>\n
#<\/p>\n
# Lets hope you never have to use this.. \ud83d\ude42<\/p>\n
#<\/p>\n
#=====================================================================<\/p>\n
# Change Log<\/p>\n
#=====================================================================<\/p>\n
#<\/p>\n
# VER 1.9 – (2004-05-25)<\/p>\n
# Small bug fix to handle spaces in LOGFILE path which contains spaces (reported by Thomas von Eyben)<\/p>\n
# Updated docs to mention that Log email can be sent to multiple email addresses.<\/p>\n
# VER 1.8 – (2004-05-01)<\/p>\n
# Added option to make backups restorable to alternate database names<\/p>\n
# meaning that a copy of the database can be created (Based on patch by Rene Hoffmann)<\/p>\n
# Seperated options into standard and advanced.<\/p>\n
# Removed ” from single file dump DBMANES because it caused an error but<\/p>\n
# this means that if DB’s have spaces in the name they will not dump when SEPDIR=no.<\/p>\n
# Added -p option to mkdir commands to create multiple subdirs without error.<\/p>\n
# Added disk usage and location to the bottom of the backup report.<\/p>\n
# VER 1.7 – (2004-04-22)<\/p>\n
# Fixed an issue where weelky backups would only work correctly if server<\/p>\n
# locale was set to English (issue reported by Tom Ingberg)<\/p>\n
# used “eval” for “rm” commands to try and resolve rotation issues.<\/p>\n
# Changed name of status log so multiple scripts can be run at the same time.<\/p>\n
# VER 1.6 – (2004-03-14)<\/p>\n
# Added PREBACKUP and POSTBACKUP command functions. (patch by markpustjens)<\/p>\n
# Added support for backing up DB’s with Spaces in the name.<\/p>\n
# (patch by markpustjens)<\/p>\n
# VER 1.5 – (2004-02-24)<\/p>\n
# Added the ability to exclude DB’s when the “all” option is used.<\/p>\n
# (Patch by kampftitan)<\/p>\n
# VER 1.4 – (2004-02-02)<\/p>\n
# Project moved to Sourceforge.net<\/p>\n
# VER 1.3 – (2003-09-25)<\/p>\n
# Added support for backing up “all” databases on the server without<\/p>\n
# having to list each one seperately in the configuration.<\/p>\n
# Added DB restore instructions.<\/p>\n
# VER 1.2 – (2003-03-16)<\/p>\n
# Added server name to the backup log so logs from multiple servers<\/p>\n
# can be easily identified.<\/p>\n
# VER 1.1 – (2003-03-13)<\/p>\n
# Small Bug fix in monthly report. (Thanks Stoyanski)<\/p>\n
# Added option to email log to any email address. (Inspired by Stoyanski)<\/p>\n
# Changed Standard file name to .sh extention.<\/p>\n
# Option are set using yes and no rather than 1 or 0.<\/p>\n
# VER 1.0 – (2003-01-30)<\/p>\n
# Added the ability to have all databases backup to a single dump<\/p>\n
# file or seperate directory and file for each database.<\/p>\n
# Output is better for log keeping.<\/p>\n
# VER 0.6 – (2003-01-22)<\/p>\n
# Bug fix for daily directory (Added in VER 0.5) rotation.<\/p>\n
# VER 0.5 – (2003-01-20)<\/p>\n
# Added “daily” directory for daily backups for neatness (suggestion by Jason)<\/p>\n
# Added DBHOST option to allow backing up a remote server (Suggestion by Jason)<\/p>\n
# Added “–quote-names” option to mysqldump command.<\/p>\n
# Bug fix for handling the last and first of the year week rotation.<\/p>\n
# VER 0.4 – (2002-11-06)<\/p>\n
# Added the abaility for the script to create its own directory structure.<\/p>\n
# VER 0.3 – (2002-10-01)<\/p>\n
# Changed Naming of Weekly backups so they will show in order.<\/p>\n
# VER 0.2 – (2002-09-27)<\/p>\n
# Corrected weekly rotation logic to handle weeks 0 – 10<\/p>\n
# VER 0.1 – (2002-09-21)<\/p>\n
# Initial Release<\/p>\n
#<\/p>\n
#=====================================================================<\/p>\n
#=====================================================================<\/p>\n
#=====================================================================<\/p>\n
#<\/p>\n
# Should not need to be modified from here down!!<\/p>\n
#<\/p>\n
#=====================================================================<\/p>\n
#=====================================================================<\/p>\n
#=====================================================================<\/p>\n
#PATH=\/usr\/local\/bin:\/usr\/bin:\/bin<\/p>\n
PATH=\/usr\/local\/bin:\/usr\/bin:\/bin:\/usr\/local\/mysql\/bin<\/p>\n
DATE=`date +%Y-%m-%d`\t\t\t# Datestamp e.g 2002-09-21<\/p>\n
DOW=`date +%A`\t\t\t\t# Day of the week e.g. Monday<\/p>\n
DNOW=`date +%u`\t\t\t\t# Day number of the week 1 to 7 where 1 represents Monday<\/p>\n
DOM=`date +%d`\t\t\t\t# Date of the Month e.g. 27<\/p>\n
M=`date +%B`\t\t\t\t# Month e.g January<\/p>\n
W=`date +%V`\t\t\t\t# Week Number e.g 37<\/p>\n
VER=1.9\t\t\t\t\t# Version Number<\/p>\n
LOGFILE=\/$DBHOST-`date +%N`.log\t\t# Logfile Name<\/p>\n
OPT=”–quote-names –opt”\t\t# OPT string for use with mysqldump ( see man mysqldump )<\/p>\n
# Run command before we begin<\/p>\n
if [ “$PREBACKUP” ]<\/p>\n
then<\/p>\n
$PREBACKUP<\/p>\n
fi<\/p>\n
# Create required directories<\/p>\n
if [ ! -e “$BACKUPDIR” ]\t\t# Check Backup Directory exists.<\/p>\n
then<\/p>\n
mkdir -p “$BACKUPDIR”<\/p>\n
fi<\/p>\n
if [ ! -e “$BACKUPDIR\/daily” ]\t\t# Check Daily Directory exists.<\/p>\n
then<\/p>\n
mkdir -p “$BACKUPDIR\/daily”<\/p>\n
fi<\/p>\n
if [ ! -e “$BACKUPDIR\/weekly” ]\t\t# Check Weekly Directory exists.<\/p>\n
then<\/p>\n
mkdir -p “$BACKUPDIR\/weekly”<\/p>\n
fi<\/p>\n
if [ ! -e “$BACKUPDIR\/monthly” ]\t# Check Monthly Directory exists.<\/p>\n
then<\/p>\n
mkdir -p “$BACKUPDIR\/monthly”<\/p>\n
fi<\/p>\n
if [ “$SEPDIR” = “yes” ]; then # Check if CREATE DATABSE should be included in Dump<\/p>\n
if [ “$CREATE_DATABASE” = “no” ]; then<\/p>\n
OPT=”$OPT –no-create-db”<\/p>\n
else<\/p>\n
OPT=”$OPT –databases”<\/p>\n
fi<\/p>\n
else<\/p>\n
OPT=”$OPT –databases”<\/p>\n
fi<\/p>\n
# Hostname for LOG information<\/p>\n
if [ “$DBHOST” = “localhost” ]; then<\/p>\n
HOST=`hostname`<\/p>\n
else<\/p>\n
HOST=$DBHOST<\/p>\n
fi<\/p>\n
# If backing up all DBs on the server<\/p>\n
if [ “$DBNAMES” = “all” ]; then<\/p>\n
DBNAMES=”`mysql –user=$USERNAME –password=$PASSWORD –host=$DBHOST –batch -N -e “show databases”| sed ‘s\/ \/%\/g’`”<\/p>\n
# If DBs are excluded<\/p>\n
for exclude in $DBEXCLUDE<\/p>\n
do<\/p>\n
DBNAMES=`echo $DBNAMES | sed “s\/\\b$exclude\\b\/\/g”`<\/p>\n
done<\/p>\n
MDBNAMES=$DBNAMES<\/p>\n
fi<\/p>\n
echo ======================================================================>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo AutoMySQLBackup VER $VER>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo http:\/\/sourceforge.net\/projects\/automysqlbackup\/>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo >>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo Backup of Database Server – $HOST>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo ======================================================================>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
# Test is seperate DB backups are required<\/p>\n
if [ “$SEPDIR” = “yes” ]; then<\/p>\n
echo Backup Start Time `date`>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo ======================================================================>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
# Monthly Full Backup of all Databases<\/p>\n
if [ $DOM = “01” ]; then<\/p>\n
for MDB in $MDBNAMES<\/p>\n
do<\/p>\n
# Prepare $DB for using<\/p>\n
MDB=”`echo $MDB | sed ‘s\/%\/ \/g’`”<\/p>\n
if [ ! -e “$BACKUPDIR\/monthly\/$MDB” ]\t\t# Check Monthly DB Directory exists.<\/p>\n
then<\/p>\n
mkdir -p “$BACKUPDIR\/monthly\/$MDB”<\/p>\n
fi<\/p>\n
echo Monthly Backup of $MDB…>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT “$MDB” > “$BACKUPDIR\/monthly\/$MDB\/$DATE.$M.$MDB.sql”<\/p>\n
gzip -f “$BACKUPDIR\/monthly\/$MDB\/$DATE.$M.$MDB.sql”<\/p>\n
echo>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo Backup Information for $BACKUPDIR\/monthly\/$MDB\/$DATE.$M.$MDB.sql.gz>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
gzip -l “$BACKUPDIR\/monthly\/$MDB\/$DATE.$M.$MDB.sql.gz” >> “$BACKUPDIR\/$LOGFILE”<\/p>\n
echo ———————————————————————->>”$BACKUPDIR\/$LOGFILE”<\/p>\n
done<\/p>\n
fi<\/p>\n
for DB in $DBNAMES<\/p>\n
do<\/p>\n
# Prepare $DB for using<\/p>\n
DB=”`echo $DB | sed ‘s\/%\/ \/g’`”<\/p>\n
# Create Seperate directory for each DB<\/p>\n
if [ ! -e “$BACKUPDIR\/daily\/$DB” ]\t\t# Check Daily DB Directory exists.<\/p>\n
then<\/p>\n
mkdir -p “$BACKUPDIR\/daily\/$DB”<\/p>\n
fi<\/p>\n
if [ ! -e “$BACKUPDIR\/weekly\/$DB” ]\t\t# Check Weekly DB Directory exists.<\/p>\n
then<\/p>\n
mkdir -p “$BACKUPDIR\/weekly\/$DB”<\/p>\n
fi<\/p>\n
# Weekly Backup<\/p>\n
if [ $DNOW = $DOWEEKLY ]; then<\/p>\n
echo Weekly Backup of Database \\( $DB \\)>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo Rotating 5 weeks Backups…>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
if [ “$W” -le 05 ];then<\/p>\n
REMW=`expr 48 + $W`<\/p>\n
elif [ “$W” -lt 15 ];then<\/p>\n
REMW=0`expr $W – 5`<\/p>\n
else<\/p>\n
REMW=`expr $W – 5`<\/p>\n
fi<\/p>\n
eval rm -fv “$BACKUPDIR\/weekly\/$DB\/week.$REMW.*” >> “$BACKUPDIR\/$LOGFILE”<\/p>\n
echo>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT “$DB” > “$BACKUPDIR\/weekly\/$DB\/week.$W.$DATE.sql”<\/p>\n
gzip -f “$BACKUPDIR\/weekly\/$DB\/week.$W.$DATE.sql”<\/p>\n
echo Backup Information for $BACKUPDIR\/weekly\/$DB\/week.$W.$DATE.sql.gz>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
gzip -l “$BACKUPDIR\/weekly\/$DB\/week.$W.$DATE.sql.gz” >> “$BACKUPDIR\/$LOGFILE”<\/p>\n
echo ———————————————————————->>”$BACKUPDIR\/$LOGFILE”<\/p>\n
# Daily Backup<\/p>\n
else<\/p>\n
echo Daily Backup of Database \\( $DB \\)>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo Rotating last weeks Backup…>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
eval rm -fv “$BACKUPDIR\/daily\/$DB\/*.$DOW.sql.gz” >> “$BACKUPDIR\/$LOGFILE”<\/p>\n
echo>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT “$DB” > “$BACKUPDIR\/daily\/$DB\/$DATE.$DOW.sql”<\/p>\n
gzip -f “$BACKUPDIR\/daily\/$DB\/$DATE.$DOW.sql”<\/p>\n
echo Backup Information for $BACKUPDIR\/daily\/$DB\/$DATE.$DOW.sql.gz>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
gzip -l “$BACKUPDIR\/daily\/$DB\/$DATE.$DOW.sql.gz” >> “$BACKUPDIR\/$LOGFILE”<\/p>\n
echo ———————————————————————->>”$BACKUPDIR\/$LOGFILE”<\/p>\n
fi<\/p>\n
done<\/p>\n
echo Backup End `date`>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo ======================================================================>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
else # One backup file for all DBs<\/p>\n
echo Backup Start `date`>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo ======================================================================>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
# Monthly Full Backup of all Databases<\/p>\n
if [ $DOM = “01” ]; then<\/p>\n
echo Monthly full Backup of \\( $MDBNAMES \\)…>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT $MDBNAMES > “$BACKUPDIR\/monthly\/$DATE.$M.all-databases.sql”<\/p>\n
gzip -f “$BACKUPDIR\/monthly\/$DATE.$M.all-databases.sql”<\/p>\n
echo>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo Backup Information for $BACKUPDIR\/monthly\/$DATE.$M.all-databases.sql.gz>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
gzip -l “$BACKUPDIR\/monthly\/$DATE.$M.all-databases.sql.gz” >> “$BACKUPDIR\/$LOGFILE”<\/p>\n
echo ———————————————————————->>”$BACKUPDIR\/$LOGFILE”<\/p>\n
fi<\/p>\n
# Weekly Backup<\/p>\n
if [ $DNOW = $DOWEEKLY ]; then<\/p>\n
echo Weekly Backup of Databases \\( $DBNAMES \\)>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo Rotating 5 weeks Backups…>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
if [ “$W” -le 05 ];then<\/p>\n
REMW=`expr 48 + $W`<\/p>\n
elif [ “$W” -lt 15 ];then<\/p>\n
REMW=0`expr $W – 5`<\/p>\n
else<\/p>\n
REMW=`expr $W – 5`<\/p>\n
fi<\/p>\n
eval rm -fv “$BACKUPDIR\/weekly\/week.$REMW.*” >> “$BACKUPDIR\/$LOGFILE”<\/p>\n
echo>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT $DBNAMES > “$BACKUPDIR\/weekly\/week.$W.$DATE.sql”<\/p>\n
gzip -f “$BACKUPDIR\/weekly\/week.$W.$DATE.sql”<\/p>\n
echo Backup Information for $BACKUPDIR\/weekly\/week.$W.$DATE.sql.gz>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
gzip -l “$BACKUPDIR\/weekly\/week.$W.$DATE.sql.gz” >> “$BACKUPDIR\/$LOGFILE”<\/p>\n
echo ———————————————————————->>”$BACKUPDIR\/$LOGFILE”<\/p>\n
# Daily Backup<\/p>\n
else<\/p>\n
echo Daily Backup of Databases \\( $DBNAMES \\)>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo Rotating last weeks Backup…>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
eval rm -fv “$BACKUPDIR\/daily\/*.$DOW.sql.gz” >> “$BACKUPDIR\/$LOGFILE”<\/p>\n
echo>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT $DBNAMES > “$BACKUPDIR\/daily\/$DATE.$DOW.sql”<\/p>\n
gzip -f “$BACKUPDIR\/daily\/$DATE.$DOW.sql”<\/p>\n
echo Backup Information for $BACKUPDIR\/daily\/$DATE.$DOW.sql.gz>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
gzip -l “$BACKUPDIR\/daily\/$DATE.$DOW.sql.gz” >> “$BACKUPDIR\/$LOGFILE”<\/p>\n
echo ———————————————————————->>”$BACKUPDIR\/$LOGFILE”<\/p>\n
fi<\/p>\n
echo Backup End Time `date`>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo ======================================================================>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
fi<\/p>\n
echo Total disk space used for backup storage..>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo Size – Location>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo `du -hs $BACKUPDIR`>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo ======================================================================>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo If you find AutoMySQLBackup valuable please make a donation at>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo http:\/\/sourceforge.net\/project\/project_donations.php?group_id=101066>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
echo ======================================================================>>”$BACKUPDIR\/$LOGFILE”<\/p>\n
if [ “$MAILLOG” = “yes” ]<\/p>\n
then<\/p>\n
cat “$BACKUPDIR\/$LOGFILE” | mail -s “MySQL Backup Log for $HOST – $DATE” $MAILADDR<\/p>\n
else<\/p>\n
cat “$BACKUPDIR\/$LOGFILE”<\/p>\n
fi<\/p>\n
# Clean up Logfile<\/p>\n
eval rm -f “$BACKUPDIR\/$LOGFILE”<\/p>\n
# Run command when we’re done<\/p>\n
if [ “$POSTBACKUP” ]<\/p>\n
then<\/p>\n
$POSTBACKUP<\/p>\n
fi<\/p>\n
exit 0<\/p>\n","protected":false},"excerpt":{"rendered":"
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<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-205","post","type-post","status-publish","format-standard","hentry","category-tech"],"_links":{"self":[{"href":"https:\/\/www.fiveforks.com\/jeb\/wp-json\/wp\/v2\/posts\/205","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fiveforks.com\/jeb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fiveforks.com\/jeb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fiveforks.com\/jeb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fiveforks.com\/jeb\/wp-json\/wp\/v2\/comments?post=205"}],"version-history":[{"count":0,"href":"https:\/\/www.fiveforks.com\/jeb\/wp-json\/wp\/v2\/posts\/205\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.fiveforks.com\/jeb\/wp-json\/wp\/v2\/media?parent=205"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fiveforks.com\/jeb\/wp-json\/wp\/v2\/categories?post=205"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fiveforks.com\/jeb\/wp-json\/wp\/v2\/tags?post=205"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}