ELK 之 Canal导出mysql表数据到es_elk canal-优快云博客

本文链接：https://blog.youkuaiyun.com/sunboychenll/article/details/108658697

可以参考,写的挺详细的

关系映射可参考：

https://blog.youkuaiyun.com/u013214151/article/details/105562423

介绍：

Canal是基于MySQL二进制日志的高性能数据同步系统属于阿里巴巴的一个开源项目，基于java实现，整体已经在很多大型的互联网项目生产环境中使用，包括阿里、美团等都有广泛的应用，是一个非常成熟的数据库同步方案，基础的使用只需要进行简单的配置即可，以提供可靠的低延迟增量数据管道。

官方文档：https://github.com/alibaba/canal/wiki
项目地址：https://github.com/alibaba/canal
下载地址：https://github.com/alibaba/canal/releases

Canal的特点

支持所有平台。
支持由Prometheus支持的细粒度系统监视。
支持通过不同方式（例如通过GTID）解析和订阅MySQL Binlog。
支持高性能，实时数据同步。（查看更多的性能）
Canal Server和Canal Client均支持由Apache ZooKeeper支持的HA /可伸缩性
Docker支持。

Canal实现原理

Canal将MySQL数据同步至Elasticsearch是基于MySQL的主从复制原理实现。
1.MySQL主从复制原理

主服务器将增删改操作记录到 binlog 中，这些记录称为binlog事件，可以通过来查看show binary events。
从服务器通过 I/O 线程与主服务器的binlog日志建立连接后进行读取，读取的binlog日志写入本地 Relay log中级日志。
从服务器在通过本身的 SQL 线程读取 Relay log 后在本机执行主服务器的操作记录。

2.Canal实现原理

Canal通过模拟成MySQL Slave的交互协议，表面上伪装成MySQL Slave，向MySQL Master发送dump协议。
MySQL Master收到dump请求，开始推送 binlog 给 Slave即Canal。
Canal解析 binlog 对象(原始为byte流)

3.C/S架构
Canal为C/S架构，分为Server端和Client端

Server的包名为canal-deployer，server端部署好以后，可以直接监听mysql binlog,因为server端是把自己模拟成了mysql slave，所以，只能接受数据，没有进行任何逻辑的处理，具体的逻辑处理，需要client端进行处理。
Client的包名为canal-adapter,client可以自动开发或者使用官方提供的canal-adapter,Adapter是可以将canal server端获取的数据转换成几个常用的中间件数据源，现在支持kafka、rocketmq、hbase、elasticsearch，针对这几个中间件的支持，直接配置即可，无需开发。

4.Canal Admin
从Canal1.1.4版本以后，阿里巴巴推出了Canal的WEB管理UI，版本必须要>=Canal1.1.4以上才可以部署，canal-admin设计上是为canal提供整体配置管理、节点运维等面向运维的功能，提供相对友好的WebUI操作界面，方便更多用户快速和安全的操作。

配置环境

目前使用的是：

阿里云的云数据库mysql 5.6 3306端口

ElasticSearch V7.7.0 9200端口 jdk1.8

Canal-deployer V1.1.15 1110/1111/1112 jdk1.8

Canal-adapter V1.1.15 8081 jdk1.8

正式开始：

首先开启mysql中的binlog日志
注意
必须在/etc/my.cnf文件中开启以下选型

[mysqld]
log-bin=/usr/local/mysql/binlogs/mysql-bin #开启 binlog
binlog-format=ROW #选择 ROW 模式,必须为ROW行模式
server_id=1 #配置 MySQL replaction 需要定义，不要和 canal 的 slaveId 重复

mysql创建授权当然有权限的用户就不用了

mysql> create user canal identified by 'Canal@2019!'; #创建canal账户
Query OK, 0 rows affected (0.08 sec)
mysql> grant select,replication slave,replication client on *.* to 'canal'@'%'; #授权canal账户查询和复制权限
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges; #刷新授权
Query OK, 0 rows affected (0.00 sec)

4.查看binlog是否正确启动

show variables like 'binlog_format';
show variables like 'log_bin';
select version();
canal 支持 binlog 格式为 ROW 的模式。如果你没开启 binlog，并且格式是非 row 的，建议修改一下 mysql 的配置文件。

5.创建需要同步的数据库

CREATE TABLE `ewj_market`.`canal_table`(

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(128) DEFAULT '' COMMENT '名字',

`age` INT(3) DEFAULT 0 COMMENT '年龄',

`address` VARCHAR(256) DEFAULT '' COMMENT '地址',

`c_time` TIMESTAMP DEFAULT NOW() COMMENT '创建时间',

PRIMARY KEY (`id`)

) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

部署Elasticsearch

以下两种方式根据需求选择其一即可
集群部署详见：Elasticsearch7.4集群部署并进行安全认证
单点部署详见：Elasticsearch7.4单点部署并进行安全认证

部署Canal-deployer服务端

1.下载并解压

wget https://github.com/alibaba/canal/releases/download/canal-1.1.5-alpha-1/canal.deployer-1.1.5-SNAPSHOT.tar.gz
mkdir /usr/local/canal-deployer
tar xf canal.deployer-1.1.5-SNAPSHOT.tar.gz -C /usr/local/canal-deployer

2.修改配置文件

vim /usr/local/canal-deployer/conf/example/instance.properties

canal.instance.mysql.slaveId=3 #修改ID，不能和MySQL数据库一致
canal.instance.gtidon=false
canal.instance.master.address=192.168.31.216:8809 #指定mysql数据库地址及端口
canal.instance.master.journal.name=
canal.instance.master.position=
canal.instance.master.timestamp=
canal.instance.master.gtid=
canal.instance.rds.accesskey=
canal.instance.rds.secretkey=
canal.instance.rds.instanceId=
canal.instance.tsdb.enable=true
canal.instance.dbUsername=canal #指定复制账号
canal.instance.dbPassword=Canal@2019! #指定复制密码
canal.instance.connectionCharset = UTF-8 #字符集格式，需要与mysql保持一致
canal.instance.enableDruid=false
canal.instance.filter.regex=.*\\..* #表名监控的正则
canal.instance.filter.black.regex=
canal.mq.topic=example
canal.mq.partition=0

注意：只要修改上面红色的

3.启动canal-deployer
因为canal-depaloyer由java开发，所以需要jdk环境，jdk版本需要大于1.5

yum install java-1.8.0-openjdk.x86_64 java-1.8.0-openjdk-devel.x86_64 -y
/usr/local/canal-deployer/bin/startup.sh

4.查看日志及端口
1）查看 server 日志

canal-deployer默认监听三个端口,11110、11111、11112
11110：为admin管理端口
11111：为canal deployer 服务器占用的端口
11112：为指标下拉端口

2）查看 instance 的日志

部署Canal-adapter客户端

1.下载并解压

wget https://github.com/alibaba/canal/releases/download/canal-1.1.5-alpha-1/canal.adapter-1.1.5-SNAPSHOT.tar.gz
mkdir /usr/local/canal-adapter
tar xf canal.adapter-1.1.5-SNAPSHOT.tar.gz -C /usr/local/canal-adapter/

2.添加mysql8.0.18连接器,这一步很重要，网上好多都没有说，我是搞了一星期才出来的.
默认canal-adapter的lib中只有mysql5.x版本的连接器，所以要下载ysql-connector-java-8.0.18.jar

mysql-connector-java-8.0.15.jar支持8.0/ 5.7 /5.6版本的mysql

wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.18/mysql-connector-java-8.0.18.jar
mv mysql-connector-java-8.0.18.jar /usr/local/canal-adapter/lib/
chmod 777 /usr/local/canal-adapter/lib/mysql-connector-java-8.0.18.jar #权限修改与其它lib库一致
chmod +st /usr/local/canal-adapter/lib/mysql-connector-java-8.0.18.jar

3.修改application.yml

注意：主要修改了这些配置，打开-name: es7时，位置要对好，其它的不要动,不然有好多奇怪的错误.

ES有集群时：

4.修改适配器映射文件，如下

vim mytest_user.yml

dataSourceKey: defaultDS
destination: example
groupId: g1
esMapping:
_index: canal_tsdb
_id: _id
upsert: true
# pk: id
sql: "select a.id as _id,a.age,a.name,a.address from canal_table a"
etlCondition: "where a.c_time>={}"
commitBatch: 3000

etlCondition: "where id<='{0}'" #etl的条件参数，可以将之前没能同步的数据同步，数据量大的话可以用logstash

注意:位置一定要对好，不然会报错.

在配置完成canal-adapter后还不要着急启动，需要事先在es中定义我们指定的索引以及mapping

Elasticsearch创建索引

打开Kibana界面，找到左侧的“开发工具”，然后进行创建索引及指定Mapping

语法如下：

POST canal_tsdb/_doc
{
"mappings":{
"_doc":{
"properties":{
"age":{
"type":"long"
},
"name":{
"type":"text"
},
"address":{
"type":"text"
}
}
}
}
}

查看创建的索引及Mapping

启动Canal-adapter并写入数据

1.在Canal-adapter机器上启动进程并查看日志

/usr/local/canal-adapter/bin/startup.sh
tail -f /usr/local/canal-adapter/logs/adapter/adapter.log

2.在MySQL再次插入一条数据并查看日志

mysql> INSERT INTO canal_tsdb.canal_table(id, age, name, address) VALUES (2, 24, 'abcops.cn', '上海市浦东新区');
Query OK, 1 row affected (0.01 sec)

3.查看Canal-deployer服务端日志

tail -f /usr/local/canal-deployer/logs/example/meta.log
2019-12-17 15:48:47.457 - clientId:1001 cursor:[mysql-bin.000005,2125,1576568901000,2,] address[192.168.31.216/192.168.31.216:8809]

4.查看Canal-apadter客户端日志

tail -f /usr/local/canal-adapter/logs/adapter/adapter.log
2019-12-17 15:48:47.060 [pool-2-thread-1] INFO c.a.o.canal.client.adapter.logger.LoggerAdapterExample - DML: {"data":[{"id":2,"age":24,"name":"abcops.cn","address":"上海市浦东新区"}],"database":"canal_tsdb","destination":"example","es":1576568901000,"groupId":null,"isDdl":false,"old":null,"pkNames":["id"],"sql":"","table":"canal_table","ts":1576568927060,"type":"INSERT"}
2019-12-17 15:48:47.062 [pool-2-thread-1] DEBUG c.a.o.canal.client.adapter.es.core.service.ESSyncService - DML: {"data":[{"id":2,"age":24,"name":"abcops.cn","address":"上海市浦东新区"}],"database":"canal_tsdb","destination":"example","es":1576568901000,"groupId":null,"isDdl":false,"old":null,"pkNames":["id"],"sql":"","table":"canal_table","ts":1576568927061,"type":"INSERT"}
Affected indexes: canal_tsdb

5.到Kibana中查看文档

6.在MySQL中更新数据

mysql> update canal_table set name='goblogs.cn',address='上海市虹口区' where id=2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from canal_table;
+----+-----+------------+-----------------------+
| id | age | name | address |
+----+-----+------------+-----------------------+
| 1 | 24 | 徐伟亮 | 上海市浦东新区 |
| 2 | 24 | goblogs.cn | 上海市虹口区 |
+----+-----+------------+-----------------------+
2 rows in set (0.00 sec)

然后去Kibana中进行查看文档是否被更新

7.在MySQL中删除数据

mysql> delete from canal_table where id=2;
Query OK, 1 row affected (0.02 sec)
mysql> select * from canal_table;
+----+-----+-----------+-----------------------+
| id | age | name | address |
+----+-----+-----------+-----------------------+
| 1 | 24 | 徐伟亮 | 上海市浦东新区 |
+----+-----+-----------+-----------------------+
1 row in set (0.00 sec)

在Kibana中查看文档是否被删除

注：当数据同步至Elasticsearch后，Elasticsearch就有了对同步来数据的CURD权限

多表关联导出到ES时

只能用left join 而不能用等值连接

1：多表映射(一对一, 多对一)索引示例sql:

select a.id as _id, a.name, a.role_id, b.role_name, a.c_time from user a

left join role b on b.id = a.role_id

2: 多表映射(一对多)索引示例sql:

select a.id as _id, a.name, a.role_id, c.labels, a.c_time from user a

left join (select user_id, group_concat(label order by id desc separator ';') as labels from label

group by user_id) c on c.user_id=a.id

注:left join 后的子查询只允许一张表, 即子查询中不能再包含子查询或者关联!!

3 其它类型的sql示例:

geo type

select ... concat(IFNULL(a.latitude, 0), ',', IFNULL(a.longitude, 0)) AS location, ...

复合主键

select concat(a.id,'_',b.type) as _id, ... from user a left join role b on b.id=a.role_id

数组字段

select a.id as _id, a.name, a.role_id, c.labels, a.c_time from user a

left join (select user_id, group_concat(label order by id desc separator ';') as labels from label

group by user_id) c on c.user_id=a.id

配置中使用:

objFields:

labels: array:;

对象字段

select a.id as _id, a.name, a.role_id, c.labels, a.c_time, a.description from user a

配置中使用:

objFields:

description: object

我的多表内容如下：

dataSourceKey: defaultDS
destination: example
groupId: g1
esMapping:
_index: user_article_idx
_id: _id
upsert: true
# pk: id
sql: "select us.id,us.name,us.age,art.id as _id,art.id as artId,art.title,art.expert,art.author_id,art.created from mytest_article art left join mytest_user us on us.id=art.author_id"
# etlCondition: "where combine.c_time>={}"
commitBatch: 3000

------------------------------------------------------------

同步所有数据:

http://121.40.42.216:8081/destinations

http://121.40.42.216:8081/syncSwitch/example

curl http://121.40.42.216:8081/etl/es7/mytest_user.yml -X POST
curl http://121.40.42.216:8081/etl/es7/user_article.yml -X POST

5 adapter管理REST接口

https://github.com/alibaba/canal/wiki/ClientAdapter#32-adapter%E7%AE%A1%E7%90%86rest%E6%8E%A5%E5%8F%A3

5.1 查询所有订阅同步的canal instance或MQ topic

http://ip:8081/destinations

[{"destination":"example","status":"on"}]

5.2 查询数据同步开关状态

查看指定 canal instance/MQ topic 的数据同步开关状态

http://ip:8081/syncSwitch/example

{"stauts":"on"}

5.3 打开数据同步开关

针对 example 这个canal instance/MQ topic 进行开关操作. off代表关闭, instance/topic下的同步将阻塞或者断开连接不再接收数据, on代表开启

注: 如果在配置文件中配置了 zookeeperHosts 项, 则会使用分布式锁来控制HA中的数据同步开关, 如果是单机模式则使用本地锁来控制开关

PUT http://ip:8081/syncSwitch/example/on

{
    "code": 20000,
    "message": "实例: example 开启同步成功"
}

改为/syncSwitch/example/off是关闭

{
    "code": 20000,
    "message": "实例: example 关闭同步成功"
}

5.4 手动ETL

导入数据到指定类型的库, 如果params参数为空则全表导入, 参数对应的查询条件在配置中的etlCondition指定

#/etl/es7/{key}/user.yml
#curl http://ip:8081/etl/es7/es71/user.yml -X POST -d "params=50"
curl http://ip:8081/etl/es7/es71/user.yml -X POST