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
- !/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