mysql数据库表的导入和导出（基于完全备份来实现导出）

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 TABLE students (
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 TABLE students (
-> 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;