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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏社区的朋友们

MySQL 入门常用命令大全(上)

作为一个 MySQL 的初学者,在短短的几个月中接触了一下,记录了一下工作中用到的 SQL 语句以及未来可能会用到的 MySQL 知识点,作为日后的参考手册。因...

1K10
来自专栏待你如初见

Day16

14530
来自专栏james大数据架构

SQL之收集SQL Server线程等待信息

要知道线程等待时间是制约SQL Server效率的重要原因,这一个随笔中将学习怎样收集SQL Server中的线程等待时间,类型等信息,这些信息是进行数据库优化...

21170
来自专栏沈唁志

WordPress添加随便看看

17530
来自专栏Ken的杂谈

CentOS 7下InfluxDB部署与使用入门

InfluxDB里存储的数据被称为时间序列数据,InfluxDB存储方式跟传统关系型数据库不同的是:传统关系型数据库通过数据库+表+字段组织数据,InfluxD...

92340
来自专栏乐沙弥的世界

MySQL 用户与权限管理

    MySQL权限系统的主要功能是证实连接到一台给定主机的用户,并且赋予该用户在数据库上的相关DML,DQL权限。MySQL存取控制包含2个阶段,一是服务器...

33010
来自专栏Ryan Miao

Ubuntu18.04(linux)安装MySQL

Ubuntu18.04 安装mysql或者mariadb之后,发现普通用户和远程都没有权限连接。

15520
来自专栏运维技术迷

连仕彤博客Mysql数据库归档利器之pt-archiver

26660
来自专栏Ryan Miao

mysql创建定时执行存储过程任务

sql语法很多,是一门完整语言。这里仅仅实现一个功能,不做深入研究。 目标:定时更新表或者清空表。 案例:曾经做过定时清空位置信息表的任务。(然而,当时并未考虑...

43770
来自专栏CaiRui

Mysql-4-数据库的基本操作

1.创建数据库 create database database_name; 例:create database aa; show create databas...

21070

扫码关注云+社区

领取腾讯云代金券