Home / server / Questions / 116183
In Process
benjisail
Asked: 2010-02-25 03:15:51 +0800 CST

Daily Weekly and Monthly DB backup with logrotate?

  • 772

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?

backup linux mysql logrotate

4 Answers

  1. 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)

    • 4

  2. 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.

    • 1

  3. 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.

    • 1

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

    • 0