Using logrotate to backup MySQL and PostgreSQL databases

Logrotate brings all the functions needed to keep a history of your latest database backups. It allows automatic rotation, compression, removal, and mailing of the backups.

Backup MySQL

Create a file /etc/logrotate.d/mysql-backup and logrotate will make a daily backup all your mysql databases.

/var/lib/mysql/mysql-dump.sql.bz2 {
   daily
   rotate 40
   nocompress
   extension .bz2
   postrotate
      /usr/bin/mysqldump --all-databases --routines --single-transaction > /var/lib/mysql/mysql-dump.sql
     /bin/chmod 640 /var/lib/mysql/mysql-dump.sql
     /bin/chown mysql:mysql /var/lib/mysql/mysql-dump.sql
     bzip2 -f /var/lib/mysql/mysql-dump.sql
  endscript
}

Create an empty a file so logrotate runs without an error
touch /var/lib/mysql/mysql-dump.sql.bz2

Backup PostgreSQL

Create a file /etc/logrotate.d/psql-backup and logrotate will make a daily backup all your mysql databases.

/var/lib/postgresql/8.4/psql-dump.sql.bz2 {
  daily
  rotate 40
  nocompress
  extension .bz2
  postrotate
    (su postgres -c '/usr/lib/postgresql/8.4/bin/pg_dumpall') >> /var/lib/postgresql/8.4/psql-dump.sql
    /bin/chmod 640 /var/lib/postgresql/8.4/psql-dump.sql
    /bin/chown postgres:postgres /var/lib/postgresql/8.4/psql-dump.sql
    /bin/bzip2 -f /var/lib/postgresql/8.4/psql-dump.sql
  endscript
}

Create an empty a file so logrotate runs without an error
touch /var/lib/postgresql/8.4/psql-dump.sql.bz2

Use of dates instead of file numbering

The backup files will be numbered. The higher the number, the older the file. Sometimes it's better have the date in the file name.
To allow this add following lines just before the postrotate line.

  dateext
  dateformat .%Y-%m-%d
  dateyesterday