Kettle--MySQL生产数据库千万、亿级数据量迁移方案及性能优化

大家好，我是贾斯汀！

【实战前言】

（1）不管你是学生，还是已经工作了的小伙伴，可能你在过去、现在或者未来，会遇到这样的问题，公司/项目用的是Oracle/DB2/MySQL等关系型数据库，因公司发展需求，需要完成旧数据库数据安全迁移到新数据库的重要使命，新旧数据库可能是同一种类型的数据库，也可能是不同类型的数据库，相同类型数据库还好，比如都是MySQL数据库，那么你主要只需要考虑如何将数据安全、高效的完成迁移就好，而不同类型的数据库，比如从DB2迁移数据到MySQL，这种情况就需要在进行数据迁移之前，先按照新的数据库MySQL的建表规范，正确完成数据表的重建工作~

（2）本文主要分享我个人在实际工作当中，==如何使用Kettle这款基于纯Java实现（意味着扩平台特性，也就是Windows/Linux等操作系统通用）的开源ETL数据挖掘工具，经过性能优化（性能是默认效率的5 ~ 10倍以上）之后，实现新旧数据库之间数据的安全高效迁移~

（3）我个人也是工作用到，一开始完成UAT测试环境模拟迁移DB2数据库一千万左右数据量到MySQL数据库的过程，到最后在实际生产环境安全高效完成五千万数据量从DB2迁移到MySQL的方案落实，不过由于个人学习环境限制，就不装DB2数据库了，本文将以本地MySQL（模拟旧库）迁移数据到远程MySQL（模拟新库），进行实战演练，原理是一样的，要说区别主要在于数据库类型不同，在进行数据迁移之前，需要先按规范建立好新数据库的相关库表~

（4）Kettle脚本的制作、测试以及性能优化这部分的工作，主要在Windows下通过可视化界面来完成，实际的UAT测试环境以及生产环境数据库服务器大多都是在Linux的，因此Linux也需要搭建一套Kettle环境，并且将在Windows下性能优化好的Kettle脚本，放到Linux环境，同时如果数据量非常大的话（亿级以上数据量），还可以根据大表制作多个Shell脚本来执行准备好的Kettle作业脚本，利用更良好的CPU性能并发执行脚本，在单个脚本执行性能瓶颈的基础上再次成倍数提高数据迁移效率，更高效完成旧库数据迁移到新库，节省实际投产时的时间成本~

前言废话有点多了，哈哈哈，进入正文吧~

---

学习目录

• 测试库表及数据
• 一、Kettle环境搭建
• • 1.1 Kettle下载及安装
  • 1.2 驱动包下载
  • 1.3 驱动包版本
  • 1.4 驱动包安装
• 二、Kettle使用
• • 2.1 Windows
  • • 2.1.1 数据库连接测试
    • 2.1.2 Kettle作业和转换
    • • 2.1.2.1 创建Kettle转换
      • 2.1.2.2 创建Kettle作业
    • 2.1.3 数据迁移测试（未优化）
    • • 2.1.3.1 执行转换测试（单表）
      • 2.1.3.2 执行作业测试（多表）
    • 2.1.4 数据迁移（性能优化）
    • • 2.1.4.1 优化Kettle作业
      • 2.1.4.2 优化Kettle转换
      • 2.1.4.3 优化Kettle连接DB参数
      • 2.1.4.4 优化JVM内存大小
      • 2.1.4.5 优化目标表的字段索引（临时删除）
    • 2.1.5 重做数据迁移（性能优化后）
• 三、Linux
• • 3.1 Kettle环境搭建
  • • 3.1.1 检查JDK
    • 3.1.2 目录规划
    • 3.1.3 kettle安装包
    • 3.1.4 验证kettle
  • 3.2 检查驱动及网络连接
  • 3.3 再次性能优化
  • 3.4 数据迁移

测试库表及数据

（1）创建测试库表

分别在Windows（模拟旧库环境）及Linux（模拟新库环境），创建一个测试库test以及两张测试库表test.demo_info、test.demo_info2，这里为了方便测试，我两张表除了表名不一样，其他字段都一样，测试表的主键为ID，使用了auto_increment设置主键从1开始自增长，MySQL中int类型占用4个byte字节，即最大数值是(2^31)-1即2147483647，大概二十多亿，数值够大，自增长主键实际使用问题不大~

– 建库测试库

create database if not exists test default character set utf8 collate utf8_general_ci;
use test;

– 创建测试表demo_info

use test;
create table test.demo_info(
	id int(7) primary key not null auto_increment,
	name varchar(255) not null,
	sex char(1) not null,
	age int(3)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

alter table test.demo_info add index index_name(name);
alter table test.demo_info add index index_age(age);
alter table test.demo_info add index index_name_age(name,age);

alter table test.demo_info comment '测试表';
alter table test.demo_info modify column id int(7) not null auto_increment comment 'ID';
alter table test.demo_info modify column name varchar(255) not null comment '姓名';
alter table test.demo_info modify column sex char(1) not null comment '性别:1-男,0-女';
alter table test.demo_info modify column age int(3) comment '年龄';

– 创建测试表demo_info2

create table test.demo_info2(
	id int(7) primary key not null auto_increment,
	name varchar(255) not null,
	sex char(1) not null,
	age int(3)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

alter table test.demo_info2 add index index_name(name);
alter table test.demo_info2 add index index_age(age);
alter table test.demo_info2 add index index_name_age(name,age);

alter table test.demo_info2 comment '测试表2';
alter table test.demo_info2 modify column id int(7) not null auto_increment comment 'ID';
alter table test.demo_info2 modify column name varchar(255) not null comment '姓名';
alter table test.demo_info2 modify column sex char(1) not null comment '性别:1-男,0-女';
alter table test.demo_info2 modify column age int(3) comment '年龄';

（2）构建测试数据

只需要插入Windows（模拟旧库环境）数据表的测试数据，Linux（模拟新库环境）不需要，我们的目的是要使用Kettle迁移数据到Linux（模拟新库环境）数据表的~
这里构建测试数据很简单，因为两张数据表的主键ID设置了自增长，直接新建文本，编辑文本另存为后缀.del文件，再使用文本编辑器（notepad++ yyds 仅个人推荐，非广告，哈哈哈！！！）编辑内容，这里先直接复制个不含ID的10w条数据（注意字段数据间的特殊分隔符是0x0f，如下图特殊符号所示）到文件中~

通过MySQL的load data infile语法指定字段快速插入数据，用这些数据，先来简单的进行数据迁移的测试，后面性能优化之后再用100w、1000w更多的数据量来进行数据迁移测试，当然实际环境的话最好按你们实际数据库大概有多少数据量，去构建多少的测试数据~

这里提供下，Kettle数据迁移10w、100w、1000w 测试del数据文件，也可以可直接下载~
csdn 下载1~
mpan 下载2~ 提取码：jj6l

说明：想学习和了解MySQL的load data infile导出数据的语法和使用技巧的话，可以先看下我的这篇文章学习下：

MySQL如何使用load data infile、into outfile高效导入导出数据…

先执行这两行命令，导入10w条数据（性能优化前测试使用）：

load data infile 'C:/Users/Administrator/Desktop/10w.del' into table test.demo_info  character set utf8 fields terminated by 0x0f (name,sex,age);

load data infile 'C:/Users/Administrator/Desktop/10w.del' into table test.demo_info2  character set utf8 fields terminated by 0x0f (name,sex,age);

通过load data infile分别导入两张表的数据，还挺快的，单表不到1秒完成10w条数据导入~

导入100w条数据（性能优化后测试再使用）：

load data infile 'C:/Users/Administrator/Desktop/100w.del' into table test.demo_info  character set utf8 fields terminated by 0x0f (name,sex,age);

load data infile 'C:/Users/Administrator/Desktop/100w.del' into table test.demo_info2  character set utf8 fields terminated by 0x0f (name,sex,age);

导入100w条数据（性能优化后测试再使用）：

load