17--数据快速导出、导入，数据库迁移
一、 介绍
在公司中，如果运营或者产品手里有几千万甚至几亿条数据，要求你将其导入数据中，请问如何做？ 如果你依据运营或产品交给你的数据文件直接使用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、数据库直接导出，拷贝文件到新服务器，在新服务器上导入。
#例如
（1）基于mysqldump
在源主机执行下述命令，需要目标主机开启远程账号权限
mysqldump -h 迁移源IP -uroot -p123 --databases bbs | mysql -h 目标IP -
uroot -p456
（2）基于LOAD DATA INFILE
#优点：
会重建数据文件，减少数据文件的占用空间（释放undo段），兼容性最好，导出导入很少发生问
题，需求灵活
#缺点：
导入导出都需要很长的时间，并且导出后的文件还要经过网络传输，也要占用一定的时间。
- 2、使用第三方迁移工具。
#例如
使用【MySQL GUI Tools】中的 MySQLMigrationTool。
#优点：
设置完成后传输无人值守，自动完成
#缺点：
1、不够灵活，设置繁琐
2、传输时间长，
3、传输中网络出现异常，不能及时的被发现，并且会一直停留在数据传输的状态不能被停止，
如不仔细观察不会被发现异常。
4、异常后很难从异常的位置继续传输。
- 3、数据文件和库表结构文件直接拷贝到新服务器，挂载到同样配置的MySQL服务下。
优点：
时间占用最短，文件可断点传输，操作步骤少。
缺点：
新旧服务器中MySQL版本及配置必须相同，可能引起未知问题。
步骤
1、保证Mysql版本一致，安装配置基本一致（注意：这里的数据文件和库表结构文件都指定在同一目录
data下）
2、停止两边的Mysql服务（A服务器--迁移-->B服务器)
3、删除B服务器Mysql的data目录下所有文件
4、拷贝A服务器Mysql的data目录下除了ib_logfile和.err之外的文件到B服务器data下
5、启动B服务器的Mysql服务，检测是否发生异常
假如数据库迁移是因为业务瓶颈或项目改造等需要变动数据表结构的(比如分区分表)，我们便只能使用第 一种方法了。
