Nov
27
2017
--

autoxtrabackup v1.5.0: A Tool for Automatic Backups

autoxtrabackup

autoxtrabackupThere 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!

Mar
06
2017
--

Improving TokuDB Hot Backup Usage with the autotokubackup Command Line Tool

autotokubackup

autotokubackup In this blog post, we’ll look at how the command line tool autotokubackup can make TokuDB hot backups easier.

I would like to share an experimental tool named autotokubackup, for TokuBackup. This tool is aimed at helping simplify the life of TokuDB system administrators. autotokubackup is written in the Python language.

General information:

So why would you need this tool? Let’s clarify a bit what you might face while using tokubackup. You have a backup solution that you can use from the MySQL shell:

mysql > set tokudb_backup_dir='/var/lib/tokubackupdir';

Now you want to automate this process. The first problem is that the second backup will fail, because it’s required that the backup directory is empty before starting a backup process. One solution is to create time-stamped directories and for the backups.

Further, you have a backup policy that requires some other necessary files are copied as part of your backup process. You need to write a script to put those files into a separate folder under backup directory.

Another issue you will face is the lack of any clear output on backup progress. The shell just pauses until the backup completes. The one possible way to obtain information about the backup process is displaying the MySQL processlist in a separate MySQL shell. But it isn’t the best way, and there are some issues, as reported here: Unclear status information of backup state while taking backups using TokuBackup.

Generally, we need to know which files are backed up during the backup process. There should also be a clear message indicating the end of the backup process.

To make your life easier, the autotokubackup tool:

  • Automates the TokuDB database backup procedures
  • Creates timestamped backups inside the backup directory, overcoming the need for copy/remove old backups to empty the backup directory
  • Copies all necessary files for your backup policy (you can specify up to ten supplementary files to be in the backup directory as part of backup process)
  • Clearly describes what is going to be in the backup director, by showing newly created files inside backup directory
  • Clearly shows the end of backup process

To start, we only need two things:

  • Installed Percona Server with TokuDB engine + TokuBackup plugin
  • Installed Python3

To install the tool you can use following methods:

* From source:

cd /home
git clone https://github.com/Percona-Lab/autotokubackup.git
cd autotokubackup
python3 setup.py install

* or via pip3:

pip3 install autotokubackup

The result will be something like:

Collecting autotokubackup
  Downloading autotokubackup-1.1-py3-none-any.whl
Collecting watchdog>=0.8.3 (from autotokubackup)
  Downloading watchdog-0.8.3.tar.gz (83kB)
    100% |????????????????????????????????| 92kB 8.2MB/s
Collecting click>=3.3 (from autotokubackup)
  Downloading click-6.7-py2.py3-none-any.whl (71kB)
    100% |????????????????????????????????| 71kB 10.6MB/s
Collecting mysql-connector>=2.0.2 (from autotokubackup)
  Downloading mysql-connector-2.1.4.zip (355kB)
    100% |????????????????????????????????| 358kB 4.7MB/s
Collecting PyYAML>=3.10 (from watchdog>=0.8.3->autotokubackup)
  Downloading PyYAML-3.12.tar.gz (253kB)
    100% |????????????????????????????????| 256kB 6.5MB/s
Collecting argh>=0.24.1 (from watchdog>=0.8.3->autotokubackup)
  Downloading argh-0.26.2-py2.py3-none-any.whl
Collecting pathtools>=0.1.1 (from watchdog>=0.8.3->autotokubackup)
  Downloading pathtools-0.1.2.tar.gz

After that there should be a configuration file for this tool located -> /etc/tokubackup.conf.

The structure of the config file is:

[MySQL]
mysql=/usr/bin/mysql
user=root
password=12345
port=3306
socket=/var/run/mysqld/mysqld.sock
host=localhost
datadir=/var/lib/mysql
[Backup]
backupdir=/var/lib/tokubackupdir
[Copy]
# The following copy_file_x options allow you to copy various files together with your backup
# Highly recommended; a copy of your my.cnf file (usually /etc/my.cnf) and any cnf files referenced from it (i.e. includedir etc.)
# You can also include other files you would like to take a copy of, like for example a text report or the mysqld error log
# copy_file_1=
# copy_file_2=
# copy_file_...=
# copy_file_10=
#copy_file_1=/etc/my.cnf
#copy_file_2=/var/log/messages
#copy_file_3=
#copy_file_4=
#copy_file_5=
#copy_file_6=
#copy_file_7=
#copy_file_8=
#copy_file_9=
#copy_file_10=

You can change options to reflect your environment and start to use. Available command line options for the tool can be displayed using --help

tokubackup --help
Usage: tokubackup [OPTIONS]
Options:
--backup              Take full backup using TokuBackup.
--version             Version information.
--defaults_file TEXT  Read options from the given file
--help                Show this message and exit.

You can prepare different config files. For example, one for the slave. Specify using the –defaults_file option, and the overall result of the run should be something like the below:

tokubackup --backup --defaults_file=/etc/tokubackup_node2.conf
Backup will be stored in  /var/lib/tokubackupdir/2017-02-09_20-25-40
Running backup command => /home/sh/percona-server/5.7.17/bin/mysql -uroot --password=msandbox --host=localhost --socket=/tmp/mysql_sandbox20194.sock -e set tokudb_backup_dir='/var/lib/tokubackupdir/2017-02-09_20-25-40'
mysql: [Warning] Using a password on the command line interface can be insecure.
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/__tokudb_lock_dont_delete_me_data
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/__tokudb_lock_dont_delete_me_logs
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/__tokudb_lock_dont_delete_me_temp
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/log000000000006.tokulog29
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/tokudb.rollback
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/tokudb.environment
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/tokudb.directory
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/tc.log
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/client-key.pem
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/server-cert.pem
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/server-key.pem
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/ca.pem
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/ca-key.pem
Created file in backup directory -> /var/lib/tokubackupdir/2017-01-31_14-15-46/mysql_data_dir/auto.cnf
Completed - OK

The backup directory will store the following:

ls -l 2017-02-09_20-25-40/
  copied_files            - Directory for copied files.
  global_variables        - File for MySQL global variables.
  mysql_data_dir          - Directory for copied MySQL datadir.
  session_variables       - File for MySQL session variables.
  tokubackup_binlog_info  - File for storing binary log position.(The new feature for TokuBackup) [Not released yet]
  tokubackup_slave_info   - File for storing slave info.(The new feature for TokuBackup) [Not released yet]

That’s it. If you test it and find bugs, send a feature request to further improve our “helper.” Thanks! ?

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com