How to use Percona xtrabackup to perform a single-table backup and restore of MySQL from the library "Go"

Premise

It should be determined that a single table space is used, otherwise single table backup and recovery is not supported.

In the configuration file inside the mysqld section Plus

innodb_file_per_table = 1

Environment Description:
Main Library: 192.168.0.1
From library 1:192.168.0.2
From library 2:192.168.0.3
Backup utility: Percona xtrabackup version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id:97330f7)


To create a Chenfeng library on the main library:

Mysql>CREATE DATABASE Chenfeng; Query OK,1Row affected (0.08sec) MySQL>Use chenfengdatabase changedmysql> CREATE TABLE Duansf (ID int( One), Name varchar (Ten)); Query OK,0Rows Affected (0.14sec) MySQL> INSERT into DUANSF values (1,'Duansf'); Query OK,1Row affected (0.01sec) MySQL> INSERT into DUANSF values (2,'Duansf'); Query OK,1Row affected (0.01Sec

To back up only the Duansf table for the Chenfeng library:

[Email protected] backup]# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=dsf0723-s/tmp/ Mysql.sock--slave-Info --safe-slave-backup--include=chenfeng.duansf/data/backup

Innobackup Partial parameter explanation:
--slave-info will save the binary log file name and offset of master to the Xtrabackup_slave_info file
--slave-info, backup from the library, plus--slave-info backup directory will generate more than one Xtrabackup_slave_info file,
The main log file and the offset are saved here, and the file contents are similar to the following:
Change MASTER to master_log_file= ' mysql-bin.000006 ', master_log_pos=826270;

--safe-slave-backup pauses the slave SQL thread until the backup is finished and then starts

--include=regexp
The encapsulation of the Xtrabackup parameter--tables also supports Ibbackup. Back up the included library table, for example:--include= "test.*", which means that you want to back up all the tables in the test library.
Omit this parameter if you need a full backup, or if you need to back up the 2 tables under test Library: Test1 and Test2, write:--include= "Test.test1|test.test2". You can also use wildcard characters, such as:--include= "test.test*".
In this example, we only back up the Duansf table under the Chenfeng library, so write--include=chenfeng.duansf


Since we only backed up the DUANSF table for the Chenfeng library, we 2017-10-15_20-33-07 only see the Chenfeng folder in the generated time directory.
[Email protected] 2017-10-15_20-33-07]# Ll/data/backup
Total dosage 12316
-rw-r-----. 1 root root 424 October 20:33 backup-my.cnf
-rw-r-----. 1 root root 593 October 20:33 Ib_buffer_pool
-rw-r-----. 1 root root 12582912 October 20:33 ibdata1
Drwxr-x---. 2 root root 42 October 20:33 Chenfeng
-rw-r-----. 1 root root 21 October 20:33 Xtrabackup_binlog_info
-rw-r-----. 1 root root 117 October 20:33 xtrabackup_checkpoints
-rw-r-----. 1 root root 573 October 20:33 xtrabackup_info
-rw-r-----. 1 root root 2560 October 20:33 xtrabackup_logfile
-rw-r-----. 1 root root 76 October 20:33 Xtrabackup_slave_info

[Email protected] 2017-10-15_20-33-07]# CD Chenfeng
[email protected] chenfeng]# LL
Total Dosage 108
-rw-r-----. 1 root root 8586 October 20:33 duansf.frm
-rw-r-----. 1 root root 98304 October 20:33 duansf.ibd


Package the Chenfeng directory in the/data/backup/bak directory:
[Email protected] 2017-10-15_20-33-07]# tar czvf chenfeng.tar.gz Chenfeng


[Email protected] 2017-10-15_20-33-07]# MV chenfeng.tar.gz/data/backup/bak/


Decompression to do recovery by:


When recovering data, go through prepare (recovery) and restore two steps,
Prepare export Table steps:

[Email protected] backup]# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=dsf0723-s/tmp/ Mysql.sock--apply-log--export/data/backup/- -15_20--171015 £ ºinnobackupex:starting the apply-log operation 

Delete the Duansf table from library 2:

Mysql>Use chenfengreading table information forCompletion of table and column namesyou can turn off this feature to get a quicker startup with-adatabase Changedmysql>show tables;+-----------------+| Tables_in_chenfeng |+-----------------+| Duansf |+-----------------+1RowinchSet (0.00sec) MySQL>show tables;+-----------------+| Tables_in_chenfeng |+-----------------+| Duansf |+-----------------+1RowinchSet (0.00sec) MySQL>Delete from Duansf; Query OK,2Rows Affected (0.06sec) MySQL>Select*From Duansf; Empty Set (0.00Sec

Recover the DUANSF table data from the Xtrabackup backup:
To delete a table:
mysql> drop table duansf;
Query OK, 0 rows affected (0.06 sec)


Rebuilding the table structure:
mysql> CREATE TABLE ' duansf ' (
' id ' int (one) DEFAULT NULL,
' Name ' varchar (+) DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8;
Query OK, 0 rows affected (0.05 sec)


To unload a table space:
mysql> ALTER TABLE chenfeng.duansf DISCARD tablespace;
Query OK, 0 rows affected (0.01 sec)




Recover from backup:
[Email protected] chenfeng]# CP/DATA/BACKUP/2017-10-15_20-33-07/CHENFENG/{DUANSF.IBD,DUANSF.CFG,DUANSF.FRM}/usr/ Local/mysql/data/chenfeng
[email protected] chenfeng]# LL
Total Dosage 116
-rw-r-----. 1 MySQL MySQL 65 October 19:00 db.opt
-rw-r--r--. 1 root root 426 October 21:13 duansf.cfg
-rw-r-----. 1 mysql mysql 8586 October 21:06 duansf.frm
-rw-r-----. 1 root root 98304 October 21:13 duansf.ibd




[Email protected] data]# chown-r Mysql:mysql/usr/local/mysql/data/chenfeng


Load table space:
mysql> ALTER TABLE chenfeng.duansf import tablespace;
Query OK, 0 rows affected, 1 warning (0.20 sec)


View DUANSF table data:
Mysql> select * from Duansf;
+------+--------+
| ID | name |
+------+--------+
| 1 | Duansf |
| 2 | Duansf |
+------+--------+
2 rows in Set (0.00 sec)

The data has been restored.

Turn from

http://blog.itpub.net/15498/viewspace-2146003/

How to use Percona xtrabackup to perform a single-table backup and restore of MySQL from the library "Go"