Online Full/Incremental MySQL backup via xtrabackup open source tool

Hi Folks,
Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB. With Percona XtraBackup, you can achieve the following benefits:

• Backups that complete quickly and reliably
• Uninterrupted transaction processing during backups
• Savings on disk space and network bandwidth
• Automatic backup verification
• Higher uptime due to faster restore time

It performs streaming, compressed, and incremental backups to your MySQL backup database.
So lets see, here I will demonstrate you following things-
(i) Automate Full / Incremental backup via shell script
(ii) Keep only last X days of backup

Pre-requisite:-

(i) Download xtrabackup :
      Here I am using xtrabackup version 2.1.4 ( percona-xtrabackup-2.1.4-656-Linux-x86_64.tar.gz )
(ii) qpress-11-linux-x64.tar (it is required for de-compression)
(iii) plugin " perl-DBD-MySQL" should be installed  
      yum install perl-DBD-MySQL
(iv) cp  xtrabackup_56 /usr/bin

(v) USER privilege required for 'xtrabackup' user


  mysql> CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password';

  mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backupuser'@'localhost';

  mysql> FLUSH PRIVILEGES; 

 Now below is the full backup script

Full-backup.sh

#! /bin/bash

now=`date +%d-%m-%Y_%T`
TYPE="full-bak.log"
source /dbscriptlog/script/variable.sh
$XtraBackup/innobackupex --host=$h --user=$u --password=$p --slave-info $BACKUP_DIR/ > $LOG_DIR/$HOSTNAME-$TYPE 2>&1
mv $LOG_DIR/$HOSTNAME-$TYPE $LOG_DIR/$HOSTNAME-$TYPE-$now

## Get the latest backup directory from /backup path
ls -lhtr $BACKUP_DIR > $_file
LAST_BACKUP_DIR=`awk '{print $9}' $_file | tail -1`
rm $_file

## Delete X days older file ###
find $BACKUP_DIR -type d -name '*20*' -mtime +$DAYS_KEEP -exec rm -rf  {} \;

Contents of variable.sh file which is including in full-backup.sh and incr-backup.sh script.


_file=/tmp/p1.txt
HOSTNAME=`hostname`
BACKUP_DIR=/backup/mysql
LOG_DIR=/dbscriptlog/log/backuplog
XtraBackup=<PATH_OF_PERCONA_PKG>/percona-xtrabackup-2.1.4-Linux-x86_64/bin

u=backuser
p='backuppassword'
h='localhost'
DAYS_KEEP=X               ## (Number of Days to store mysql backup)

incr-backup.sh   

#! /bin/bash

now=`date +%d-%m-%Y_%T`
source /dbscriptlog/script/variable.sh
TYPE="incremental-bak.log"
ls -lhtr $BACKUP_DIR > $_file
LAST_BACKUP_DIR=`awk '{print $9}' $_file | tail -1`
rm $_file
$XtraBackup/innobackupex --host=$h --user=$u --password=$p --slave-info --incremental $BACKUP_DIR/ --incremental-basedir=$BACKUP_DIR/$LAST_BACKUP_DIR > $LOG_DIR/$HOSTNAME-$TYPE 2>&1
mv $LOG_DIR/$HOSTNAME-$TYPE $LOG_DIR/$HOSTNAME-$TYPE-$now

## Delete X days older file ###
find $BACKUP_DIR -type d -name '*20*' -mtime +$DAYS_KEEP -exec rm -rf  {} \;

Role of "innodb_fast_shutdown" (very useful for DBA) in case of MySQL Database Disaster

innodb_fast_shutdown - This is the Innodb shutdown mode and it is a Dynamic variable.

innodb_fast_shutdown=0; (slow shutdown)

If innodb_fast_shutdown is set to 0, the log files are purged when mysqld shuts down - larger files mean a longer shutdown time.

innodb_fast_shutdown=1 (default)
The default for innodb_fast_shutdown is 1, which means that the log files are not purged before a shutdown.

innodb_fast_shutdown=2 (Fast shutdown but may take longer startup)
A value is 2 simulates a crash, and at the next startup InnoDB will do a crash recovery.

This can be useful when testing to see approximately how long InnoDB takes to recover from a crash, to determine if the size of your log files is too large.

One Query: Two output

Using "SQL_CALC_FOUND_ROWS" with SELECT STATEMENT

Example:-

mysql> select * from test;
+---------------------+--------+---------+----------+
| triggerDate         | Medium | Serious | Critical |
+---------------------+--------+---------+----------+
| 2012-12-20 00:00:00 |     10 |      20 |       30 |
| 2012-12-21 00:00:00 |     10 |      20 |       30 |
| 2012-12-22 00:00:00 |     10 |      20 |       30 |
| 2013-01-22 00:00:00 |     10 |      20 |       30 |
| 2013-02-22 00:00:00 |     10 |      20 |       30 |
| 2013-03-22 00:00:00 |     10 |      20 |       30 |
| 2013-03-22 00:00:00 |     10 |      20 |       30 |
| 2013-03-21 00:00:00 |     10 |      20 |       30 |
+---------------------+--------+---------+----------+
8 rows in set (0.00 sec)

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM test limit 3;
+---------------------+--------+---------+----------+
| triggerDate         | Medium | Serious | Critical |
+---------------------+--------+---------+----------+
| 2012-12-20 00:00:00 |     10 |      20 |       30 |
| 2012-12-21 00:00:00 |     10 |      20 |       30 |
| 2012-12-22 00:00:00 |     10 |      20 |       30 |
+---------------------+--------+---------+----------+
3 rows in set (0.00 sec)

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            8 |
+--------------+
1 row in set (0.00 sec)

**
SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() enables you to determine how many other pages are needed for the rest of the result.