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é.

Advertisements

10 thoughts on “Percona Xtrabackup Scripts

    1. Hi, one more question,
      How do you delete the old files?
      For example, to keep only the latest 24 hours of incremental backups and7 days of full backups.
      Thanks

      1. Hey Angel,

        Yeap the scripts are still valid, I’m using them in production in my company. Don’t hesitate to contact me if you got any problem with them.

        About your second question, this is not the aim of the scripts. You may need to setup crons and use the linux find command to remove incremental backup older than 24 hours and full backups older than 7 days.

      2. Hi!
        Thanks for the reply. I manage to create a clean.sh script to execute after those and delete the older ones. 🙂
        Also I have created another one that store the backup on the S3 so I think everything is perfect now.

        Thank you very much!

        Best

  1. Hi again Deviantony,

    I have been testing the script and I have a problem. Maybe you could helpme.
    I make the full backups at 3:00 am everyday and the incremental backups every hour at :30.
    The problem I have is with the temp folder and the list file, that I think it is needed to make a restore.
    Because it use the same folder and file name for all the backups, I am able to get the list of all the Incremental Backups from 3:00 to 23:59 that day, but the three INC files 00:30, 01:30 and 02:30 are deleted when I create the new full backup at 3:00 am.
    If I did the backups at midnight, it will work fine, but that is a heavy traffic moment on my website so it make sense to make the full at 3:00am or later.

    What do you suggest to fix it?

    Thank you ver much!a

    1. Hello Angel,

      I see what is the problem here. The backup script is using the current day date to create the backup folders. I’ve not tried to use the backup script this way because Xtrabackup should not have any impact on a live server performances (I’ve read it on the Percona website, but I’ve made no benches).

      So, let’s say you’re trying to create an incremental backup cycle for the current day with the base backup starting at 3:00 AM, then the incremental backups from 00:30, 01:30 and 02:30 will belong to the backup cycle for the day before.
      I don’t understand why these 3 backups are deleted, they should be present on the filesystem but not accessible via the restoration script because they are specified in the XTRABACKUP_BACKUP_LIST.txt of the previous incremental backup cycle. Thing is, only the backup file name is specified, not the absolute path.

      I’ll review my scripts this week or maybe the next one, stay on touch for an update on this blog entry.

      1. Hi deviantony.
        You are right the files where there but not on the list.
        I made a kind of fix it saving the list into another folder inside the date. I rule that script at 2:45 before to create the new full backup, so it is working ok now but thank you for taking the time to review the script!

      2. Hi!
        I have a busy week ahead but I will have a look to the script for sure and give you feedback. I just do not know when. 🙂

        Thank you very much!

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