Rotating Daily and Weekly MySQL Dump Cron

mysqlbup.cron

# mysqlbup.cron by josephdpurcell
#
# This cron will export all databases every day at 4am. Those exports are
# automatically rotating from servername.com.day0 to servername.com.day6 so you
# always have a bup of the last 7 days of backups.
#
# Also, this cron will export all databases every week for the last 4 weeks at
# 2am on Sunday. Those exports are automatically rotating from
# servername.com.week0 to servername.com.week3 so you always have a bup of the
# last 4 weeks of backups.
#
# rotating past 7 days mysql bup
0 4 * * * mysqldump -u mysqluser -pmysqlpasswd --all-databases > /opt/local/bup/sql/server-name.com.day$(date +\%w).sql
# rotating past 4 weeks mysql bup
0 2 * * 0 mysqldump -u mysqluser -pmysqlpasswd --all-databases > /opt/local/bup/sql/server-name.com.week$(expr $(date +\%U) \% 4).sql

Thanks! I've modified it a little, trying to setup:

• daily backup on Monday to Saturday
• monthly backup on first Sunday of the month
• weekly backups on every non-first Sunday of the month
• aiming to have just one backup each day, but have one every day, and always running at the same time (4AM)

Unfortunately, for some reason my weekly job keeps running on other days as well, I don't know why, maybe my timezone or regional settings :P Otherwise, general idea should be ok.

# backup database
# mysql backup - rotating Monday-to-Saturday at 4AM - files: dump-day-1.gz to dump-day-6.gz
0 4 * * 1-6 mysqldump --all-databases | gzip > /mnt/mysqldump/dump-day-$(date +\%w).gz
# mysql backup - rotating last 4 weeks at 4AM all Sundays except first of month - files: dump-week-0.gz to dump-week-5.gz
# minute=0 hour=4 day=8-31 month=any/all day=0/Sunday
0 4 8-31 * 0 mysqldump --all-databases | gzip > /mnt/mysqldump/dump-week-$(expr $(date +\%U) \% 4).gz
# mysql backup - rotating 12 months of the year at 4AM only on first Sunday of the month - files: dump-month-Jan.gz to dump-month-Dec.gz
0 4 1-7 * 0 mysqldump --all-databases | gzip > /mnt/mysqldump/dump-month-$(date +\%b).gz

Hope it helps someone! And if you know where I've made a mistake let me know ;D