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


声明:本文转载自https://my.oschina.net/qrmc/blog/1815220,转载目的在于传递更多信息,仅供学习交流之用。如有侵权行为,请联系我,我会及时删除。

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

 

本文发表于2018年05月20日 20:00
(c)注:本文转载自https://my.oschina.net/qrmc/blog/1815220,转载目的在于传递更多信息,并不代表本网赞同其观点和对其真实性负责。如有侵权行为,请联系我们,我们会及时删除.

阅读 1827 讨论 0 喜欢 0

抢先体验

扫码体验
趣味小程序
文字表情生成器

闪念胶囊

你要过得好哇,这样我才能恨你啊,你要是过得不好,我都不知道该恨你还是拥抱你啊。

直抵黄龙府,与诸君痛饮尔。

那时陪伴我的人啊,你们如今在何方。

不出意外的话,我们再也不会见了,祝你前程似锦。

这世界真好,吃野东西也要留出这条命来看看

快捷链接
网站地图
提交友链
Copyright © 2016 - 2021 Cion.
All Rights Reserved.
京ICP备2021004668号-1