There is a new version of the autoxtrabackup tool. In this post, I’ll provide some of the highlights available this time around.
autoxtrabackup is a tool created by PerconLabs. We’ve now put out the 1.5.0 version, and you can test it further.
Note: PerconaLabs is a repository for various open source scripts and tools created by Percona staff. Items added to the PerconaLabs repository are not official Percona products, and aren’t covered by support or services agreements.
autoxtrabackup is written in Python3 and hosted in PerconaLab (forked from Shako’s repo). Basically, this tool automates backup/prepare/copy-back actions. I want to talk about recent changes and additions.
First of all, autoxtrabackup now has a --test_mode option, intended to test XtraBackup automation process.
Here is the brief flow for this:
- Clone percona-qa repo
- Clone Percona Server for MySQL 5.6 and 5.7 from github.
- Build PS servers in debug mode.
- Get 2.3 and 2.4 versions of XtraBackup
- Generate autoxtrabackup .conf files for each version of PS and XtraBackup
- Pass different combination of options to PS start command and initialize PS servers each time with different options
- Run sysbench against each started PS server
- Take backup in cycles for each started PS + prepare
- If make_slaves is defined, then create slave1 server from this backup (i.e., copy-back to another directory and start the slave from it)
- Then take a backup, prepare and copy-back from this new slave1 to create slave2
- Run pt-table-checksum on the master to check backup consistency
I have prepared my environment, and now want to start --test_mode. Basically, it creates option combinations and passes them to the start script:
2017-11-15 22:28:21 DEBUG Starting cycle1 2017-11-15 22:28:21 DEBUG Will start MySQL with --innodb_buffer_pool_size=1G --innodb_log_file_size=1G --innodb_page_size=64K --early-plugin-load=keyring_file.so --keyring_file_data=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/mysql-keyring/keyring --log-bin=mysql-bin --log-slave-updates --server-id=1 --gtid-mode=ON --enforce-gtid-consistency --binlog-format=row
So as you see, it is starting MySQL with --innodb_buffer_pool_size=1G --innodb_log_file_size=1G --innodb_page_size=64K. In cycle2, it will likely pick --innodb_buffer_pool_size=1G --innodb_log_file_size=1G --innodb_page_size=32K, and so on. It depends what you have passed in config:
# Do not touch; this is for --test_mode, which is testing for XtraBackup itself.
[TestConf]
ps_branches=5.6 5.7
gitcmd=--recursive --depth=1 https://github.com/percona/percona-server.git
testpath=/home/shahriyar.rzaev/XB_TEST/server_dir
incremental_count=3
#make_slaves=1
xb_configs=xb_2_4_ps_5_6.conf xb_2_4_ps_5_7.conf xb_2_3_ps_5_6.conf
default_mysql_options=--log-bin=mysql-bin,--log-slave-updates,--server-id={},--gtid-mode=ON,--enforce-gtid-consistency,--binlog-format=row
mysql_options=--innodb_buffer_pool_size=1G 2G 3G,--innodb_log_file_size=1G 2G 3G,--innodb_page_size=4K 8K 16K 32K 64K
You can pass more options by changing the mysql_options in the config file. Also you can specify how many incremental backups you want by setting the incremental_count option. You can enable creating slaves from backup to test it as well, by enabling the make_slaves option. This is not recommended for daily usage. You can read more about it here: –test_mode.
For daily backup actions, I have added the --tag and --show_tags options, which can be quite useful. They help you to tag your backups. Take a full backup:
$ sudo autoxtrabackup --tag="My Full backup" -v -lf /home/shahriyar.rzaev/autoxtrabackup_2_4_5_7.log -l DEBUG --defaults_file=/home/shahriyar.rzaev/XB_TEST/server_dir/xb_2_4_ps_5_7.conf --backup
Take an incremental one:
$ autoxtrabackup --tag="First incremental backup" -v -lf /home/shahriyar.rzaev/autoxtrabackup_2_4_5_7.log -l DEBUG --defaults_file=/home/shahriyar.rzaev/XB_TEST/server_dir/xb_2_4_ps_5_7.conf --backup
Take a second incremental one:
$ autoxtrabackup --tag="Second incremental backup" -v -lf /home/shahriyar.rzaev/autoxtrabackup_2_4_5_7.log -l DEBUG --defaults_file=/home/shahriyar.rzaev/XB_TEST/server_dir/xb_2_4_ps_5_7.conf --backup
Now you can use the --show_tags to list tags:
$ sudo autoxtrabackup --show_tags --defaults_file=/home/shahriyar.rzaev/XB_TEST/server_dir/xb_2_4_ps_5_7.conf Backup Type Status TAG ------------------------------------------- 2017-11-16_20-10-53 Full OK 'My Full backup' 2017-11-16_20-12-23 Inc OK 'First incremental backup' 2017-11-16_20-13-39 Inc OK 'Second incremental backup'
It would be quite nice if we could prepare those backups with a tag name. In other words, if I have a full backup and five incremental backups, what if I want to prepare until the second or third incremental, or just a full backup?
Pass the tag name with the --prepare option, and it will do the trick:
$ autoxtrabackup --tag="First incremental backup" -v -lf /home/shahriyar.rzaev/autoxtrabackup_2_4_5_7.log -l DEBUG --defaults_file=/home/shahriyar.rzaev/XB_TEST/server_dir/xb_2_4_ps_5_7.conf --prepare
It will prepare the full and “First incremental backup” – the remaining incremental backups will be ignored.
autoxtrabackup 1.5.0 also has a --dry_run option, which is going to show but not run exact commands. It is described here: –dry_run.
How about autoxtrabackup 1.5.0 installation? You can install it from the source or use pip3:
pip3 install mysql-autoxtrabackup
For more please read: Installation.
Do you want to enable encryption and compression for backups? Yes? You can enable this from the autoxtrabackup config as described here: Config file structure.
You can enable taking partial backups again by editing the config: partial backups.
autoxtrabackup 1.5.0 allows you to perform a partial recovery – i.e., restoring only a single specified table from a full backup. If the table was dropped, autoxtrabackup will try to extract the create table statement from the .frm file using the mysqlfrm tool and then discard/import the tablespace from full backup. This is related to the transportable tablespace concept. You can read more here: restoring-single-table-after-drop.
For a full list of available options, read the DOC: autoxtrabackup DOC.
Thanks for reading! If you are going to try autoxtrabackup 1.5.0, don’t hesitate to provide some feedback!