MySQL backup plugin#

This Bacularis plugin enables to backup and restore the MySQL databases.

Base functions#

The Bacularis MySQL backup plugin provides the following functions:

SQL logical backups

Binary physical online backups

Point-in-Time Recovery (PITR)

Database system data backups (users, grants …)

Binary log backups

Special database server files backup

Backup methods#

This plugin supports four MySQL database backup methods. They are:

Dump backup method - it is also called logical database backup method. It backs up data in the SQL dump form. There are available the following three types of the dump backup method:

all databases in one dump - this is a way that that saves all databases in one consistent between databases dump file.

all databases in separate dumps - this way stores each backed up database in separate SQL dump file. With this method there is possible to restore single selected database(s).

selected databases dumps - in this way the Bacularis administrator defines which databases should be backed up. This method enables restore single selected database(s).

Binary backup method - it is also called physical online backup method. This method backs up all databases on the database file level.

Binary log backup method - this method backs up the database binary logs. The backed up log files are flushed and consistent.

File backup method - this method enables to include to the database backup also other files such as the MySQL datatabase configuration files or other important files that can be useful for restoring the database server from scratch.

All the backup methods can be used separately or they can be mixed together. For example, there is possible to configure the Dump backup method in one job. There is also possible to use the Dump backup method, Binary log backup method and for example File backup method in one backup job and so on.

General requirements#

The MySQL database backup plugin is available in Bacularis since version 4.5.0. To use it there is required to have:

Bacularis used as the web interface in version 4.5.0 or greater.

Bacularis used as API in version 4.5.0 or greater installed on the Bacula file daemon host where is the database server to backup.

The MySQL tools on the host with the Bacula file daemon:

mysqldump

mysql

mysqlbinlog

Percona XtraBackup tools:

xtrabackup

xbstream

Bacula file daemon with the Bacula bpipe plugin installed. In most cases it is installed by default with the Bacula file daemon.

If the Bacularis web interface is used on the same host as the database server to backup then only this one Bacularis instance is needed.

Bacula BPIPE plugin#

Please check if your Bacula client that will back up the databases has the bpipe plugin installed. It can be done by the status client=xxx Bconsole command or directly in the Bacularis as below:

*status client=debian-fd
Connecting to Client debian-fd at localhost:9102

debian-fd Version: 9.6.7 (10 December 2020)  x86_64-pc-linux-gnu debian bookworm/sid
Daemon started 02-gru-24 12:34. Jobs: run=0 running=0.
Heap: heap=266,240 smbytes=27,253 max_bytes=27,270 bufs=101 max_bufs=101
Sizes: boffset_t=8 size_t=8 debug=0 trace=0 mode=0,0 bwlimit=0kB/s
Plugin: bpipe-fd.so

Bacularis bpipe plugin on the status client status

IMPORTANT NOTE: In Bacula version 15.0.x the bpipe plugin has a bug that causes after finishing backup the Bacula file daemon crash with the segfault error. Read here how to solve it: File daemon crashes after running Bacularis plugins (MySQL, MariaDB…).

Backup#

Before using the MySQL plugin there is needed to prepare the plugin command in the FileSet resource. This action consists of two smaller actions:

create MySQL backup plugin configuration

use this configuration in the Bacula FileSet Plugin directive

Creating the plugin configuration can be done in the same form and place as all other Bacularis plugin configurations. On the Bacularis web interface it is in the Application settings page (gear wheel icon at the top left) on the Plugins tab. It can be also done directly in the FileSet resource configuration at the Add plugin link. In the second case creating plugin configuration and using it in the Bacula FileSet happens in one step.

The window to create plugin configuration and to use it you see after clicking at the Add plugin menu item as you can see on the image below.

Bacularis plugin configuration in the FileSet

After clickcing the Add plugin item a new window will appear (as below) where you will be able to configure and use MySQL plugin. Please choose option second or third depending on your needs.

NOTE: After preparing the Plugin directive please don’t forget to save the FileSet resource.

Restore#

Restore to database server#

To restore the plugin backup data directly to the database server, on the restore wizard step is needed to choose option Restore to original location as shown on the image below. Restore to the database server works for the dump method backups.

Bacularis plugin data restore directly to the database server

Restore in file form#

To restore the plugin backup data in the file form, on the restore wizard step is needed to choose option Restore to a given directory that is selected by default.

Bacularis MySQL plugin data restore in the file form

Dump backup method#

Levels#

In this MySQL database backup method there are supported the following three backup levels:

Full - it backs up all data

Incremental - it backs up changes since the last full, incremental or differential backup

Differential - it backs up changes since the last full level backup

Requirements#

Before using this method please make sure that on the host with the file daemon you have the following tools installed:

Bacularis version 4.5.0 or greater

mysqldump

mysql MySQL CLI tool

mysqlbinlog

enabled binary log in the database server with logs stored in a separated directory

Bacula bpipe plugin in the Bacula file daemon

Prepare environment#

Before using the MySQL backup plugin please make sure that your environment meets all requirements.

Binary log

To enable binary logging, you need to create a separated directory for that. For example:

mkdir -m 750 /var/lib/mysql-log
chown mysql:mysql /var/lib/mysql-log

After that in the MySQL configuration file (ex. /etc/my.cnf) we can add a directive to enable binary logging and to set the binary log format:

[mysqld]
log_bin=/var/lib/mysql-log/binary-log
binlog_format=ROW

Please note that the log format directive (binlog_format) should be set to the row-based logging (binlog_format=ROW) or to mixed logging (binlog_format=MIXED).

After adding the binary logging the database server needs to be restarted.

Dump all databases separately#

Description#

This dump backup type gets all the database list from the MySQL database server and backs them up separately. Each database is in a separate SQL dump file. This backup provides data consistency in each database (for InnoDB storage engine) but not between databases because databases are not backed up in single transaction but one by one.

The main advantage of this backup type is capability doing single database restore without any need to restoring all databases. The restore can be done directly to the database server (with the same or a new database name) or to local filesystem as file restore.

On full backup to the backed up databases are added also dumps with the MySQL system data like users or user permissions. The system information can be useful on restoring all the database server from scratch or for preparing another database server instance (like a clone) that bases on the original one. Additionally the full backup includes in each database schema the routines, event definitions and triggers.

On incremental or differential backup there are backed up the databases in the incremental or differential way and without including the system data.

Backup data structure#

Below you can see example data structure for this dump backup type that can be restored to the database server or in form of the file restore.

/
/#MySQLBackup/
/#MySQLBackup/SERVER1/
/#MySQLBackup/SERVER1/main/
/#MySQLBackup/SERVER1/main/.SYSTEM/users.sql
/#MySQLBackup/SERVER1/main/databaseAAA/
/#MySQLBackup/SERVER1/main/databaseAAA/schema.sql
/#MySQLBackup/SERVER1/main/databaseAAA/sql-data-45795-2024-12-01_141120-Full.sql
/#MySQLBackup/SERVER1/main/databaseAAA/sql-data-45796-2024-12-02-141329-Differential.sql
/#MySQLBackup/SERVER1/main/databaseAAA/sql-data-45797-2024-12-03-152210-Incremental.sql
/#MySQLBackup/SERVER1/main/databaseAAA/sql-data-45798-2024-12-04-141510-Incremental.sql
/#MySQLBackup/SERVER1/main/databaseBBB/
/#MySQLBackup/SERVER1/main/databaseBBB/schema.sql
/#MySQLBackup/SERVER1/main/databaseBBB/sql-data-45795-2024-12-01_141120-Full.sql
/#MySQLBackup/SERVER1/main/databaseBBB/sql-data-45796-2024-12-02-141329-Differential.sql
/#MySQLBackup/SERVER1/main/databaseBBB/sql-data-45797-2024-12-03-152210-Incremental.sql
/#MySQLBackup/SERVER1/main/databaseBBB/sql-data-45798-2024-12-04-141510-Incremental.sql

The file descriptions are the following:

schema.sql - is created on full backup and contains the database table schema without database data. With this schema file each backed up database structure can be recreated. In case restore the schema.sql is automatically restored first both for restoring directly to the database and for restoring on the local file system.

sql-data-*-Full.sql - it contains the full backup database data (without schema). It is restored after restorng schema.sql to newly recreated database tables.

sql-data-*-Incremental.sql it contains the incremental backup data. It is restored after restoring schema.sql and sql-data-*-Full.sql and sql-data-*-Differential.sql (if used) and older sql-data-*-Incremental.sql (if used). The order of restoring is kept automatically to the correct one from the oldest to the newest backups and with taking into account appropriate full, differential and incremental backups.

sql-data-*-Differential.sql - it contains the differential backup data. It is restored after restoring schema.sql and sql-data-*-Full.sql. The order of restoring is kept automatically to the correct one from the oldest to the newest backups and with taking into account appropriate full backup.

.SYSTEM - directory contains the database system information. It can be restored in whole on in parts depending what system data needs to be restored.

users.sql - users, roles and grants

Configuration#

Dump all databases separately - configuration parameters#
Name	Parameter	Description	Required
Enable logical dump backup method	`--dump-method`	This is an option to enable dump backup method at all. Without this option enabled none of dump backup types works.	Yes
Dump all databases separately	`--all-databases-list`	Enable dumping all databases, each in a separate dump	Yes
Add DROP TABLE before each CREATE TABLE statement	`--add-drop-table`	Add to the schema.sql DROP TABLE command before creating each table command	No
Additional dump program option	`--dump-option="--opt1=value1"`	Pass to mysqldump program additional options	No

Video guide#

Dump all databases in one dump#

Description#

This dump backup type creates one dump file with all databases. If there is used InnoDB database storage engine, then data is consistent both in each database and between the databases. Having single dump with “everything” may be useful for case restoring all databases at once (ex. disaster recovery) or if there is needed to create a new MySQL environment with the same data (clone).

On full backup to the backed up databases are added also dumps with the MySQL system data like users or user permissions. The system information can be useful on restoring all the database server from scratch or for preparing another database server instance that bases on the original one. Additionally the full backup includes in each database schema the routines, event definitions and triggers.

On incremental or differential backup there are backed up the databases in the incremental or differential way and without including the system data.

Restoring data can be done directly to the database server or to local filesystem as file restore.

Backup data structure#

Below you can see example data structure for this dump backup type that can be restored to the database server or in form of the file restore.

/
/#MySQLBackup/
/#MySQLBackup/SERVER1/
/#MySQLBackup/SERVER1/main/
/#MySQLBackup/SERVER1/main/.SYSTEM/users.sql
/#MySQLBackup/SERVER1/main/all-databases/
/#MySQLBackup/SERVER1/main/all-databases/sql-all-databases-45809-2024-12-01_010956-Full.sql
/#MySQLBackup/SERVER1/main/all-databases/sql-all-databases-45810-2024-12-02_011040-Differential.sql
/#MySQLBackup/SERVER1/main/all-databases/sql-all-databases-45811-2024-12-03_011020-Incremental.sql
/#MySQLBackup/SERVER1/main/all-databases/sql-all-databases-45812-2024-12-04_011038-Incremental.sql

The file descriptions are the following:

all-databases-*-Full.sql - it contains the full backup database data with schema.

all-databases-*-Incremental.sql - it contains the incremental backup database data. On restore the order of full, incremental and differential is kept automatically. The incremental backups are restored automatically after restoring all-databases-*-Full.sql, all-databases-*-Differential.sql (if used) and older all-databases-*-Incremental.sql (if any).

all-databases-*-Differential.sql - it contains the differential backup database data. On restore the order of full, incremental and differential is kept automatically. The differential backup is restored automatically after restoring all-databases-*-Full.sql.

.SYSTEM - directory contains the database system information.

users.sql - users and grants

Configuration#

Dump all databases in one dump - configuration parameters#
Name	Parameter	Description	Required
Enable logical dump backup method	`--dump-method`	This is an option to enable dump backup method at all. Without this option enabled none of dump backup types works.	Yes
Dump all databases in one dump	`--all-databases-one-dump`	Enable dumping all databases in one dump file	Yes
Add DROP TABLE before each CREATE TABLE statement	`--add-drop-table`	Add to the schema.sql DROP TABLE command before creating each table command	No
Additional dump program option	`--dump-option="--opt1=value1"`	Pass to mysqldump program additional options	No

Video guide#

Dump only selected databases#

Description#

This backup type enables selecting databases to backup. It can be useful if there is needed to backup only some databases. This way enables restoring the single databases as well.

The MySQL system data (users, user permissions…) is not added to the backup.

On incremental or differential backup there are backed up the databases in the incremental or differential way.

Restoring data can be done directly to the database server or to local filesystem as file restore.

Backup data structure#

Below you can see example data structure for this dump backup type that can be restored to the database server or in form of the file restore.

/
/#MySQLBackup/
/#MySQLBackup/SERVER1/
/#MySQLBackup/SERVER1/main/
/#MySQLBackup/SERVER1/main/databaseAAA/
/#MySQLBackup/SERVER1/main/databaseAAA/schema.sql
/#MySQLBackup/SERVER1/main/databaseAAA/sql-data-45795-2024-12-01_141120-Full.sql
/#MySQLBackup/SERVER1/main/databaseAAA/sql-data-45796-2024-12-02-141329-Differential.sql
/#MySQLBackup/SERVER1/main/databaseAAA/sql-data-45797-2024-12-03-152210-Incremental.sql
/#MySQLBackup/SERVER1/main/databaseAAA/sql-data-45798-2024-12-04-141510-Incremental.sql
/#MySQLBackup/SERVER1/main/databaseBBB/
/#MySQLBackup/SERVER1/main/databaseBBB/schema.sql
/#MySQLBackup/SERVER1/main/databaseBBB/sql-data-45795-2024-12-01_141120-Full.sql
/#MySQLBackup/SERVER1/main/databaseBBB/sql-data-45796-2024-12-02-141329-Differential.sql
/#MySQLBackup/SERVER1/main/databaseBBB/sql-data-45797-2024-12-03-152210-Incremental.sql
/#MySQLBackup/SERVER1/main/databaseBBB/sql-data-45798-2024-12-04-141510-Incremental.sql

The file descriptions are the following:

schema.sql - is created on full backup and contains the database table schema without database data. With this schema file each backed up database structure can be recreated. In case restore the schema.sql is automatically restored first both for restoring directly to the database and for restoring on the local file system.

sql-data-*-Full.sql - it contains the full backup database data (without schema). It is restored after restorng schema.sql to newly recreated database tables.

sql-data-*-Incremental.sql it contains the incremental backup data. It is restored after restoring schema.sql and sql-data-*-Full.sql and sql-data-*-Differential.sql (if used) and older sql-data-*-Incremental.sql (if used). The order of restoring is kept automatically to the correct one from the oldest to the newest backups and with taking into account appropriate full, differential and incremental backups.

sql-data-*-Differential.sql - it contains the differential backup data. It is restored after restoring schema.sql and sql-data-*-Full.sql. The order of restoring is kept automatically to the correct one from the oldest to the newest backups and with taking into account appropriate full backup.

Configuration#

Dump only selected databases - configuration parameters#
Name	Parameter	Description	Required
Enable logical dump backup method	`--dump-method`	This is an option to enable dump backup method at all. Without this option enabled none of dump backup types works.	Yes
Databases to backup (comma separated)	`--databases="dbX,dbY,dbZ"`	Enable dumping selected databases each in a separate dump.	Yes
Add DROP TABLE before each CREATE TABLE statement	`--add-drop-table`	Add to the schema.sql DROP TABLE command before creating each table command	No
Additional dump program option	`--dump-option="--opt1=value1"`	Pass to mysqldump program additional options	No

Binary backup method#

Description#

The binary backup method does consistent online backup all the MySQL database server. It is fast both for backup and restore but it takes more space than the dump backup.

Because of the nature of this backup method, it makes unavailable restoring single databases. There can be backed up all the database server in the safe physical way.

The Bacularis MySQL plugin during backup does the online full or incremental backup to the selected directory and it sends it using the Bacula file daemon to storing by the storage daemon. The backup files on the file daemon side can be kept or they can be removed after finishing backup job.

Levels#

In this MySQL database backup method there are supported the following two backup levels:

Full - it backs up all data

Incremental - it backs up changes since the last full or last incremental backup

Requirements#

Before using this method please make sure that on the host with the file daemon you have the following tools installed:

Bacularis version 4.5.0 or greater

xtrabackup - Percona XtraBackup backup tool

xbstream - Percona XtraBackup serialize/deserialize files tool

Bacula bpipe plugin in the Bacula file daemon

Prepare environment#

Before using the MySQL backup plugin please make sure that your environment meets all requirements.

Percona XtraBackup tools

To the binary backup method are used the Percona XtraBackup tools. You need to install them before using the binary backup method. The install instruction you can find here:

for RPM-based systems: https://docs.percona.com/percona-xtrabackup/8.0/yum-repo.html

for DEB-based systems: https://docs.percona.com/percona-xtrabackup/8.0/apt-repo.html

Binary backup path

The binary backup method uses a separate directory to store backup files locally on the file daemon before sending them to the storage daemon. This is the path defined in the option Physical binary backup base path (parameter --binary-backup-path). This path does not need to be created before backup (it will be created automatically if it does not exist), but please make sure that you have enough space on the file daemon host to store the backup files. For example if directory with your database files take 50GB, then this directory should be able to store about 50GB for the full backup.

After finishing the binary backup, this directory is no longer needed. It can be kept or it can be removed for example in the Bacula Job Runscript configured with option RunsWhen = After. Here you can see a sample:

Job {
   Name = "Binary backup ABC"
   ...other directives.
   Runscript {
       RunsWhen = After
       RunsOnClient = Yes
       Command = "command_or_script_to_cleanup"
    }
}

Backup data structure#

Below you can see example data structure for this binary backup method that can be restored in form of the file restore.

/
/#MySQLBackup/
/#MySQLBackup/SERVER1/
/#MySQLBackup/SERVER1/binary-data-45830-2024-12-01_085315-Full.xb
/#MySQLBackup/SERVER1/binary-data-45831-2024-12-02_085342-Incremental.xb
/#MySQLBackup/SERVER1/binary-data-45832-2024-12-03_085408-Incremental.xb

The file descriptions are the following:

binary-data-*-Full.xb - it contains the full binary backup data in the XBSTREAM format. If there is used the --prepare-backup parameter, this file contains the database files after doing the prepare action.

binary-data-*-Incremental.xb - it contains the incremental binary backup data in the XBSTREAM format. The incremental data is taken from the position where the latest full or incremental backup finished working. The ‘prepare’ action is not realized for the incremental backups and needs to be done manually in the restore time.

Configuration#

Binary backup - configuration parameters#
Name	Parameter	Description	Required
Enable physical binary backup method	`--binary-method`	This is an option to enable binary backup method at all. Without this option enabled binary backup will not work.	Yes
Physical binary backup base path	`--binary-backup-path="/some/path"`	It defines a directory inside which will be stored backup files on the file daemon host before sending them to the storage daemon	Yes
Run prepare action on backup	`--prepare-backup`	Run ‘prepare’ action on the backup files before sending them to the storage daemon. It saves time on restore. This option works only for full backup level.	No

Video guide#

Performing restore#

To restore the binary backup data there is needed to do a file restore the backed up files to a place on the destination host. In Bacularis restore there can be choosen for that the latest incremental backup (or full backup if incremental backup was not used).

In this example the file restore is done do the directory /dir/restore:

/dir/restore/
/dir/restore/#MySQLBackup
/dir/restore/#MySQLBackup/SERVER2
/dir/restore/#MySQLBackup/SERVER2/binary-data-45830-2024-12-03_085315-Full.xb
/dir/restore/#MySQLBackup/SERVER2/binary-data-45831-2024-12-03_085342-Incremental.xb
/dir/restore/#MySQLBackup/SERVER2/binary-data-45832-2024-12-03_085408-Incremental.xb

The restore prodcedure can look as following:

# Go to restore directory
cd /dir/restore/#MySQLBackup/SERVER2

# Create directories to extract the full and incremental backups
mkdir full1 inc1 inc2

# Extract XBSTREAM files to appropriate directories.
xbstream -C full1 -x < binary-data-45830-2024-12-03_085315-Full.xb
xbstream -C inc1 -x < binary-data-45831-2024-12-03_085342-Incremental.xb
xbstream -C inc2 -x < binary-data-45832-2024-12-03_085408-Incremental.xb

# Run prepare action on the full backup if it was not done during backup
xtrabackup --prepare --apply-log-only --target-dir=full1

# NOTE: --apply-log-only paramter should be used when merging the incremental backups except the last one.

# Run prepare action on the incremental backups. NOTE: The backup order has meaning.
xtrabackup --prepare --apply-log-only --target-dir=full1 --incremental-dir=inc1
xtrabackup --prepare --target-dir=full1 --incremental-dir=inc2

# Stop the MySQL server if it is running
systemctl stop mysqld

# Move the original database files somehwere.
mv /var/lib/mysql /var/lib/mysql-old

# Create empty directory for the MySQL server data
mkdir /var/lib/mysql

# NOTE: If you use binary logging to a separate directory, please make sure that this directory exists and is empty

# Run restore to the original path (default /var/lib/mysql)
xtrabackup --copy-back --target-dir=full1

# Set proper ownership to MySQL data directory
chown -R mysql:mysql /var/lib/mysql

# If you use the binary log set the ownership for that as well
chown -R mysql:mysql /var/lib/mysql-log

# NOTE: If you use SELinux, please make sure that files have the correct SELinux context settings

# Run the MySQL server
systemctl start mysqld

Binary log backup method#

Description#

With this backup method are backed up the binary log files. All backed up files are flushed before backing them up.

This method is useful if there is needed to have a binary log backup in the binary form, ex. for the Point-in-Time Recovery (PITR). It can be also used together with the PITR compatible all databases backup done using the dump method in one dump file or binary backup method.

This method supports backup the binary log files from the dedicated directory for storing the binary logs.

Levels#

In this backup method there are supported the following two backup levels:

Full - it backs up all data

Incremental - it backs up changes since the last full or last incremental backup

Requirements#

Before using this method please make sure that on the host with the file daemon you have the following tools installed:

Bacularis version 4.5.0 or greater

mysql - MySQL CLI tool

tar - archiving utility

Bacula bpipe plugin in the Bacula file daemon

Prepare environment#

Before using the MySQL backup plugin please make sure that your environment meets all requirements.

Because this method expects that the MySQL stores binary log files in a separate directory, you need to know this directory path to provide it to the Bacularis MySQL plugin configuration.

Backup data structure#

Below you can see example data structure for this binary log backup method that can be restored in form of the file restore. In this example the binary log is stored in /var/lib/mysql-log directory.

/
/var
/var/lib
/var/lib/mysql-log-45856-2024-12-01_123426-Full
/var/lib/mysql-log-45856-2024-12-02_123120-Incremental
/var/lib/mysql-log-45856-2024-12-03_123326-Incremental

The file descriptions are the following:

mysql-log-*-Full - it contains the flushed all binary log files.

mysql-log-*-Incremental - it contains the flushed binary log files since time last full or incremental backup.

Please note that during restoring the file structure is automatically unpacked to form like below:

/
/var
/var/lib
/var/lib/mysql-log/
/var/lib/mysql-log/binary-log.000001
/var/lib/mysql-log/binary-log.000002
/var/lib/mysql-log/binary-log.000003
/var/lib/mysql-log/binary-log.000004
/var/lib/mysql-log/binary-log.index

The file descriptions are the following:

binary-log.00000* - it contains the flushed binary log backup files. With using them there is possible to perform the Point-in-Time Recovery (PITR) if we have a consistent and PITR compatible all databases backup (dump or binary)

binary-log.index - it contains index of all backed up binary log files plus one current log file that has been created after flushing logs during the backup process.

NOTE: In the Bacularis restore wizard the single binary log files are not displayed but there are displayed only the full and incremental archives.

Configuration#

Binary log backup - configuration parameters#
Name	Parameter	Description	Required
Enable binary log backup method	`--binlog-method`	This is an option to enable binary log backup method at all. Without this option enabled the binary log backup will not work.	Yes
Binary log path to backup	`--binlog-path="/var/lib/mysql-log"`	The binary log files path to backup	Yes

Performing restore#

Restoring the binary log files is realized in the same way as every file restore to a directory.

Point-in-Time Recovery#

Once the files are restored, there can be performed the Point-in-Time Recovery (PITR). In the example below we assume that the full all database backup has been already restored. Now there is needed to add to the restored database server data since point where the all databases backup ended until point where we would like to finish the recovery.

In the PITR restore there are used: mysqlbinlog tool, the binary log position coordinates and binary log file names.

Finding the binary log file and start position of the all databases backups depends on the backup method. For the dump method, the binary log coordinates can be found in the dump file. In the example below it is the log file binary-log.000599 and the log position 387.

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binary-log.000599', MASTER_LOG_POS=387;

For the binary log method, the log file name and the position coordinates we can find in the extracted XBSTREAM file in file xtrabackup_binlog_info. Here the log file is binary-log.000589 and the log position is 387.

cat xtrabackup_binlog_info
binary-log.000589    387     0-1-4487

Once we have the log file name and the start position, we can run:

mysqlbinlog --start-position 387 binary-log.000589

In the output we can see in the SQL form all commands recorded in this log. We can change the log file to the next files to see more SQL commands from the time after the previous log.

mysqlbinlog binary-log.000590
mysqlbinlog binary-log.000591

It the output we can see for example:

# at 281109
#241203 15:14:51 server id 1  end_log_pos 281151 CRC32 0xca459932    GTID 0-1-12193 trans
 /*!100001 SET @@session.gtid_seq_no=12193*//*!*/;
START TRANSACTION
/*!*/;
# at 281151
#241203 15:14:51 server id 1  end_log_pos 281183 CRC32 0x91b8a462    Intvar
SET INSERT_ID=2024/*!*/;
# at 281183
#241203 15:14:51 server id 1  end_log_pos 281293 CRC32 0x4f81074f    Query   thread_id=7818  exec_time=0     error_code=0    xid=0
SET TIMESTAMP=1733235291/*!*/;
INSERT INTO proba VALUES(NULL, '2024')
/*!*/;
# at 281293
#241203 15:14:51 server id 1  end_log_pos 281324 CRC32 0x6852d541    Xid = 15672
COMMIT/*!*/;

The end position for each log event are in string end_log_pos like this end_log_pos 281324 that will end the recovery on the last command COMMIT that commits these SQL commands:

SET INSERT_ID=2024
SET TIMESTAMP=1733235291
INSERT INTO proba VALUES(NULL, '2024')

Once we find the SQL command on which we would like to stop the recovery, we can save all SQL output to a file and after apply it to the MySQL database server.

# prepare logs
mysqlbinlog --start-position 387 binary-log.000589 > /tmp/pitr.sql
mysqlbinlog binary-log.000590 >> /tmp/pitr.sql
mysqlbinlog --stop-position 281324 binary-log.000591 >> /tmp/pitr.sql

# apply everything
mysql -u root -p -e "source /tmp/pitr.sql"

File backup method#

Description#

This backup method is for including in the backups realized by other backup methods important files for the MySQL server that can be crucial in the restore (disaster recovery). Using the file backup method there can be included for example the MySQL configuration files.

Levels#

In this backup method there is supported only the Full backup level that backs up all the files in the Full backup.

Requirements#

Before using this method please make sure that on the host with the file daemon you have the following tools installed:

Bacularis version 4.5.0 or greater

tar - archiving utility

Bacula bpipe plugin in the Bacula file daemon

Backup data structure#

Below you can see example data structure for this file backup method that can be restored in form of the file restore. In this example backed up are: /etc/my.cnf.d directory and /etc/my.cnf file.

/
/etc/
/etc/my.cnf-45871-2024-12-03_154141-Full
/etc/my.cnf.d-45871-2024-12-03_154141-Full

The file descriptions are the following:

my.cnf-*-Full - it contains the /etc/my.cnf file.

my.cnf.d-*-Full - it contains the /etc/my.cnf.d directory content

Please note that during restoring the file structure is automatically unpacked to form like below:

/
/etc/
/etc/my.cnf
/etc/my.cnf.d/client.cnf
/etc/my.cnf.d/mysql-server.cnf
/etc/my.cnf.d/spider.cnf
...other directory files

MySQL backup plugin

Contents

MySQL backup plugin#

Base functions#

Backup methods#

General requirements#

Bacula BPIPE plugin#

Backup#

Restore#

Restore to database server#

Restore in file form#

Dump backup method#

Levels#

Requirements#

Prepare environment#

Dump all databases separately#

Description#

Backup data structure#

Configuration#

Video guide#

Dump all databases in one dump#

Description#

Backup data structure#

Configuration#

Video guide#

Dump only selected databases#

Description#

Backup data structure#

Configuration#

Binary backup method#

Description#

Levels#

Requirements#

Prepare environment#

Backup data structure#

Configuration#

Video guide#

Performing restore#

Binary log backup method#

Description#

Levels#

Requirements#

Prepare environment#

Backup data structure#

Configuration#

Performing restore#

Point-in-Time Recovery#

File backup method#

Description#

Levels#

Requirements#

Backup data structure#