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 条评论
登录 后参与评论

相关文章

来自专栏待你如初见

Day16

1313
来自专栏逸鹏说道

SQL Server 执行计划缓存

概述 了解执行计划对数据库性能分析很重要,其中涉及到了语句性能分析与存储,这也是写这篇文章的目的,在了解执行计划之前先要了解一些基础知识,所以文章前面会讲一些...

3699
来自专栏从零开始的linux

mysql基础语句

show命令 mysql> show databases; +--------------------+ | Database | +---...

2885
来自专栏james大数据架构

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

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

1857
来自专栏奇梦博客

CentOS下mysql数据库常用命令总结 MySQL 参数配置

1241
来自专栏Ryan Miao

Ubuntu18.04(linux)安装MySQL

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

1102
来自专栏社区的朋友们

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

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

8651
来自专栏菩提树下的杨过

mysql 学习笔记

以前主要使用oracle做数据库,现在换成mysql了,发现不一样的地方还是挺多的,记录一下: 一、centos上的yum install方式安装  完全卸...

1987
来自专栏运维

zabbix2.4.5迁移到zabbix3.0

http://qicheng0211.blog.51cto.com/3958621/1744603

1633
来自专栏CaiRui

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

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

1907

扫码关注云+社区