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