Combat: Percona-xtrabackup 2.1.9 for MySQL 5.6.19

----1. Compile and install Percona-xtrabackup

Yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr \
Bison Libtool Ncurses-devel zlib-devel libgcrypt-devel

wget http://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.9/source/percona-xtrabackup-2.1.9.tar.gz

Tar xvzf percona-xtrabackup-2.1.9.tar.gz

Mkdir-p/usr/local/xtrabackup/

MV percona-xtrabackup-2.1.9/usr/local/xtrabackup/

cd/usr/local/xtrabackup/percona-xtrabackup-2.1.9

Auto_download= "yes"./utils/build.sh innodb56

/***** re-installation required
Rm-rf/usr/bin/innobackupex
Rm-rf/usr/bin/xtrabackup

***********/

CP innobackupex/usr/bin/

CP src/xtrabackup_56/usr/bin/

Ln-s/usr/local/mysql/bin/*/usr/bin/

---2. Install the relevant plugin

wget http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.8-1.noarch.rpm

Yum Install PERL-DBI
Yum Install Perl-dbd-mysql
Yum Install Perl-time-hires
Yum Install Perl-io-socket-ssl

RPM-IVH percona-toolkit-2.2.8-1.noarch.rpm

*****************************************************************
Normal backup and restore
*****************************************************************

----1. Backup

CREATE TABLE T1
(
Sid int NOT NULL,
Sname varchar (+) NOT NULL
) Engine=innodb CHARSET=GBK;

DELIMITER//
Create PROCEDURE Proc1 ()
BEGIN
DECLARE I int DEFAULT 0;
Set I=1;
Set autocommit=0;
While i<=1000000 do
INSERT into T1 values (i, ' I ');
Set i=i+1;
END while;
Commit
Set autocommit=1;
END
//
DELIMITER;

Call Proc1;

---2. Backing up data

Innobackupex--user=root--password=password--defaults-file=/usr/local/mysql/my.cnf \
--port=3306/backup

---3. Recovering data

Service MySQL Stop

mv/usr/local/mysql/data/innodb_data//wind/
Or
CP-RVF innodb_data//wind/

----DATADIR Data directory and log directory need to be empty

rm-rf/usr/local/mysql/innodb_data/*
rm-rf/usr/local/mysql/data/*
rm-rf/usr/local/mysql/mysql_logs/innodb_log/*

----4. Prepare the log (using memory 100M by default)

Innobackupex--ibbackup=xtrabackup_56--user=root--password=password--defaults-file=/usr/local/mysql/my.cnf \
--apply-log--use-memory=4g/backup/2014-06-06_10-27-47

----5. Restoring a Database

Innobackupex--ibbackup=xtrabackup_56--defaults-file=/usr/local/mysql/my.cnf--copy-back/backup/2014-06-06_ 10-27-47

Both the data file and the transaction log file, including the InnoDB log, need to be deleted before the restore. Determine MySQL data location according to/ET/MY.CNF

---6. Permission settings

Chown-r mysql:mysql/usr/local/mysql/

Service MySQL Start

*****************************************************************
Incremental backup and restore
*****************************************************************

----1. Preparing the data

Create database Wind;

Use wind;

CREATE TABLE T1
(
Sid int NOT NULL,
Sname varchar (+) NOT NULL
) Engine=innodb CHARSET=GBK;

DELIMITER//
Create PROCEDURE Proc1 ()
BEGIN
DECLARE I int DEFAULT 0;
Set I=1;
Set autocommit=0;
While i<=1000000 do
INSERT into T1 values (i, ' I ');
Set i=i+1;
END while;
Commit
Set autocommit=1;
END
//
DELIMITER;

Call Proc1;

---2. Fully prepared data

Mkdir-p/backup/full

Innobackupex--user=root--password=password--defaults-file=/usr/local/mysql/my.cnf \
--port=3306/backup/full

----3. Change the data

CREATE TABLE T2
(
Sid int NOT NULL,
Sname varchar (+) NOT NULL
) Engine=innodb CHARSET=GBK;

DELIMITER//
Create PROCEDURE proc2 ()
BEGIN
DECLARE I int DEFAULT 0;
Set I=1;
Set autocommit=0;
While i<=2000000 do
INSERT into T2 values (I, ' Today is a good day ');
Set i=i+1;
END while;
Commit
Set autocommit=1;
END
//
DELIMITER;

Call proc2;

----4. Incremental backup

Mkdir-p/backup/incre

Innobackupex--use-memory=4g--user=root--password=password--defaults-file=/usr/local/mysql/my.cnf \
--port=3306--incremental/backup/incre--incremental-basedir=/backup/full/2014-06-06_12-26-10

---3. Recovering data

Service MySQL Stop

mv/usr/local/mysql/data/innodb_data//wind/
Or
CP-RVF innodb_data//wind/

----DATADIR Data directory and log directory need to be empty

rm-rf/usr/local/mysql/innodb_data/*
rm-rf/usr/local/mysql/data/*
rm-rf/usr/local/mysql/mysql_logs/innodb_log/*

----4. Prepare the log (using memory 100M by default)

---4.1 Prepare full backup set

/* Syntax: Innobackupex--apply-log--redo-only Base-dir */

Innobackupex--ibbackup=xtrabackup_56--user=root--password=password--defaults-file=/usr/local/mysql/my.cnf \
--apply-log--redo-only/backup/full/2014-06-06_12-26-10--use-memory=4g

---4.2 Prepare incremental backup set

/* Syntax: Innobackupex--apply-log--redo-only base-dir--incremental-dir= * *

Innobackupex--ibbackup=xtrabackup_56--user=root--password=password--defaults-file=/usr/local/mysql/my.cnf \
--apply-log--redo-only/backup/full/2014-06-06_12-26-10--incremental-dir=/backup/incre/2014-06-06_12-38-16 \
--use-memory=4g

---4.3 Prepare the full set again, roll back those uncommitted transactions
/* Syntax: Innobackupex--apply-log base-dir */

Innobackupex--ibbackup=xtrabackup_56--user=root--password=password--defaults-file=/usr/local/mysql/my.cnf \
--apply-log/backup/full/2014-06-06_12-26-10--use-memory=4g

----5. Restoring a Database

Innobackupex--ibbackup=xtrabackup_56--defaults-file=/usr/local/mysql/my.cnf \
--copy-back/backup/full/2014-06-06_12-26-10

Both the data file and the transaction log file, including the InnoDB log, need to be deleted before the restore. Determine MySQL data location according to/ET/MY.CNF

---6. Permission settings

Chown-r mysql:mysql/usr/local/mysql/

---7. Start MySQL

Service MySQL Start