If run daily, will keep daily snapshots for one week, weekly snapshots for one month, and monthly snapshots for one year.
You will need a database user with SELECT privileges for the given database. This example suggests to place the mysql creds in the .my.cnf file - suggest to set the permissions on this file to 600.
| #!/bin/bash | |
| ## | |
| # Create a ~/.my.cnf file with an entry for your mysqldump user and password | |
| # see sample.my.cnf | |
| ## | |
| YEAR=$(date +"%Y") | |
| MONTH=$(date +"%m") | |
| DAY=$(date +"%d") | |
| DAYOFWEEK=$(date +"%w") | |
| BASEDIR=/path/to/backups | |
| #Phase 1 - dump | |
| mysqldump --result-file=$BASEDIR/current.sql --default-character-set=utf8 --lock-tables=false --quick d_backup1 table1 table2 table3 | |
| gzip -9 $BASEDIR/current.sql | |
| ######## ALTERNATE DUMP | |
| ######## mysqldump --lock-tables=false --quick d_backup1 | gzip -9 > $BASEDIR/current.sql.gz | |
| cp $BASEDIR/current.sql.gz $BASEDIR/daily/daily$YEAR$MONTH$DAY.sql.gz | |
| #Phase 2 - monthly snapshot | |
| if [ $DAY -eq 9 ]; then | |
| cp $BASEDIR/current.sql.gz $BASEDIR/monthly/month$YEAR$MONTH$DAY.sql.gz | |
| fi | |
| #Phase 3 - weekly snapshot | |
| if [ $DAYOFWEEK -eq 1 ]; then | |
| cp $BASEDIR/current.sql.gz $BASEDIR/weekly/week$YEAR$MONTH$DAY.sql.gz | |
| fi | |
| #Phase 4 - remove old snapshots | |
| # Delete files older than 7 days in daily folder | |
| find $BASEDIR/daily/* -mtime +7 -type f -exec rm -f {} \; | |
| # Delete files older than 29 days in weekly folder | |
| find $BASEDIR/weekly/* -mtime +29 -type f -exec rm -f {} \; | |
| # Delete files older than 370 days in weekly folder | |
| find $BASEDIR/monthly/* -mtime +370 -type f -exec -f rm {} \; |
| [mysqldump] | |
| user=myusername | |
| password=mypassword |