Reference implementations:
Https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/innobackupex_script.html
restoring individual tables#5.6 is not supported before;in server Versions prior to 5.6, it is not possible to copy tables between servers by copying the files, even with innodb_file_per_ Table. however, with the percona xtrabackup, you can export individual Tables from any innodb database, and import them into percona Server with XtraDB or MySQL 5.6 (THE SOURCE DOESN ' T have to be xtradb or or mysql 5.6,but the destination does). this only works on individual .ibd files, and cannot export a table that is not contained in its own .ibd file.
Data backup:
[[email protected] ~] $systemctl start mariadb[[email protected] ~] $mysql < hellodb_InnoDB.sql one file for each table by default [[[Email protected] ~] $ll /var/lib/mysql/total &NBSP;122936-RW-RW---- 1 mysql mysql 16384 feb 25 16:11 &NBSP;ARIA_LOG.00000001-RW-RW---- 1 mysql mysql 52 &NBSP;FEB&NBSP;25&NBSP;16:11&NBSP;ARIA_LOG_CONTROL-RW-RW---- 1 mysql mysql &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;5&NBSP;FEB&NBSP;25&NBSP;16:18&NBSP;CENTOS7X.PIDDRWX------ 2 mysql &NBSP;MYSQL&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;272&NBSP;FEB&NBSP;25&NBSP;16:18&NBSP;HELLODB-RW-RW---- 1 mysql mysql 2795 feb 25 16:11 ib_buffer_ POOL-RW-RW----&NBSP;1&NBSP;MYSQL&NBSP;MYSQL&NBSP;12582912&NBSP;FEB&NBSP;25&NBSP;16:18&NBSP;IBDATA1-RW-RW---- 1 mysql mysql 50331648&nBsp FEB&NBSP;25&NBSP;16:18&NBSP;IB_LOGFILE0-RW-RW---- 1 mysql mysql 50331648 feb 25 &NBSP;16:11&NBSP;IB_LOGFILE1-RW-RW---- 1 mysql mysql 12582912 feb 25 16:18 &NBSP;IBTMP1-RW-RW---- 1 mysql mysql 0 feb 25 16:18 multi-master.infodrwx--x--x 2 mysql mysql 4096 Feb 25 16:11 mysqlsrwxrwxrwx 1 mysql mysql &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;0&NBSP;FEB&NBSP;25&NBSP;16:18&NBSP;MYSQL.SOCKDRWX------ 2 mysql Mysql 20 feb 25 16:11 performance_ SCHEMA-RW-RW---- 1 mysql mysql 24576 Feb 25 16:18 tc.logdrwxr-xr-x 2 mysql mysql 6 feb 25 16:11 test[[email pRotected] ~] $ll &NBSP;/VAR/LIB/MYSQL/HELLODB/TOTAL&NBSP;704-RW-RW---- 1 mysql mysql &NBSP;1277&NBSP;FEB&NBSP;25&NBSP;16:18&NBSP;CLASSES.FRM-RW-RW---- 1 mysql mysql 98304 &NBSP;FEB&NBSP;25&NBSP;16:18&NBSP;CLASSES.IBD-RW-RW---- 1 mysql mysql 976 &NBSP;FEB&NBSP;25&NBSP;16:18&NBSP;COC.FRM-RW-RW---- 1 mysql mysql 98304 feb 25 &NBSP;16:18&NBSP;COC.IBD-RW-RW---- 1 mysql mysql 1251 feb 25 16:18 &NBSP;COURSES.FRM-RW-RW---- 1 mysql mysql 98304 Feb 25 16:18 COURSES.IBD-RW-RW---- 1 mysql mysql 61 Feb 25 16:18 DB.OPT-RW-RW---- 1 mysql mysql 1001 Feb 25 16:18 SCORES.FRM-RW-RW---- 1 mysql mysql 98304 Feb 25 16:18 SCORES.IBD-RW-RW----&NBSP;1&NBSP;MYSQL&NBSP;MYSQL&NBSP;&NBSP;1208&NBsp FEB&NBSP;25&NBSP;16:18&NBSP;STUDENTS.FRM-RW-RW---- 1 mysql mysql 98304 feb 25 &NBSP;16:18&NBSP;STUDENTS.IBD-RW-RW---- 1 mysql mysql 1298 Feb 25 16:18&NBSP;TEACHERS.FRM-RW-RW---- 1 mysql mysql 98304 Feb 25 16:18 TEACHERS.IBD-RW-RW---- 1 mysql mysql 973 Feb 25 16:18 TOC.FRM-RW-RW---- 1 mysql mysql 98304 feb 25 16:18 toc.ibd[[email protected] ~] $mkdir -pv /backupsmkdir: created directory '/backups ' [[Email protected] ~] $innobackupex --include= ' hellodb.students ' /backups/It's just backing up the data.;[[email Protected] ~] $ll /backups/2018-02-25_16-23-08/hellodb/total 100-rw-r----- 1 root root 1208 feb 25 16:23 students.frm-rw-r----- 1 root root 98304 feb 25&nbSp;16:23 students.ibd
Backup table Definition:
Therefore, the table definition needs to be also directed out, otherwise the future recovery process will need to create a table definition; [[email protected] ~] $mysql-e ' show create table hellodb.students\g; ' 1. Row *************************** table:studentscreate table:create Table ' Students ' (' stuid ' int (ten) unsigned not Null auto_increment, ' Name ' varchar (not null), ' Age ' tinyint (3) unsigned not NULL, ' Gender ' enum (' F ', ' M ') is not NULL, ' ClassID ' tinyint (3) unsigned default null, ' Teacherid ' int (ten) unsigned default NULL, PRIMARY KEY (' Stuid ')) engine=i Nnodb auto_increment=26 DEFAULT Charset=utf8
Table Break Operation:
The destruction of the table; [[email protected] ~] $mysql -e ' drop table hellodb.students; ' mariadb [hellodb]> show tables; +------------ -------+ | tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | teachers | | toc | +-------------------+ 6 rows in set (0.00 sec)
Data and table definition recovery operations:
Restore operation: first tidy; [[email protected] ~] $innobackupex --apply-log --export /backups/2018-02-25_ 16-23-08/to organize and export the data before and after; [[email protected] ~] $ll /backups/2018-02-25_16-23-08/hellodb/-rw-r----- 1 root root 1208 feb 25 16:23 students.frm-rw-r----- 1 root root 98304 feb 25 16:23 students.ibd[[email protected] ~]$ ll /backups/2018-02-25_16-23-08/hellodb/total 120-rw-r--r-- 1 root root 640 feb 25 16:49 students.cfg-rw-r----- 1 root root 16384 Feb 25 16:49 students.exp-rw-r----- 1 root root 1208 feb 25 16:23 students.frm-rw-r----- 1 root root 98304 feb 25 16:23 &NBSP;STUDENTS.IBD Create a table structure before restoring the table; mariadb [(none)]> use hellodb; Reading table information for complEtion of table and column namesyou can turn off this feature to get a quicker startup with -adatabase changedmariadb [hellodb ]> create table ' Students ' ( -> ' Stuid ') int (Ten) unsigned NOT NULL AUTO_INCREMENT, -> ' Name ' varchar NOT NULL, -> ' age ' tinyint (3) unsigned NOT NULL, -> ' Gender ' enum ( ' F ', ' M ') NOT NULL, -> ' ClassID ' tinyint (3) unsigned default null, -> ' TeacherID ' int (Ten) unsigned default null, -> primary key (' StuID ') -> ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8; query ok, 0 rows affected (0.01 sec) mariadb [hellodb]> select * from students; empty set (0.00 sec) mariadb [hellodb]> desc students;+-----------+----------- ----------+------+-----+---------+----------------+| field | type | null | key | default | extra |+- ----------+---------------------+------+-----+---------+----------------+| stuid | int (Ten) unsigned | NO | PRI | null | auto_increment | | name | varchar (&NB)sp; | no | | NULL | | | age | tinyint (3) unsigned | NO | | NULL | | | gender | enum (' F ', ' M ') | no | | NULL | | | classid | tinyint (3) unsigned | YES | | NULL | | | teacherid | int (Ten) unsigned | YES | | NULL | |+-----------+---------------------+------+-----+---------+--------- -------+6 rows in set (0.00 sec), although there is a table structure and data files, but no data; [[email protected] ~]$ LL&NBSP;/VAR/LIB/MYSQL/HELLODB/&NBSP;-HTOTAL&NBSP;704K-RW-RW---- 1 mysql mysql 1.3K FEB&NBSP;25&NBSP;16:18&NBSP;CLASSES.FRM-RW-RW---- 1 mysql mysql 96k feb 25 &NBSP;16:18&NBSP;CLASSES.IBD-RW-RW---- 1 mysql mysql 976 feb 25 16:18 &NBSP;COC.FRM-RW-RW---- 1 mysql mysql 96K Feb 25 16:18 COC.IBD-RW-RW---- 1 mysql mysql 1.3k feb&NBSP;25&NBSP;16:18&NBSP;COURSES.FRM-RW-RW---- 1 mysql mysql 96k feb 25 &NBSP;16:18&NBSP;COURSES.IBD-RW-RW---- 1 mysql mysql 61 Feb 25 16:18&NBSP;DB.OPT-RW-RW---- 1 mysql mysql 1001 Feb 25 16:18 SCORES.FRM-RW-RW---- 1 mysql mysql 96K Feb 25 16:18 SCORES.IBD-RW-RW---- 1 mysql mysql 1.2K Feb 25 16:53 STUDENTS.FRM-RW-RW---- 1 mysql mysql 96K Feb 25 16:53 STUDENTS.IBD-RW-RW---- 1 mysql mysql 1.3K Feb 25 16:18 TEACHERS.FRM-RW-RW---- 1 mysql mysql 96K Feb 25 16:18 TEACHERS.IBD-RW-RW---- 1 mysql mysql 973 Feb 25 16:18 TOC.FRM-RW-RW----&NBSP;1&NBSP;MYSQL&NBSP;MYSQL&NBSP;&NBSP;96K&NBSP;FEB&NBSP;25&NBSP;16:18&NBSP;TOCIBD then deletes the tablespace file, which is the data file, but does not use RM Delete, but instead uses the command to delete it; mariadb [hellodb]> alter table students discard tablespace; query ok, 0 rows affected (0.00&NBSP;SEC) [[email protected] ~] $ll /var /LIB/MYSQL/HELLODB/&NBSP;-HTOTAL&NBSP;608K-RW-RW---- 1 mysql mysql 1.3k feb 25 &NBSP;16:18&NBSP;CLASSES.FRM-RW-RW---- 1 mysql mysql 96k feb 25 16:18 &NBSP;CLASSES.IBD-RW-RW---- 1 mysql mysql 976 Feb 25 16:18 COC.FRM-RW-RW---- 1 mysql mysql 96K Feb 25 16:18 COC.IBD-RW-RW---- 1 mysql mysql 1.3K Feb 25 16:18 COURSES.FRM-RW-RW---- 1 mysql mysql 96K Feb 25 16:18 COURSES.IBD-RW-RW---- 1 mysql mysql 61 Feb 25 16:18 DB.OPT-RW-RW----&NBSP;1&NBSP;MYSQL&NBSP;MYSQL&NBSP;1001&NBSP;FEB&NBSP;25&NBSP;16:18&NBSP;SCORES.FRM-RW-RW---- 1 mysql mysql 96k &NBSP;FEB&NBSP;25&NBSP;16:18&NBSP;SCORES.IBD-RW-RW---- 1 mysql mysql 1.2k feb 25 &NBSP;16:53&NBSP;STUDENTS.FRM-RW-RW---- 1 mysql mysql 1.3k feb 25 16:18 &NBSP;TEACHERS.FRM-RW-RW---- 1 mysql mysql 96K Feb 25 16:18 TEACHERS.IBD-RW-RW---- 1 mysql mysql 973 Feb 25 16:18 TOC.FRM-RW-RW---- 1 mysql mysql 96k feb 25 16:18 toc.ibd[[email protected] ~] $CP /backups/2018-02-25_16-23-08/hellodb/students. {cfg,ibd,exp} /var/lib/mysql/hellodb/[[email protected] ~] $ll /var/lib/mysql/hellodb/ &NBSP;-HTOTAL&NBSP;724K-RW-RW---- 1 mysql mysql 1.3K Feb 25 16:18 CLASSES.FRM-RW-RW----&NBSP;1&NBSP;MYSQL&NBSP;MYSQL&NBSP;&NBSP;96K&NBSP; FEB&NBSP;25&NBSP;16:18&NBSP;CLASSES.IBD-RW-RW---- 1 mysql mysql 976 feb 25 &NBSP;16:18&NBSP;COC.FRM-RW-RW---- 1 mysql mysql 96k feb 25 16:18 &NBSP;COC.IBD-RW-RW---- 1 mysql mysql 1.3K Feb 25 16:18 COURSES.FRM-RW-RW---- 1 mysql mysql 96K Feb 25 16:18 COURSES.IBD-RW-RW---- 1 mysql mysql 61 Feb 25 16:18 DB.OPT-RW-RW----&NBSP;1&NBSP;MYSQL&NBSP;MYSQL&NBSP;1001&NBSP;FEB&NBSP;25&NBSP;16:18&NBSP;SCORES.FRM-RW-RW---- 1 mysql mysql 96k feb 25 16:18 scores.ibd-rw-r--r-- 1 root root 640 feb 25 16:59 students.cfg-rw-r----- 1 &NBSP;ROOT&NBSP;&NBSP;ROOT&NBSP;&NBSP;&NBSP;16K&NBSP;FEB&NBSP;25&NBSP;16:59&NBSP;STUDENTS.EXP-RW-RW---- 1 mysql mysql 1.2k feb 25&Nbsp;16:53 students.frm-rw-r----- 1 root root 96k feb 25 &NBSP;16:59&NBSP;STUDENTS.IBD-RW-RW---- 1 mysql mysql 1.3k feb 25 16:18 &NBSP;TEACHERS.FRM-RW-RW---- 1 mysql mysql 96K Feb 25 16:18 TEACHERS.IBD-RW-RW---- 1 mysql mysql 973 Feb 25 16:18 TOC.FRM-RW-RW---- 1 mysql mysql 96k feb 25 16:18 toc.ibd[[email protected] ~] $chown -r mysql.mysql /var/lib/mysql/hellodb/followed by the import table space, even though the file was placed in the data directory, But the tablespace is not yet associated;mariadb [hellodb]> alter table students import tablespace; query ok, 0 rows affected (0.02&NBSP;SEC)
Data and table structure validation actions:
Verification, mariadb [hellodb]> select * from students;+-------+---------------+-----+------ --+---------+-----------+| stuid | name | age | gender | classid | teacherid |+-------+---------------+ + ----+--------+---------+-----------+| 1 | shi zhongyu | 22 | M | 2 | 3 | | 2 | shi potian | 22 | M | 1 | 7 | | 3 | xie yanke | 53 | m | 2 | 16 | | 4 | ding dian | 32 | M | 4 | 4 |
Database backup and Restore series--detailed complete implementation of single-table backup and recovery