Canal-保存mysql篇

Canal-保存mysql篇

一、java实现

先用java代码手写一遍，方便后续业务逻辑理解

1、maven配置：

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.24</version>
        </dependency>

        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.6</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba.otter</groupId>
            <artifactId>canal.client</artifactId>
            <version>1.1.4</version>
        </dependency>

2、application.properties配置

# 服务端口
server.port=10000
# 服务名
spring.application.name=canal-client

# 环境设置：dev、test、prod
spring.profiles.active=dev

# mysql数据库连接
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/cancal_mysql?serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=603409875

3、代码实现

• 首先声明一个队列来接收sql语句

  		//sql队列
      private Queue<String> SQL_QUEUE = new ConcurrentLinkedQueue<>();
  		@Resource
      private DataSource dataSource;
  

• 编写主流程方法

  // 创建链接
          CanalConnector connector = CanalConnectors.newSingleConnector(
                  new InetSocketAddress("127.0.0.1",
                  11111), "example", "", "");
          int batchSize = 1000;
          try {
              connector.connect();
              connector.subscribe("cancal_mysql\\..*");//订阅cancal_mysql库下的全表
              connector.rollback();
              try {
                  while (true) {
                      //尝试从master那边拉去数据batchSize条记录，有多少取多少
                      Message message = connector.getWithoutAck(batchSize);
                      long batchId = message.getId();
                      int size = message.getEntries().size();
                      if (batchId == -1 || size == 0) {
                          Thread.sleep(1000);
                      } else {
                          //数据处理
                          dataHandle(message.getEntries());
                      }
                      connector.ack(batchId);
  
                      //当队列里面堆积的sql大于一定数值的时候就模拟执行
                      if (SQL_QUEUE.size() >= 1) {
                          executeQueueSql();
                      }
                  }
              } catch (InterruptedException e) {
                  e.printStackTrace();
              } catch (InvalidProtocolBufferException e) {
                  e.printStackTrace();
              }
          } finally {
              connector.disconnect();
          }
  

• 数据处理方法

   /**
       * 数据处理
       *
       * @param entrys
       */
      private void dataHandle(List<CanalEntry.Entry> entrys) throws InvalidProtocolBufferException {
          for (CanalEntry.Entry entry : entrys) {
              if (CanalEntry.EntryType.ROWDATA == entry.getEntryType()) {
                  CanalEntry.RowChange rowChange = CanalEntry.RowChange.parseFrom(entry.getStoreValue());
                  CanalEntry.EventType eventType = rowChange.getEventType();
                  if (eventType == CanalEntry.EventType.DELETE) {
                      saveDeleteSql(entry);
                  } else if (eventType == CanalEntry.EventType.UPDATE) {
                      saveUpdateSql(entry);
                  } else if (eventType == CanalEntry.EventType.INSERT) {
                      saveInsertSql(entry);
                  }
              }
          }
      }
  

• 解析sql并保存sql语句方法

  • 删除(只做了简单的语句删除，下面类似)

    /**
         * 保存删除语句
         *
         * @param entry
         */
        private void saveDeleteSql(CanalEntry.Entry entry) {
            try {
                CanalEntry.RowChange rowChange = CanalEntry.RowChange.parseFrom(entry.getStoreValue());
                List<CanalEntry.RowData> rowDatasList = rowChange.getRowDatasList();
                for (CanalEntry.RowData rowData : rowDatasList) {
                    List<CanalEntry.Column> columnList = rowData.getBeforeColumnsList();
                    StringBuffer sql = new StringBuffer("delete from " + entry.getHeader().getTableName() + " where ");
                    for (CanalEntry.Column column : columnList) {
                        if (column.getIsKey()) {
                            //暂时只支持单一主键
                            if(column.getMysqlType().contains("varchar")){
                                sql.append(column.getName() + "='" + column.getValue()+"'");
                            }else {
                                sql.append(column.getName() + "=" + column.getValue());
                            }
                            break;
                        }
                    }
                    SQL_QUEUE.add(sql.toString());
                }
            } catch (InvalidProtocolBufferException e) {
                e.printStackTrace();
            }
        }
    

  • 新增

    /**
         * 保存插入语句
         *
         * @param entry
         */
        private void saveInsertSql(CanalEntry.Entry entry) {
            try {
                CanalEntry.RowChange rowChange = CanalEntry.RowChange.parseFrom(entry.getStoreValue());
                List<CanalEntry.RowData> rowDatasList = rowChange.getRowDatasList();
                for (CanalEntry.RowData rowData : rowDatasList) {
                    List<CanalEntry.Column> columnList = rowData.getAfterColumnsList();
                    StringBuffer sql = new StringBuffer("insert into " + entry.getHeader().getTableName() + " (");
                    for (int i = 0; i < columnList.size(); i++) {
                        sql.append(columnList.get(i).getName());
                        if (i != columnList.size() - 1) {
                            sql.append(",");
                        }
                    }
                    sql.append(") VALUES (");
                    for (int i = 0; i < columnList.size(); i++) {
                        sql.append("'" + columnList.get(i).getValue() + "'");
                        if (i != columnList.size() - 1) {
                            sql.append(",");
                        }
                    }
                    sql.append(")");
                    SQL_QUEUE.add(sql.toString());
                }
            } catch (InvalidProtocolBufferException e) {
                e.printStackTrace();
            }
        }
    

  • 修改

    /**
         * 保存更新语句
         *
         * @param entry
         */
        private void saveUpdateSql(CanalEntry.Entry entry) {
            try {
                CanalEntry.RowChange rowChange = CanalEntry.RowChange.parseFrom(entry.getStoreValue());
                List<CanalEntry.RowData> rowDatasList = rowChange.getRowDatasList();
                for (CanalEntry.RowData rowData : rowDatasList) {
                    List<CanalEntry.Column> newColumnList = rowData.getAfterColumnsList();
                    StringBuffer sql = new StringBuffer("update " + entry.getHeader().getTableName() + " set ");
                    for (int i = 0; i < newColumnList.size(); i++) {
                        sql.append(" " + newColumnList.get(i).getName()
                                + " = '" + newColumnList.get(i).getValue() + "'");
                        if (i != newColumnList.size() - 1) {
                            sql.append(",");
                        }
                    }
                    sql.append(" where ");
                    List<CanalEntry.Column> oldColumnList = rowData.getBeforeColumnsList();
                    for (CanalEntry.Column column : oldColumnList) {
                        if (column.getIsKey()) {
                            //暂时只支持单一主键
                            if(column.getMysqlType().contains("varchar")){
                                sql.append(column.getName() + "='" + column.getValue()+"'");
                            }else {
                                sql.append(column.getName() + "=" + column.getValue());
                            }
                            break;
                        }
                    }
                    SQL_QUEUE.add(sql.toString());
                }
            } catch (InvalidProtocolBufferException e) {
                e.printStackTrace();
            }
        }
    

• jdbc操作

   /**
       * 入库
       * @param sql
       */
      public void execute(String sql) {
          Connection con = null;
          try {
              if(null == sql) return;
              con = dataSource.getConnection();
              QueryRunner qr = new QueryRunner();
              int row = qr.update(con, sql);
              System.out.println("update: "+ row);
          } catch (SQLException e) {
              e.printStackTrace();
          } finally {
              DbUtils.closeQuietly(con);
          }
      }
  

4、效果

运行sql语句：

INSERT INTO `tb_commodity_info` ( `id`, `commodity_name`, `commodity_price`, `number`, `description` )
VALUES
	( '030acbd3b71011ecb9760242ac110005', '测试0001', '5.88', 11, '描述信息0001' );

执行结果：

库1:

库2:

二、使用canal.adapter来做实现

上面的代码初步可以实现一般情况下的数据迁移，接下来我们来实现一下给予adapter的方式

1、安装canal.adapter

• 先从github上将adapter下载下来

wget https://github.com/alibaba/canal/releases/download/canal-1.1.5/canal.adapter-1.1.5.tar.gz
wget https://github.com/alibaba/canal/releases/download/canal-1.1.5-alpha-2/canal.adapter-1.1.5-SNAPSHOT.tar.gz

• 解压

mkdir /tmp/canal_adapter
tar zxvf canal.adapter-1.1.5.tar.gz  -C /tmp/canal_adapter

• 修改配置文件conf/application.yml

server:
  port: 8081
spring:
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
    default-property-inclusion: non_null

canal.conf:
  mode: tcp #tcp kafka rocketMQ rabbitMQ
  flatMessage: true
  zookeeperHosts:
  syncBatchSize: 1000
  retries: 0
  timeout:
  accessKey:
  secretKey:
  consumerProperties:
    # canal tcp consumer
    canal.tcp.server.host: 127.0.0.1:11111
    canal.tcp.zookeeper.hosts:
    canal.tcp.batch.size: 500
    canal.tcp.username:
    canal.tcp.password:
    # kafka consumer
    #kafka.bootstrap.servers: 127.0.0.1:9092
    #kafka.enable.auto.commit: false
    #kafka.auto.commit.interval.ms: 1000
    #kafka.auto.offset.reset: latest
    #kafka.request.timeout.ms: 40000
#    kafka.session.timeout.ms: 30000
#    kafka.isolation.level: read_committed
#    kafka.max.poll.records: 1000
    # rocketMQ consumer
#    rocketmq.namespace:
#    rocketmq.namesrv.addr: 127.0.0.1:9876
#    rocketmq.batch.size: 1000
#    rocketmq.enable.message.trace: false
#    rocketmq.customized.trace.topic:
#    rocketmq.access.channel:
#    rocketmq.subscribe.filter:
    # rabbitMQ consumer
#    rabbitmq.host:
#    rabbitmq.virtual.host:
#    rabbitmq.username:
#    rabbitmq.password:
#    rabbitmq.resource.ownerId:

  srcDataSources:
    defaultDS:
      url: jdbc:mysql://witpool.tpddns.cn:12321/cancal_mysql?useUnicode=true
      username: root
      password: root
  canalAdapters:
  - instance: example # canal instance Name or mq topic name
    groups:
    - groupId: g1
      outerAdapters:
#      - name: logger
      - name: rdb
        key: mysql1
        properties:
          jdbc.driverClassName: com.mysql.jdbc.Driver
          jdbc.url: jdbc:mysql://127.0.0.1:3306/cancal_mysql?useUnicode=true
          jdbc.username: root
          jdbc.password: 603409875
#      - name: rdb
#        key: oracle1
#        properties:
#          jdbc.driverClassName: oracle.jdbc.OracleDriver
#          jdbc.url: jdbc:oracle:thin:@localhost:49161:XE
#          jdbc.username: mytest
#          jdbc.password: m121212
#      - name: rdb
#        key: postgres1
#        properties:
#          jdbc.driverClassName: org.postgresql.Driver
#          jdbc.url: jdbc:postgresql://localhost:5432/postgres
#          jdbc.username: postgres
#          jdbc.password: 121212
#          threads: 1
#          commitSize: 3000
#      - name: hbase
#        properties:
#          hbase.zookeeper.quorum: 127.0.0.1
#          hbase.zookeeper.property.clientPort: 2181
#          zookeeper.znode.parent: /hbase
#      - name: es
#        hosts: 127.0.0.1:9300 # 127.0.0.1:9200 for rest mode
#        properties:
#          mode: transport # or rest
#          # security.auth: test:123456 #  only used for rest mode
#          cluster.name: elasticsearch
#        - name: kudu
#          key: kudu
#          properties:
#            kudu.master.address: 127.0.0.1 # ',' split multi address

• 修改conf/rdb/tb_commodity_info.yml

  修改需要监听的表名为yml，若监听多个表可以生产多个yml

  mv mytest_user.yml tb_commodity_info.yml
  

  在修改配置文件

  dataSourceKey: defaultDS
  destination: example
  groupId: g1
  outerAdapterKey: mysql1
  concurrent: true
  dbMapping:
    database: cancal_mysql
    table: tb_commodity_info
    targetTable: tb_commodity_info
    targetPk:
      id: id
    mapAll: true
  #  targetColumns:
  #    id:
  #    name:
  #    role_id:
  #    c_time:
  #    test1:
  #  etlCondition: "where c_time>={}"
    commitBatch: 3000 # 批量提交的大小
  
  
  ## Mirror schema synchronize config
  #dataSourceKey: defaultDS
  #destination: example
  #groupId: g1
  #outerAdapterKey: mysql1
  #concurrent: true
  #dbMapping:
  #  mirrorDb: true
  #  database: mytest
  
  

• 注意：由于我存的是mysql8，所以需要在lib中加入8的mysql-connector-java-8.0.24.jar

• 执行启动命令

sh bin/startup.sh

不出意外的果然出意外了

由于出现了意外，所以我们吧源码拉下来跑一遍后，更换了上面的配置等问题，终于成功了

三、源码解析

1、拉取源码到本地

git clone https://github.com/alibaba/canal.git

2、打开项目

3、修改配置

adapter项目启动入口是client-adapter包下的launcher模块

修改配置文件：application.yml与rdb目录下的tb_commodity_info.yml

4、启动项目

启动类CanalAdapterApplication

5、记录问题

• spi找不到rdb扩展实现类

  

  通过源码追溯

  

  在启动时，依据配置需要加载rdb的spi，需要在之前加载logger，所以需要在配置中加入logger

  canalAdapters:
      - instance: example # canal instance Name or mq topic name
        groups:
          - groupId: g1
            outerAdapters:
            - name: logger   #解决此bug问题
              - name: rdb
                key: mysql1
                properties:
                  jdbc.driverClassName: com.mysql.jdbc.Driver
                  jdbc.url: jdbc:mysql://localhost:3306/cancal_mysql?serverTimezone=GMT%2B8
                  jdbc.username: root
                  jdbc.password: 603409875
  

• 解决了logger问题后还是有上面问题

这时候可以将client-adapter的maven进行install一遍，会出现不少缺jar包的方式，我执行时缺了俩个jar

将这俩个jar打包出来后，在install adapter就可以了

注意：若还不行，可以先将launcher package然后在执行

• mysql1的kay找不到bug

进入ConfigLoader&load(Properties envProperties)方法中，看到加载表映射关系，跟代码后发现，说没有映射文件

为了查这个问题，我又跑到spi加载文件的地方去查，查到加载的路径是：

canal/1.1.5/canal-canal-1.1.5/client-adapter/launcher/target/canal-adapter/plugin/client-adapter.rdb-1.1.5-jar-with-dependencies.jar

打开jar包，原来是之前没改的时候的jar，此时又去查询install了rdb的包，在重新pack一遍，终于可以运行了😢

• 执行主库sql又又出错了，java.lang.RuntimeException: java.lang.NullPointerException

  

  在RDB同步业务操作类RdbSyncService&insert(batchExecutor, config, dml)方法中，可以看到下面的数据是没问题的，都传过来了，那么问题出在什么地方呢？

  

  在接下来往下看，我们跟到SyncUtil.getColumnsMap(dbMapping, data)，这个方法是映射的字段，若没有字段映射，则表示全表映射，原来是我把配置文件配的有问题，以下这个字段问题，若未配置指定字段，则设置为true

  

• 终于成功了

源库：

目标库：

最后，请特别注意一个事：若启动没有日志，也没启动成功，那请注意是不是jdk版本导致的，我本地11就导致了这个问题，找了半天，可以借鉴上一个文章解决**canal初学**中的JDK版本太高解决方案

6、日志乱码问题

部署成功后打开adapter日志发现日志输出有乱码问题，需要在日志文件加入下面这段

<encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
			<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} %-5level %logger Line:%-3L - %msg%n</pattern>
			<charset>utf-8</charset>
		</encoder>

7、异常处理

2022-04-11 15:59:21.406 [destination = example , address = /192.168.1.122:3306 , EventParser] ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:example[java.io.IOException: Received error packet: errno = 1236, sqlstate = HY000 errmsg = Client requested master to start replication from position > file size
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:238)
at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$1.run(AbstractEventParser.java:262)
at java.base/java.lang.Thread.run(Thread.java:834)
]

解决：

先停止运行canal

cd conf/example/  #进入目录
rm -rf meta.dat #删除元节点文件

然后在进入到mysql中，在主库上通过"flush logs"命令重新生成信息binlog，然后使用"show master status"查询信息位点，重新使用“CHANGE MASTER TO MASTER_LOG_FILE=‘log-bin.00000xx’,MASTER_LOG_POS=xxx;”重新同步binlog

7、权限

#授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.* TO `canal`@`%`
FLUSH PRIVILEGES;
#查询权限
SHOW GRANTS FOR 'canal'@'%';
#删除权限
REVOKE SELECT ON *.* FROM 'canal'@'%';
FLUSH PRIVILEGES;

8、特殊语句问题

因为INSERT ... SELECT语句的结果 取决于行的顺序，SELECT并且不能始终保证此顺序，所以在记录INSERT ... SELECT ON DUPLICATE KEY UPDATE源和副本的语句时可能会出现 分歧。因此，INSERT ... SELECT ON DUPLICATE KEY UPDATE对于基于语句的复制， 语句被标记为不安全。此类语句在使用基于语句的模式时会在错误日志中产生警告，并在使用MIXED模式时使用基于行的格式写入二进制日志。INSERT ... ON DUPLICATE KEY UPDATE针对具有多个唯一键或主键的表的 语句也被标记为不安全

官网里面有一段关于on duplicate key update的描述:

因为INSERT … SELECT语句的结果 取决于行的顺序，SELECT并且不能始终保证此顺序，所以在记录INSERT … SELECT ON DUPLICATE KEY UPDATE源和副本的语句时可能会出现 分歧。因此，INSERT … SELECT ON DUPLICATE KEY UPDATE对于基于语句的复制， 语句被标记为不安全。此类语句在使用基于语句的模式时会在错误日志中产生警告，并在使用MIXED模式时使用基于行的格式写入二进制日志。INSERT … ON DUPLICATE KEY UPDATE针对具有多个唯一键或主键的表的 语句也被标记为不安全。（错误 #11765650、错误 #58637）