Using Logrotate with MySQL Database Backups

Posted on by Jordansphere

Scenario: I wanted to backup all the databases individually from a MySQL server and keep for x days. I set about achieving this using a custom script combined with logrotate. The server is running FreeBSD 8.x.

 

1) Firstly, the script (backup-all-dbs.sh): 

#!/usr/local/bin/bash

# backup-all-dbs.sh
# backup each mysql db into a different file, rather than one big file
# (--all-databases) - to make individual restores easier

USER="root"                                                                       # User with relevant permissions
PASSWORD="password"                                             # Use your actual password 
OUTPUTDIR="/data/DUMPS"
MYSQLDUMP="/usr/local/bin/mysqldump"
MYSQL="/usr/local/bin/mysql"

# get a list of databases

databases=`/bin/echo "show databases" | /usr/local/bin/mysql -u $USER -p$PASSWORD | grep -v Database | grep -v information_schema`

# dump each database in turn
for db in $databases; do
/bin/echo $db
$MYSQLDUMP --force --opt -u $USER -p$PASSWORD \
--databases $db > "$OUTPUTDIR/$db.bak"
done

 

2) Now we need to install/configure logrotate 

# cd /usr/ports/sysutils/logrotate
# make install clean

When installed we'll need to copy the sample config and edit anything unwanted 

# cp /usr/local/etc/logrotate.conf.sample /usr/local/etc/logrotate.conf
# vi /usr/local/etc/logrotate.conf

# see "man logrotate" for details
# rotate log files weekly
weekly

# keep 4 weeks worth of backlogs
rotate  4

# create new (empty) log files after rotating old ones
create

# uncomment this if you want your log files compressed
compress

# RPM packages drop log rotation information into this directory
include /usr/local/etc/logrotate.d

/var/log/lastlog {
monthly
rotate 1
}

 

Next you have to create the logrotate.d/ folder and make a file in that directory: 

# mkdir -p /usr/local/etc/logrotate.d
# vi /usr/local/etc/logrotate.d/mysqldumps

Then insert the following: 

/data/DUMPS/*.bak {
     daily
    rotate 7
   nocreate
   compress
   sharedscripts
   postrotate
       /home/jordansphere/backup-all-dbs.sh || true
   endscript
}

( :wq! to save and exit)

Now that is complete, all we need to do is set the logrotate command to run every evening (4am in this case) 

# vi /etc/crontab

0 4 * * * root /usr/local/sbin/logrotate -f /usr/local/etc/logrotate.conf > /dev/null 2>&1

You can run logrotate as a daemon but I have newsyslog taking care of everything else on this server so I just wanted it to run it for this particular instance.

Note: You can run /usr/local/sbin/logrotate -d /usr/local/etc/logrotate.conf to test beforehand (debug mode). -f instructs logrotate to force the rotation immediately.