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).
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).
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).
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: 
Português (Portuguese (Brazil))
English
Español (Spanish)
