How to do Database Backups with Logrotate

Example for how to create database backups with logrotate.

Example Configuration

The following configuration is still in testing so don't rely on this and take this with a grain of salt.

Create file /etc/logrotate.d/database_backup

# daily backup for 7 days
/var/backups/mariadb/database_dump.sql { 
  missingok
  rotate 7
  daily
#  compress
#  compresscmd gzip
#  compressext .gz
  nocopy
  nocreate
  postrotate
    /opt/backup-database.sh
  endscript
}


# weekly backup for 4 weeks
#/var/backups/mariadb/database_dump.sql.7.gz {
/var/backups/mariadb/database_dump.sql.7 {
  weekly
  missingok
  rotate 4
}

# monthly backup for 12 months
#/var/backups/mariadb/database_dump.sql.7.gz.4 {
/var/backups/mariadb/database_dump.sql.7.4 {
  monthly
  missingok
  rotate 12
}

# yearly backup for 10 years
/var/backups/mariadb/database_dump.sql.7.4.12 {
  yearly
  missingok
  rotate 10
}

Notes:

The backup-database.sh script creates a database dump with mysqldump, which is not compressed.

The configuration above is meant to do compression in the future (see commented lines), but I did not test it yet.

Sources:

https://sautter.com/blog/using-logrotate-to-backup-mysql-and-postgresql-databases/

https://betterstack.com/community/guides/logging/how-to-manage-log-files-with-logrotate-on-ubuntu-20-04/