Percona Xtrabackup Scripts – Update

Hey everyone,

I’ve recently reviewed and release the version 2.1.2 of my wrapper Shell scripts for the Percona Xtrabackup utility (see my previous post).

These scripts have been tested on Ubuntu 12.04 using Percona Xtrabackup 2.2.x against MySQL server 5.5.

It’s available on Github: https://github.com/deviantony/xtrabackup-scripts

You can check the new documentation below.

Requirements

Percona packages

You’ll need the xtrabackup binaries, they are available via the Percona debian repositories.

First, add the repository to your repository list (do this as the super-user) :

$ apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
$ echo "deb http://repo.percona.com/apt precise main" | sudo tee -a /etc/apt/sources.list.d/percona.list
$ echo "deb-src http://repo.percona.com/apt precise main" | sudo tee -a /etc/apt/sources.list.d/percona.list

Then install the package :

$ sudo apt-get update && sudo apt-get install xtrabackup qpress

You’ll need the qpress package in order to create compressed backups.

MySQL

You will also need to create a MySQL user with the appropriate permissions to use the tools:

CREATE USER 'backup-user'@'localhost' IDENTIFIED BY 'changeme';
GRANT SELECT, SHOW VIEW, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup-user'@'localhost';
FLUSH PRIVILEGES;

Xtrabackup 2.2.x and MySQL < 5.6.8

If you try to use the scripts with Xtrabackup 2.2.x and MySQL server < 5.6.8, they will fail. To work around this problem, you’ll need to ensure the setting innodb_log_file_size is set in your MySQL configuration file under the [mysqld] section.

The default value for this setting is 5M:

innodb_log_file_size = 5M

Create and restore a backup archive

Backup

Use the xb-backup-fs.sh script to create a binary backup of a local MySQL server. The data will be compressed and archived in a specified repository.

Example of use:

$ sudo ./xb-backup-fs.sh -r <PATH TO REPOSITORY> -u <MYSQL USER> [-p <MYSQL PASSWORD>]

Additional options

You can also specify the following options:

  •  –tmp-dir: Specify the temporary directory used by the script (default: /tmp/xb_backup_fs)
  •  –log-file: Log file for the script (default: /var/log/mysql/xb-backup-fs.log)
  •  –backup-threads: You can specify more threads in order to backup quicker (default: 1)

Restoration

The archive is containing a binary backup of a MySQL server, all you need to do in order to restore the backup is to extract the content of the archive in your MySQL datadir, setup the permissions for the files and start your server:

$ sudo rm -rf /path/to/mysql/datadir/*
$ sudo tar xvpzf /path/to/backup_archive.tar.gz -C /path/to/mysql/datadir
$ sudo chown -R mysql:mysql /path/to/mysql/datadir
$ sudo service mysql start

Setup an incremental backup cycle

An incremental backup cycle is consisting of a full binary backup (the base backup) and one or more incremental backups containing only the data difference between it and the last backup.

See Wikipedia: Incremental backup for more information.

Backup

Use the xb-backup-incremental.sh script to create an incremental backup cycle.

First you’ll need to take a full backup which will serve as the base backup:

$ sudo ./xb-backup-incremental.sh -r <PATH TO REPOSITORY> -u <MYSQL USER> [-p <MYSQL PASSWORD>]

Once the base backup is finished, a data file /opt/xb-backup/xb_incremental_cycle_data.txt will be created, containing the following key/values:

  • BASEDIR = Path to the repository where the base backup is stored
  • INCREMENTAL_STEP = Current incremental backup count
  • LAST_LSN = Log Sequence Number, used by innobackupex to know from which point the next incremental backup will be created

Now, you can start to add incremental backups to the cycle by using the –increment option:

$ sudo ./xb-backup-incremental.sh -r <PATH TO REPOSITORY> -u <MYSQL USER> [-p <MYSQL PASSWORD>] --increment

The script will read from the data file, create the incremental backup in a subfolder INC in the BASEDIR directory and update the data file.

NOTE: The data file is reset every time a base backup is created (without the –increment option).

Additional options

You can also specify the following options:

  • –data-dir: Data directory where the script will store the file containing incremental backup cycle related data (default: /opt/xb-backup/)
  • –tmp-dir: Specify the temporary directory used by the script (default: /tmp/xb_backup_inc)
  • –log-file: Log file for the script (default: /var/log/mysql/xb-backup-incremental.log)
  • –backup-threads: You can specify more threads in order to backup quicker (default: 1)

Restoration

WARNING: The folder structure and the file names created by the xb-backup-incremental.sh script needs to be respected in order to restore successfully:

  • TIMESTAMP_FOLDER/
    • base_archive.tar.gz
    • INC/incremental_archive_01.tar.gz
    • INC/incremental_archive_N.tar.gz

To restore an incremental backup, you’ll need to use the xb-restore-incremental.sh the following way:

$ sudo ./xb-restore-incremental.sh -b <PATH TO BASE BACKUP> -i <PATH TO INCREMENTAL BACKUP>

The script will stop the MySQL service, remove all files present in MySQL datadir and import all the incremental backups up to the specified last incremental backup.

For example, using the following parameters:

$ sudo ./xb-restore-incremental.sh -b /tmp/repo/20140518_1100/base_archive.tar.gz -i /tmp/repo/20140518_1100/INC/incremental_archive_05.tar.gz

The script will restore the incremental_archive_N.tar.gz from 1 to 5.

Additional options

You can also specify the following options:

  • –data-dir: Path to MySQL datadir (default: /var/lib/mysql/)
  • –restart: Restart the MySQL service after restoration (default: false)
  • –tmp-dir: Specify the temporary directory used by the script (default: /tmp/xb_backup_inc)
  • –log-file: Log file for the script (default: /var/log/mysql/xb-restore-incremental.log)

Stream a backup between 2 hosts

You can directly stream the state of a local server into another MySQL server on your network using the following scripts:

  • xb-backup-stream.sh on the host to backup
  • xb-restore-stream.sh on the host which will receive the backup

NOTE: You need to start the restoration phase first.

Restoration

The xb-restore-incremental.sh script will shutdown the MySQL service, clean the MySQL datadir and put the server in listening mode:

$ sudo ./xb-restore-stream.sh

Additional options

You can also specify the following options:

  • –checksum: Use sha1sum to create a checksum and verify backup integrity (default: false)
  • –data-dir: Path to MySQL datadir (default: /var/lib/mysql/)
  • –restart: Restart the MySQL service after restoration (default: false)
  • –tmp-dir: Specify the temporary directory used by the script (default: /tmp/xb_backup_inc)
  • –log-file: Log file for the script (default: /var/log/mysql/xb-restore-incremental.log)
  • –threads: You can specify more threads in order to decompress quicker (default: 1)
  • –netcat-port: Port used by netcat (default: 9999)

Backup

When the destination host is in listening mode, you can start the backup script:

$ sudo ./xb-backup-stream.sh -d <DESTINATION HOST> -u <MYSQL USER> [-p <MYSQL PASSWORD>]

The transfer to the DESTINATION HOST will begin directly after the script is started.

Additional options

You can also specify the following options:

  • –checksum: Use sha1sum to send a checksum of the backup (default: false)
  • –restart: Restart the MySQL service after restoration (default: false)
  • –tmp-dir: Specify the temporary directory used by the script (default: /tmp/xb_backup_inc)
  • –log-file: Log file for the script (default: /var/log/mysql/xb-restore-incremental.log)
  • –backup-threads: You can specify more threads in order to restore quicker (default: 1)
  • –compress-threads: You can specify more threads in order to compress quicker (default: 1)
  • –netcat-port: Port used by netcat (default: 9999)

That’s all folks ! Don’t hesitate to give feedback via comments !

Advertisements

12 thoughts on “Percona Xtrabackup Scripts – Update

    1. Hey Esteban, I don’t really know what is the problem.

      I’ve tried my scripts on a fresh installation, without knowing that the percona xtrabackup package was pointing to the 2.2 version and the script failed to execute. So I’ve decided to install the percona-xtrabackup-21 package which install the 2.1 version and work with these scripts.

      I may review the scripts during the next weeks. But for the moment, I suggest you to use the 2.1 version if you don’t need the 2.2 functionalities.

      1. Thank you. At the end I made a simple script to execute a daily backup. But I will use your script in the future to have incremental backups. Keep up the great work!

  1. Thanks a lot for your great scripts!
    By the way, i see in your previous post that exists the possibility to do partial backups, but that possiblity disappear in the new scripts. Is it because partial backups aren’t reliable?

    1. Hey Jose,

      For me, partial backups are not reliable. Xtrabackup will backup the server and a set of tables but when you’ll try to import the backup then MySQL will yell at you saying it is missing some tables. So you’ll need to tweak the server after restoration. I’m still using mysqldump when I want to backup a subset of tables.

  2. Hi Anthony!

    Thanks for sharing your script. I really would like to use it, but I have some problems using it in Centos 6.x The error I have is:

    [root@db5 xtrabackup]# /usr/bin/pyxtrabackup-inc /backup –user=root
    ERROR:xtrabackup.incremental_backup:An error occured during the backup process.
    Traceback (most recent call last):
    File “/usr/lib/python2.6/site-packages/xtrabackup/incremental_backup.py”, line 35, in main
    arguments[‘–backup-threads’])
    File “/usr/lib/python2.6/site-packages/xtrabackup/backup_tools.py”, line 207, in start_incremental_backup
    self.exec_full_backup(user, password, threads)
    File “/usr/lib/python2.6/site-packages/xtrabackup/backup_tools.py”, line 84, in exec_full_backup
    self.workdir)
    File “/usr/lib/python2.6/site-packages/xtrabackup/command_executor.py”, line 15, in exec_filesystem_backup
    subprocess.check_output([
    AttributeError: ‘module’ object has no attribute ‘check_output’
    [root@db5 xtrabackup]#

    Is there an easy fix that you know of? I’m a missing any dependencies?

    1. Hi binadit !

      I’m still working on the Python version and I’ve not tested it under CentOS. Could you file an issue on the Github repository?

      https://github.com/deviantony/xtrabackup-scripts/issues

      I’ll try to reproduce the issue and fix it ! But from what I can read the problem is that the check_output method is not supported by Python 2.6, seems that this method has appeared only in Python 2.7 : https://docs.python.org/2/library/subprocess.html

      You could try to update your Python version to 2.7.x at least.

  3. Hello,

    the incremental script makes this structure:

    20160109_1341/backup_base_20160109_1341.tar.gz
    20160109_1341/INC/backup_inc_1_20160109_2116.tar.gz
    20160109_1341/INC/backup_inc_2_20160110_1000.tar.gz

    How exactly am I supposed to restore, the filenames differ from the described ones.

    Great work by the way.

    Cheers, David

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s