Innobackupex of mysql backup tools

Xtrabackup is a data backup tool for the InnoDB Storage engine. It supports online Hot Backup (which does not affect Data Reading and Writing during Backup) and is a good alternative to InnoDBHotbackup. xtrabackup has two main tools: xtrabackup and innobackupex. You can only back up tables of InnoDB and XtraDB storage engines, but cannot back up MyIS.

Xtrabackup is a data backup tool for the InnoDB Storage engine. It supports online Hot Backup (which does not affect Data Reading and Writing during Backup) and is a good alternative to InnoDB Hotbackup. xtrabackup has two main tools: xtrabackup and innobackupex. You can only back up tables of InnoDB and XtraDB storage engines, but cannot back up MyIS.

Xtrabackup is a data backup tool for the InnoDB Storage engine. It supports online Hot Backup (which does not affect Data Reading and Writing during Backup) and is a good alternative to InnoDB Hotbackup. xtrabackup has two main tools: xtrabackup and innobackupex. You can only back up tables of InnoDB and XtraDB storage engines, but cannot back up MyISAM data tables. the innobackupex tool must specify the data directory in the mysql configuration file. The xtrabackup tool can only back up data, but cannot back up the table structure.

Mysql dba Technical Group 378190849

Wuhan-linux O & M group 236415619

1. download and install the xtrabackup Tool

[Root @ tong2 ~] # Wget
Http://www.percona.com/downloads/XtraBackup/XtraBackup-2.2.7/binary/redhat/6/x86_64/Percona-XtraBackup-2.2.7-r5050-el6-x86_64-bundle.tar

[Root @ tong2 ~] # Tar xvf Percona-XtraBackup-2.2.7-r5050-el6-x86_64-bundle.tar

[Root @ tong2 ~] # Ll percona-xtrabackup -*
-Rw-r --. 1 root 4863276 Dec 4 22:18 percona-xtrabackup-2.2.7-5050.el6.x86_64.rpm
-Rw-r --. 1 root 648012 Dec 4 22:18 percona-xtrabackup-debuginfo-2.2.7-5050.el6.x86_64.rpm
-Rw-r --. 1 root 961432 Dec 4 22:18 percona-xtrabackup-test-2.2.7-5050.el6.x86_64.rpm [root @ tong2 ~] # Yum localinstall percona-xtrabackup-*-y

[Root @ tong2 ~] # Rpm-ql percona-xtrabackup-2.2.7-5050.el6.x86_64.
/Usr/bin/innobackupex
/Usr/bin/xbcrypt
/Usr/bin/xbstream
/Usr/bin/xtrabackup
/Usr/share/doc/percona-xtrabackup-2.2.7
/Usr/share/doc/percona-xtrabackup-2.2.7/COPYING
[Root @ tong2 ~] #

2. Create databases and innodb tables

[Root @ tong1 ~] # Mysql-u root-p
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 5
Server version: 5.6.22 Source distribution

Copyright (c) 2000,201 4, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be trademarks of their respective
Owners.

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

Mysql> create database tong;
Query OK, 1 row affected (0.00 sec)

Mysql> \ u tong
Database changed
Mysql> create table t (a int );
Query OK, 0 rows affected (0.21 sec)

Mysql> insert into t values (1), (2), (3), (4 );
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0

Mysql> select * from t;
+ ------ +
| A |
+ ------ +
| 1 |
| 2 |
| 3 |
| 4 |
+ ------ +
4 rows in set (0.00 sec)

Mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Mysql> exit
Bye
[Root @ tong1 ~] #

3. Use the innobackupex tool to completely back up the table of the innodb Storage Engine

[Root @ tong2 ~] # Innobackupex -- help | more -- View help information

[Root @ tong1 mysql-5.6.22] # innobackupex -- user = root -- password = -- host = localhost/opt/all/-- back up all databases

[Root @ tong1 mysql-5.6.22] # ll/opt/all1/2015-04-24_13-12-46/
Total 112680
-Rw-r --. 1 root 295 Apr 24 backup-my.cnf
Drwxr-xr-x. 2 root 4096 Apr 24 database
-Rw-r -----. 1 root 12582912 Apr 24 13:13 ibdata1
-Rw-r --. 1 root 50331648 Apr 24 ib_logfile0
-Rw-r --. 1 root 50331648 Apr 24 ib_logfile1
Drwxr-xr-x. 2 root 4096 Apr 24 mysql
Drwxr-xr-x. 2 root 4096 Apr 24 performance_schema
Drwxr-xr-x. 2 root 4096 Apr 24 test
Drwx ------. 2 root 4096 Apr 24 13:12 tong
-Rw-r --. 1 root 21 Apr 24 13:12 xtrabackup_binlog_info
-Rw-r --. 1 root 21 Apr 24 13:13 xtrabackup_binlog_pos_innodb
-Rw-r -----. 1 root 89 Apr 24 xtrabackup_checkpoints -- backup data checkpoint-rw-r --. 1 root 592 Apr 24 xtrabackup_info -- backup information

-Rw-r -----. 1 root 2097152 Apr 24 13:13 xtrabackup_logfile -- Log File

4. delete data and restore all databases

[Root @ tong1 ~] # Cd/usr/local/mysql-5.6.22/

[Root @ tong1 mysql-5.6.22] # mysqladmin-u root-p shutdown
Enter password:
[Root @ tong1 mysql-5.6.22] # mv data data3
[Root @ tong1 mysql-5.6.22] # innobackupex -- user = root -- password = -- host = localhost -- apply-log/opt/all1/2015-04-24_13-12-46/

[Root @ tong1 mysql-5.6.22] # innobackupex -- user = root -- password = -- host = localhost -- copy-back/opt/all1/2015-04-24_13-12-46/

[Root @ tong1 mysql-5.6.22] # chown-R mysql: mysql data
[Root @ tong1 mysql-5.6.22] #/etc/init. d/mysqld restart
ERROR! MySQL server PID file cocould not be found!
Starting MySQL. SUCCESS!
[Root @ tong1 mysql-5.6.22] # mysql-u root-p
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 1
Server version: 5.6.22 Source distribution

Copyright (c) 2000,201 4, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be trademarks of their respective
Owners.

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

Mysql> \ u tong
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-

Database changed
Mysql> select * from t;
+ ------ +
| A |
+ ------ +
| 1 |
| 2 |
| 3 |
| 4 |
+ ------ +
4 rows in set (0.00 sec)

Mysql> exit
Bye
[Root @ tong1 ~] #

5. Back up a single database

[Root @ tong1 mysql-5.6.22] # mysql-u root-p-e "create table tong. t1 (q int )"
Enter password:

[Root @ tong1 mysql-5.6.22] # mysql-u root-p-e "flush tables"
Enter password:
[Root @ tong1 mysql-5.6.22] # mysql-u root-p-e "insert into tong. t1 values (9), (8 )"
Enter password:
[Root @ tong1 mysql-5.6.22] # mysql-u root-p-e "select * from tong. t1"
Enter password:
+ ------ +
| Q |
+ ------ +
| 9 |
| 8 |
+ ------ +

[Root @ tong1 mysql-5.6.22] # innobackupex -- user = root -- password = -- host = localhost -- database = tong/opt/tong/-- socket =/tmp/mysql. sock
[Root @ tong1 mysql-5.6.22] # ll/opt/tong/2015-04-24_14-30-00/
Total 12312
-Rw-r --. 1 root 295 Apr 24 backup-my.cnf
-Rw-r -----. 1 root 12582912 Apr 24 ibdata1
Drwx ------. 2 root 4096 Apr 24 tong
-Rw-r --. 1 root 21 Apr 24 14:30 xtrabackup_binlog_info
-Rw-r -----. 1 root 89 Apr 24 xtrabackup_checkpoints
-Rw-r --. 1 root 608 Apr 24 xtrabackup_info
-Rw-r -----. 1 root 2560 Apr 24 xtrabackup_logfile
[Root @ tong1 mysql-5.6.22] #

6. Restore a single database

[Root @ tong1 data] # mysqladmin-u root-p shutdown-S/tmp/mysql. sock -- close the database
Enter password:
[Root @ tong1 data] # [root @ tong1 mysql-5.6.22] # mv data desdata -- backup the current data directory

[Root @ tong1 mysql-5.6.22] # innobackupex -- user = root -- password = -- host = localhost -- database = tong -- apply-log/opt/tong/2015-04-24_13-31-46/

[Root @ tong1 mysql-5.6.22] # innobackupex -- user = root -- password = -- host = localhost -- database = tong -- copy-back/opt/tong/2015-04-24_13-31-46/

[Root @ tong1 mysql-5.6.22] # mv data tong -- rename the recovered Database

[Root @ tong1 mysql-5.6.22] # mv desdata data -- restore the previous data directory

[Root @ tong1 mysql-5.6.22] # cd data

[Root @ tong1 data] # cp-a ../tong. -- move the recovered tong database to the current data directory

[Root @ tong1 data] # chown-R mysql: mysql tong
[Root @ tong1 data] #/etc/init. d/mysqld restart
ERROR! MySQL server PID file cocould not be found!
Starting MySQL. SUCCESS!
[Root @ tong1 data] # mysql-u root-p-e "select * from tong. t1"
Enter password:
+ ------ +
| Q |
+ ------ +
| 9 |
| 8 |
+ ------ +
[Root @ tong1 data] #

7. Add Incremental backup to the full backup

[Root @ tong1 data] # mysql-u root-p-e "select * from tong. t"
Enter password:
+ ------ +
| A |
+ ------ +
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+ ------ +
[Root @ tong1 data] #

[Root @ tong1 mysql-5.6.22] # mysql-u root-p-e "insert into tong. t values (6), (7 )"
Enter password:
[Root @ tong1 mysql-5.6.22] # mysql-u root-p-e "select * from tong. t"
Enter password:
+ ------ +
| A |
+ ------ +
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+ ------ +

[Root @ tong1 mysql-5.6.22] # innobackupex -- user = root -- password = -- host = localhost -- incremental-basedir =/opt/all1/2015-04-24_13-12-46 // opt/increm/-- socket =/tmp/mysql. sock

-- Incremental Backup

-- Incremental-basedir -- path of the complete backup file

/Opt/tong -- storage location of Incremental Backup Files

[Root @ tong1 mysql-5.6.22] # ll/opt/increm/2015-04-24_14-11-07/
Total 172
-Rw-r --. 1 root 295 Apr 24 backup-my.cnf
Drwxr-xr-x. 2 root 4096 Apr 24 database
-Rw-r -----. 1 root 131072 Apr 24 ibdata1.delta
-Rw-r -----. 1 root 44 Apr 24 ibdata1.meta
Drwxr-xr-x. 2 root 4096 Apr 24 mysql
Drwxr-xr-x. 2 root 4096 Apr 24 performance_schema
Drwxr-xr-x. 2 root 4096 Apr 24 test
Drwx ------. 2 root 4096 Apr 24 tong
-Rw-r --. 1 root 21 Apr 24 14:11 xtrabackup_binlog_info
-Rw-r -----. 1 root 93 Apr 24 xtrabackup_checkpoints
-Rw-r --. 1 root 667 Apr 24 xtrabackup_info
-Rw-r -----. 1 root 2560 Apr 24 xtrabackup_logfile
[Root @ tong1 mysql-5.6.22] #

8. Restore Incremental Backup Data

[Root @ tong1 mysql-5.6.22] # mysqladmin-u root-p shutdown-S/tmp/mysql. sock
Enter password:
[Root @ tong1 mysql-5.6.22] # mv data srcdata -- remove directory data files

[Root @ tong1 mysql-5.6.22] # innobackupex -- user = root -- password = -- host = localhost -- apply-log/opt/all1/2015-04-24_13-12-46/-- first perform full recovery

[Root @ tong1 mysql-5.6.22] # innobackupex -- user = root -- password = -- host = localhost -- apply-log -- redo-only/opt/all1/2015-04-24_13-12-46/-- incremental-dir =/ opt/increm/2015-04-24_14-11-07/-- execute incremental recovery

[Root @ tong1 mysql-5.6.22] # innobackupex -- user = root -- password = -- host = localhost -- copy-back/opt/all1/2015-04-24_13-12-46/-- the last test bay Data File

[Root @ tong1 mysql-5.6.22] # chown-R mysql: mysql data -- Modify permissions
[Root @ tong1 mysql-5.6.22] #/etc/init. d/mysqld restart -- start the service
ERROR! MySQL server PID file cocould not be found!
Starting MySQL... SUCCESS!
[Root @ tong1 mysql-5.6.22] # mysql-u root-p-e "select * from tong. t"
Enter password:
+ ------ +
| A |
+ ------ +
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 | -- the data is returned.
+ ------ +
[Root @ tong1 mysql-5.6.22] #

9. Compression during full backup to save resources

[Root @ tong1 mysql-5.6.22] # innobackupex -- user = root -- password = -- host = localhost -- stream = tar -- include = tong -- socket =/tmp/mysql. sock/home | bzip2-z>/home/tong.tar.bzip2 -- back up the specified database and compress the backup

-- Stream = tar -- compression backup

-- Include = tong -- only back up the tong Database

[Root @ tong1 mysql-5.6.22] # ll/home/tong *-d

Drwx ------. 3 mysql 4096 Apr 24/home/tong
-Rw-r --. 1 root 11464 Apr 24/home/tong.tar.bzip2
[Root @ tong1 mysql-5.6.22] #

10. decompress the data file

[Root @ tong1 mysql-5.6.22] # cd/home/

[Root @ tong1 home] # mkdir 123
[Root @ tong1 home] # mv tong.tar.bzip2 123/
[Root @ tong1 home] # cd 123/
[Root @ tong1 123] # tar xvfi tong.tar.bzip2 -- the-I parameter must be added.
/Backup-my.cnf
Ibdata1
Tong/t. ibd
Tong/t1.ibd
Tong/t. frm
Tong/t1.frm
Tong/db. opt
Tong12/t. frm
Tong12/t1.frm
Tong12/db. opt
./Xtrabackup_binlog_info
Xtrabackup_logfile
Xtrabackup_checkpoints
./Xtrabackup_info
[Root @ tong1 123] #