26.05.2023

How to Backup MySQL on CentOS 8 with Bacula

We have already briefly examined what the Bakula consists of. In this tutorial, we will set up a regular MySQL database backup process using Bacula on CentOS. If you want to set up backups of a remote CentOS 8 server, read this article. After completing these settings, you can simply add the settings from this tutorial and get a backup of the MySQL database from a remote server.
In the Serverspace you can create a server with already installed app "MySQL".

Packages installation

Let's install all the necessary packages.

dnf install bacula-director bacula-storage bacula-console bacula-client

MySQL configuration

Change the default database management system to MySQL if it is not already set.

alternatives --config libbaccats.so
There are 3 programs which provide 'libbaccats.so'.
Selection Command
-----------------------------------------------
1 /usr/lib64/libbaccats-mysql.so
...

Select 1.
Connect to MySQL.

mysql -u root -p

Create a database and user for Bacula.

create database bacula;
create user bacula@localhost identified by 'password';
grant all privileges on bacula.* to bacula@'localhost';
flush privileges;
exit

Run this script to create the table structure.

/usr/libexec/bacula/make_mysql_tables -p

For the backup task, create a new user and assign it only the necessary access rights.

mysql -u root -p
create user dump@localhost identified by 'password';
grant select, lock tables on *.* to dump@localhost;
grant process on *.* to dump@localhost;
flush privileges;
exit

Preparing scripts

To make Bacula capable of creating backups of a MySQL database, you must first dump it. We will use Bakula's ability to run custom scripts before and after the backup Job. Let's create a script to create a database dump.

nano /etc/bacula/pre-mysql.sh

Insert these commands into it. Note that the dump user password must be entered after the -p parameter without a space and change database_name to the name of the target DB.

#!/bin/bash
# Preparation of the folder
rm -rf /opt/mysql-backup/
mkdir -p /opt/mysql-backup/
# Backup
mysqldump -u dump -ppassword database_name > /opt/mysql-backup/dump$(date +%Y-%m-%d_%H:%M).sql

And a script to clear disk space after backup.

nano /etc/bacula/post-mysql.sh

Insert the following lines here.

#!/bin/bash
# Clearing the folder
rm -rf /opt/mysql-backup/

Make the scripts executable.

chmod +x /etc/bacula/pre-mysql.sh
chmod +x /etc/bacula/post-mysql.sh

Bacula configuration

Open the /etc/bacula/bacula-dir.conf file and set the password for MySQL bacula user in the dbpassword parameter of the Catalog section.

Catalog {
Name = MyCatalog
dbname = "bacula"; dbuser = "bacula"; dbpassword = "password"
}

Insert the sections with MySQL backup settings here.

FileSet {
Name = MySQLFileSet
Include {
Options {
signature = MD5
}
File = /opt/mysql-backup
}}
Schedule {
Name = MySQL
Run = Full daily at 03:00
}
Job {
Name = "MySQLBackup"
JobDefs = "DefaultJob"
Enabled = yes
Level = Full
FileSet = MySQLFileSet
Schedule = MySQL
Storage = File1
ClientRunBeforeJob = "/etc/bacula/pre-mysql.sh"
ClientRunAfterJob = "/etc/bacula/post-mysql.sh"
Write Bootstrap = "/var/spool/bacula/%c.bsr"
}

Start and enable Bacula services.

systemctl enable --now bacula-dir.service
systemctl enable --now bacula-fd.service
systemctl enable --now bacula-sd.service

Now backups of the selected database will be made at the specified time.