MySQL的数据迁移

一、前言

数据库的数据迁移是我们经常遇到的问题，如因磁盘空间不够、业务出现瓶颈、项目改造等原因。

二、MySQL数据迁移常用的方法

1、使用第三方迁移工具。
2、数据文件和库表结构直接拷贝到新服务器，挂载到同样配置的MySQL服务下。
3、数据库直接导出，拷贝文件到新服务器上，在新服务器导入。

第一种方案的优点：时间占用短，文件可断点传输，操作步骤少。缺点：新旧服务器中MySQL版本及配置必须相同，可能引起未知问题。

第二种方案的优点：设置完成后传输无人值守，自动完成。缺点：不够灵活，设置繁琐，传输时间长，异常后很难从异常的位置继续传输。

第三种方案的优点：会重建数据文件，减少数据文件的占用空间，兼容性最好，导出导入很少发生问题，需求灵活。缺点:使用传统导出导入时间占用长，数据较大时，容易发生导入中断。

1.1、第三方迁移工具，可以参考：第三方工具迁移数据

2.1、拷贝方法和挂载步骤可以参考：先备份，然后利用服务器的xshell中Xtfp传输备份文件到另一台服务器，之后执行1.1.4的方法

3.1、数据库直接导出，可以参考：导出数据库

3.1.1、表的数据量小，且表结构不变，可以选择直接导出表的数据和表的结构。
3.1.2、表的数据量大，且需要改动表结构，可以选择单独导出表的结构，之后使用MySQL的SELECT INTO OUTFILE 、LOAD DATA INFILE快速导出导入数据，可以看MySQL官方文档，该方法比一次性插入一条数据性能快20倍。

3.1.2.1、利用MySQL自身的语句快速导出导入数据

SELECT INTO…OUTFILE语句把表数据导出到一个文本文件中，并用LOAD DATA …INFILE语句恢复数据。但是这种方法只能导出或导入数据的内容，不包括表的结构，如果表的结构文件损坏，则必须先恢复原来的表的结构。

select [列名] from table [where 语句] into outfile ‘目标文件’ [option]

该语句分为两个部分。前半部分是一个普通的select语句，后半部分是导出数据。‘目标文件’参数是将查询的记录导出到哪个文件；‘option’参数为选参数选项，可能的取值有：
1、FIELDS TERMINATED BY ‘字符串’：设置字符串为字段之间的分隔符，可以为单个或多个字符。默认值是“\t”。
2、FIELDS ENCLOSED BY ‘字符’：设置字符来括住字段的值，只能为单个字符。默认情况下不使用任何符号。
3、FIELDS OPTIONALLY ENCLOSED BY ‘字符’：设置字符来括住CHAR、VARCHAR和TEXT等字符型字段。默认情况下不使用任何符号。
4、FIELDS ESCAPED BY ‘字符’：设置转义字符，只能为单个字符。默认值为“\”。

5、LINES STARTING BY ‘字符串’：设置每行数据开头的字符，可以为单个或多个字符。默认情况下不使用任何字符。
6、 LINES TERMINATED BY ‘字符串’：设置每行数据结尾的字符，可以为单个或多个字符。默认值是“\n”。

提示：

1、FIELDS和LINES两个子句都是自选的，但是如果两个子句都被指定了，FIELDS必须位于LINES的前面。
2、该语法中的“目标文件”被创建到服务器主机上，因此必须拥有文件写入权限（FILE权限）后，才能使用此语法。同时，“目标文件”不能是一个已经存在的文件。
3、SELECT…INTO OUTFILE语句可以非常快速地把一个表转储到服务器上。如果想要在服务器主机之外的部分客户主机上创建结果文件，则不能使用SELECT…INTO OUTFILE语句（或者创建文件后，再用第三方工具导出到客户主机上）。
4、不管是SELECT INTO…OUTFILE，还是LOAD DATA …INFILE，文件都需要存放在指定的路径中，参考：secure_file_priv参数说明

3.1.2.2、实例：

使用SELECT…INTO OUTFILE语句来导出example数据库下employee表的记录。其中，字段之间用“，”隔开，字符型数据用双引号括起来。SQL代码如下：

SELECT ... FROM example.employee   INTO OUTFILE "/path/to/st6.txt"  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' " '  LINES TERMINATED  BY '\n';

注意：如果表中包含中文字符，使用上面的语句会输出乱码，可以加入CHARACTER SET gbk语句即可解决这一个问题。修改SQL代码如下：

SELECT ... FROM example.employee   INTO OUTFILE "/path/to/st6.txt"  CHARACTER SET GBK FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' " '  LINES TERMINATED  BY '\n';

导出数据的文本内容

导入表数据

LOAD DATA INFILE"/path/to/st6.txt" INTO TABLE  example.employee  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED  BY '\n'

好处：导入数据的速度会提高三倍左右，不过要注意的是它进行导出时的速度与mysqldump的方式来导出的速度没有太大的区别
注意:

1. 用 load data 是较快的方法
2. 大数据量情况下, 最好是创建好表之后, 同时也要创建好相关的索引。 虽然说没有索引时导入更快, 但是数据导入完成之后再创建索引总共的耗时比事先创建好了再导入要来的多多了。
3. 另外,如果是myisam表,则最好是导入之前先禁用表的索引,导完之后再启用;或者是一开始不创建索引,导完之后再创建,都会比导入的同时更新索引来的快很多