Mysql数据库数据实时监控之canal

「爱情、让人受尽委屈。」 2023-03-13 06:22 34阅读 0赞

目前遇到一个需求，是基于mysql某表数据得变动，同步捕获变动的数据，经分析，选择阿里巴巴的canal作为mysql实时监控的中间件。[canal github地址][canal github]

流程如下，抄袭的canal官网上的流程

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmZ1amlhbjE5OTk_size_16_color_FFFFFF_t_70]  
canal我们可以选择 server-client模式，这样我们的client,就可以选择自己喜欢的实现方式和业务逻辑去读取server监听到的mysql数据变化了。

**首先**，被监听的mysql需要开启binlog,也就是mysql数据的变化会写道一个二进制的log文件中。

定位到mysql的my.cnf配置文件：

vim etc/my.cnf

添加开启log\_bin

server-id = 1
    log_bin = /var/log/mysql-bin.log
    binlog_format = ROW

server-id为唯一即可；log\_bin为二进制log文件的保存地址；binlog\_forrmat=ROW为固定

重启mysql，可以在  
/var/log/下看到生成的日志文件

**其次**，下载canal的server端，[下载链接][Link 1]，下载canal.deployer-\*.tar.gz，我选择的是canal.deployer-1.1.2.tar.gz，下载并解压到目录下。

配置修改

vi conf/example/instance.properties

## mysql serverId
    canal.instance.mysql.slaveId = 1234
    #position info，需要改成自己的数据库信息
    canal.instance.master.address = 127.0.0.1:3306 
    canal.instance.master.journal.name = 
    canal.instance.master.position = 
    canal.instance.master.timestamp = 
    #canal.instance.standby.address = 
    #canal.instance.standby.journal.name =
    #canal.instance.standby.position = 
    #canal.instance.standby.timestamp = 
    #username/password，需要改成自己的数据库信息
    canal.instance.dbUsername = canal  
    canal.instance.dbPassword = canal
    canal.instance.defaultDatabaseName =
    canal.instance.connectionCharset = UTF-8
    #table regex
    canal.instance.filter.regex = .\*\\\\..\*

这里主要修改一下需要监听的数据库地址以及监听数据库中已创建的用户名及密码，这个用户信息需要在被监听数据库中创建，如下，创建了一个canal用户，并授权slave的权限

CREATE USER canal IDENTIFIED BY 'canal';  
    GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
    -- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
    FLUSH PRIVILEGES;

启动 canal server

sh bin/startup.sh

如果在`/canal/logs/example`下看到有example.log,打开如下所示，即启动成功

2020-05-12 14:21:46.488 [Thread-6] INFO  c.a.otter.canal.instance.core.AbstractCanalInstance - stop CannalInstance for null-example 
    2020-05-12 14:21:47.038 [Thread-6] INFO  c.a.otter.canal.instance.core.AbstractCanalInstance - stop successful....
    2020-05-12 14:21:54.472 [main] INFO  c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [canal.properties]
    2020-05-12 14:21:54.478 [main] INFO  c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [example/instance.properties]
    2020-05-12 14:21:54.763 [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.lang.String)]: [public void com.alibaba.otter.canal.parse.inbound.mysql.AbstractMysqlEventParser.setConnectionCharset(java.nio.charset.Charset)]
    2020-05-12 14:21:54.814 [main] INFO  c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [canal.properties]
    2020-05-12 14:21:54.815 [main] INFO  c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [example/instance.properties]
    2020-05-12 14:21:55.068 [main] ERROR com.alibaba.druid.pool.DruidDataSource - testWhileIdle is true, validationQuery not set
    2020-05-12 14:21:55.475 [main] INFO  c.a.otter.canal.instance.spring.CanalInstanceWithSpring - start CannalInstance for 1-example 
    2020-05-12 14:21:55.516 [main] INFO  c.a.otter.canal.instance.core.AbstractCanalInstance - start successful....
    2020-05-12 14:21:55.646 [destination = example , address = /127.0.0.1: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
    2020-05-12 14:21:55.647 [destination = example , address = /127.0.0.1:3306 , EventParser] WARN  c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - prepare to find start position just show master status
    2020-05-12 14:21:56.453 [destination = example , address = /127.0.0.1:3306 , EventParser] WARN  c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> find start position successfully, EntryPosition[included=false,journalName=mysql-bin.000001,position=62749,serverId=1,gtid=<null>,timestamp=1589262896000] cost : 793ms , the next step is binlog dump
    2020-05-12 14:22:30.986 [New I/O server worker #1-1] INFO  c.a.otter.canal.instance.core.AbstractCanalInstance - subscribe filter change to .*\..*
    2020-05-12 14:29:06.059 [New I/O server worker #1-2] INFO  c.a.otter.canal.instance.core.AbstractCanalInstance - subscribe filter change to .*\..*

注意该server启动后默认端口为11111

**然后**，配置client端来读取server监听到的数据变化。我这里是创建了一个springboot

pom.xml中依赖：

<dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>com.alibaba.otter</groupId>
                <artifactId>canal.client</artifactId>
                <version>1.1.0</version>
            </dependency>
        </dependencies>

写一个java类，main函数里写读取变化数据的逻辑

package com.hjsc.canalclient.client;
    
    import java.net.InetSocketAddress;
    import java.util.List;
    
    
    import com.alibaba.otter.canal.client.CanalConnectors;
    import com.alibaba.otter.canal.client.CanalConnector;
    import com.alibaba.otter.canal.common.utils.AddressUtils;
    import com.alibaba.otter.canal.protocol.Message;
    import com.alibaba.otter.canal.protocol.CanalEntry.Column;
    import com.alibaba.otter.canal.protocol.CanalEntry.Entry;
    import com.alibaba.otter.canal.protocol.CanalEntry.EntryType;
    import com.alibaba.otter.canal.protocol.CanalEntry.EventType;
    import com.alibaba.otter.canal.protocol.CanalEntry.RowChange;
    import com.alibaba.otter.canal.protocol.CanalEntry.RowData;
    
    public class CanalClientExample {
    
        public static void main(String args[]) {
            // 创建链接
            CanalConnector connector = CanalConnectors.newSingleConnector(new InetSocketAddress(AddressUtils.getHostIp(),
                    11111), "example", "", "");
            int batchSize = 1000;
            int emptyCount = 0;
            try {
                connector.connect();
                connector.subscribe(".*\\..*");
                connector.rollback();
                int totalEmptyCount = 120;
                while (emptyCount < totalEmptyCount) {
                    Message message = connector.getWithoutAck(batchSize); // 获取指定数量的数据
                    long batchId = message.getId();
                    int size = message.getEntries().size();
                    if (batchId == -1 || size == 0) {
                        emptyCount++;
                        System.out.println("empty count : " + emptyCount);
                        try {
                            Thread.sleep(1000);
                        } catch (InterruptedException e) {
                        }
                    } else {
                        emptyCount = 0;
                        // System.out.printf("message[batchId=%s,size=%s] \n", batchId, size);
                        printEntry(message.getEntries());
                    }
    
                    connector.ack(batchId); // 提交确认
                    // connector.rollback(batchId); // 处理失败, 回滚数据
                }
    
                System.out.println("empty too many times, exit");
            } finally {
                connector.disconnect();
            }
        }
    
        private static void printEntry(List<Entry> entrys) {
            for (Entry entry : entrys) {
                if (entry.getEntryType() == EntryType.TRANSACTIONBEGIN || entry.getEntryType() == EntryType.TRANSACTIONEND) {
                    continue;
                }
    
                RowChange rowChage = null;
                try {
                    rowChage = RowChange.parseFrom(entry.getStoreValue());
                } catch (Exception e) {
                    throw new RuntimeException("ERROR ## parser of eromanga-event has an error , data:" + entry.toString(),
                            e);
                }
    
                EventType eventType = rowChage.getEventType();
                System.out.println(String.format("================> binlog[%s:%s] , name[%s,%s] , eventType : %s",
                        entry.getHeader().getLogfileName(), entry.getHeader().getLogfileOffset(),
                        entry.getHeader().getSchemaName(), entry.getHeader().getTableName(),
                        eventType));
    
                for (RowData rowData : rowChage.getRowDatasList()) {
                    if (eventType == EventType.DELETE) {
                        printColumn(rowData.getBeforeColumnsList());
                    } else if (eventType == EventType.INSERT) {
                        printColumn(rowData.getAfterColumnsList());
                    } else {
                        System.out.println("-------> before");
                        printColumn(rowData.getBeforeColumnsList());
                        System.out.println("-------> after");
                        printColumn(rowData.getAfterColumnsList());
                    }
                }
            }
        }
    
        private static void printColumn(List<Column> columns) {
            for (Column column : columns) {
                System.out.println(column.getName() + " : " + column.getValue() + "    update=" + column.getUpdated());
            }
        }
    
    
    }

springboot 项目打成jar包后，我需要执行入口是这个类的main函数，所以在pom.xml中要添加main-class入口，如下：

<properties>
            <start-class>com.hjsc.canalclient.client.CanalClientExample</start-class>
            <java.version>1.8</java.version>
        </properties>

**最后**，数据库端的变化，就会在client端读取到了，实时监听搭建完毕。

[canal github]: https://github.com/alibaba/canal
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmZ1amlhbjE5OTk_size_16_color_FFFFFF_t_70]: /images/20230312/6f83ac4fb27540c3a11af92b39b35add.png
[Link 1]: https://github.com/alibaba/canal/releases