EmergencyEMERGENCY? Get 24/7 Help Now!

autoxtrabackup v1.5.0: A Tool for Automatic Backups

 | November 27, 2017 |  Posted In: Backups, Percona XtraBackup, Quality Assurance

PREVIOUS POST
NEXT POST

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 and Percona-QA are open source GitHub repositories for unofficial scripts and tools created by Percona staff. While not covered by Percona support or services agreements, these handy utilities can help you save time and effort.

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:

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:

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:

Take an incremental one:

Take a second incremental one:

Now you can use the --show_tags to list tags:

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:

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:

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!

PREVIOUS POST
NEXT POST
Shahriyar Rzayev

Shako from Azerbaijan/Baku. My hobby is cooking kebap.
Bug lover by design.

7 Comments

  • prepare with item 2,can’t start mysql

    2017-11-30T10:08:37.085905+08:00 0 [ERROR] InnoDB: Unable to open undo tablespace ‘.//undo001’.
    2017-11-30T10:08:37.085961+08:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
    2017-11-30T10:08:38.186950+08:00 0 [ERROR] Plugin ‘InnoDB’ init function returned error.
    2017-11-30T10:08:38.186999+08:00 0 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
    2017-11-30T10:08:38.187009+08:00 0 [ERROR] Failed to initialize plugins.
    2017-11-30T10:08:38.187015+08:00 0 [ERROR] Aborting

    • Thank your reply!

      xtrabackup –version
      xtrabackup version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)

      /usr/local/mysql/bin/mysql –version
      /usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.18-16, for Linux (x86_64) using 6.2

      /opt/Python-3.5.3/bin/autoxtrabackup –version
      Developed by Shahriyar Rzayev from Azerbaijan MUG(http://mysql.az)
      Link : https://github.com/ShahriyarR/MySQL-AutoXtraBackup
      Email: rzayev.shahriyar@yandex.com
      Based on Percona XtraBackup: https://github.com/percona/percona-xtrabackup/
      MySQL-AutoXtraBackup Version: 1.5.0

      step:
      /opt/Python-3.5.3/bin/autoxtrabackup –backup
      /opt/Python-3.5.3/bin/autoxtrabackup –defaults_file=/data/backup/bck.conf –prepare
      – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

      Preparing full/inc backups!
      What do you want to do?
      1. Prepare Backups and keep for future usage. NOTE(‘Once Prepared Backups Can not be prepared Again’)
      2. Prepare Backups and restore/recover/copy-back immediately
      3. Just copy-back previously prepared backups
      Please Choose one of options and type 1 or 2 or 3: 2

      – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

      ls /data/mysql/3336/data/
      ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql performance_schema sys xtrabackup_binlog_pos_innodb xtrabackup_info

      the datadir has no undo logs.

      but with only xtrabackup application , can start mysql normally

      • I know the reason,because mysql config file was not in /etc/,it was in /data/mysql/3336/etc/my.cnf.
        and I think copy_back command should with –defaults-file=mycnf

  • If we backup with one full backup and three increment backup,we should prepare with –apply-log-only on full backup then first incr backup then second,,and last incr backup with only prepare,but in https://github.com/ShahriyarR/MySQL-AutoXtraBackup/blob/master/backup_prepare/prepare.py I haven’t seen xtra_prepare=–apply-log-only been used anywhere.

  • Yes you should change autoxtrabackup config file to reflect you changes(environment). You can create a number of this configs, if you want and pass it via –default_file.
    Also you can enable stdoud to console by appending -v -l DEBUG options.
    You can enable logging to file as well by specifying -lf path/logfile.log.

    I have noticed that you did not pass the –defaults_file with –backup.
    The full command for you backup will be something like:
    /opt/Python-3.5.3/bin/autoxtrabackup -v -l DEBUG –defaults_file=/data/backup/bck.conf –backup
    Same for –prepare.

Leave a Reply