Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am currently keeping daily backup of my database by doing a daily mysqldump and by using logrotate to keep the 7 last days of mysqldump.

I would like to improve this backup process to keep 7 daily backup, 3 weekly backups and 12 monthly backup.

I found this article which explain how to di this with logrotate : https://web.archive.org/web/20070625054821/http://hotcoding.com/os/sysadmin/35751.html

However I am using the dateext logrotate option to name my backup files so I cannot use this solution.

How can I do daily, weekly and monthly backup with logrotate and with the dateext option?

share|improve this question
    
Please specify the type of database in your tags rather than just using "database". –  Darth Satan Feb 24 '10 at 12:44
    
The response will not be database dependent. It could apply to any database backup file. –  benjisail Feb 24 '10 at 13:11
    
Yes and no. When you have database dump, you could apply logrotate. When you have set of full/differential and binlog backups (from InnoDB Hot Backup or xtrabackup), you should avoid using logrotate and create your own solution for recycle old backups. –  sumar Mar 1 '10 at 20:14

4 Answers 4

Check out the script AutoMySQLBackup http://www.debianhelp.co.uk/mysqlscript.htm

This is a great script to backup one/all databases, using mysqldump (assuming these are mysql databases)

share|improve this answer

You can add nodateext option to your database logrotate conf.

You can also put some script in postrotate section, which copy/move your daily and weekly monthly backup to new location, which will prevent from deleting it by logrotate.

Another solution is to write simple shell script to do and rotate backups and put it to cron. Some usefull commands to use in the script

date +%u - day of week
date +%d - day of month
find /path -type f -daystart -ctime +30 -exec rm -f '{}' \; - remove files older than 30 days

Why you want to have this kind of backups? For me this stategy has no sense. Mysqldump create only full database backup, so you don't weekly/monthly backups.

Daily, weekly and monthly backups are necessary, when you use full/differential/incremental backups. You need only few last backups in case of database corruption.

share|improve this answer
4  
Keeping older backups is very useful in case of database corruption or data loss that is not directly noticed. We keep monthly backups for 3 years and 6 months backups "forever". Few things hurts quite as much as finding that your oldest backup is 7 days old and somebody in accounting dropped the majority of a database 10 days ago... –  pehrs Mar 1 '10 at 12:38
1  
I agree with you, that keeping older backups may be useful. I assumed, that benjisail blindly tried to use daily/weekly/monthly backup strategy, because he read somewhere, that he should use that strategy. Backup recycle policy should be based on real business needs (sometimes also due law requirements). One week was just an example. For some kinds of data backups from last week should be more than enough (especially, where daily data growth is more than few Gigs and data lifecycle is one month). Backup retension periods should reflect data value. Not all data has equal value. –  sumar Mar 1 '10 at 20:09

Probably logrotate is not the best tool to do this.

I have used mysqldump and rsnapshot for long time to achive exactly same requirement.

Instead of mysqldump, you may also consider using xtrabackup.

share|improve this answer

Here is another hand made version of it which is much simpler and easy to configure. https://github.com/jeevandongre/backup-restore

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.