MySQL backups
Jump to navigation
Jump to search
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: filserver:~# 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 ---------------------------------- filserver:~# 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 ------------------------------------------- filserver:~# 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