Bacula: Fixing huge MySQL "Log" tables

Background

Bacula, by default, writes all information on every single job to the “Log” table in the Bacula database. Each job that is run will have multiple entries in the database because each line in a job file is stored in a separate entry in the Log table, in “BLOB” format. For example, the following lines would each receive their own entry in the Log database:

30-Apr 03:00 bacula-dir JobId 428: Recycled volume "client-0122"
30-Apr 03:00 bacula-dir JobId 428: Using Device "FileStorage"
30-Apr 03:00 client-fd JobId 428: shell command: run ClientRunBeforeJob "/usr/local/sbin/backup.sh"

These log entries can add up over time, especially if you run into a situation where the storage medium is unavailable for any length of time, as Bacula will repeatedly write entries to the log file specifying that it is unable to connect to the storage point to start the backup.

Disable needless logging to the database

As it turns out, this Log table is never used internally by Bacula — it’s only purpose is for external add-on products like the BWeb management system to be able to read the log files. So if you are not using anything else to control Bacula, you can go ahead and disable sending messages to the Log table.
Edit the Bacula Director configuration file, bacula-dir.conf, and find the “Messages” section. By default, there is a “catalog” entry set to “all.” If you have a catalog entry, delete the entire catalog line. That is, make sure there is no “catalog” section defined in the Messages section of your bacula-dir.conf file. This will prevent messages from being written to the Bacula Log table in the Bacula database.

Remove existing log entries in the Bacula database to reduce the database size

As noted in the previous section, the Log table in the Bacula database is not used internally by Bacula, so unless you are using external management products that do use this table, it’s safe to empty this table to reduce the size of your database.
To do so, use a product like phpMyAdmin to empty (TRUNCATE) the table, or use the mysql command line like so:

mysql -u username -p
USE bacula;
TRUNCATE TABLE Log;

Additional Reading

The Bacula manual has this to say about the catalog directive:

Catalog
Send the message to the Catalog database. The message will be written to the table named Log and a timestamp field will also be added. This permits Job Reports and other messages to be recorded in the Catalog so that they can be accessed by reporting software. Bacula will prune the Log records associated with a Job when the Job records are pruned. Otherwise, Bacula never uses these records internally, so this destination is only used for special purpose programs (e.g. bweb).