MySQL backups

Edit to set the weeklys using (install dateutils package first!) dateutils.dconv today -f %c (returns week of month number) and then edit monthly to not delete weekly NB Need to run this command a few times to see if it doesn't just return day of the week.

 set up a user 

GRANT SELECT ON *.* TO backup@localhost IDENTIFIED BY 'password';

3 scripts for backing up:

tripraid:~# cat /export/home/scripts/mysqldatasnapdaily.sh
#!/bin/sh

#################################################################
# Environment Variables                                         #
#################################################################
PATH=/sbin:/bin:/usr/bin:/usr/sbin

SNAPSHOT_DIR=/export/mysql/data
STORE_DIR=/export/mysql/store
MYSQLDUMP=`which mysqldump`
TSTAMPD=`date +%u`
TMPFILES=/tmp/sqlbackups.*

#################################################################
# MAIN Daily Backup Script                                      #
#################################################################
#/bin/mount /export/mysqldatasnap

# Make the rotating day backup
$MYSQLDUMP -A -u backup -ppassword > $STORE_DIR/daily/mysqldump-$TSTAMPD

/bin/gzip -f  $STORE_DIR/daily/mysqldump-$TSTAMPD
/bin/tar czf $STORE_DIR/daily/rawdb-$TSTAMPD.tgz $SNAPSHOT_DIR/* 2> /dev/null
#/bin/umount /export/mysqldatasnap

cp -p /export/home/docu/MoreIn/Lotus/triporg.OR6 $STORE_DIR/daily/triporg.OR6-$TSTAMPD

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

tripraid:~# cat /export/home/scripts/mysqldatasnapweekly.sh
#!/bin/sh

#################################################################
# Environment Variables                                         #
#################################################################
PATH=/sbin:/bin:/usr/bin:/usr/sbin

SNAPSHOT_DIR=/export/mysql/data
STORE_DIR=/export/mysql/store
MYSQLDUMP=`which mysqldump`
TSTAMPW=`date +%V`

#################################################################
# MAIN Weekly                                                   #
#################################################################
#/bin/mount /export/mysqldatasnap

# Make week backups
$MYSQLDUMP -A -u backup -ppassword > $STORE_DIR/weekly/mysqldump-$TSTAMPW
/bin/gzip -f $STORE_DIR/weekly/mysqldump-$TSTAMPW
/bin/tar czf $STORE_DIR/weekly/rawdb-$TSTAMPW.tgz $SNAPSHOT_DIR/ 2> /dev/null
#/bin/umount /export/mysqldatasnap

cp -p /export/home/docu/MoreIn/Lotus/triporg.OR6 $STORE_DIR/weekly/triporg.OR6-$TSTAMPW
cp -p /export/home/people/Robin/migratie.xls $STORE_DIR/weekly/migratie-$TSTAMPW

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

tripraid:~# cat /export/home/scripts/mysqldatasnapmonthly.sh
#!/bin/sh

#################################################################
# Environment Variables                                         #
#################################################################
PATH=/sbin:/bin:/usr/bin:/usr/sbin

SNAPSHOT_DIR=/export/mysql/data
STORE_DIR=/export/mysql/store
MYSQLDUMP=`which mysqldump`
TSTAMPM=`date +%Y"_"%m`

#################################################################
# MAIN Monthly                                                  #
#################################################################
#/bin/mount /export/mysqldatasnap

# Make month backups and delete week backups
$MYSQLDUMP -A -u backup -ppassword > $STORE_DIR/monthly/mysqldump-$TSTAMPM
/bin/gzip -f $STORE_DIR/monthly/mysqldump-$TSTAMPM
/bin/tar czf $STORE_DIR/monthly/rawdb-$TSTAMPM.tgz $SNAPSHOT_DIR/ 2> /dev/null
/bin/rm $STORE_DIR/weekly/*
#/bin/umount /export/mysqldatasnap

cp -p /export/home/docu/MoreIn/Lotus/triporg.OR6 $STORE_DIR/monthly/triporg.OR6-$TSTAMPM

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

Add to /etc/crontab

35 0 * * 0      root    /usr/bin/myisamchk --fast --silent /export/mysql/data/*/*.MYI
00 2 * * *      root    /export/home/scripts/mysqldatasnapdaily.sh
00 3 * * 7      root    /export/home/scripts/mysqldatasnapweekly.sh
00 4 1 * *      root    /export/home/scripts/mysqldatasnapmonthly.sh