Xtrabackup is a free database hot backup software from Percona. It can backup InnoDB database and XtraDB storage engine database without blocking (MyISAM backup also requires table lock). Mysqldump backup is a logical backup. The biggest disadvantage of mysqldump backup is its slow backup and recovery speed. If the database is larger than 50 GB, mysqldump backup is not suitable. Innobackupex and Xtrabackup are two important backup tools. 1) Xtrabackup is specially used to backup InnoDB tables and does not interact with mysql Server. Innobackupex isa Perl script that covers Xtrabackup. It supports both InnoDB and MyISam backup, but requires a global read lock for myISam backup. Xbcrypt encryption and decryption backup tool 4) XBStream distribution package transmission tool, similar to tar
Xtrabackup advantages
1) backup speed, reliable physical backup 2) backup process will not interrupt the executing transactions (need not lock table) 3) can be based on function such as compression to save disk space and traffic 4) automatic backup check 5) reduction speed 6) can be spread to transmit the backup to another machine on the 7) without increasing the load on the server backup data
Xtrabackup backups principles backup at the beginning of the first detected opens a background process, real-time detection of mysql redo changes, once found a new log write, log file xtrabackup_log immediately log into the background, Flush tables with readlock, then copy. FRM MYI MYD. Finally, unlock tables is executed to stop Xtrabackup_log. The following information is displayed
xtrabackup: Transaction log of lsn (2543172) to (2543181) was copied.
171205 10:17:52 completed OK!
Copy the codeXtrabackup Installation Download Install Xtrabackup
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/Percona-XtraBackup-2.4.9-ra 467167cdd4-el6-x86_64-bundle.tar [root@centos ~]# llTotal 703528-RW-r --r-- 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. The tar
[root@centos ~]# yum install percona-xtrabackup-24-2.1.9-1.el6.x86_64. RPM -y # yum install percona-xtrabackup-24-2.1.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)Copy the codeThe installation is complete
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(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.01sec) mysql> insert into T1 values('zhan'.'man'); Query OK, 1 row affected (0.01sec) 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)
Copy the code
Innobackupex –defaults-file=/etc/my. CNF –user=root –password=”123456″ –backup /root
[root@VM_0_8_centos ~]# ll /root/2017-12-04_14-43-20/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 andtest
-rw-r----- 1 root root 22 Dec 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
Copy the codeHere is the associated backup file, as well as the name of the library we created
mysql> drop table T1; Query OK, 0 rows affected (0.01sec) mysql> select * from T1; ERROR 1146 (42S02): Table'test.T1' doesn't exist [root@VM_0_8_centos ~]# innobackupex --apply-log /root/2017-12-04_14-43-20/Copy the code# Use this parameter to maintain consistency status with related data files
Innobackupex –defaults-file=/etc/my.cnf –copy-back innobackupex –defaults-file=/etc/my.cnf –copy-back /root/2017-12-04_13-57-29/ #
[root@centos ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@centos ~]# 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 | man |
| zhan | man |
| sun | woman |
+-------+-------+
3 rows in set (0.00 sec)
Copy the code## The recovery succeeded
Note that incremental backup can only be applied to InooDB or XtraDB tables. For MyISAM tables, incremental backup is the same as full backup
mysql> select * from T1;
+-------+-------+
| name | sex |
+-------+-------+
| zhang | man |
| zhan | man |
| sun | woman |
| susun | woman |
| sige | man |
| mgg | man |
+-------+-------+
6 rows in set (0.00 sec)
Copy the codeCreate incremental backup data that simulates a full backup deletion. [root@Vcentos ~]# innobackupex –defaults-file=/etc/my.cnf –user=root –password=123456 –incremental /backup/ –incremental-basedir=/root/2017-12-04_13-57-29 #–incremental-basedir =/root/2017-12-04_13-57-29 # Specifies the directory for the last full or incremental backup
[root@Vcentos ~]# 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 44 Dec 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:27test
-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_09-27-06/
[root@centos 2017-12-05_09-27-06]# cat xtrabackup_binlog_info
mysql-bin.000001 945
[root@centos 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
Copy the codeDelete a piece of data to test incremental recovery
mysql> delete from T1 where name='susun';
Query OK, 1 row affected (0.06 sec)
Copy the code
[root@centos ~]# innobackupex --apply-log --redo-only /root/2017-12-04_13-57-29/
[root@centos ~]# innobackupex --apply-log --redo-only /root/2017-12-04_13-57-29/ --incremental-dir=/backup/2017-12-05_09-27-06/
Copy the codeRestore 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 start
Starting MySQL.. SUCCESS!
[root@centos ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 23217 mysql 21u IPv6 283226 0t0 TCP *:mysql (LISTEN)
Copy the codeView restored data integrity
More exciting content please pay attention to the migrant elder brother public number