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