前提：业务需要将Tidb(Mysql内核)中8亿多条数据从Mysql导到Elasticsearch7中。
一开始我首先想到，“那就使用久负盛名的ELK来做数据迁移呀！”。Logstash的作用就是一个数据收集器，可以将各种格式的数据通过过滤后，格式化地输入到Elasticsearch，其中Logstash的jdbc插件也确保了任何具有jdbc接口的数据都可以作为输入。
Logstash是一款开源的数据收集引擎，具备实时管道处理能力。简单来说，logstash作为数据源与数据存储分析工具之间的桥梁，结合ElasticSearch以及Kibana，能够极大方便数据的处理与分析。通过200多个插件，logstash可以接受几乎各种各样的数据。包括日志、网络请求、关系型数据库、传感器或物联网等等。
Logstash的工作过程
除了在ES集群上安装好Logstash（已结合机器性能对logstash做了调优），还需要写Logstash的配置文件，包括ES7的索引模板我会在讲Datax时贴出。
logstash.conf
input {
jdbc {
jdbc_driver_library => "/opt/jar/mysql-connector-java-5.1.48.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://xxx:4000/ODS_MES_FinishedProd_B400?characterEncoding=utf8&useSSL=false"
jdbc_user => "xxx"
jdbc_password => "xxx"
jdbc_paging_enabled => "true"
lowercase_column_names => "false"
jdbc_page_size => "1000"
statement => "SELECT LOTID,LOTNO,PRODUCTIONORDERID,PARTID FROM LOT"
}
}
output {
elasticsearch {
hosts => ["xxx:9200","xxx:9200","xxx:9200"]
index => "lot"
template_name => "lot_template"
}
stdout {
codec => rubydebug
}
}
当执行./logstash -f /opt/file/logstash.conf后，一开始速度我觉得还行，观察了10min成功导入了160W条（后来我问离线的同事，他跟我说这个太慢了！！！），但是我发现预计本应一小时后导入960W条的数据量，结果只有不到600W，而且之前的几个小时内数据增量只有几十万条，我惊觉大事不妙！
后来我查阅社区，发现，原来logstash执行全量导入后会遇到深度分页的瓶颈，所以并不适合做大体量的导入 。社区有对sql分页查询慢的策略，但是我看sql写的太复杂，加上当时耐心不足，所以没有深入研究了。（回头有时间可以了解一下深度分页）
进入本文章的重头戏啦，经离线组的同事指点，可以用Datax来做数据迁移，据他说Datax在他们数据组使用每小时可以导3亿条。我一开始以为不就是数据导入嘛，数据准备阶段的活能费多少功夫，没想到好不容易学会用logstash后，现在没想到还要换导入工具从头来过，唉，原是我没做好工具的调研，白花功夫不讨好。
好啦，接下来就来讲Datax部分
首先，我们先去阿里爸爸开源的datax把源码下下来，接下来选我需要的module，避免编译时间过长。说到编译，也是苦逼的不行不行的，这里传授各位小伙伴一套编译datax大全：
- 首先在maven的配置文件配置好阿里的镜像仓库，不然pom上的依赖下不下来！！！
<mirrors>
<!-- 阿里云的maven私服-->
<mirror>
<id>alimaven</id>
<name>aliyun maven</name>
<url>http://maven.aliyun.com/nexus/content/groups/public/</url>
<mirrorOf>central</mirrorOf>
</mirror>
<mirror>
<id>alimaven</id>
<mirrorOf>central</mirrorOf>
<name>aliyun maven</name>
<url>http://maven.aliyun.com/nexus/content/repositories/central</url>
</mirror>
<mirror>
<id>nexus-aliyun</id>
<mirrorOf>central</mirrorOf>
<name>Nexus aliyun</name>
<url>https://maven.aliyun.com/repository/central</url>
</mirror>
<!-- 阿里云的maven私服-->
<!-- 中央仓库 -->
<mirror>
<id>repo1</id>
<mirrorOf>central</mirrorOf>
<name>Human Readable Name for this Mirror.</name>
<url>http://repo1.maven.org/maven2/</url>
</mirror>
<mirror>
<id>repo2</id>
<mirrorOf>central</mirrorOf>
<name>Human Readable Name for this Mirror.</name>
<url>http://repo2.maven.org/maven2/</url>
</mirror>
<!-- 中央仓库 -->
</mirrors>
- 然后在datax的pom.xml把不需要的module注释掉（ps：必要的module不要删），加快编译时间。这里我只留下了mysqlreader和elasticsearchwriter的读写module：
<modules>
<module>common</module>
<module>core</module>
<module>transformer</module>
<module>mysqlreader</module>
<module>elasticsearchwriter</module>
<!-- reader -->
<!--
<module>drdsreader</module>
<module>sqlserverreader</module>
<module>postgresqlreader</module>
<module>kingbaseesreader</module>
<module>oraclereader</module>
<module>odpsreader</module>
<module>otsreader</module>
<module>otsstreamreader</module>
<module>txtfilereader</module>
<module>hdfsreader</module>
<module>streamreader</module>
<module>ossreader</module>
<module>ftpreader</module>
<module>mongodbreader</module>
<module>rdbmsreader</module>
<module>hbase11xreader</module>
<module>hbase094xreader</module>
<module>tsdbreader</module>
<module>opentsdbreader</module>
<module>cassandrareader</module>
<module>gdbreader</module>
-->
<!-- writer -->
<!--
<module>drdswriter</module>
<module>odpswriter</module>
<module>txtfilewriter</module>
<module>ftpwriter</module>
<module>hdfswriter</module>
<module>streamwriter</module>
<module>otswriter</module>
<module>oraclewriter</module>
<module>sqlserverwriter</module>
<module>postgresqlwriter</module>
<module>kingbaseeswriter</module>
<module>osswriter</module>
<module>mongodbwriter</module>
<module>adswriter</module>
<module>ocswriter</module>
<module>rdbmswriter</module>
<module>hbase11xwriter</module>
<module>hbase094xwriter</module>
<module>hbase11xsqlwriter</module>
<module>hbase11xsqlreader</module>
<module>elasticsearchwriter</module>
<module>tsdbwriter</module>
<module>adbpgwriter</module>
<module>gdbwriter</module>
<module>cassandrawriter</module>
<module>clickhousewriter</module>
<module>oscarwriter</module>
-->
<!-- common support module -->
<module>plugin-rdbms-util</module>
<module>plugin-unstructured-storage-util</module>
<module>hbase20xsqlreader</module>
<module>hbase20xsqlwriter</module>
<module>kuduwriter</module>
</modules>
cmd进datax窗口，输入打包命令mvn -U clean package assembly:assembly -Dmaven.test.skip=true。如果是Powershell，-Dmaven.test.skip=true需要打上单引号。
3.成功编译后，在 /DataX/target下生成了一个datax.tar.gz，把包移到linux上解压，至此，我们迈出了入门datax的一大步！
4.接下来写读出端Mysql到写入段Elasticsearch的最终正确的配置文件tidb2es.json：
{
"job": {
"setting": {
"speed": {
"channel": 32 #开启32个并发通道
"batchSize": 4096
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"splitPk": "LOTID", # 注意这里splitPk的值不为空才能真正开启并行通道能力
"connection": [
{
"querySql": ["SELECT LOTID,LOTNO,PRODUCTIONORDERID,DATE_FORMAT(CREATEDDATE,'%Y-%m-%d %H:%i:%S') as CREATEDDATE FROM LOT"],
"jdbcUrl": [
"jdbc:mysql://xxx:4000/ODS_MES_FinishedProd_B400?useUnicode=true&characterEncoding=utf-8"
]
}
],
"username": "xxx",
"password": "xxx"
}
},
"writer": {
"name": "elasticsearchwriter",
"parameter": {
"endpoint": "http://xxx:9200",
"index": "lot",
"type":"_doc", #这里一个大坑，注意es7只能有一个type
"settings": {"index" :{"number_of_shards": 3, "number_of_replicas": 1, "refresh_interval": "120s"}},
"discovery": false, #轮询es集群地址也需要打开
"dynamic": true, #true的话是使用es的索引模板，否则使用datax的模板
"column": [
{ "name": "LOTID", "type": "keyword"},
{ "name": "LOTNO","type": "keyword" },
{ "name": "PRODUCTIONORDERID","type": "keyword" },
{ "name": "CREATEDDATE","type": "date" }
]
}
}
}
]
}
}
- 写完配置文件后，我们兴高采烈的到linux上试一下，
python /datax/bin/datax.py --jvm="-Xms8G -Xmx8G" tidb2es.json。
接下来开始一系列踩坑报错问题：
Caused by: java.lang.IllegalArgumentException: Preemptive authentication set without credentials因为我们内网是没有accessId和accessKey，所以需要到源码把.setPreemptiveAuth(new HttpHost(endpoint))注释掉，取消身份验证，重新编译后打包上传去替换/datax/plugin/writer/elasticsearchwriter就可以了。
路径：\DataX-master\DataX-master\elasticsearchwriter\src\main\java\com\alibaba\datax\plugin\writer\elasticsearchwriter\ESClient.java
Builder httpClientConfig = new HttpClientConfig
.Builder(endpoint)
// .setPreemptiveAuth(new HttpHost(endpoint)) 这行注释掉
.multiThreaded(multiThread)
.connTimeout(30000)
.readTimeout(readTimeout)
.maxTotalConnection(200)
.requestCompressionEnabled(compression)
.discoveryEnabled(discovery)
.discoveryFrequency(5l, TimeUnit.MINUTES);
ERROR RetryUtil - Exception when calling callable, 异常Msg:No Server is assigned to client to connect
原因在配置文件tidb2es.json中的discovery一定要设为false, 启用节点发现将(轮询)并定期更新客户机中的服务器列表。
Rejecting mapping update to [lot] as the final mapping would have more than 1 type: [_doc, lot]
之前我在es中写好了索引模板，经过观察，索引模板回自动帮我生成type为_doc，datax之所以汇报这个错是因为一开始我没在tidb2es.json中设置type，因为我想索引模板已经有type字段了，而es7只能有一个type，结果报的错是说我设置了两个type，我就纳闷了。
于是我尝试把tidb2es.json中的type也设置为_doc，Success！原来如果我没在tidb2es.json设置type的话，会自动帮我生成和索引一样值的type，所以出现了两个不一样的type。
PUT _template/lot_template
{
"template": "lot*",
"order": 1,
"settings": {
"number_of_shards": 3,
"number_of_replicas": 1,
"refresh_interval": "120s"
},
"mappings": {
"properties": {
"LOTID": {
"type": "keyword",
"doc_values": false
},
"LOTNO": {
"type": "keyword",
"doc_values": false
},
"PRODUCTIONORDERID": {
"type": "keyword"
},
"CREATEDDATE": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss",
"index": false
}
}
}
}
GC life time is shorter than transaction duration, transaction starts at 2021-03-26 19:58:12.823 +0800 CST, GC safe point is 2021-03-26 20:00:03.523 +0800 CST
这里是说tidb gc的时间比长事务读取时间短，数据还未读到就被清理了，我看文章对应的方法是调大tidb的gc时间。但是我组长告诉我tidb现在厂家也在做大批量初始化数据，所以gc的时候直接把我的进程杀死了。让我手动where按一批一批的迁移（我觉得这个办法太笨了），所有这里我暂未做出调整。。。（更新待续，敬请期待！）
--------------------------------------------- 我是华丽分割线 -----------------------------------------------
更新啦更新啦，上回不是说到要手动一批一批的跑嘛，我心里是拒绝的，但是身体还是很诚实的去搜了对应的方案哈哈-。-
如果要分批跑，那就需要用的limit，offset：
// 表示取50000W条数据之后的6000W条，这条sql相当于前面的50000W条我也全都取出来，然后抛弃。
// 按亿级搜索查询的limit，Mysql在这方面简直死亡。
SELECT * FROM tableName LIMIT 500000000, 60000000;
// 表示取50000W条数据之后的6000W条，这条sql表示我只从50000W开始往后取6000W，这样速度提升到数倍
SELECT * FROM tableName
WHERE id >= (SELECT id FROM tableName ORDER BY id LIMIT 500000000 , 1)
LIMIT 60000000;
为什么是6000W，因为据我前几次跑挂了的数据，都是到了6000W左右就报错，而且我之前用的是第一条sql，肯定涉及到深度分页瓶颈，服务器也等了这么久也确实gc了未处理的数据了。
接下来我按照第二条sql，开了九台服务器，每台服务器按LOTID增量跑6000W，现在运行情况良好，顶呱呱！相同的，我们上文的logstash问题也可以解决了，用第二条sql去跑，就不会出现到后面跑不动的情况了。
后边鉴于mysqld的lot表是一直有数据读入的，所以计划使用lostash来做简单的数据增量同步。
--------------------------------------------- 我是华丽分割线 -----------------------------------------------
各位小伙伴我又来了，上次同步到3亿的时候，就又爆出了GC life time is shorter than transaction duration, transaction starts at ，靓女无语。
思来想去，是我的sql有问题导致越往深处读，mysql所消耗时间越多。跟组长讨论了后，觉得可能是索引失效的问题，但是我搜索后发现，并没有写导致索引失效的sql表达呀。
问题可能处在我的子查询上，我可以在外边把对应LOTID编号先查好后，在把数值写在sql中。
其中order by对查出的数据又做了一边排序，也很耗费资源。组长建议我用范围查询（针不戳），sql优化后语句：
SELECT * FROM tableName WHERE LOTID >= 200000000 and LOTID < 300000000。
除此之外，我一直想在我跑的是不是单线程？我一开始是将整个sql写在“querysql”中，但是我看别的官方模板都是将字段写在column，然后开启的splitPk。
于是我又对json做了一次大改动，把sql语句拆成column，where和from分开写，这样splitPk才能捕获代表的字段进行数据分片，才能真正开启并发读！
最终模板
{
"job": {
"setting": {
"speed": {
"channel": 32,
"batchSize":4096
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"splitPk": "LOTID",
"connection": [
{
"table": [
"LOT"
],
"jdbcUrl": [
"jdbc:mysql://xxx:4000/xxx?useUnicode=true&characterEncoding=utf-8"
]
}
],
"where": "LOTID >= 200000000 and LOTID < 250000000",
"username": "xxx",
"password": "xxx",
"column": [
"LOTID",
"LOTNO",
"DATE_FORMAT(CREATEDDATE,'%Y-%m-%d %H:%i:%S') as CREATEDDATE"
]
}
},
"writer": {
"name": "elasticsearchwriter",
"parameter": {
"endpoint": "http://xxx:9200",
"index": "lot",
"type":"_doc",
"settings": {"index" :{"number_of_shards": 3, "number_of_replicas": 1, "refresh_interval": "120s"}},
"discovery": false,
"dynamic": true,
"column": [
{ "name": "LOTID", "type": "keyword"},
{ "name": "LOTNO","type": "keyword" },
{ "name": "CREATEDDATE","type": "date" }
]
}
}
}
]
}
}
我截取了datax的测试报告，其中每1s跑了10W+，预计1小时能跑3亿。至此，问题解决。
任务启动时刻 : 2021-03-29 10:19:05
任务结束时刻 : 2021-03-29 10:25:57
任务总计耗时 : 411s
任务平均流量 : 23.76MB/s
记录写入速度 : 119267rec/s
读出记录总数 : 48899522
读写失败总数 : 0
总结一下，真正在生产环境做数据迁移，就不能凭借几篇网文的配置就开始随便写，因为你完全不知道他的参数为什么要这样配置，或许你的业务背景和别人一点不一样，都会出问题。
最好的方法是自已的官网查，只有自已知道每个配置字段代表什么，才能真正完成工作，并且掌握好知识。
