Shulou(Shulou.com)06/01 Report--

Xtrabackup introduction

Xtrabackup is a free database hot backup software opened by percona, it can back up InnoDB database and XtraDB storage engine database non-blocking (also need table lock for MyISAM backup); mysqldump backup mode is logical backup, its biggest defect is that backup and recovery speed is slow, if the database is greater than 50GB mysqldump backup is not suitable.

There are 4 executable files after Xtrabackup installation, among which two important backup tools are innobackupex and xtrabackup.

1) xtrabackup is specially used to back up InnoDB tables and has no interaction with mysql server

2) innobackupex is a Perl script that encapsulates xtrabackup, which supports backing up innodb and myisam at the same time, but a global read lock is required when backing up myisam.

3) xbcrypt encryption and decryption backup tool

4) xbstream streaming package transfer tool, similar to tar

Advantages of Xtrabackup

1) Fast backup and reliable physical backup

2) the backup process will not interrupt the transaction in progress (no need to lock the table)

3) can save disk space and traffic based on compression and other functions

4) automatic backup check

5) Fast reduction speed

6) it can be spread and transferred to another machine.

7) backup data without increasing the load on the server

Principle of Xtrabackup backup

Backup principle

At the beginning of the backup, a background detection process will be started to detect mysq redo changes in real time. Once new log writes are found, the log will be recorded in the background log file xtrabackup_log immediately, and then the innodb data file-system tablespace file ibdatax will be copied. After the copy is finished, flush tables with readlock will be executed, then .frm MYI MYD and other files will be copied, finally unlock tables will be executed, and finally xtrabackup_log will be stopped.

Output the following prompt information

Xtrabackup: Transaction log of lsn (2543172) to (2543181) was copied.171205 10:17:52 completed OK!

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[root@centos ~] # lltotal 703528 RWMurray RWMurray Rafael-1 root root 654007697 Sep 27 09:18 mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz-rw-r--r-- 1 root root 65689600 Nov 30 00 11 Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar [root@centos ~] # tar xf Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar [root@centos ~] # yum install percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm-y [root@centos ~] # which xtrabackup / usr/bin/xtrabackup [root@centos ~] # innobackupex-vinnobackupex version 2.4.9 Linux (x86 / 64) (revision id: a467167cdd4)

# installation has been completed

Create test data

Mysql > create database test;Query OK, 1 row affected (0.00 sec) mysql > use test;Database changedmysql > create table T1 (name varchar (10) not null,sex varchar (10) not null); Query OK, 0 rows affected (0.15 sec) mysql > insert into T1 values ('zhang','man'); Query OK, 1 row affected (0.01 sec) mysql > insert into T1 values (' zhan','man'); Query OK, 1 row affected (0 sec) mysql > insert into T1 values ('sun','woman') Query OK, 1 row affected (0.00 sec) mysql > select * from T1 + | name | sex | +-+-+ | zhang | man | | zhan | man | | sun | woman | +-+-+ 3 rows in set (0.00 sec)

Full backup and recovery

Innobackupex-defaults-file=/etc/my.cnf-user=root-password= "123456"-backup / root

You can see from the screenshot of the backup process that a directory of time will be created.

[root@VM_0_8_centos] # ll / root/2017-12-04 Dec 14-43-20/total 12352 root root 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 ibdata1drwxr-x--- 2 root root 4096 Dec 4 13:57 mysqldrwxr-x- -- 2 root root 4096 Dec 4 13:57 performance_schemadrwxr-x--- 2 root root 12288 Dec 4 13:57 sysdrwxr-x--- 2 root root 4096 Dec 4 13:57 test-rw-r- 1 root root 22 Dec 4 13:57 xtrabackup_binlog_info-rw-r- 1 root root 113Dec 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, and you can also see the name of the library we created

Mysql > drop table T1 challenge query OK, 0 rows affected (0.01mm) mysql > select * from T1 candidate error 1146 (42S02): Table 'test.T1' doesn't challenge [root @ VM_0_8_centos] # innobackupex-- apply-log / root/2017-12-04 / 14-43-20 /

# use this parameter to maintain a consistent state using relevant data files

Next, we are ready to restore the mistakenly deleted data.

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

[root@centos] # innobackupex-- defaults-file=/etc/my.cnf-- copy-back / root/2017-12-04 / 13-57-29 /

# look at the log screenshot

[root@centos] # / etc/init.d/mysqld startStarting MySQL. SUCCESS! [root@centos ~] # lsof-I: 3306COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 5935 mysql 21u IPv6 21850 0t0 TCP *: mysql (LISTEN) mysql > use test;Database changedmysql > select * from T1 + | name | sex | +-+-+ | zhang | | zhan | man | | sun | woman | +-+-+ 3 rows in set

# # restore successfully

Xtrabackup incremental backup and recovery

# it should be noted that incremental backups can only be applied to InooDB or XtraDB tables. For MyISAM tables, incremental backups are the same as complete.

Mysql > select * from T1 + | name | sex | +-+-+ | zhang | man | | zhan | man | | sun | sun | susun | woman | | sige | man | | mgg | man | +-+-+ 6 rows in set (0.00 sec)

Create data for incremental backup to simulate whether the fully deleted data can be restored through incremental backup files.

[root@Vcentos] # innobackupex-- defaults-file=/etc/my.cnf-- user=root-- password=123456-- incremental / backup/-- incremental-basedir=/root/2017-12-04mm 13-57-29

#-incremental / backup/ specifies the directory where incremental backup files are backed up

#-incremental-basedir specifies the directory of the last full or incremental backup

[root@Vcentos] # ll / backup/2017-12-05 October 09-27-06/total 312 root root RWMI 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 44 Dec 5 09:27 ibdata1.metadrwxr-x- -- 2 root root 4096 Dec 5 09:27 mysqldrwxr-x--- 2 root root 4096 Dec 5 09:27 performance_schemadrwxr-x--- 2 root root 12288 Dec 5 09:27 sysdrwxr-x--- 2 root root 4096 Dec 5 09:27 test-rw-r- 1 root root 21 Dec 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 [root @ centos ~] # cd / backup/2017-12-05 cat xtrabackup_binlog_infomysql-bin.000001 09-27-06 / [root@centos 2017-12-05 09-27-06] # cat xtrabackup_binlog_infomysql-bin.000001 945 [root@centos 2017-12-05mm 09-27-06] # cat xtrabackup_checkpointsbackup_type = incrementalfrom_lsn = 2542843to_lsn = 2547308last_lsn = 2547317compact = 0recover_binlog_info = 0

Delete a piece of data to test incremental recovery

Mysql > delete from T1 where name='susun';Query OK, 1 row affected (0.06 sec)

The procedure for incremental recovery is as follows

[root@centos] # innobackupex-- apply-log-- redo-only / root/2017-12-04mm 13-57-29 / [root@centos] # innobackupex-- apply-log-- redo-only / root/2017-12-04mm 13-57-29 /-- incremental-dir=/backup/2017-12-05mm 09-27-06 /

Recover all data

[root@centos] # innobackupex-- defaults-file=/etc/my.cnf-- copy-back / root/2017-12-04 / 13-57-29 / [root@centos] # / etc/init.d/mysqld startStarting MySQL.. SUCCESS! [root@centos] # lsof-I: 3306COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 23217 mysql 21u IPv6 283226 0t0 TCP *: mysql (LISTEN)

View restored data integrity

For more exciting content, please follow the official account of migrant workers (mingogge-linux).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.