mysql数据库表的导入和导出
基于完全备份来实现导出
将jobs库students表导入到newjob库
1 制作一个完全备份
[root@master ~]# innobackupex --user=root --password=123 /backup/
[root@master ~]# innobackupex --apply-log --export /backup/2020-09-13_05-05-02/(完全备份的目录文件)
2 找出创建students表的命令
use jobs;
show create table students\G
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLEstudents
(
StuID
int(10) unsigned NOT NULL AUTO_INCREMENT,
Name
varchar(50) NOT NULL,
Age
tinyint(3) unsigned NOT NULL,
Gender
enum(‘F’,‘M’) NOT NULL,
ClassID
tinyint(3) unsigned DEFAULT NULL,
TeacherID
int(10) unsigned DEFAULT NULL,
PRIMARY KEY (StuID
)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
3 在newjob库中创建表
create database newjobs;\c
use newjobs;
CREATE TABLEstudents
(
->StuID
int(10) unsigned NOT NULL AUTO_INCREMENT,
->Name
varchar(50) NOT NULL,
->Age
tinyint(3) unsigned NOT NULL,
->Gender
enum(‘F’,‘M’) NOT NULL,
->ClassID
tinyint(3) unsigned DEFAULT NULL,
->TeacherID
int(10) unsigned DEFAULT NULL,
-> PRIMARY KEY (StuID
)
-> ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
4 删除表空间
use newjobs;
ALTER TABLE students DISCARD TABLESPACE;
5 从备份目录下将表文件复制到新库中
[root@master ~]# cd /backup/2020-09-13_05-05-02/jobs/
[root@master jobs]# cp students.* /data/mysql/mysql3306/data/newjobs/
6 在数据库中导入
ALTER TABLE students IMPORT TABLESPACE;