[mythtv-users] Really nice MySQL backup script

[Andrew Davis]

FYI: mysqldump is not a very restorable solution for active databases as 
the tables aren't locked during a backup. mysqlhotcopy is much better. 
Check out the script I use on my production mysql servers...

--------------------------------------------------------------------------------------------------------

#!/bin/bash
#
# Author: Andrew Davis
# Revision History:
#	Original Script: 04-28-2005
#	Added logging logic and email notification: 04-30-2005
#       Configured for <hostname>: 9-5-2006
#	Note: for <hostname>, this runs as mysql, not root, so mysql pass is not
#	  needed
#
# Purpose: backup mysql databases in as consistent and restorable a state as
# possible
#
# Restoration is as simple as copying the files, telling mysql to use them
# then running "myisamchk -rq" and "isamchk -rq"
#
#####
#
# Variables:
#
# Path to mysql
MYSQL=/usr/bin/mysql
# Path to mysqlhotcopy
HC=/usr/bin/mysqlhotcopy
# Log file
LOG=/tmp/mysqlhotcopy.out
# mySQL root user:
MYROOT=root
# mySQL root user password:
PASS=<mysql root user password>
# PATH to backup files - where do you want to store them
STORE=/hsphere/backups/mysql/
# Number of days to keep backup files...
KEEP=6
# Set month, day, year
DATESTAMP=`date +%m-%d-%Y`

#####

echo "mySQL Backups on <hostname> started at: " > $LOG
date >> $LOG
echo "" >> $LOG
echo "*** mySQL backups are done via mysqlhotcopy" >> $LOG

# Determine all databases to backup, then hotcopy them...
for x in `$MYSQL -e "show databases;" | awk -F "|" '{print $1}' | grep -v "Database"`
do
$HC $x --noindices --allowold $STORE
done

# Next, let's append a date stamp...
cd $STORE
for y in `$MYSQL -e "show databases;" | awk -F "|" '{print $1}' | grep -v "Database"`
do
mv $y database.$y.$DATESTAMP; touch database.$y.$DATESTAMP/*
done

echo "mySQL Backups on <hostname> Completed at: " >> $LOG
date >> $LOG
echo "" >> $LOG

# Lastly, let's do some rotation and cleanup old copies...
#
# A note on find: the -mtime +<num> option should be one less then the total
# number you want to keep. ie: if you want 7 days worth of backups, you should
# set -mtime +6; for 3 days worth set -mtime +2

cd $STORE
for z in `find $STORE -mtime +$KEEP -print`
do
rm -rf $z
done

echo "All mysqlhotcopy backups of <hostname> were completed successfully" >> $LOG
cd $STORE
echo "" >> $LOG
echo "    Today's backups on <hostname>: " >> $LOG
echo "" >> $LOG
ls /hsphere/backups/mysql/*.$DATESTAMP* >> $LOG
echo "" >> $LOG
echo "" >> $LOG
echo "Output of cron job: " >> $LOG
cat /tmp/mysql_backup_cron.out >> $LOG
echo "" >> $LOG

cat $LOG | mail -s "<hostname> mySQL Backup results for $DATESTAMP" backups at mydomain.com

--------------------------------------------------------------------------------------------------------



Intense Red wrote:
>  > I found a really nice MySQL backup script that handles daily, weekly,
>  > and monthly rotations.
>
>    FWIW, here's the one that I use.  With Debian, if I want daily (weekly, 
> monthly, etc.) backups I just put the backup script into /etc/cron.daily 
> (etc.) or a similar subdirectory.
>
> - - - snip - - -
> #!/bin/sh
>
> # Dump all databases on the SQL server:
>
> USER=root
>
> # ***Edit the below variable: ***
> PASSWORD=YourMySqlRootPassword
>
> HOST=localhost
> HOSTNAME=`cat /etc/hostname`
>
> # ***Edit the below variable: ***
> # No trailing slash below!
> BACKUPDIR=/backups
>
> TIMESTAMP=`date +%y%m%d`
>
> for EachDatabase in $(echo 'SHOW DATABASES;' |
> mysql -u$USER -p$PASSWORD -h$HOST|grep -v '^Database$');
>    do
>       nice -n 10 mysqldump \
>          -u$USER -p$PASSWORD -h$HOST \
>          -Q -c -C --add-drop-table --add-locks --quick --lock-tables \
>          $EachDatabase > $BACKUPDIR/$HOSTNAME-$EachDatabase-$TIMESTAMP.sql
>       nice -n 17 bzip2 $BACKUPDIR/$HOSTNAME-$EachDatabase-$TIMESTAMP.sql
> done;
>
> - - - snip - - -
>
>
>