Integrating Oracle and MySQL with Bacula

aleofreddi

— 1 min read

After putting your precious data into your preferred database (Oracle or MySQL if you’re here) your next worry should be how to back them up. Here Bacula comes to help as a valid opensource network enterprise backup system – but how to get it working with your database?

In this post you’ll find some scripts which I developed to integrate Oracle or MySQL seamlessly with Bacula, allowing it to:

  • Do (hot) full backup of the database
  • Do (hot) incremental backup (also known as incremental differential backup)
  • Do (hot) differential backup (also known as incremental cumulative backup)

This is achieved on MySQL using the binary logs for incremental/differential, while on Oracle using the provided RMAN utility.

You can download the scripts here:

To have them just follow this procedure:

  • Setup a directory on the database server to hold backups. This directory will be referred as BACKUP_HOME. In the example I’ve chosen /u01/backup.
  • Configure the Bacula director as follows (adjusting the configuration to your BACKUP_HOME):
# MySQL backup
ClientRunBeforeJob = "/u01/backup/bin/mysql_backup.sh -l %l"
ClientRunAfterJob = "/u01/backup/bin/mysql_backup.sh -c -l %l"

# Oracle backup
ClientRunBeforeJob = "/u01/backup/bin/oracle_backup.sh -l %l"
ClientRunAfterJob = "/u01/backup/bin/oracle_backup.sh -c -l %l"
  • (MySQL) To have incremental backups working, it’s very important to provide the mtimeonly flag in the client fileset, as shown in the following snippet:
FileSet { 
    Include { 
        Options { 
            signature = MD5 mtimeonly = yes 
        } 

        File = /u01/backup/data 
   }

   ...
  • (MySQL) Enable log updates (in /etc/my.cnf): # Uncomment the following if you want to log updates:
log-bin=mysql-bin
  • Create the $BACKUP_HOME/etc, $BACKUP_HOME/bin and $BACKUP_HOME/data directories
  1. Configure the $BACKUP_HOME/etc/mysql_backup.conf or $BACKUP_HOME/etc/oracle_backup.conf  file setting the proper values: USER="<<your username here (usually root for MySQL, sys for Oracle)>>" PASSWORD="<>"

You can the test the script with the -l [Full|Differential|Incremental] arguments. Some data should be then saved in the $BACKUP_HOME/data directory.

How to restore

  • MySQL: import the full dump via the mysql command, convert log files to SQL files (via the mysqlbinlog command) and import them in the same way (in the backup order).
  • Oracle: TODO!

Read more