Advantages and Disadvantages of Percona Xtrabackup, Installation and Steps to take DB backup through innobackupex tool

PERCONA XTRABACKUP:-

           This post is about advantages, disadvantages, Installation and DB backup steps of percona xtrabackup/innobackupex and I will let you know about innobackupex tool which is written in Perl,  It enables more functionality by integrating xtrabackup and other functions such as file copying and streaming, It perform point in time backup for Innodb, MyISAM , Archive engines. 

  I have picked a DB which size is 28GB to test the same. I got below results: 

  Backup Time Taken with innobackupex: 5 minutes
  Backup Size : - 5.8 GB

and below are the findings (Advantages and Disadvantages):-

Advantages:- 
1) It supports completely non-blocking backups of InnoDB/XtraDB (Which support Transactions) storage engines.
2) It supports on fly compression which help to cater disk space with (--stream=xbstream).
3) We can take incremental backup as well with this. This can be done because each InnoDB page has a log sequence number, LSN, which acts as a version number of the entire database. Every time the database is modified, this number gets incremented. An incremental backup copies all pages since a specific LSN.
4) We can take partial backup as well which means that we can take backup only for some specific tables. But for this there must be innodb_file_per_table enabled 
5) Use --slave-info when we are backing up from backup slave server because it will provide binlog file name and position of the master server. It also writes this information to the xtrabackup_slave_info file.



Disadvantages:-
1) When it perform backup of MyISAM tables, it stops slave to maintain consistency.
2) In case of MyISAM Incremental Backup, it will take full backup of MyISAM tables. 

Installation and Configuration Steps : -

Follow below steps on Source and Destination Servers:-

Source Server :192.168.1.10

Destination Server :192.168.1.20

$ git clone https://github.com/percona/percona-xtrabackup.git
$ cd percona-xtrabackup
$ git checkout 2.4

$ yum install cmake libaio libaio-devel automake autoconf bison libtool ncurses-devel libgcrypt-devel libev-devel libcurl-devel  vim-common nc

$ cmake -DBUILD_CONFIG=xtrabackup_release -DWITH_MAN_PAGES=OFF && make -j4

$ make install

After completion of installation,  Now you have a directory name with /usr/local/xtrabckup

If you want to change the installation directory other than /usr/local, you have to run below command:

$ make DESTDIR=... install        ##Where you can put your desire directory after DESTDIR=


Create a directory on Destination server (192.168.1.20). Here I am creating directory /data/xtrabackup_data/ where backup will store.

Create a user with below minimum grants in DB instance which you want to take backup:

$ GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, SUPER ON *.* TO 'bkpuser'@'localhost' identified by 'bkpUs3r;

Then execute below command on Destination Server (192.168.1.20) when go with xbstream option which will create on fly backup:

$ nc -l 3407 |/usr/local/xtrabackup/bin/xbstream -x -C /data/xtrabackup_data/   ######Please use port which is not in use. Here I am using 3407

then execute below command on Source Server (192.168.1.10):

$ /usr/local/xtrabackup/bin/innobackupex --defaults-file=/data/data_MySQL/my.cnf --safe-slave-backup --compress --stream=xbstream --tmpdir=/tmp/ --slave-info  --user=bkpuser --password=bkpUs3r --socket=/tmp/mysql.sock /tmp/  |nc 192.168.1.20 3407

default file= Provide path of my.cnf file. Mine my.cnf path is /data/data_MySQL/my.cnf 

Restoration Steps :-

First of all download qpress on Destination Server through below link:

$ cd /opt && wget http://www.quicklz.com/qpress-11-linux-x64.tar 
$ tar -xvf qpress-11-linux-x64.tar
now use /opt/qpress as command ....


Use Below command for decompression and as well as deletion of all qp extension files.

$ for i in $(find -name "*.qp"); do /opt/qpress -vd $i $(dirname ${i}) && rm -f $i; done

Now apply-log

$ /usr/local/xtrabackup/bin/innobackupex --apply-log xtrabackup_data

####Actually after creating a backup, the data is not ready to be restored. There might be uncommitted transactions to be undone or transactions in the logs to be replayed. Doing those pending operations will make the data files consistent and it is the purpose of the prepare stage. #####

Now start the mysql and pass the change master. 

Below are the files from where you get all the coordinates (Binlog files and position)
1) xtrabackup_slave_info : it will provide binary cordinates of master of the source (master under which backup lying).
2) xtrabackup_binlog_info : it will provide binary cordinate of source (here source is backup slave).