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

Percona Xtrabackup Scripts

Yosh !

Today I’ll blog about a database backup utility I’ve recently discovered : Percona Xtrabackup.

This tool allow to create binary backups (what is a binary backup?) of a database server. But more important, it doesn’t lock the database during the backup ! Besides this primary feature, it can also create incremental backups, stream a backup live into another server while compressing it… I had a few time to experience it, so here is a short list of the pro/con I’ve evaluated for now :

Pros

  1. Hot backups with no performance degradation
  2. Streaming backup via network
  3. Incremental backups
  4. Partial backups
  5. Save/Restoration time

Cons

  1. Backup size (approximatively 2~3 times more than mysqldump)
  2. Partial backups are not really partials *

(*) Fact is, you cannot only dump a specific table file or database file set for example, the utility will always backup the server files (ibdata, logfile, binlogs…).

After playing a few times with the binary, I’ve setted up some scripts to wrap it. These are available on GitHub : xtrabackup-scripts.

I will explain how you can use these scripts to setup a topnotch backup solution on Ubuntu 12.04.

 

NOTE: This HOW-TO is deprecated ! You should check my update on this bill here: Percona Xtrabackup Scripts – Update

I – Pre-requisites

1. System

In order to use the innobackupex binary, you’ll need to install it. I recommend install it from the Percona repository. First, add the repository to your repository list (do this as the super-user) :

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
cat << EOF >> /etc/apt/sources.list.d/percona.list
deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main
EOF

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.

2. MySQL

You will also need a specific MySQL user (only on the server you want to backup), with the appropriate rights, check the file create_backup_user.sql in the GitHub repository for a working example.

3. Configuration

You will have to tune some variables inside the scripts in order to make them work.

xb-backup.sh

  1. Adjust the BACKUP_USER variable, it defines the username used for backup.
  2. Adjust the BACKUP_PASSWORD, it defines the password for the backup user.
  3. Adjust the TMP_DIR variable. It defines where the script will create the temporary backup folder, before compressing it and send it to another place on the filesystem. I recommend an available disk space equals or greater than 1.5x the MYSQL_DATA_DIR folder’s space used.
  4. Adjust the LOG_FILE variable. It defines where the log file is located.
  5. (Opt.) Adjust the PARALLEL_THREADS variable, it defines how many threads will be used to enhance the backup creation and compression.
  6. (Opt.) Adjust the NETCAT_PORT variable. It defines on which port a streamed backup will be send. Must be the same as xb-restore.sh in order to work.

xb-restore.sh

  1. Adjust the TMP_DIR variable. It defines where the script will create the temporary backup folder, before compressing it and send it to another place on the filesystem. I recommend an available disk space equals or greater than 1.5x the MYSQL_DATA_DIR folder’s space used.
  2. Adjust the LOG_FILE variable. It defines where the log file is located.
  3. (Opt.) Adjust the PARALLEL_THREADS variable, it defines how many threads will be used to enhance the backup decompression and restoration.
  4. (Opt.) Adjust the NETCAT_PORT variable. It defines on which port it will listen to receive the backup via network. Must be the same as xb-backup.sh in order to work.

II – Backup a server and store the archive on a filesystem

The xb-backup.sh script can create a backup of a database server, compress it and move it to a specific place on the filesystem. In order to do this, execute the scripts as follow :


sudo ./xb-backup.sh -h <DISTANT REPOSITORY>

Specify a repository for the backup instead of DISTANT REPOSITORY. The script will create a folder in that place named by the day date in YYYYmmDD format and move the compressed backup into this one.

III – Restore a compressed backup

In order to restore a database server from a backup. Use the xb-restore.sh script :


sudo ./xb-restore.sh -h <PATH TO THE COMPRESSED BACKUP>

Just replace the PATH TO THE COMPRESSED BACKUP by the real path to the backup archive.

Note: If you want to restore an incremental backup, just add the -i option :


sudo ./xb-restore.sh -ih <PATH TO THE COMPRESSED INCREMENTAL BACKUP>

IV – Stream a backup via the network

To backup a database server directly onto another server on the network, you’ll have to execute the scripts on both machines.

On the server which will receive the backup, execute the script as follows, it will put the server into a listening state in order to receive the backup:


sudo ./xb-restore.sh -s

After that, you can use the backup script on the server to backup:


sudo ./xb-backup.sh -s <DISTANT HOST ADDRESS>

Just replace the DISTANT HOST ADDRESS by the a address of the server which will receive the backup.

V – Setup an incremental backup cycle

An incremental backup cycle is composed of a mandatory BASE backup and of one or more incremental backups.

The BASE backup has to be a full/partial backup.

In order to create a base backup, add the -e option to the backup script.


sudo ./xb-backup.sh -eh <DISTANT REPOSITORY>

This will create a backup archive containing also a specific file called XTRABACKUP_LAST_LSN.txt, this file will also be stored in the TMP_DIR folder.

It contains the last Log Sequence Number, which is used by innobackupex to know from which point it will create an incremental backup.

So, once you got this LSN, you can use the backup script this way :


sudo ./xb-backup.sh -eh <DISTANT REPOSITORY> -i `cat TMP_DIR/XTRABACKUP_LAST_LSN.txt`

Note: the -e option is still required, it will overwrite the value in the TMP_DIR/XTRABACKUP_LAST_LSN.txt file.

It will create an incremental backup, containing the data that have changed or are new since the last backup. The backup will be stored in the DISTANT_REPOSITORY/YYYYmmDD/INC folder in order to facilitate the backup restoration.

This backup archive will contains two more files :

  • A copy of the XTRABACKUP_LAST_LSN.txt file
  • The XTRABACKUP_BACKUP_LIST.txt file, this file will contains the ordered list of the backup archives used for the incremental backup cycle. It will be used for restoration.

From these informations, you can now create a cron for example, which will create a base backup everyday at midnight and an incremental backup every hour.

@midnight root /bin/bash /path/to/scripts/xb-backup.sh -eh /path/to/repository

@hourly root /bin/bash /path/to/scripts/xb-backup.sh -eh /path/to/repository -i `cat /path/to/tmp/dir/XTRABACKUP_LAST_LSN.txt`

VI – Misc

1. Partial backups

These scripts are also capable of creating partial backups of a database server, which means a backup of only a selected set of databases/tables. In order to do so, use the xb-backup.sh script this way :


sudo ./xb-backup.sh -h <DISTANT REPOSITORY> -p <TABLE DEFINITION LIST>

As for the full backup, you specify a DISTANT REPOSITORY in which the archive will be stored and another parameter : the TABLE DEFINITION LIST. This is a file containing a list of tables to dump.

You can adapt the following query to retrieve the table list you want to backup from one or multiple databases :


mysql --skip-column-names -e "SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_name NOT REGEXP '^abc';" > TABLES_LIST.txt

This query will retrieve all the tables names from all databases not beginning with ‘abc’ and put them into the TABLES_LIST.txt file.

2. Replication


After restoring a backup, you’ll see an extra file in the MYSQL_DATA_DIR folder  : xtrabackup_binlog_pos_innodb.

You can use the data contained in this file to setup a replicated server :

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST="MASTER_IP", MASTER_USER="REPLICATION_USER", MASTER_PASSWORD="REPLICATION_PASSWORD", MASTER_LOG_FILE="BINLOG_FILE", MASTER_LOG_POS="BINLOG_POS";
START SLAVE;

You can find the BINLOG_FILE (use only the name, without the path) and BINLOG_POS info in the file.

Awé.