MySQL backups

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

1. !/bin/sh

2. 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.*

2. MAIN Daily Backup Script #
3. /bin/mount /export/mysqldatasnap

1. 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

1. /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

1. !/bin/sh

2. 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`

2. MAIN Weekly #
3. /bin/mount /export/mysqldatasnap

1. 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

1. /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

1. !/bin/sh

2. 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`

2. MAIN Monthly #
3. /bin/mount /export/mysqldatasnap

1. 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/*

1. /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