搭建基于canal的mysql-Binlog日志同步服务

canal是阿里开源的中间件,主要用于同步mysql数据库变更。目前公司部分项目使用canal将数据同步至es和redis，搭建学习以便对canal同步有更多的了解。

注：canal基于java开发，需提前搭建好java环境，可参考centos7下安装Java8运行环境。

以下操作默认已搭建java8+php7。

经网上搜索相关资料，在Github看到官方文档：https://github.com/alibaba/canal/wiki/AdminGuide。

除java环境，canal的原理是基于mysql binlog技术，所以这里一定需要开启mysql的binlog写入功能，并且配置binlog模式为row。

在centos上基于docker创建mysql容器。

其中mysql容器开启binlog日志，编码设置为utf-8:

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin # 开启Binlog
binlog-format=ROW # 设置Binlog同步模式为ROW
server_id=1 # 
binlog-do-db=test_canal_sync #这里将test库加到binlog同步
#[mysqld]
character_set_server=utf8
#
[client]
default-character_set=utf8
#
[mysql]
default-character_set=utf8

 重启mysql容器

#binlog状态
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

下载canal文件包并解压：

[root@guangzhou src]# wget https://raw.github.com/alibaba/canal/gh-pages/download/canal.deployer-1.0.4.tar.gz

[root@guangzhou conf]# pwd
/usr/local/canal.deployer/conf
[root@guangzhou conf]# vim canal.properties
#################################################
#########               destinations            #############
#################################################
canal.destinations = test_canal_sync #修改destinations配置
# conf root dir
canal.conf.dir = ../conf
# auto scan instance dir add/remove and start/stop instance
canal.auto.scan = true
canal.auto.scan.interval = 5

[root@guangzhou conf]# mkdir test_canal_sync && cp example/instance.properties test_canal_sync/instance.properties
canal.instance.master.address=172.16.1.2:3306 #mysql容器IP
canal.instance.master.journal.name=
canal.instance.master.timestamp=
canal.instance.master.gtid=

# rds oss binlog
canal.instance.rds.accesskey=
canal.instance.rds.secretkey=
canal.instance.rds.instanceId=

# table meta tsdb info
#canal.instance.tsdb.enable=true
#canal.instance.tsdb.url=jdbc:mysql://127.0.0.1:3306/canal_tsdb
#canal.instance.tsdb.dbUsername=canal
#canal.instance.tsdb.dbPassword=canal

#canal.instance.standby.address =
#canal.instance.standby.journal.name =
#canal.instance.standby.position =
#canal.instance.standby.timestamp =
#canal.instance.standby.gtid=

# username/password
canal.instance.dbUsername=root #myslq用户名
canal.instance.dbPassword=123456 #myslq密码
canal.instance.connectionCharset = UTF-8 
canal.instance.defaultDatabaseName=test_canal_sync #待同步数据库

重启canal：

[root@guangzhou canal.deployer]# ./bin/stop.sh && ./bin/startup.sh &
[1] 18499
[root@guangzhou canal.deployer]# guangzhou: stopping canal 11479 ...
Oook! cost:2
cd to /usr/local/canal.deployer/bin for workaround relative path
LOG CONFIGURATION : /usr/local/canal.deployer/bin/../conf/logback.xml
canal conf : /usr/local/canal.deployer/bin/../conf/canal.properties
CLASSPATH :/usr/local/canal.deployer/bin/../conf:/usr/local/canal.deployer/bin/../lib/zookeeper-3.4.5.jar:/usr/local/canal.deployer/bin/../lib/zkclient-0.10.jar:/usr/local/canal.deployer/bin/../lib/spring-tx-3.2.18.RELEASE.jar:/usr/local/canal.deployer/bin/../lib/spring-orm-3.2.18.RELEASE.jar:/usr/local/canal.deployer/bin/../lib/spring-jdbc-3.2.18.RELEASE.jar:/usr/local/canal.deployer/bin/../lib/spring-expression-3.2.18.RELEASE.jar:/usr/local/canal.deployer/bin/../lib/spring-core-3.2.18.RELEASE.jar:/usr/local/canal.deployer/bin/../lib/spring-context-3.2.18.RELEASE.jar:/usr/local/canal.deployer/bin/../lib/spring-beans-3.2.18.RELEASE.jar:/usr/local/canal.deployer/bin/../lib/spring-aop-3.2.18.RELEASE.jar:/usr/local/canal.deployer/bin/../lib/snappy-java-1.1.7.1.jar:/usr/local/canal.deployer/bin/../lib/snakeyaml-1.19.jar:/usr/local/canal.deployer/bin/../lib/slf4j-api-1.7.12.jar:/usr/local/canal.deployer/bin/../lib/simpleclient_pushgateway-0.4.0.jar:/usr/local/canal.deployer/bin/../lib/simpleclient_httpserver-0.4.0.jar:/usr/local/canal.deployer/bin/../lib/simpleclient_hotspot-0.4.0.jar:/usr/local/canal.deployer/bin/../lib/simpleclient_common-0.4.0.jar:/usr/local/canal.deployer/bin/../lib/simpleclient-0.4.0.jar:/usr/local/canal.deployer/bin/../lib/scala-reflect-2.11.12.jar:/usr/local/canal.deployer/bin/../lib/scala-logging_2.11-3.8.0.jar:/usr/local/canal.deployer/bin/../lib/scala-library-2.11.12.jar:/usr/local/canal.deployer/bin/../lib/rocketmq-srvutil-4.5.2.jar:/usr/local/canal.deployer/bin/../lib/rocketmq-remoting-4.5.2.jar:/usr/local/canal.deployer/bin/../lib/rocketmq-logging-4.5.2.jar:/usr/local/canal.deployer/bin/../lib/rocketmq-common-4.5.2.jar:/usr/local/canal.deployer/bin/../lib/rocketmq-client-4.5.2.jar:/usr/local/canal.deployer/bin/../lib/rocketmq-acl-4.5.2.jar:/usr/local/canal.deployer/bin/../lib/protobuf-java-3.6.1.jar:/usr/local/canal.deployer/bin/../lib/oro-2.0.8.jar:/usr/local/canal.deployer/bin/../lib/netty-tcnative-boringssl-static-1.1.33.Fork26.jar:/usr/local/canal.deployer/bin/../lib/netty-all-4.1.6.Final.jar:/usr/local/canal.deployer/bin/../lib/netty-3.2.2.Final.jar:/usr/local/canal.deployer/bin/../lib/mysql-connector-java-5.1.47.jar:/usr/local/canal.deployer/bin/../lib/metrics-core-2.2.0.jar:/usr/local/canal.deployer/bin/../lib/lz4-java-1.4.1.jar:/usr/local/canal.deployer/bin/../lib/logback-core-1.1.3.jar:/usr/local/canal.deployer/bin/../lib/logback-classic-1.1.3.jar:/usr/local/canal.deployer/bin/../lib/kafka-clients-1.1.1.jar:/usr/local/canal.deployer/bin/../lib/kafka_2.11-1.1.1.jar:/usr/local/canal.deployer/bin/../lib/jsr305-3.0.2.jar:/usr/local/canal.deployer/bin/../lib/jopt-simple-5.0.4.jar:/usr/local/canal.deployer/bin/../lib/jctools-core-2.1.2.jar:/usr/local/canal.deployer/bin/../lib/jcl-over-slf4j-1.7.12.jar:/usr/local/canal.deployer/bin/../lib/javax.annotation-api-1.3.2.jar:/usr/local/canal.deployer/bin/../lib/jackson-databind-2.9.6.jar:/usr/local/canal.deployer/bin/../lib/jackson-core-2.9.6.jar:/usr/local/canal.deployer/bin/../lib/jackson-annotations-2.9.0.jar:/usr/local/canal.deployer/bin/../lib/ibatis-sqlmap-2.3.4.726.jar:/usr/local/canal.deployer/bin/../lib/httpcore-4.4.3.jar:/usr/local/canal.deployer/bin/../lib/httpclient-4.5.1.jar:/usr/local/canal.deployer/bin/../lib/h2-1.4.196.jar:/usr/local/canal.deployer/bin/../lib/guava-18.0.jar:/usr/local/canal.deployer/bin/../lib/fastsql-2.0.0_preview_973.jar:/usr/local/canal.deployer/bin/../lib/fastjson-1.2.58.jar:/usr/local/canal.deployer/bin/../lib/druid-1.1.9.jar:/usr/local/canal.deployer/bin/../lib/disruptor-3.4.2.jar:/usr/local/canal.deployer/bin/../lib/commons-logging-1.1.3.jar:/usr/local/canal.deployer/bin/../lib/commons-lang3-3.4.jar:/usr/local/canal.deployer/bin/../lib/commons-lang-2.6.jar:/usr/local/canal.deployer/bin/../lib/commons-io-2.4.jar:/usr/local/canal.deployer/bin/../lib/commons-compress-1.9.jar:/usr/local/canal.deployer/bin/../lib/commons-codec-1.9.jar:/usr/local/canal.deployer/bin/../lib/commons-cli-1.2.jar:/usr/local/canal.deployer/bin/../lib/commons-beanutils-1.8.2.jar:/usr/local/canal.deployer/bin/../lib/canal.store-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.sink-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.server-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.protocol-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.prometheus-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.parse.driver-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.parse.dbsync-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.parse-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.meta-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.instance.spring-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.instance.manager-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.instance.core-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.filter-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.deployer-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/canal.common-1.1.4.jar:/usr/local/canal.deployer/bin/../lib/aviator-2.2.1.jar:/usr/local/canal.deployer/bin/../lib/aopalliance-1.0.jar:.:/usr/local/java/lib/dt.jar:/usr/local/java/lib/tools.jar:/usr/local/java/jre/lib:
cd to /usr/local/canal.deployer for continue

[1]+  完成                  ./bin/stop.sh && ./bin/startup.sh
[root@guangzhou canal.deployer]#

这里模拟php客户端请求canal，下载地址：https://github.com/xingwenge/canal-php，其他语言客户端可在https://github.com/xingwenge找到。

[root@guangzhou canal-php]# ls
assets  composer.json  composer.lock  index.php  php_errors.log  README.md  src  vendor
[root@guangzhou canal-php]# vim src/sample/client.php
<?php
namespace xingwengecanal_phpsample;

use xingwengecanal_phpCanalClient;
use xingwengecanal_phpCanalConnectorFactory;
use xingwengecanal_phpFmt;

require_once __DIR__. '/../../vendor/autoload.php';

ini_set('display_errors', 'On');
error_reporting(E_ALL);

try {
    $client = CanalConnectorFactory::createClient(CanalClient::TYPE_SOCKET_CLUE);
    # $client = CanalConnectorFactory::createClient(CanalClient::TYPE_SWOOLE);

    $client->connect("127.0.0.1", 11111);
    $client->checkValid();
    $client->subscribe("1", "test_canal_sync", ".*\..*"); //修改destinations配置
    # $client->subscribe("1001", "example", "db_name.tb_name"); # 设置过滤

    while (true) {
        $message = $client->get(100);
        if ($entries = $message->getEntries()) {
        foreach ($entries as $entry) {
                Fmt::println($entry);
            }
        }
        sleep(1);
    }

    $client->disConnect();
} catch (Exception $e) {
    echo $e->getMessage(), PHP_EOL;
}

#创建表结构
mysql> show create table test_canal_sync.abcG;
*************************** 1. row ***************************
       Table: abc
Create Table: CREATE TABLE `abc` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(11) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `len` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

ERROR:
No query specified

#开跑php客户端监听脚本
[root@guangzhou canal-php]# /usr/local/php72/bin/php src/sample/client.php

#同时向test_canal_sync.abc表录入新数据
mysql> insert into test_canal_sync.abc(name,age) values('testone',20,10);
Query OK, 1 row affected (0.00 sec)

#php客户端成功拿到新录入的数据
[root@guangzhou canal-php]# /usr/local/php72/bin/php src/sample/client.php
================> binlog[mysql-bin.000005 : 4082],name[test_canal_sync,abc], eventType: 1

id : 25  update= true
name : testone  update= true
age : 20  update= true
len : 10  update= true

所以到目前为止canal整个同步已完成，php这里可以将拿到的数据写入redis等第三方服务。

期间遇到一些麻烦，

1.最初设想canal和mysql都由docker创建，发现两容器正常互通。但拿不到数据，经过一段时间摸索没啥进展。

  后面尝试canal改用Github上的压缩包。

2.mysql的配置binlog配置一开始设置为：

binlog-do-db = test,test_canal_sync

后面发现多个db需要分开写:

binlog-do-db = test

binlog-do-db = test_canal_sync

 3.canal基于java开发的，java需占用较多内存，如出现启动失败，可以查看log信息，出现一下输出表示启动成功

[root@guangzhou canal.deployer]# tail -17 logs/test_canal_sync/test_canal_sync.log
2021-09-25 16:49:22.779 [Thread-5] INFO  c.a.otter.canal.instance.core.AbstractCanalInstance - stop CannalInstance for null-test_canal_sync
2021-09-25 16:49:23.253 [Thread-5] INFO  c.a.otter.canal.instance.core.AbstractCanalInstance - stop successful....
2021-09-25 16:49:40.175 [main] INFO  c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [canal.properties]
2021-09-25 16:49:40.190 [main] INFO  c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [test_canal_sync/instance.properties]
2021-09-25 16:49:40.370 [main] WARN  o.s.beans.GenericTypeAwarePropertyDescriptor - Invalid JavaBean property 'connectionCharset' being accessed! Ambiguous write methods found next to actually used [public void com.alibaba.otter.canal.parse.inbound.mysql.AbstractMysqlEventParser.setConnectionCharset(java.nio.charset.Charset)]: [public void com.alibaba.otter.canal.parse.inbound.mysql.AbstractMysqlEventParser.setConnectionCharset(java.lang.String)]
2021-09-25 16:49:40.385 [main] INFO  c.a.otter.canal.instance.spring.CanalInstanceWithSpring - start CannalInstance for 1-test_canal_sync
2021-09-25 16:49:40.396 [main] WARN  c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^.*..*$
2021-09-25 16:49:40.396 [main] WARN  c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table black filter :
2021-09-25 16:49:40.525 [destination = test_canal_sync , address = /172.16.1.2:3306 , EventParser] WARN  c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> begin to find start position, it will be long time for reset or first position
2021-09-25 16:49:40.561 [main] INFO  c.a.otter.canal.instance.core.AbstractCanalInstance - subscribe filter change to .*..*
2021-09-25 16:49:40.562 [main] WARN  c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^.*..*$
2021-09-25 16:49:40.562 [main] INFO  c.a.otter.canal.instance.core.AbstractCanalInstance - subscribe filter change to .*.abc.*
2021-09-25 16:49:40.562 [main] WARN  c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^.*.abc.*$
2021-09-25 16:49:40.562 [main] INFO  c.a.otter.canal.instance.core.AbstractCanalInstance - start successful....
2021-09-25 16:49:40.586 [destination = test_canal_sync , address = /172.16.1.2:3306 , EventParser] WARN  c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - prepare to find start position just last position
 {"identity":{"slaveId":-1,"sourceAddress":{"address":"172.16.1.2","port":3306}},"postion":{"gtid":"","included":false,"journalName":"mysql-bin.000003","position":5188,"serverId":1,"timestamp":1632552858000}}
2021-09-25 16:49:40.633 [destination = test_canal_sync , address = /172.16.1.2:3306 , EventParser] WARN  c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> find start position successfully, EntryPosition[included=false,journalName=mysql-bin.000003,position=5188,serverId=1,gtid=,timestamp=1632552858000] cost : 62ms , the next step is binlog dump