How to do Database Backups with Logrotate

Example for how to create database backups with logrotate and also some notes on how to accomplish that

Daily Backup

The following configuration is meant to do daily backups from a SQL dump for 60 days for rapid restoration.

Meaning: The SQL dump itself must be done separately from that. But in the next section you find some thoughts on how to accomplish this in one process.

For longer time periods consider an additional backup which allows increments and deduplication like BorgBackup. Check out my notes on it here: BorgBackup (Debian)

Create file /etc/logrotate.d/database_backup

# daily backup for 60 days
/var/backups/mariadb/database_dump.sql { 
  rotate 60
  daily
  nocreate
  nocopy
  compress
  delaycompress
}

Notes:

Logrotate is meant for rotating logs by creating copies and altering existing logs.

As we to use Logrotate for backup of SQL dumps, we want to prevent it to change the files as follows:

nocreate and nocopy are meant to prevent Logrotate from changing the contents of the SQL dump.

delaycompress will not compress the file immediately, but with one day delay in order to prevent coping a file which is possibly in use.

compress simply compresses the file to reduce space requirements.

Daily Backup with SQL Dump (not tested successfully)

WARNING: The following informations are not tested successfully, so be careful when trying.

In theory the rotate script could also create a database by including a script in the postrotate section:

# daily backup for 7 days
/var/backups/mariadb/database_dump.sql { 
  rotate 7
  daily
  nocreate
  nocopy
  compress
  delaycompress
  postrotate
    /usr/local/bin/backup-database.sh
  endscript
}

The problem is, that it is not guaranteed, that a backup will be created. I think this happens when database_dump.sql is missing, then the postrotate section will not be executed.

Not sure if there is a prerotate option and if it would be executed if the database_dump.sql would be missing.

For how to create the database_dump.sql check out: MariaDB Cheatsheet (Debian)

Daily, Weekly, Monthly Backups (not tested successfully)

WARNING: The following informations are not tested properly.

# daily backup for 7 days
/var/backups/mariadb/database_dump.sql { 
  rotate 7
  daily
  compress
  nocopy
  nocreate
}

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

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

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/