MySQL Enterprise Bacula Plugin Quick Guide

This quick guide presents techniques and strategies for MySQL and MariaDB backup with the MySQL Bacula Enterprise Plugin.

All versions from MySQL 4.0.x and superior are supported, and also the MariaDB equivalents should be.

The plugin is able to perform incremental and differential backups of the databases, and will also save log files generated during the backup for PITR recovery purposes.

The plugin supports both Dump stream and Binary stream backup techniques.

Dump stream allows to edit dumps and restore only a few database objects, such as table and schemas.

Binary stream doesn’t allow a single granular object restore, but backup and restore operations are faster.

Installation

The MySQL plugin is available as a Bacula Enterprise package for all supported platforms.

You need to install this plugin on the Client where your MySQL server resides. The Bacula client package, usually “bacula-enterprise-client” should also be installed, tools such as mysqldump and mysql should be available.

When using Binary Mode, you need to install xtrabackup tool and make sure that innobackupex and xtrabackup packages are properly installed and available in the PATH. RPMs and Debs are available on Percona Website – https://www.percona.com/doc/percona-xtrabackup/LATEST/installation/apt_repo.html.

rpm -ivh bacula-enterprise-mysql-plugin-8.10.1-1.el7.x86_64.rpm

MySQL Configuration

In order to use Point In Time Recovery feature of MySQL the log_bin feature must be enabled, but binary logging is enabled by default (the log_bin system variable is set to ON).

If it is not, you can enable the path to the log file in /etc/my.cnf. For example, log_bin=/var/lib/mysql/mysql.index.

Other exception is if you use mysqld to initialize the data directory manually by invoking it with the –initialize or –initialize-insecure option, when binary logging is disabled by default. It is possible to enable binary logging in this cases by specifying the –log-bin option to the MySQL startup command.

The procedure may differ between major MySQL versions, so we advise you to read the documentation corresponding to your version.

Dump FileSet and Plugin Configuration

As shown in Figure 1, FileSet creation can be performed using bweb (Edit Plugin – with all available options).

MySQL Enterprise Bacula Plugin Quick Guide 1

Figure 1. bweb FileSet MySQL Edit Plugin Dump Option Screen

Or text, as in the following minimum configuration template for all found databases backup:

FileSet {
  Name = FS_mysql_dump
  Include {
    Options {
      Signature = MD5
    }
  Plugin = mysql
  }
}

It is possible to use the database name with a string pattern, or use several lines to select only a few databases for backup. E.g.:

Plugin = "mysql: database=hfaria"
Plugin = "mysql: database=guru"

In this last example, a specific MySQL user is used to access the MySQL, and a table is ignored from backup:

Plugin = "mysql: user=hfaria dump_opt="--ignore-table=db_name.tbl_name""

As listed in Table 1, these are all the Dump method supported options:

Option Default Description Example
dump_opt This string will be passed to the mysqldump command dump_opt=”-X”
unix_user mysql Operating system user to use for MySQL commands user=hfaria
service MySQL server name service=main
mycnf_dir Path where MySQL my.cnf file is stored my_cnf=/tmp
use_sudo Use sudo instead to run MySQL commands (when not root) use_sudo
database Will backup on databases matching this string database=prod*
all_databases Will generate a single dump of all databases
bin_dir MySQL Binaries Location bin_dir=/usr/bin/
user root MySQL super user user=hfaria
password MySQL user password password=xxx
logbin_dir mysqld log_bin directory
encoding utf8 Character set used to dump data encoding=utf8

Table 1. MySQL Plugin Dump Method Options

Binary FileSet and Plugin Configuration

As shown in Figure 2, FileSet creation can be performed using bweb (Edit Plugin – with all available options).

MySQL Enterprise Bacula Plugin Quick Guide 2

Figure 2. bweb FileSet MySQL Edit Plugin Binary Option Screen

Or text, as in the following minimum configuration template for all found databases backup:

FileSet {
  Name = FS_mysql_dump
  Include {
    Options {
      Signature = MD5
    }
  Plugin = "mysql: mode=binary"
}

As exhibited in Table 2, the plugin supports the following options in the binary mode:

Option Default Description Example
mode=binary dump Needed to enable Binary backup
unix_user mysql MySQL Unix user unix_user=mysql
service main MySQL server information service=main
user root MySQL super user user=hfaria
password MySQL user password password=xxx
bin_dir MySQL binaries location bin_dir=/usr/bin
bin_format xbstream Binary format (tar or xbstream) bin_format=tar
config_file /etc/mysql/my.cnf Path to my.cnf mysqld configuration file /etc/mysql/my.cnf

Table 2. MySQL Plugin Binary Method Options

Situational MySQL Connection Options

If your MySQL installation is using specific connection options as TCP connection, non-standard port, or to avoid Bacula having the database user password in the plugin connection options, it is possible to create a .my.cnf file that is read by the specified mycnf_dir plugin options. E.g.:

# cat /opt/bacula/etc/.my.cnf
[client]
user=admin
password=admin1

Backup Job Creation

Create a new backup job using the MySQL machine client and the new FileSet with the desired plugin options. E.g.:

Job {
  Name = "MySQL-BIN"
  Client = laptop1-fd
  FileSet = FS_mysql
  ...
}

Backup Job Test

You can use the estimate command to verify that the MySQL plugin is well configured. It should list the list of database dumps that Bacula would backup in case of a Job execution.

* estimate listing job=my-test

Note that with the dump mode, Bacula can’t compute the dump size for databases, so it will display database size instead.

Dump Method Restore

Using the bweb Web Restore, BAT or bconsole restore file selection, it is possible to select different granular elements from the MySQL service and Databases to restore. As follows:

  • createdb.sql – Database creation script
  • schema.sql – Database schema creation script
  • data.sql – Database data
  • grants.sql – all associated database users list
  • global-grants.sql – Global MySQL users, password, and options list.
  • settings.txt – Global current variables for the MySQL server
  • my.cnf – MySQL server configuration

Dump Single Database Restore

As shown in Figure 3, to restore a single database with the Bacula Enterprise MySQL plugin, you need to select the database directory in the restore command, the selection should contain the data file (data.sql) and the database creation script (createdb.sql).

MySQL Enterprise Bacula Plugin Quick Guide 3

Figure 3. MySQL Dump Method Database Restore

When the database directory is selected, you can use the where parameter to restore the database to a new database. If you set where to a single word that contains only a..z, 0-9, . and _, Bacula will create the specified database and restore data into it.

If the where parameter is a directory (containing /), Bacula will restore all files into this directory. Doing so, you will be able to use mysql directly and restore yourself.

MySQL Users and Roles Restore

To restore roles and users to your MySQL server, you just have to select the global-grants.sql file located in /@MYSQL/<service>/global-grants.sql.

Then, using where=/ or where= the plugin will load this SQL file to your database. If some roles already exist, errors will be printed in the Job log. Note that it’s possible to restore the global-grants.sql file to a local directory, edit the file and load it with mysql to restore only a selection.

Dump Method PITR Restore

To restore data from the binary log, you must know the name and location of the current binary log files when the backup was made. This information is available in the “CHANGE MASTER” line on the top of the data.sql file.

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE=’sql-bin.000004’, MASTER_LOG_POS=2083;

This information is also printed in the Bacula job report when restoring a dump directly into a new database using where=newdb parameter.

...
Found MASTER_LOG position sql-bin.000004:2083 for "database5276"
...

Once you have this information and all log files generated between the Full backup and the point in time when you want to restore, you need to use the mysqlbinlog program.

# mysqlbinlog -j 2083 sql-bin.000004 sql-bin.000005...

This command will generate a SQL script that you can load into your restored database to run the recover process. You may want to stop the recover process in a middle of a log file, for that, mysqlbinlog provides several options such as –stop-datetime to control this behavior. Refer to the mysqlbinlog documentation for all parameters http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html.

As the output of mysqlbinlog program is a SQL script, you can also edit the script to fit your needs. For example, if the database has a new name, you will need to edit the SQL script to change database references.

# mysqlbinlog -j 2083 mysql-bin.000004 ... | 
sed ’s/use ‘orgname‘/use ‘newname‘/’ | 
mysql -u root newname

For more information on PITR with MySQL, refer to the MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html

Single Table Restore

To restore a single item such as a table, you currently need to restore the dump file to a directory and use the mysql command. E.g.:

$ sed -n -e ’/Table structure for table .mytable.$/,/Table structure for table/p’ data.sql

This sed command will extract the table structure, the index and the data from the dump.

Complete MySQL Server Restore

To restore the all databases and the server configuration, just select all files located in /@MYSQL/<service>, use replace=always and where=/.

Binary Method Restore

Complete Server Restore

Once you restored the backup content with Bacula, files using the tar format should be extracted with tar -i option. With xbstream format, you can extract data with the -x option.

% cd @MYSQL/main
% xbstream -x < all-databases.xbstream
% ls
all-databases.xbstream     ibdata1.delta           performance_schema
xtrabackup_logfile         ibdata1.meta            testdb
backup-my.cnf              xtrabackup_checkpoints  mysql
xtrabackup_binary          xtrabackup_binlog_info

When the files are uncompressed you can prepare the backup with the –apply-log option of the innobackupex tool. If you plane to apply incremental backups, you need also to use the –redo-only option.

% innobackupex --apply-log --redo-only $PWD
...
120604 02:50:02 innobackupex: completed OK!

Each incremental should be extracted in a specific directory, then they should be applied to the base data.

% mkdir incr1
% cd incr1
% xbstream -x < ../all-databases-1220202.xbstream
% cd ..
% innobackupex --apply-log --redo-only --incremental-dir=incr1 $PWD
...
120604 02:51:02 innobackupex: completed OK!
% mkdir incr2
% cd incr2
% xbstream -x < ../all-databases-1320402.xbstream
% cd ..
% innobackupex --apply-log --redo-only --incremental-dir=incr2 $PWD
...
120604 02:52:02 innobackupex: completed OK!

When the files are uncompressed you can prepare the backup with the –apply-log option of the innobackupex tool:

% innobackupex --apply-log $PWD
...
120604 02:51:02 innobackupex: completed OK!

Now the files in the local directory are ready to be used by the server. The –copy-back option will copy the prepared data back to its original location as defined by the datadir in your my.cnf. Note that you can use –defaults-file=/path/to/my.cnf to specify the my.cnf configuration file.

% innobackupex --copy-back $PWD
...
120604 02:58:44 innobackupex: completed OK!

You should check the file permissions after copying the data back. You may need to adjust them with something like:

% chown -R mysql:mysql /var/lib/mysql

Now the datadir contains the restored data. You are ready to start the server.

References

MySQL 8.0 Reference Manual – https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html

MySQL Backup Using Bacula Enterprise Edition whitepaper – http://baculaystems.com

Disponível em: pt-brPortuguês (Portuguese (Brazil))enEnglishesEspañol (Spanish)

Leave a Reply