快速导出和导入
一 介绍
在公司中，如果运营或者产品手里有几千万甚至几亿条数据，要求你将其导入数据中，请问如何做？
如果你依据运营或产品交给你的数据文件直接使用insert语句，一行一行地批量插入，那至少需要1-2天时间才能插入完毕，此时我们可以用LOAD DATA INFILE语句。
LOAD DATA INFILE语句可以从一个文本文件中，将数据以很高的速度读入一个表中。MySQL官方文档也说明了，该方法比一次性插入一条数据性能快20倍。
此外，mysql也支持快速导出语句SELECT INTO OUTFILE
使用MySQL的SELECT INTO OUTFILE 、LOAD DATA INFILE快速导出导入数据，12G的数据导出用时3分钟左右，导入用时4分钟左右（执行时间根据机器的配置会有所不同，不具有参考价值）
二 快速导出
表的快速导出
语法：
SELECT... INTO OUTFILE 导出文本文件
要想导出成功，需要设置安全目录才行
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
示例：
SELECT * FROM db1.t1
INTO OUTFILE '/tmp/db1_t1.txt'
FIELDS TERMINATED BY ',' -- 定义字段分隔符
OPTIONALLY ENCLOSED BY '"' -- 定义字符串使用什么符号括起来
LINES TERMINATED BY '\n'; -- 定义换行符快速导入
语法
LOAD DATA INFILE 导入的文本文件路径
示例
mysql> DELETE FROM student1;
mysql> create table new_t1(表结构与文件中数据保持一致);
mysql> LOAD DATA INFILE '/tmp/db1_t1.txt'
INTO TABLE new_db.new_t1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';其他导出
mysql 命令导出文本文件
示例:
# mysql -u root -pEgon123 -e 'select * from db1.t1' > /tmp/db1_t1.txt
# mysql -u root -pEgon123 --xml -e 'select * from db1.t1' > /tmp/db1_t1.xml
# mysql -u root -pEgon123 --html -e 'select * from db1.t1' > /tmp/db1_t1.html三 示例
1、准备测试数据
create database egon_test;
use egon_test;
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male',
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','God',7300.33,401,1),
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1);设置安全目录为/tmp，并重启
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp快速导出
SELECT * FROM egon_test.employee
INTO OUTFILE '/tmp/test.txt'
FIELDS TERMINATED BY ',' -- 定义字段分隔符
OPTIONALLY ENCLOSED BY '"' -- 定义字符串使用什么符号括起来
LINES TERMINATED BY '\n'; -- 定义换行符导入
# 创建表，表结构与数据保持一致
create database if not exists test;
use test;
create table t1(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male',
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);
# 快速导入
LOAD DATA INFILE '/tmp/test.txt'
INTO TABLE test.t1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';四 数据库迁移
4.1 为何要迁移数据库
数据库迁移是我们经常可遇到的问题，对于少量的数据，迁移基本上不会有什么问题。生产环境中，有以下情况需要做迁移工作：
-
1、磁盘空间不够
比如一些老项目，选用的机型并不一定适用于数据库。随着时间的推移，硬盘很有可能出现短缺；
-
2、业务出现瓶颈
比如项目中采用单机承担所有的读写业务，业务压力增大，不堪重负。
如果 IO 压力在可接受的范围，会采用读写分离方案；
-
3、机器出现瓶颈
机器出现瓶颈主要在磁盘 IO 能力、内存、CPU，此时除了针对瓶颈做一些优化以外，选择迁移是不错的方案；
-
4、项目改造
某些项目的数据库存在跨机房的情况，可能会在不同机房中增加节点，或者把机器从一个机房迁移到另一个机房。再比如，不同业务共用同一台服务器，为了缓解服务器压力以及方便维护，也会做迁移。
4.2 MySQL迁移数据库的方案
通常有三种方案
-
1、数据库直接导出，拷贝文件到新服务器，在新服务器上导入。