xtrabackup--backup MySQL

Xtrabackup Introduction

Xtrabackup is a free database hot-backup software Percona open source, which can back up non-blocking databases of InnoDB databases and XTRADB storage engines (as well as a table lock for MyISAM backups); The Mysqldump backup method is a logical backup that takes The biggest drawback is the slow backup and restore, which is less appropriate if the database is larger than the 50g,mysqldump backup.

Xtrabackup after the installation is complete, there are 4 executables, 2 of which are more important backup tools are Innobackupex, xtrabackup

1) Xtrabackup is specifically used to back up the InnoDB table, and MySQL server does not interact;

2) Innobackupex is a Perl script that encapsulates Xtrabackup, supports simultaneous backup of InnoDB and MyISAM, but requires a global read lock for MyISAM backups.

3) Xbcrypt encryption and decryption Backup tool

4) Xbstream circulated package Transfer tool, similar to tar

Xtrabackup Advantages

Xtrabackup Advantages

1) Fast backup speed, reliable physical backup

2) The backup process does not interrupt the executing transaction (no lock table required)

3) Ability to save disk space and traffic based on functions such as compression

4) Automatic backup check

5) Fast restore speed

6) can be circulated to transfer the backup to another machine

7) Back up data without increasing server load

Xtrabackup Backup Principle

Backup principle

Backup starts with a background detection process, real-time detection of changes in the MYSQ redo, once a new log is found to write, immediately log into the background log file Xtrabackup_log, and then copy the InnoDB data file a system tablespace file Ibdatax, After the copy finishes, the flush tables with Readlock is executed, and then the. frm MYI myd, and so on, finally executes unlock tables and eventually stops Xtrabackup_log

Output the following message

Xtrabackup:transaction Log of LSN (2543172) to (2543181) was copied.

171205 10:17:52 completed ok!

Xtrabackup Incremental Backup Introduction

Xtrabackup the principle of incremental backups is:

1), first complete a full backup, and record the checkpoint LSN at this point;

2), and then incremental backup, compares the LSN of each page in the tablespace to the LSN of the last backup, if the page is backed up and the LSN of the current checkpoint is logged.

Incremental backup Benefits:

1), the database is too large not enough space for full backup, incremental backup can effectively save space, and high efficiency;

2), support hot backup, the backup process does not lock the table (for InnoDB), does not block the database read and write;

3), daily backup only produce a small amount of data, can also use remote backup, saving local space;

4), backup recovery based on file operations, reduce the risk of direct database operations;

5), more efficient backup, more efficient recovery.

Xtrabackup installation

Download and install Xtrabackup

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/ Percona-xtrabackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar

[email protected] ~]# LL

Total 703528

-rw-r--r--1 root root 654007697 Sep 09:18 mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

-rw-r--r--1 root root 65689600 Nov 00:11 Percona-xtrabackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar

[Email protected] ~]# Tar XF Percona-xtrabackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar

[email protected] ~]# Yum install percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm-y

[email protected] ~]# which xtrabackup

/usr/bin/xtrabackup

[Email protected] ~]# innobackupex-v

Innobackupex version 2.4.9 Linux (x86_64) (revision id:a467167cdd4)

#已经安装完成

Create test data

mysql> CREATE DATABASE test;

Query OK, 1 row Affected (0.00 sec)

mysql> use test;

Database changed

Mysql> CREATE TABLE T1 (name varchar () not null,sex varchar (TEN) not NULL);

Query OK, 0 rows affected (0.15 sec)

mysql> INSERT into T1 values (' Zhang ', ' Mans ');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT into T1 values (' Zhan ', ' Mans ');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT into T1 values (' Sun ', ' woman ');

Query OK, 1 row Affected (0.00 sec)

Xtrabackup Full-scale backup and recovery

[Email protected] ~]# Innobackupex--defaults-file=/etc/my.cnf--user=root--password= "123456"--backup/root

From the backup process you can see that a directory of time is created

[Email protected] ~]# ll/root/2017-12-04_13-57-29/

Total 12352

-rw-r-----1 root root 425 Dec 4 13:57 backup-my.cnf

-rw-r-----1 root root 322 Dec 4 13:57 Ib_buffer_pool

-rw-r-----1 root root 12582912 Dec 4 13:57 ibdata1

Drwxr-x---2 root root 4096 Dec 4 13:57 MySQL

Drwxr-x---2 root root 4096 Dec 4 13:57 Performance_schema

Drwxr-x---2 root root 12288 Dec 4 13:57 sys

Drwxr-x---2 root root 4096 Dec 4 13:57 test

-rw-r-----1 root root 4 13:57 xtrabackup_binlog_info

-rw-r-----1 root root 113 Dec 4 13:57 xtrabackup_checkpoints

-rw-r-----1 root root 537 Dec 4 13:57 xtrabackup_info

-rw-r-----1 root root 2560 Dec 4 13:57 xtrabackup_logfile

# This is the relevant backup file, you can also see the name of the library we created

[[Email protected] ~] #innobackupex--apply-log/root/2017-12-04_13-57-29/

# Use this parameter to maintain consistent state with related data files

mysql> drop table T1;

Query OK, 0 rows affected (0.01 sec)

Mysql> select * from T1;

ERROR 1146 (42S02): Table ' Test. T1 ' doesn ' t exist

Next, you're ready to recover the deleted data

# you need to ensure that the data directory is empty before recovering the data

[Email protected] ~]# Innobackupex--defaults-file=/etc/my.cnf--copy-back/root/2017-12-04_13-57-29/

# specific look at the log

[[email protected] ~]#/etc/init.d/mysqld start

Starting MySQL. success!

[[email protected] ~]# lsof-i: 3306

COMMAND PID USER FD TYPE DEVICE size/off NODE NAME

mysqld 5935 MySQL 21u IPv6 21850 0t0 TCP *:mysql (LISTEN)

mysql> use test;

Database changed

Mysql> select * from T1;

+-------+-------+

| name | sex |

+-------+-------+

| Zhang | Mans |

| Zhan | Mans |

| Sun | Woman |

+-------+-------+

3 Rows in Set (0.00 sec)

# #恢复成功

Xtrabackup incremental backup and recovery

# It is important to note that incremental backups can only be applied to inoodb or xtradb tables, and for MyISAM tables, the increments are the same as the full

Mysql> select * from T1;

+-------+-------+

| name | sex |

+-------+-------+

| Zhang | Mans |

| Zhan | Mans |

| Sun | Woman |

| Susun | Woman |

| SiGe | Mans |

| MGG | Mans |

+-------+-------+

6 rows in Set (0.00 sec)

Create data for incremental backups to simulate the removal of fully prepared data and to recover from an incremental backup file

[Email protected] ~]# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=123456--incremental/backup/-- incremental-basedir=/root/2017-12-04_13-57-29

#--incremental/backup/ Specify a directory for incremental backup file backups

#--incremental-basedir Specify the directory for the last full or incremental backup

[Email protected] ~]# ll/backup/2017-12-05_09-27-06/

Total 312

-rw-r-----1 root root 425 Dec 5 09:27 backup-my.cnf

-rw-r-----1 root root 412 Dec 5 09:27 Ib_buffer_pool

-rw-r-----1 root root 262144 Dec 5 09:27 Ibdata1.delta

-rw-r-----1 root root 5 09:27 Ibdata1.meta

Drwxr-x---2 root root 4096 Dec 5 09:27 MySQL

Drwxr-x---2 root root 4096 Dec 5 09:27 Performance_schema

Drwxr-x---2 root root 12288 Dec 5 09:27 sys

Drwxr-x---2 root root 4096 Dec 5 09:27 test

-rw-r-----1 root root 5 09:27 xtrabackup_binlog_info

-rw-r-----1 root root 117 Dec 5 09:27 xtrabackup_checkpoints

-rw-r-----1 root root 560 Dec 5 09:27 Xtrabackup_info

-rw-r-----1 root root 2560 Dec 5 09:27 xtrabackup_logfile

[Email protected] ~]# cd/backup/2017-12-05_09-27-06/

[email protected] 2017-12-05_09-27-06]# cat Xtrabackup_binlog_info

mysql-bin.000001 945

[email protected] 2017-12-05_09-27-06]# cat xtrabackup_checkpoints

Backup_type = Incremental

FROM_LSN = 2542843

TO_LSN = 2547308

LAST_LSN = 2547317

Compact = 0

Recover_binlog_info = 0

Delete a piece of data to test for incremental recovery

Mysql> Delete from T1 where name= ' Susun ';

Query OK, 1 row affected (0.06 sec)

The incremental recovery operation process is as follows

[Email protected] ~]# Innobackupex--apply-log--redo-only/root/2017-12-04_13-57-29/

[Email protected] ~]# Innobackupex--apply-log--redo-only/root/2017-12-04_13-57-29/--incremental-dir=/backup/ 2017-12-05_09-27-06/

Recover all data

[[Email protected] ~] #innobackupex--defaults-file=/etc/my.cnf--copy-back/root/2017-12-04_13-57-29/

[[email protected] ~]#/etc/init.d/mysqld start

Starting MySQL. success!

[[email protected] ~]# lsof-i: 3306

COMMAND PID USER FD TYPE DEVICE size/off NODE NAME

Mysqld 23217 mysql 21u IPv6 283226 0t0 TCP *:mysql (LISTEN)

To view the restored data integrity

xtrabackup--backup MySQL