Xtrabackup单表导出和导入

1、单表备份

[root@centos7 backup]# innobackupex --include='hellodb_innodb.students' /data/mysql/backup
200308 15:42:17 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.
200308 15:42:17 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
......
MySQL binlog position: filename 'mariadb-bin.000001', position '245'
200308 15:42:19 [00] Writing backup-my.cnf
200308 15:42:19 [00]        ...done
200308 15:42:19 [00] Writing xtrabackup_info
200308 15:42:19 [00]        ...done
xtrabackup: Transaction log of lsn (1639958) to (1639958) was copied.
200308 15:42:19 completed OK!

2、备份表结构

[root@centos7 ~]# mysql -e 'show create table hellodb_innodb.students'>/data/scripts/student.sql
[root@centos7 ~]# cat /data/scripts/student.sql 
Table	Create Table
students	CREATE TABLE `students` (\n  `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,\n  `Name` varchar(50) NOT NULL,\n  `Age` tinyint(3) unsigned NOT NULL,\n  `Gender` enum('F','M') NOT NULL,\n  `ClassID` tinyint(3) unsigned DEFAULT NULL,\n  `TeacherID` int(10) unsigned DEFAULT NULL,\n  PRIMARY KEY (`StuID`)\n) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8

3、删除表

[root@centos7 ~]# mysql -e 'drop table hellodb_innodb.students'

4、导出表

[root@centos7 backup]# innobackupex --apply-log --export /data/mysql/backup/2020-03-08_15-42-17/

5、创建表（方法一：根据备份的表结构信息创建，直接复制粘贴即可。方法二：直接编辑备份的表结构文件student.sql，把多余的语句删除）

[root@centos7 ~]# vim /data/scripts/student.sql 

CREATE TABLE `students` (\n  `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,\n  `Name` varchar(50) NOT NULL,\n  `Age` tinyint(3) unsigned NOT NULL,\n  `Gender` enum('F','M') NOT NULL,\n  `ClassID` tinyint(3) unsigned DEFAULT NULL,\n  `TeacherID` int(10) unsigned DEFAULT NULL,\n  PRIMARY KEY (`StuID`)\n) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8

导入数据库中

[root@centos7 ~]# mysql hellodb_innodb </data/scripts/student.sql 
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout

6、在导入表结构时，会创建默认的表空间（表数据），这里需要删除默认的表空间。

[root@centos7 ~]# ll /var/lib/mysql/hellodb_innodb/ | grep students
总用量 856
-rw-rw---- 1 mysql mysql  8736 3月   8 16:21 students.frm
-rw-rw---- 1 mysql mysql 98304 3月   8 16:21 students.ibd

MariaDB [hellodb_innodb]> alter table students discard tablespace
    -> ;
Query OK, 0 rows affected (0.14 sec)

[root@centos7 ~]# ll /var/lib/mysql/hellodb_innodb/ | grep students
-rw-rw---- 1 mysql mysql  8736 3月   8 16:21 students.frm

7、复制备份目录下的相关文件到数据库目录中（无需复制表结构文件，即students.frm）

[root@centos7 ~]# ls /data/mysql/backup/2020-03-08_15-42-17/hellodb_innodb/
students.cfg  students.exp  students.frm  students.ibd
[root@centos7 ~]# cp  /data/mysql/backup/2020-03-08_15-42-17/hellodb_innodb/students.{cfg,exp,ibd} /var/lib/mysql/hellodb_innodb/

8、修改文件属性，改为MySQL用户所拥有。

[root@centos7 ~]# chown -R mysql.mysql /var/lib/mysql/hellodb_innodb/
[root@centos7 ~]# ll /var/lib/mysql/hellodb_innodb/ | grep students
-rw-r--r-- 1 mysql mysql   647 3月   8 16:49 students.cfg
-rw-r----- 1 mysql mysql 16384 3月   8 16:49 students.exp
-rw-rw---- 1 mysql mysql  8736 3月   8 16:21 students.frm
-rw-r----- 1 mysql mysql 98304 3月   8 16:49 students.ibd

9、导入表空间（即数据）

MariaDB [hellodb_innodb]> alter table students import tablespace;