5

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?

Improve this question
3
  • Please specify the type of database in your tags rather than just using "database".
    – Maximus Minimus
    Feb 24, 2010 at 12:44
  • The response will not be database dependent. It could apply to any database backup file.
    – benjisail
    Feb 24, 2010 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, 2010 at 20:14

4 Answers 4

Reset to default
4

Check out the script AutoMySQLBackup https://sourceforge.net/projects/automysqlbackup/

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

Improve this answer
2
  • that link seems to be broken
    – Nagaraju
    Nov 27, 2018 at 6:59
  • Thanks @Nagaraju - I have replaced with another link
    – AliGibbs
    Nov 28, 2018 at 8:33
1

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.

Improve this answer
2
  • 5
    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, 2010 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, 2010 at 20:09
1

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.

Improve this answer
0

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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .