记一次canal抽取mysql的binlog日志到kafka环境搭建

记一次调研canal抽取mysql的binlog日志到kafka环境搭建

1.mysql相关配置

1.canal的原理是基于mysql binlog技术，需要开启mysql的binlog写入功能，并且配置binlog模式为row.

#mysql启动操作指令
service mysqld start
service mysqld status
service mysqld stop

2.开启mysql的binlog功能：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#binlog数据中包含server_id,标识该数据是由那个server同步过来的,不能和canal的slaveId重复
server-id = 1
#mysql向文件名前缀添加数字后缀
log-bin = $datadir/mysql-bin
#选择基于行的日志记录方式
binlog_format = ROW

3.测试 my.cnf 配置是否生效:

#进去客户端
mysql -uroot -proot
use mysql;
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

4.canal的原理是模拟mysql slave，需要配置相关mysql slave的权限

mysql> CREATE USER canal IDENTIFIED BY 'canal';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#权限查看
mysql> show grants for 'canal';
+----------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for canal@%                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%' IDENTIFIED BY PASSWORD '*E3619321C1A937C46A0D8BD1DAC39F93B27D4458' |
+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.canal搭建

1.安装包下载，如需下载其它版本，可直接修改所需版本号进行下载

https://github.com/alibaba/canal/releases/download/canal-1.1.4/canal.deployer-1.1.4.tar.gz

2.安装包解压

# -C将压缩包解压到指定目录
tar -zxf canal.deployer-1.1.4.tar.gz -C /usr/canal/

3.配置canal server

#canal/conf目录下文件
-rwxrwxrwx 1 root root  291 Sep  2  2019 canal_local.properties
-rwxrwxrwx 1 root root 5140 Apr 15 05:56 canal.properties
drwxrwxrwx 2 root root 4096 Apr 15 05:29 example
-rwxrwxrwx 1 root root 3119 Sep  2  2019 logback.xml
drwxrwxrwx 2 root root 4096 Apr 15 04:41 metrics
drwxrwxrwx 3 root root 4096 Apr 15 04:41 spring

a.修改canal.properties

# 默认值tcp
canal.serverMode = kafka
# 解析器的线程数，不配置可能出现阻塞或者不进行解析的情况
canal.instance.parser.parallelThreadSize = 16
# Kafka bootstrap.servers
canal.mq.servers = 192.168.198.158:9092
# 投递失败的重试次数，默认0，改为3
canal.mq.retries = 3
# Kafka batch.size，即producer一个微批次的大小，默认16K
canal.mq.batchSize = 16384
# Kafka max.request.size，即一个请求的最大大小，默认1M
canal.mq.maxRequestSize = 1048576
# Kafka linger.ms，即sender线程在检查微批次是否就绪时的超时，默认0ms
# 满足batch.size和linger.ms其中之一，就会发送消息
canal.mq.lingerMs = 200
# Kafka buffer.memory，缓存大小，默认32M
canal.mq.bufferMemory = 33554432
# 获取binlog数据的批次大小，默认50
canal.mq.canalBatchSize = 50
# 获取binlog数据的超时时间，默认200ms
canal.mq.canalGetTimeout = 200
# 是否将binlog转为JSON格式。如果为false，就是原生Protobuf格式
canal.mq.flatMessage = true
# 压缩类型
canal.mq.compressionType = none
# Kafka acks，默认all，表示分区leader会等所有follower同步完才给producer发送ack,0表示不等待ack，1表示leader写入完毕之后直接ack
canal.mq.acks = all
# Kafka消息投递是否使用事务
canal.mq.transaction = true

b.修改example/instance.properties

#简单测试只需修改topic配置就可看到效果其它配置参数请查阅官方文档
# mq config
# MQ/KAFka TOPIC配置
 canal.mq.topic=canal  #在kafka中提前创建好topic
# dynamic topic route by schema or table regex
#canal.mq.dynamicTopic=mytest1.user,mytest2\\..*,.*\\..*
canal.mq.partition=0
# hash partition config
#canal.mq.partitionsNum=3
#canal.mq.partitionHash=test.table:id^name,.*\\..*

4.canal启动指令

bin/start.sh
bin/stop.sh

5.kafka中数据查看

{"data":[{"id":"1","name":"a"}],"database":"test","es":1586900538000,"id":8,"isDdl":false,"mysqlType":{"id":"int","name":"varchar(10)"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":4,"name":12},"table":"Student","ts":1586900539360,"type":"INSERT"}
{"data":[{"id":"1","name":"b"}],"database":"test","es":1586900671000,"id":10,"isDdl":false,"mysqlType":{"id":"int","name":"varchar(10)"},"old":[{"name":"a"}],"pkNames":["id"],"sql":"","sqlType":{"id":4,"name":12},"table":"Student","ts":1586900671586,"type":"UPDATE"}
{"data":[{"id":"1","name":"b"}],"database":"test","es":1586900790000,"id":12,"isDdl":false,"mysqlType":{"id":"int","name":"varchar(10)"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":4,"name":12},"table":"Student","ts":1586900798554,"type":"DELETE"}
{"data":[{"id":"2","name":"a"}],"database":"test","es":1586900817000,"id":13,"isDdl":false,"mysqlType":{"id":"int","name":"varchar(10)"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":4,"name":12},"table":"Student","ts":1586900817797,"type":"INSERT"}

当需要配置其它实例时需要复制example文件夹到新的目录，修改instance.properties，无需重启服务