|NO.Z.00004|——————————|Deployment|——|Hadoop&EbProjec电商实时数仓项目.v04|——|EbProject.v04|canal部署&kafka客户端测试|

一、Kafka客户端测试
### --- 启动相关服务

~~~     # 启动zookeeper服务
[root@hadoop01 ~]# ./zk.sh start
~~~     # 启动kafka服务
[root@hadoop01 ~]# kafka-server-start.sh -daemon /opt/yanqi/servers/kafka_2.12/config/server.properties
~~~     # 启动canal服务
[root@hadoop02 ~]# sh /opt/yanqi/servers/canal/bin/startup.sh
### --- 创建kafka主题

~~~     # 创建主题
[root@hadoop02 ~]# kafka-topics.sh --zookeeper hadoop01:2181,hadoop02:2181,hadoop03:2181/myKafka --create --replication-factor 3 -partitions 1 --topic canal
~~~     # 查看主题
[root@hadoop02 ~]# kafka-topics.sh --zookeeper localhost:2181/myKafka --list
canal
### --- 启动kafka主题

~~~     # 启动kafka生产者
[root@hadoop02 ~]# kafka-console-producer.sh --broker-list hadoop02:9092 --topic canal
~~~     # 启动kafka消费者
[root@hadoop02 ~]# kafka-console-consumer.sh --zookeeper hadoop02:2181/myKafka --topic canal --from-beginning
二、在kafka验证canal采集MySQL生成binlog数据
### --- 验证kafka通过canal采集mysql生成的binlog数据
~~~      在mysql下yanqi_trade_orders表中对数据进行操作

~~~     # insert插入操作
mysql> INSERT INTO `yanqi_trade_orders` VALUES ('2', '23a0b124546', '121', '2', '0.12', '6331.00', '2', '0', '370203', '0', '0', '0', '1', '2020-06-28 16:55:02', '2020-06-28 16:55:02', '2020-10-21 22:54:32');
mysql> INSERT INTO `yanqi_trade_orders` VALUES ('3', '23a0b124546', '35', '2', '0.12', '1987.50', '4', '0', '370203', '0', '0', '0', '1', '2020-06-28 12:07:01', '2020-06-28 12:07:01', '2020-10-21 22:54:34');
~~~     # update更新操作
mysql> update `dwshow`.`yanqi_trade_orders` set `status` = '2' where `orderId` = '1';
~~~     # delete删除操作
mysql> DELETE FROM `dwshow`.`yanqi_trade_orders` WHERE `orderId` = '3';
### --- 在kafka消费者下查看采集到的数据

~~~     # 在kafka消费者主题下查看采集到的数据
[root@hadoop02 ~]# kafka-console-consumer.sh --zookeeper hadoop02:2181/myKafka --topic canal --from-beginning
~~~     # 在mysql下insert插入操作采集到数据
{"data":[{"orderId":"2","orderNo":"23a0b124546","userId":"121","status":"2","productMoney":"0.12","totalMoney":"6331.0","payMethod":"2","isPay":"0","areaId":"370203","tradeSrc":"0","tradeType":"0","isRefund":"0","dataFlag":"1","createTime":"2020-06-28 16:55:02","payTime":"2020-06-28 16:55:02","modifiedTime":"2020-10-21 22:54:32"}],"database":"dwshow","es":1638097808000,"id":3,"isDdl":false,"mysqlType":{"orderId":"bigint(11)","orderNo":"varchar(20)","userId":"bigint(11)","status":"tinyint(4)","productMoney":"decimal(11,2)","totalMoney":"decimal(11,2)","payMethod":"tinyint(4)","isPay":"tinyint(4)","areaId":"int(11)","tradeSrc":"tinyint(4)","tradeType":"int(11)","isRefund":"tinyint(4)","dataFlag":"tinyint(4)","createTime":"varchar(25)","payTime":"varchar(25)","modifiedTime":"timestamp"},"old":null,"pkNames":["orderId"],"sql":"","sqlType":{"orderId":-5,"orderNo":12,"userId":-5,"status":-6,"productMoney":3,"totalMoney":3,"payMethod":-6,"isPay":-6,"areaId":4,"tradeSrc":-6,"tradeType":4,"isRefund":-6,"dataFlag":-6,"createTime":12,"payTime":12,"modifiedTime":93},"table":"yanqi_trade_orders","ts":1638097808750,"type":"INSERT"}
{"data":[{"orderId":"3","orderNo":"23a0b124546","userId":"35","status":"2","productMoney":"0.12","totalMoney":"1987.50","payMethod":"4","isPay":"0","areaId":"370203","tradeSrc":"0","tradeType":"0","isRefund":"0","dataFlag":"1","createTime":"2020-06-28 12:07:01","payTime":"2020-06-28 12:07:01","modifiedTime":"2020-10-21 22:54:34"}],"database":"dwshow","es":1638098012000,"id":4,"isDdl":false,"mysqlType":{"orderId":"bigint(11)","orderNo":"varchar(20)","userId":"bigint(11)","status":"tinyint(4)","productMoney":"decimal(11,2)","totalMoney":"decimal(11,2)","payMethod":"tinyint(4)","isPay":"tinyint(4)","areaId":"int(11)","tradeSrc":"tinyint(4)","tradeType":"int(11)","isRefund":"tinyint(4)","dataFlag":"tinyint(4)","createTime":"varchar(25)","payTime":"varchar(25)","modifiedTime":"timestamp"},"old":null,"pkNames":["orderId"],"sql":"","sqlType":{"orderId":-5,"orderNo":12,"userId":-5,"status":-6,"productMoney":3,"totalMoney":3,"payMethod":-6,"isPay":-6,"areaId":4,"tradeSrc":-6,"tradeType":4,"isRefund":-6,"dataFlag":-6,"createTime":12,"payTime":12,"modifiedTime":93},"table":"yanqi_trade_orders","ts":1638098012708,"type":"INSERT"}
~~~     # 在mysql下update更新操作采集到数据
{"data":[{"orderId":"1","orderNo":"23a0b124546","userId":"98","status":"2","productMoney":"0.12","totalMoney":"10468.0","payMethod":"2","isPay":"0","areaId":"370203","tradeSrc":"0","tradeType":"0","isRefund":"1","dataFlag":"2","createTime":"2020-06-28 18:14:01","payTime":"2020-06-28 18:14:01","modifiedTime":"2021-11-28 19:20:52"}],"database":"dwshow","es":1638098452000,"id":6,"isDdl":false,"mysqlType":{"orderId":"bigint(11)","orderNo":"varchar(20)","userId":"bigint(11)","status":"tinyint(4)","productMoney":"decimal(11,2)","totalMoney":"decimal(11,2)","payMethod":"tinyint(4)","isPay":"tinyint(4)","areaId":"int(11)","tradeSrc":"tinyint(4)","tradeType":"int(11)","isRefund":"tinyint(4)","dataFlag":"tinyint(4)","createTime":"varchar(25)","payTime":"varchar(25)","modifiedTime":"timestamp"},"old":[{"status":"1","modifiedTime":"2021-11-28 19:19:53"}],"pkNames":["orderId"],"sql":"","sqlType":{"orderId":-5,"orderNo":12,"userId":-5,"status":-6,"productMoney":3,"totalMoney":3,"payMethod":-6,"isPay":-6,"areaId":4,"tradeSrc":-6,"tradeType":4,"isRefund":-6,"dataFlag":-6,"createTime":12,"payTime":12,"modifiedTime":93},"table":"yanqi_trade_orders","ts":1638098452275,"type":"UPDATE"}
~~~     # 在mysql下delete删除操作采集到数据
{"data":[{"orderId":"3","orderNo":"23a0b124546","userId":"35","status":"2","productMoney":"0.12","totalMoney":"1987.50","payMethod":"4","isPay":"0","areaId":"370203","tradeSrc":"0","tradeType":"0","isRefund":"0","dataFlag":"1","createTime":"2020-06-28 12:07:01","payTime":"2020-06-28 12:07:01","modifiedTime":"2020-10-21 22:54:34"}],"database":"dwshow","es":1638098556000,"id":7,"isDdl":false,"mysqlType":{"orderId":"bigint(11)","orderNo":"varchar(20)","userId":"bigint(11)","status":"tinyint(4)","productMoney":"decimal(11,2)","totalMoney":"decimal(11,2)","payMethod":"tinyint(4)","isPay":"tinyint(4)","areaId":"int(11)","tradeSrc":"tinyint(4)","tradeType":"int(11)","isRefund":"tinyint(4)","dataFlag":"tinyint(4)","createTime":"varchar(25)","payTime":"varchar(25)","modifiedTime":"timestamp"},"old":null,"pkNames":["orderId"],"sql":"","sqlType":{"orderId":-5,"orderNo":12,"userId":-5,"status":-6,"productMoney":3,"totalMoney":3,"payMethod":-6,"isPay":-6,"areaId":4,"tradeSrc":-6,"tradeType":4,"isRefund":-6,"dataFlag":-6,"createTime":12,"payTime":12,"modifiedTime":93},"table":"yanqi_trade_orders","ts":1638098557109,"type":"DELETE"}
附录一：此时MySQL数据表若有变化，会将row类型的log写进Kakfa，具体格式为json：
### --- 此时MySQL数据表若有变化，会将row类型的log写进Kakfa，具体格式为json：

data：       # --- 最新的数据，为json数组。 如果是插入则表示最新插入的数据； 如果是更新，则表示更新后的最新数据； 如果是删除，则表示被删除的数据
database：   # --- 数据库名称
es：         # --- 事件时间，13位的时间戳
id：         # --- 事件操作的序列号，1,2,3...
isDdl：      # --- 是否是DDL操作
mysqlType：  # --- 字段类型
old：        # --- 旧数据
pkNames：    # --- 主键名称
sql：        # --- SQL语句
sqlType：    # --- 是经过canal转换处理的，比如unsigned int会被转化为Long，unsigned long会被转换为BigDecimal
table：      # --- 表名
ts：         # --- 日志时间
type：       # --- 操作类型，比如DELETE，UPDATE，INSERT
附录二：insert操作
{
  "data": [
    {
      "orderId": "3",
      "orderNo": "23a0b124546",
      "userId": "35",
      "status": "2",
      "productMoney": "0.12",
      "totalMoney": "1987.50",
      "payMethod": "4",
      "isPay": "0",
      "areaId": "370203",
      "tradeSrc": "0",
      "tradeType": "0",
      "isRefund": "0",
      "dataFlag": "1",
      "createTime": "2020-06-28 12:07:01",
      "payTime": "2020-06-28 12:07:01",
      "modifiedTime": "2020-10-21 22:54:34"
    }
  ],
  "database": "dwshow",
  "es": 1638098012000,
  "id": 4,
  "isDdl": false,
  "mysqlType": {
    "orderId": "bigint(11)",
    "orderNo": "varchar(20)",
    "userId": "bigint(11)",
    "status": "tinyint(4)",
    "productMoney": "decimal(11,2)",
    "totalMoney": "decimal(11,2)",
    "payMethod": "tinyint(4)",
    "isPay": "tinyint(4)",
    "areaId": "int(11)",
    "tradeSrc": "tinyint(4)",
    "tradeType": "int(11)",
    "isRefund": "tinyint(4)",
    "dataFlag": "tinyint(4)",
    "createTime": "varchar(25)",
    "payTime": "varchar(25)",
    "modifiedTime": "timestamp"
  },
  "old": null,
  "pkNames": [
    "orderId"
  ],
  "sql": "",
  "sqlType": {
    "orderId": -5,
    "orderNo": 12,
    "userId": -5,
    "status": -6,
    "productMoney": 3,
    "totalMoney": 3,
    "payMethod": -6,
    "isPay": -6,
    "areaId": 4,
    "tradeSrc": -6,
    "tradeType": 4,
    "isRefund": -6,
    "dataFlag": -6,
    "createTime": 12,
    "payTime": 12,
    "modifiedTime": 93
  },
  "table": "yanqi_trade_orders",
  "ts": 1638098012708,
  "type": "INSERT"
}
附录三：update操作
{
  "data": [
    {
      "orderId": "1",
      "orderNo": "23a0b124546",
      "userId": "98",
      "status": "2",
      "productMoney": "0.12",
      "totalMoney": "10468.0",
      "payMethod": "2",
      "isPay": "0",
      "areaId": "370203",
      "tradeSrc": "0",
      "tradeType": "0",
      "isRefund": "1",
      "dataFlag": "2",
      "createTime": "2020-06-28 18:14:01",
      "payTime": "2020-06-28 18:14:01",
      "modifiedTime": "2021-11-28 19:20:52"
    }
  ],
  "database": "dwshow",
  "es": 1638098452000,
  "id": 6,
  "isDdl": false,
  "mysqlType": {
    "orderId": "bigint(11)",
    "orderNo": "varchar(20)",
    "userId": "bigint(11)",
    "status": "tinyint(4)",
    "productMoney": "decimal(11,2)",
    "totalMoney": "decimal(11,2)",
    "payMethod": "tinyint(4)",
    "isPay": "tinyint(4)",
    "areaId": "int(11)",
    "tradeSrc": "tinyint(4)",
    "tradeType": "int(11)",
    "isRefund": "tinyint(4)",
    "dataFlag": "tinyint(4)",
    "createTime": "varchar(25)",
    "payTime": "varchar(25)",
    "modifiedTime": "timestamp"
  },
  "old": [
    {
      "status": "1",
      "modifiedTime": "2021-11-28 19:19:53"
    }
  ],
  "pkNames": [
    "orderId"
  ],
  "sql": "",
  "sqlType": {
    "orderId": -5,
    "orderNo": 12,
    "userId": -5,
    "status": -6,
    "productMoney": 3,
    "totalMoney": 3,
    "payMethod": -6,
    "isPay": -6,
    "areaId": 4,
    "tradeSrc": -6,
    "tradeType": 4,
    "isRefund": -6,
    "dataFlag": -6,
    "createTime": 12,
    "payTime": 12,
    "modifiedTime": 93
  },
  "table": "yanqi_trade_orders",
  "ts": 1638098452275,
  "type": "UPDATE"
}
附录四：delete操作
{
  "data": [
    {
      "orderId": "3",
      "orderNo": "23a0b124546",
      "userId": "35",
      "status": "2",
      "productMoney": "0.12",
      "totalMoney": "1987.50",
      "payMethod": "4",
      "isPay": "0",
      "areaId": "370203",
      "tradeSrc": "0",
      "tradeType": "0",
      "isRefund": "0",
      "dataFlag": "1",
      "createTime": "2020-06-28 12:07:01",
      "payTime": "2020-06-28 12:07:01",
      "modifiedTime": "2020-10-21 22:54:34"
    }
  ],
  "database": "dwshow",
  "es": 1638098556000,
  "id": 7,
  "isDdl": false,
  "mysqlType": {
    "orderId": "bigint(11)",
    "orderNo": "varchar(20)",
    "userId": "bigint(11)",
    "status": "tinyint(4)",
    "productMoney": "decimal(11,2)",
    "totalMoney": "decimal(11,2)",
    "payMethod": "tinyint(4)",
    "isPay": "tinyint(4)",
    "areaId": "int(11)",
    "tradeSrc": "tinyint(4)",
    "tradeType": "int(11)",
    "isRefund": "tinyint(4)",
    "dataFlag": "tinyint(4)",
    "createTime": "varchar(25)",
    "payTime": "varchar(25)",
    "modifiedTime": "timestamp"
  },
  "old": null,
  "pkNames": [
    "orderId"
  ],
  "sql": "",
  "sqlType": {
    "orderId": -5,
    "orderNo": 12,
    "userId": -5,
    "status": -6,
    "productMoney": 3,
    "totalMoney": 3,
    "payMethod": -6,
    "isPay": -6,
    "areaId": 4,
    "tradeSrc": -6,
    "tradeType": 4,
    "isRefund": -6,
    "dataFlag": -6,
    "createTime": 12,
    "payTime": 12,
    "modifiedTime": 93
  },
  "table": "yanqi_trade_orders",
  "ts": 1638098557109,
  "type": "DELETE"
}
 
 
 
 
 
 
 
 
 
Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart
                                                                                                                                                   ——W.S.Landor
 

posted on   yanqi_vip  阅读(23)  评论(0)  编辑  收藏  举报

相关博文：
· |NO.Z.00009|——————————|^^ 配置 ^^|——|Hadoop&实时数仓.V09|——|项目.v09|Canal同步业务数据|kafka客户端测试|
· |NO.Z.00012|——————————|BigDataEnd|——|Hadoop&实时数仓.V12|——|项目.v12|ODS层处理|JSON日志数据格式|
· Canal 使用入门
· springboot+kafka(centos7集群部署kafka)
· 光说不练假把式，一起Kafka业务实战。
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5

导航

统计

点击右上角即可分享
微信分享提示