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?

natevw
  • 153
  • 1
  • 1
  • 9
benjisail
  • 1,331
  • 5
  • 19
  • 32
  • Please specify the type of database in your tags rather than just using "database". – Maximus Minimus 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 01 '10 at 20:14

4 Answers4

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)

AliGibbs
  • 2,323
  • 21
  • 34
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.

sumar
  • 2,106
  • 12
  • 12
  • 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 01 '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 01 '10 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.

Nehal Dattani
  • 581
  • 2
  • 10
0

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

Jeevan Dongre
  • 741
  • 2
  • 17
  • 33