❤️大家好，我是贾斯汀！❤️
【实战前言】
**（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性能并发执行脚本，在单个脚本执行性能瓶颈的基础上再次成倍数提高数据迁移效率，更高效完成旧库数据迁移到新库，节省实际投产时的时间成本~**
前言废话有点多了，哈哈哈，进入正文吧~
学习目录
测试库表及数据
（1）创建测试库表
**分别在Windows（模拟旧库环境）及Linux（模拟新库环境），创建一个测试库test以及两张测试库表
test.demo_info、
test.demo_info2，这里为了方便测试，我两张表除了表名不一样，其他字段都一样，测试表的主键为ID，使用了auto_increment设置主键从1开始自增长，MySQL中int类型占用4个byte字节，即最大数值是(2^31)-1即2147483647，大概二十多亿，数值够大，自增长主键实际使用问题不大~**
– 建库测试库
createdatabaseifnotexists test defaultcharacterset utf8 collate utf8_general_ci;use test;
– 创建测试表demo_info
use test;createtable test.demo_info(
id int(7)primarykeynotnullauto_increment,
name varchar(255)notnull,
sex char(1)notnull,
age int(3))ENGINE=InnoDBDEFAULTCHARSET=utf8 COLLATE=utf8_bin;altertable test.demo_info addindex index_name(name);altertable test.demo_info addindex index_age(age);altertable test.demo_info addindex index_name_age(name,age);altertable test.demo_info comment'测试表';altertable test.demo_info modifycolumn id int(7)notnullauto_incrementcomment'ID';altertable test.demo_info modifycolumn name varchar(255)notnullcomment'姓名';altertable test.demo_info modifycolumn sex char(1)notnullcomment'性别:1-男,0-女';altertable test.demo_info modifycolumn age int(3)comment'年龄';
– 创建测试表demo_info2
createtable test.demo_info2(
id int(7)primarykeynotnullauto_increment,
name varchar(255)notnull,
sex char(1)notnull,
age int(3))ENGINE=InnoDBDEFAULTCHARSET=utf8 COLLATE=utf8_bin;altertable test.demo_info2 addindex index_name(name);altertable test.demo_info2 addindex index_age(age);altertable test.demo_info2 addindex index_name_age(name,age);altertable test.demo_info2 comment'测试表2';altertable test.demo_info2 modifycolumn id int(7)notnullauto_incrementcomment'ID';altertable test.demo_info2 modifycolumn name varchar(255)notnullcomment'姓名';altertable test.demo_info2 modifycolumn sex char(1)notnullcomment'性别:1-男,0-女';altertable test.demo_info2 modifycolumn 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 data infile 'C:/Users/Administrator/Desktop/1000w.del' into table test.demo_info character set utf8 fields terminated by 0x0f (name,sex,age);
load data infile 'C:/Users/Administrator/Desktop/1000w.del' into table test.demo_info2 character set utf8 fields terminated by 0x0f (name,sex,age);
测试库表及数据构建完成，接下来进入正文学习如何制作Kettle脚本及性能优化的实战演练吧~
一、Kettle环境搭建
1.1 Kettle下载及安装
Kettle安装包官方下载 https://sourceforge.net/projects/pentaho/files/
**我这里以最新版本pdi-ce-9.2.0.0-290.zip为例，直接下载
zip压缩包后解压得到目录
data-integration即可~**
**这里通过双击Spoon.bat即可~**
**不过，在运行之前，还是要先装好相关相关数据库的连接驱动包，不然无法连接到数据库~**
1.2 驱动包下载
Kettle是基于纯Java实现的，通过JDBC与数据库建立连接，连接过程需要依赖数据库连接驱动jar包，这里提供几种常用的驱动包的官方下载地址如下：
**官方OracleJava驱动包下载：** https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html
**官方DB2Java驱动包下载：**https://www.ibm.com/support/pages/node/382667
**官方MySQLJava驱动包下载：** https://dev.mysql.com/downloads/connector/j/
我这里下载MySQL的驱动包，选择哪个版本呢？~
1.3 驱动包版本
**关于驱动包的版本选择，这个要根据实际相关数据库的版本去选择对应驱动包版本下载，这里我以
MySQL数据库驱动包及版本下载为例~**
**进入上面给的MySQL官方下载地址，可以看到最新的驱动是8.0.26的驱动（下拉选
Platform Independent）：**
**这里说明一下，只要你的MySQL是5.6及以后的数据库版本，直接下载
8.0+的最新驱动包
mysql-connector-java-8.0.26即可，不一定非要MySQL 5.7就下5.7的驱动包，而且MySQL官方文档也说明建议MySQL 5.6以后的升级驱动包为8.0+的，如下：**
**即MySQL驱动包8.0+版本能够兼容
MySQL5.6及之后的所有版本的数据库连接~**
**如果你使用的MySQL低于5.6版本的，比如
MySQL 5.5，驱动包
8.0+是不兼容的，无法完成数据库的连接~**
**这时，只需要去历史版本Archives下载对应的5.1.+版本的驱动包即可~**
**解压下载得到的zip压缩包，得到官方MySQL连接驱动
jar包~**
1.4 驱动包安装
**将下载得到的MySQL数据库连接驱动包
mysql-connector-java-8.0.26.jar
放到
kettle
如下目录：**
data-integration\lib
如果你觉得不够稳妥的话，比如放到Linux会不会加载不到驱动，那么以下这几个目录，都放驱动包，妥妥的：
\data-integration\libswt
\data-integration\libswt\osx64
\data-integration\libswt\win64
二、Kettle使用
2.1 Windows
**Windows下，主要通过运行
Spoon.bat，打开Kettle客户端可视化操作页面，创建数据迁移用到的
ktr转换脚本、
kjb作业脚本（
转换和
作业后面会介绍），以及相关数据库连接配置、读写性能优化配置等，配置完成后最后一般都是上传kettle脚本到Linux上执行，当然也可直接在Windows下Kettle客户端可视化页面直接执行转换或作业脚本，完成数据库之间的数据迁移，但是生产一般是在Linux，而且Windows执行数据迁移的效率一般要比Linux慢一些~**
2.1.1 数据库连接测试
**双击
\data-integration\Spoont.bat
脚本运行~**
**视图
主对象树->
转换->右键新建 -> 直接快捷键
Ctrl + S另存为
test.ktr（自定义后缀，这里建议使用
.ktr）**
**选中DB连接，按如下图，操作验证相关数据库是否能正确连接，这里以MySQL数据库为例~**
注意：两个DB连接，都要右键，设置为共享！！！
本地数据库连接可以使用自己的IP，方便后面Linux中模拟两台IP服务器之间测试：
几种导致数据库连接报错的情况：
…
（1）缺少驱动或者无法正确加载到驱动都会报错，提示：
Driver class
org.gjt.mm.mysql.Driver
could not be found…**
…
**（2）远程连接MySQL的用户(
root
)，没有被放开远程连接的权限，，则会报错如下：**
Access denied for user ‘
root
’@’
119.168.xxx.xxx
’ (using password: YES)
…
（3）配置连接参数不对，会报错如下：
Connection failed. Verify all connection
parameters
and confirm that the appropriate driver is installed.
…
**缺少驱动的具体就不说了，直接下载数据库驱动包放到
\data-integration\lib
目录下即可~**
…
MySQL放开用户的远程连接（flush刷新权限生效）方式如下：
mysql>
select user,host from mysql.user;
mysql>
update mysql.user set host='%' where user = 'root';
mysql>
flush privileges;
…
同样如果需要在Kettle中建立Oracle、DB2连接时，如果没有驱动也会报同样类似的错误，这时需要下载数据库连接驱动包并正确安装驱动包即可~
2.1.2 Kettle作业和转换
**使用
Kettle
进行数据迁移的过程中，主要有两个专业名词即
转换（Kettle Transformation）
和
作业（Kettle Job）
~**
名词说明转换一般文件后缀命名为
.ktr
，单表迁移数据，构建表输入（读取数据），表输出（写入数据），Linux下使用
kitchen.sh
脚本调用执行作业文件后缀为
.kjb
，可以关联多个
.ktr
执行，实现多表（串行/并行）迁移数据，Linux下使用
pan.sh
脚本调用执行
2.1.2.1 创建Kettle转换
**在前面
数据库连接测试
时，新建的
test.ktr
就是一个Kettle
转换
文件，只不过前面只是测试了
DB连接
是否正常，至于
表输入
和
表输出
的相关配置，实现数据表数据迁移的相关配置还没作具体说明**表输出简单理解为插入数据到指定的数据表（新库）的过程~
选项说明表输入简单理解为从指定的数据表（旧库）读取数据的过程
**（1）同前面创建
test.ktr
方式，创建
demo_info
数据表对应的转换文件
demo_info.ktr
**
**（2）双击
表输入
进行编辑，如下图：**
**（3）双击
表输出
进行编辑，如下图：**
**（4）同样创建测试表
demo_info2
对应的转换文件
demo_info2.ktr
**
2.1.2.2 创建Kettle作业
**（1）完成kjb作业的创建，并关联demo_info表的ktr转换，作业执行过程为
Start -> 转换 -> 成功
，如下图：**
注意这里Transformation一般建议使用相对路径，移植性比较高，如：
${Internal.Entry.Current.Directory}/demo_info.ktr
**表示
kjb
会加载跟自己同目录的
ktr
文件，建议不要写死
ktr
的绝对路径，容易出错~**
（2）同样将测试表demo_info2的转换也添加到作业中~
**最后，快捷键
Ctrl + S
或者点击左上角保存，保存得到
kjb
作业文件（跟
ktr
转换放同一个目录下）~**
2.1.3 数据迁移测试（未优化）
**前面已通过在Windows下可视化来创建好Kettle数据迁移需要用到的
作业和
转换了，测试环境可以直接在Kettle客户端点击执行转换（单表）或作业（多表）的运行按钮，完成数据的迁移即可~**
2.1.3.1 执行转换测试（单表）
建议在测试每个表的转换时，先少量数据，验证每个转换的正确性，最后执行作业时，关联所有转换完成多个数据表的全部数据迁移~
我这里先正常用前面插入的10w条记录，测试看看Kettle不做任何性能优化，单表demo_info数据迁移的效率如何~
通过转换单表数据迁移的过程…
**最后看到，Windows环境下，Kettle默认不做任何优化，单表数据迁移10w条数据大概
100条左右/秒
，总耗时
16分
左右，汗！！！虽然也跟我Linux服务器（目标数据库）配置有关，就单核1G，但主要还是没做性能优化~**
**同样，测试下demo_info2表的转换是否正确，不过这里就不再测10w条记录了，太慢了，双击修改下demo_info2表输入，select查询语句中加个
limit 0,100
测试100条数据迁移，验证转换正确性即可****
**验证转换demo_info2.ktr没问题
也可以检查下Linux（模拟新库环境）测试表demo_info2的数据是否是100条~
2.1.3.2 执行作业测试（多表）
把前面通过转换迁移到Linux（模拟新库环境）数据表demo_info和demo_info2的数据先清了，并重置ID从1开始自增~
- - Linux下mysql命令行执行：
use test;deletefrom test.demo_info;altertable test.demo_info auto_increment1;deletefrom test.demo_info2;altertable test.demo_info2 auto_increment1;
重新来测试测试下通过作业关联两个转换的多表的数据迁移（ 数据量比较大的话，建议先配置每个转换的SQL语句查询少量数据迁移，先验证完整作业执行的正确性）…
这里不用测试也知道，因为都是没做任何性能优化，因此跟前面通过转换demo_info.ktr迁移demo_info单表10w条数据的耗时没有任何区别，只不过是串行的加了一张表，耗时增加了一倍多一些~~
**这里有时间也跑一下吧，花了37分钟迁移20w条数据，
还是大概90条记录/秒
，汗！！！前面16分钟10w条数据，
100条左右/秒
~**
虽然也跟个人电脑及服务器的CPU性能以及网络传输能力有关，但这些硬件条件有时候没法改变，接下来会从软件层面来进行优化，相信会有飞一样的感觉！！！~~
2.1.4 数据迁移（性能优化）
通过前面不做任何性能优化的Kettle，在做数据迁移时，效率真的是非常低下，接下来对Kettle数据迁移优化，做完优化，整体迁移效率至少提升5 ~ 10倍以上的效率，主要从以下几个方面的优化：
- 优化Kettle
作业 - 优化Kettle
转换 - 化Kettle连接
DB参数 - 优化
JVM内存大小 - 优化目标表的字段
索引（临时删除）
2.1.4.1 优化Kettle作业
（1）优化Kettle作业
第一步：统计数据量比较大的表
统计数据量比较大的表，将这些表作为并发执行的第一张表，其他小数据量的表平均放在后面即可，这里假设我们构建的两张数据表demo_info和demo_info都是两张大表~
第二步：重新改造作业为并发执行转换~
把原来串联执行的demo_info和demo_info2改成并发执行~
第三步：Kettle -> kjb作业文件 -> 选中"Start" -> “右键” -> Run next Entries in parallel -> 确定(I understand)，完成并发生效，如图即为并发执行：
2.1.4.2 优化Kettle转换
（1）加大提交记录数
加大每一个表对应的ktr转换的表输出，每次的提交记录数~
加大步骤：Kettle -> ktr转换文件 -> 双击表输出 -> 提交记录数量: 10000 (默认1000，范围1000~50000，百千万级大表，建议最大设置为50000即可，最大不要超过65535！！)
2.1.4.3 优化Kettle连接DB参数
（1）使用连接池
配置步骤：kettle -> ktr转换文件 -> DB连接 -> 双击数据库连接 ->连接池~
比如：
设置连接池初始化大小：50 最大空闲：151 以及勾选关键的几个参数并赋值~
连接池的相关参数要配置多少合适呢？
这个可以按你们工程jdbc.properties数据库配置中的参数值来填写以及根据自己数据库当前配置的max_connections最大连接数来确定~
**注意：关于
max_connections
最大连接数，MySQL 5.7版本默认
151
，最大可设置为2的14次方=``16384**
可以查看下自己的MySQL服务配置的最大连接数是多少：
mysql >
show variables like '%max_connections%';
当然有些小伙伴说，数据库最大连接数多少咱也不懂，咱也不敢改啊，OK，没问题，如果你不敢改，那就使用数据库默认的最大连接数，起码默认最大连接数也有151，只不过可能会慢一些~
如果你有权限，想让自己生产的数据迁移效率高一些，还是建议申请修改一下最大连接数，默认的太小了，可以通过修改MySQL的配置my.ini（Linux是my.cnf），加大MySQL的最大连接数，在[mysqld]组下添加或者修改以下内容：
max_connections=2000
Windows下直接net stop mysql/ net start mysql重启生效MySQL服务~
Linux下可能会不生效，若不生效则需要配置Linux操作系统的
limits.conf
配置文件~
vi /etc/security/limits.conf
在最后加入以下MySQL服务配置信息：
mysql hard nofile 65535
mysql soft nofile 65535
查看Linux最大连接显示是否设置成功~
ulimit -n
重启MySQL服务后，查看Linux下最大连接数是否设置成功~
（2）优化DB读写参数
– 表输入（读）核心参数优化
双击DB连接（读库），对选项下的相关参数进行配置：
- - 参数说明
参数及赋值参数说明
defaultFetchSize=10000
每次与数据库交互，读多少条数据加入内存中缓存，不设置默认把所有数据读取出来，容易内存溢出（OOM），我这里设置10000，大表CPU性能高建议设置最大50000，不能超过65535
useServerPrepStmts=true
是否在使用服务端的预编译语句，true表示以占位符的方式发送SQL到服务端进行拼接
cachePrepStmts=true
是否客户端缓存预处理语句
useCursorFetch=true
是否允许部分数据到客户端就进行处理，如果为false表示所有数据到达客户端后，才进行处理
参数不在于多，在于使用几个简单高效的参数即可，其他参数可以查看MySQL性能优化官方文档：
https://dev.mysql.com/doc/connectors/en/connector-j-reference-configuration-properties.html
– 表输出（写）核心参数优化
双击DB连接（写库），对选项下的相关参数进行配置：
- - 参数说明
参数及赋值参数说明
defaultFetchSize=5000
每次与数据库交互，从内存中读取多少条数据写入数据表，不设置默认把所有数据写入， rewriteBatchedStatements设置true，该参数会失效
rewriteBatchedStatements=true
是否开启批量写入，true表示开启，原多条insert变成单条insert执行
useServerPreStmts=false
是否使用服务端预编译，设置为false，表示在客户端编译好
useCompression=true
是否使用压缩，使用压缩优化客户端与服务端传输效率
useCursorFetch=true
是否允许部分数据到客户端就进行处理，如果为false表示所有数据到达客户端后，才进行处理
2.1.4.4 优化JVM内存大小
**修改Kettle安装目录data-integration/
Spoon.bat
（Linux是
Spoon.sh
）文件中的
PENTAHO_DI_JAVA_OPTIONS
参数中的JVM相关参数值~**
默认如下：
PENTAHO_DI_JAVA_OPTIONS="-Xms1024m" "-Xmx2048m"
– JVM内存优化说明
参数说明
-Xms
堆区最小内存，默认物理内存的1/64，建议-Xms、-Xmx设置成一样，避免每次GC后调整堆区大小
-Xmx
堆区最大内存，默认物理内存的1/4 ，建议最大值设置为实际可用物理内存的3/4即可，不要超过80%，不过有个度，32位操作系统有限制，Winows 32限制2-3G，Linux限制2-3G~ ，64位操作系统不限制，取决于实际可用的物理内存~
-Xmn
新生代内存大小，JDK1.8 新生代:年老代=1:3 ，官方建议设置为最大堆区内存值的3/8，不知道这里不配置也行-XX:MaxPermSize永久代最大内存，默认64m，JDK1.8取消了永久代，这里该参数就不要配置了，配置了也会失效，JDK1.7及之前版本，默认64m，建议加大一些，不然64m随时发生OutOfMemoryError内存溢出
（1）Windows配置JVM参数
查看物理内存~
理论上建议设置的JVM最大堆区内存值（8g * 3/4 = 6g即 6 * 1024m = 6144m）：
PENTAHO_DI_JAVA_OPTIONS="-Xms6144m" "-Xmx6144m"
不过我本地电脑实际物理内存就30%（2g）左右了，配置再大也没用，再稍微优化下，勉强配置个3g=3072m最大堆区内存吧：
PENTAHO_DI_JAVA_OPTIONS="-Xms3072m" "-Xmx3072m"
（2）Linux配置JVM参数
查看Linux的内存大小（默认单位KB）：
cat /proc/meminfo |grep'MemTotal'cat /proc/meminfo |grep'MemFree'
或（更简单的）
free -m
**可以看到我的
"高性能"单核1g Linux服务器
，就剩余83M空闲内存！！！！对写数据效率影响大！**
不过一般生产的机器基本标配都是4G或8G起步了吧，像我当时做数据迁移时，UAT环境就MySQL服务器16G，生产环境MySQL服务器的物理内存是32G（32768M，33554432KB，三千多万KB，我们老板，有钱！！！），按堆区最大内存设置为物理内存的1/4，年轻代为最大堆区的3/8的话，可以设置的Kettle的JVM内存对应配置为：
-Xms24g -Xmx24g -Xmn9g -XX:MaxPermSize=1024m
不过单个作业执行，肯定用不到那么大内存，而且要考虑可能并发执行多个作业，建议单个作业最大配置个3g就OK了~
2.1.4.5 优化目标表的字段索引（临时删除）
在目标库建立库表时，先不要创建表的相关非主键字段索引，如果已经创建的，建议先临时删除相关表（新库）的字段索引，完成数据迁移之后，再重新建立字段索引~
MySQL建立字段索引，用得好会给查询的速度有大大的提升，但是进行插入、修改数据时，MySQL也需要进行动态的维护索引，比较消耗性能，因此在做数据迁移时建议先临时删除索引，特别是百千万级大表，如果存在字符串组合索引，在做数据迁移插入数据时效率非常低~
– 查看指定表的索引
showindexfrom test.demo_info;
前面我们创建测试表的时候，也建立了索引，因此需要先删除索引~
– 临时删除非主键索引（数据迁移前）
altertable test.demo_info dropindex index_name;altertable test.demo_info dropindex index_age;altertable test.demo_info dropindex index_name_age;altertable test.demo_info2 dropindex index_name;altertable test.demo_info2 dropindex index_age;altertable test.demo_info2 dropindex index_name_age;
– 重新建立非主键索引（数据迁移完成后）
altertable test.demo_info addindex index_name(name);altertable test.demo_info addindex index_age(age);altertable test.demo_info addindex index_name_age(name,age);altertable test.demo_info2 addindex index_name(name);altertable test.demo_info2 addindex index_age(age);altertable test.demo_info2 addindex index_name_age(name,age);
2.1.5 重做数据迁移（性能优化后）
完成关键的几个方面的性能优化后，接下来就是见证奇迹的时候了，来迁移下20w条数据，看看效率如何~
（1）测试迁移两张表20w条数据
执行作业~
16秒！！！！两张表20w条数据，大概12500条/秒~
前面我们没做任何优化前，单表10w条数据，要16分钟，100条/秒左右，两张表20w条数据，要37分钟，90条/秒左右~
性能直接飙升120倍以上？？ 当然实际肯定不会提高那么高倍数的，只是数据量太少了，接下来再测一下200w条数据量迁移效率如何~
（2）测试迁移两张表200w条数据~
清理Windows本地数据：
deletefrom test.demo_info;deletefrom test.demo_info2;altertable test.demo auto_increment1;altertable test.demo2 auto_increment1;
同样，清理Linux远程数据：
deletefrom test.demo_info;deletefrom test.demo_info2;altertable test.demo auto_increment1;altertable test.demo2 auto_increment1;
Windows本地两张表分别导入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);
load data infile导入数据还是挺快的，单表100w条数据，4秒左右~
完成本地库两张表数据导入，执行重新执行作业，看看还是不是有120倍，如下所示：
耗时2分48秒，两张表200w条数据，11904条/秒左右，不错了，不过比我预期的低一些，毕竟我本地Windows配置和Linux远程服务器的性能比较一般！！！
（3）测试迁移两张表2000w条数据~
同样，来测试下2000w，数据迁移效率如何~
Windows本地两张表分别导入1000w条数据：
load data infile 'C:/Users/Administrator/Desktop/1000w.del' into table test.demo_info character set utf8 fields terminated by 0x0f (name,sex,age);
load data infile 'C:/Users/Administrator/Desktop/1000w.del' into table test.demo_info2 character set utf8 fields terminated by 0x0f (name,sex,age);
两张表都load data infile导入数据，单表导入1000w条数据耗时45 ~ 50秒左右~
执行作业
耗时30分钟左右，两张表2000w条数据，大概11111条/秒的速度，个人觉得挺不错了，最辣鸡的配置了，生产好一些的配置，连接池配大一些，JVM参数再加大一些，单个作业能达到了25000+条/秒以上，Linux可以分成4个作业，并发执行，即便每个脚本下降到2w条/秒左右，但总的还是能达到8w条/秒，1分钟 480w，10分钟4800w，30分钟内还是能轻松实现亿级数据量的高效迁移的~
三、Linux
3.1 Kettle环境搭建
3.1.1 检查JDK
Kettle基于纯java编写，基于JDK环境运行，因此需要检查下环境~
java -version
如没有配置好JDK，需先配置好JDK环境~
3.1.2 目录规划
目录位置及命名可以根据实际需要定义，这里建议创建这四个目录：
mkdir -p $HOME/kettle
mkdir -p $HOME/kettle/kettle_file
mkdir -p $HOME/kettle/kettle_sh
mkdir -p $HOME/kettle/kettle_log
mkdir -p $HOME/kettle/sql
**注意：Linux下
$HOME
表示当前操作用户的主目录，如我都是用
mysql
用户操作的，主目录
/home/mysql
**
– 目录说明
目录说明
kettle_file
存放数据迁移准备好的kettle的转换ktr和作业kjb文件
kettle_sh
存放Shell脚本，脚本内容是多表，通过kitchen.sh调用作业kjb，单表的话，通过pan.sh调用转换ktr~
kettle_log
存放执行脚本以及调用Kettle作业和转换进行数据迁移打印的日志文件~
sql
存放统计数据量的SQL语句文件~
先上传kettle作业和转换文件到kettle_file目录下~
3.1.3 kettle安装包
提前在Windows下往安装包pdi-ce-9.0.0.0-423.zip的lib目录中放好数据库驱动包~
上传压缩包到MySQL服务器，如：
$HOME/kettle/
解压完成安装：
cd$HOME/kettle/
unzip pdi-ce-9.0.0.0-423.zip
3.1.4 验证kettle
执行测试kettle命令：
cd$HOME/kettle/data-integration
./kitchen.sh --version
./pan.sh --version
分别出现关键信息如下，表明kettle解压安装成功！
...
Kitchen - Start of run.
...
Pan - Start of run.
...
如果服务器内存不足，执行测试可能会报错如下：
关键报错：
Native memory allocation (mmap) failed to
map 715849728 bytes
for committing reserved memory.
即最低至少要分配715849728 bytes约等于699M的内存，才能运行kitchen.sh脚本测试~
查看了我的单核1gLinux服务器，总物理内存才990M左右，剩余内存就211M了，汗！！！
通过top命令查看，光是mysqld服务进程就占用了44%，大概占用435M了，基本没内存了~
一般如果生产需要在Linux执行kettle脚本，内存肯定不会这么小的~
**这种情况，我这里为了方便在Linux简单测试，只能先修改data-integration/
spoon.sh
中的JVM内存小一点了，改成
128m
吧，如下（搜索
PENTAHO_DI_JAVA_OPTIONS
定位）：**
if[ -z "$PENTAHO_DI_JAVA_OPTIONS"];thenPENTAHO_DI_JAVA_OPTIONS="-Xms128m -Xmx128m"fi
**实际CPU性能好一些的测试环境或者生产环境建议改为
3g
即
3072m
：**
if[ -z "$PENTAHO_DI_JAVA_OPTIONS"];thenPENTAHO_DI_JAVA_OPTIONS="-Xms3072m -Xmx3072m"fi
再次测试下
cd$HOME/kettle/data-integration
./kitchen.sh --version
./pan.sh --version
3.2 检查驱动及网络连接
**kettle安装成功不代表两台服务器之间能开始进行数据迁移了，还需要检查相关
数据库驱动
以及新旧数据库两台服务器之间的
网络是否连通
~**
（1）检查驱动
检查kettle安装目录lib目录下表是否存在相关数据库的连接驱动，如MySQL数据库连接驱动包
data-integration/lib/
mysql-connector-java-8.0.26.jar
（2）检查网络及连接
**在新数据库服务器检查与旧数据库服务器的
网络连通性
以及
连接权限指定端口
~**
– 检查与旧数据库服务器的网络是否连通
ping 旧数据库服务器的IP
– 是否有权限连接到指定端口（如MySQL的3306端口）
telnet 就数据库服务器的IP 3306
当然如果要在Linux执行telnet Windows的3306端口，需要做以下几件件事：
第一：配置防火墙入站规则天机3306端口
防火墙未开启，可以忽略这步~
第二：勾选telnet服务端
第三：开启telnet服务
3.3 再次性能优化
这一步主要是，准备Shell脚本！！！
（1）主要是针对亿级以上的数据量，再次进行性能优化的方案，数据量比较小的可以忽略~
（2）前面通过Windows下的可视化操作等对Kettle进行了优化，可以直接在Kettle客户端执行作作业或者转换，完成数据迁移，也可以直接上传脚本到Linux服务器执行~
（3）实际的测试环境或生产环境，大多一般都是两台远程Linux服务器之间的数据迁移，如果库表数据量达到亿级以上，这种情况就可以根据每一张大表（如1000w以上）创建一个Shell脚本，每个脚本的内容是执行指定的作业~
（4）如果存在多张大表，则对应创建多个Shell脚本，这样可以在实际投产时并发的执行Shell脚本，最大化的利用CPU的性能，并发数据迁移，再次成倍数的提高数据迁移效率，进一步节省投产的时间成本~~
亿级以上数据量，Linux再次优化大概思路：
（1）假设有100张表，大概1亿左右的数据量，统计超过1000w的表有5张~
（2）在Windows下给每张大表（如1000w以上）建一个对应的Kettle作业，这个作业除了关联那张大表转换之外，还要再平均关联其他小表的转换（比如平均再关联20个小表）~
（3）不过虽然这里有5张表，但是建议总作业数不要超过4个，每个作业一般分配3g内存左右，4个作业总需物理内存就是12g左右内存，如果你生产内存远远大于12g，那没啥问题，建5个作业，对应5个Shell脚本~~
（4）每一个作业的创建按照前面Windows下提的几个方面优化，特别是并发执行，验证每个作业的正确性
（5）创建每一个Shell脚本，如demo_info.sh、demo_info2.sh，Shell脚本内容是通过span.sh脚本去调用指定的作业
（6）准备好这几张大表为主的Shell脚本后，就可以在Linux同时执行这几个Shell脚本并发数据迁移~
为什么不直接一个作业，然后这个作业并发执行多个大表转换即可？
因为Linux环境下，通过kettle的kitchen.sh脚本执行作业时，只会开启一个Java进程，JVM的内存是有上限的，作业并发再多转换，内存也就那么多，所以还是建议并行执行多个Shell脚本（对应多个作业）执行，意味着内存的叠加使用，可以最大化的利用生产比较好的CPU配置，更高效的完成数据的迁移~
当然，如果不想麻烦，就想一个脚本，一个作业，关联所有转换也可以，不过需要给Kettle配置JVM最大内存调到最优，比如一个作业整这么多转换~
**本文为方便测试，这里就一个作业，创建一个数据迁移的Shell脚本
test.sh
进行测试，内容如下：**
#!/bin/sh#一、使用自定义的JDK版本exportJAVA_HOME=/usr/java/jdk1.8.0.221
exportPATH=$JAVA_HOME/bin:$PATHexportCLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
#二、执行kettle脚本，作业通过 kitchen.sh执行，转换通过pan.sh执行#作业（多表）$HOME/kettle/data-integration/kitchen.sh -file=$HOME/kettle/kettle_file/test.kjb >$HOME/kettle/kettle_log/transTest.log 2>&1&#转换（单表），这里先注释，如果数据迁移数据总量不一致，排查出哪张表数据不一致，需要单独迁移这张表，可以把这个放开，前面的作业注释#$HOME/kettle/data-integration/pan.sh -file=$HOME/kettle/kettle_file/demo_info.kjb > $HOME/kettle/kettle_log/demo_info.log 2>&1 &
注意JDK等相关目录改成自己的~
**将
test.sh
脚本上传到
kettle_sh
目录下：**
$HOME/kettle/kettle_sh
往下进行数据迁移~
3.4 数据迁移
实际做数据迁移的时间成本，不只是kettle作业脚本的执行时间，你还需要花一些时间进行其他相关操作，比如统计数据迁移前（旧库）和数据迁移后（新库）的数据量总量~
**（1）先统计
旧库
的总数据量**
**提前准备一个
selectCount.sql
语句文件，统计所有数据表的数据量，内容如：**
selectsum(selectCount.ct)as totalRows from(selectcount(*)as ct from test.demo_info
unionallselectcount(*)as ct from test.demo_info2
) selectCount;
**上传
selectCount.sql
语句文件到
sql
目录下：**
$HOME/kettle/sql/
执行SQL语句文件，查看数据量（旧库）~
mysql >
source C:/Users/Administrator/Desktop/selectCount.sql;
（2）执行数据迁移Shell脚本
/bin/sh $HOME/kettle/kettle_sh/test.sh
可以查看Shell脚本调用的Kettle作业的执行进程~
ps -ef|grep kjb
测试时，可以可以通过top命令，查看CPU性能消耗及内存使用情况~
（3）实时查看数据迁移日志
Shell脚本test.sh中，有指定日志文件存放路径~
tail -f $HOME/kettle/kettle_log/transTest.log
（4）最后统计新库的总数据量
**提前准备一个
selectCount.sql
语句文件，统计所有新库，数据表的数据量，内容如：**
selectsum(selectCount.ct)as totalRows from(selectcount(*)as ct from test.demo_info
unionallselectcount(*)as ct from test.demo_info2
) selectCount;
执行SQL语句文件，查看数据量~
mysql >
source /home/mysql/kettle/sql/selectCount.sql;
（4）数据量不一致需查看日志
vi /home/mysql/kettle/kettle_log
日志中会记录每张表的表输入记录、表输出记录，根据这个排查总数据量不一致，是哪张表导致的~
**原创不易，觉得有用的小伙伴来个
三连（点赞+收藏+评论 ）+关注
支持一下，非常感谢~**
版权归原作者 吾日三省贾斯汀 所有， 如有侵权，请联系我们删除。