Practice: percona-xtrabackup 2.1.9 for mysql 5.6.19

---- 1. Compile and install percona-xtrabackup

Yum install cmake gcc-c ++ 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/

Music Video percona-xtrabackup-2.1.9/usr/local/xtrabackup/

 

Cd/usr/local/xtrabackup/percona-xtrabackup-2.1.9

 

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

/***** Required for reinstallation
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 related plug-ins

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 Restoration
**************************************** *************************

---- 1. Backup

 

Create table t1
(
Sid int not null,
Sname varchar (100) 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. Back up data

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

 

--- 3. Restore data

Service mysql stop

Mv/usr/local/mysql/data/innodb_data // wind/
Or
Cp-rvf innodb_data // wind/

---- The datadir data directory And log directory must 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 logs (memory 100 MB 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. Restore the database

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

 

Before restoration, data files and transaction log files, including innodb logs, must be deleted. MySQL DATA location can be determined based on/et/my. cnf.

--- 6. permission settings

Chown-R mysql: mysql/usr/local/mysql/

 

Service mysql start

 

**************************************** *************************
Incremental backup and Restoration
**************************************** *************************

---- 1. Prepare data

Create database wind;

Use wind;

 

Create table t1
(
Sid int not null,
Sname varchar (100) 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. Full backup data

Mkdir-p/backup/full

 

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

 

 

---- 3. Change Data

Create table t2
(
Sid int not null,
Sname varchar (100) 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/incret

 

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

 

 

 

--- 3. Restore data

Service mysql stop

Mv/usr/local/mysql/data/innodb_data // wind/
Or
Cp-rvf innodb_data // wind/

---- The datadir data directory And log directory must 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 logs (memory 100 MB by default)

--- 4.1 Prepare complete 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 re-prepare the full backup set to 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. Restore the database

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

 

Before restoration, data files and transaction log files, including innodb logs, must be deleted. MySQL DATA location can be determined based on/et/my. cnf.

--- 6. permission settings

Chown-R mysql: mysql/usr/local/mysql/

 

--- 7. Start mysql

Service mysql start