MySQL-大批量数据如何快速的数据迁移

MySQL-大批量数据如何快速的数据迁移

背景：最近接触到一个诊所的项目，主要做二次开发，由于甲方没法提供测试数据库（只有生产环境），且二次开发还是基于之前的数据库结构，给了数据库文档和生产库数据地址。由于生产库数据量比较大，我们也没法直接在生产库下二次开发（胆小），我们打算从生产库环境下迁移需要用到表导入自己的开发环境下，迁移的是表结构和表中数据，大概一个表在400M左右（300万条数据），全是InnoDB的存储引擎，而且都带有索引结构。针对如上的迁移数据的需求，我们尝试过直接通过从生产库下导出SQL文件，直接在本地执行SQL，由于数据量太大了，该方法根本不可行，一个表的导入大概需要7、8个小时。这个时候我们也参考了百度到的一些方案，总结了一套比较简单的方法来做数据迁移，下面我们就来介绍一下该方法的详细流程。

流程：

 1. 从生产库导出SQL文件，这个耗时不是很长，强烈建议导出的Insert语句为多值形式的，这样在导入的时候效率比较高。

多值写法（推荐）：

INSERT INTO `table` VALUES (记录01),(记录02),(记录03);

单值写法（不推荐）：

INSERT INTO `table` VALUES (记录01);
INSERT INTO `table` VALUES (记录02);
INSERT INTO `table` VALUES (记录03);

2. 由于我们测试环境也没要求非得多快的查询数据，所以当SQL表结构中存在索引，我们可以考虑将索引删除，要是需要考虑到性能的话，也可以先删除，等导入过后再重新进行索引的创建。

3. 因生产环境的表存储引擎都是InnoDB的，可以将InnoDB改为MyISAM，因为在数据量比较大的情况MyISAM的插入速度比Innodb高，这里也是当数据导入完成后再将存储引擎修改为InnoDB。

4. 到这里我们已经修改多值插入、删除索引、改完存储引擎，准备好SQL文件后直接在MySQL中执行会执行不了，会抛出ERROR : (2006, 'MySQL server has gone away')错误。这里是发送的SQL语句太长，以致超过了max_allowed_packet的大小，如果是这种原因，我们只需要重新设置一下max_allowed_packet的大小就可以解决，可针对本身SQL大小设置对应的max_allowed_packet值，这里我设置为600M。

set global max_allowed_packet = 6*1024*1024*100

5. 到这里基本已经完成了大数据的迁移工作，这个时候我们通过如下数据得到结论。

-- ====================统计====================
-- 大小    时间   
-- 1.31M   215s  有索引、InnoDB
-- 2.23M  0.213s  无索引、MyISAM
-- 100万数据  127.823s  无索引、MyISAM