java实现通过canal和mysql同步

实验中需要到的工具

下载 canal.deployer-1.1.6
下载mysql 我用的5.7
java8

mysql的配置

# 打开binlog
log-bin=mysql-bin
# 选择ROW(行)模式
binlog-format=ROW
# 配置MySQL replaction需要定义，不要和canal的slaveId重复
server_id=1

在mysql中测试一下
查看日志开启状态

查看binlog日志文件列表

查看当前正在写入的binlong文件

然后在mysql中创建一个canal账户

-- 创建用户 用户名：canal 密码：Canal@123456
create user 'canal'@'%' identified by 'Canal@123456';
-- 授权 *.*表示所有库
grant SELECT, REPLICATION SLAVE, REPLICATION CLIENT on *.* to 'canal'@'%' identified by 'Canal@123456';

canal配置

先对canal配置
打开cananl.deployer-1.1.6下的conf目录

vim cananl.deployer-1.1.6/conf/canal.properties

主要对下面几项进行配置

canal.ip =127.0.0.1  #canal的地址因为我部署在本机，填写本机地址
canal.port = 11111 #端口
canal.destinations = example #这个后边会用到

然后对cananl.deployer-1.1.6目录下example配置

canal.instance.mysql.slaveId=1234 #这个id和mysql_id要不重复
canal.instance.master.address=127.0.0.1:3306 # mysql的地址
canal.instance.master.journal.name=mysql-bin.000001 #mysql的日志
canal.instance.master.position=154
canal.instance.dbUsername=canal #mysql创建的canal账户
canal.instance.dbPassword=Canal@123456
canal.instance.filter.regex= #这个标识对哪个表进行监视 不写代表所有

以上就是对canal的配置
启动canal
./startup.sh
查看日志

启动成功

java写代码 查看canal情况

package com.example.demo.util;

import com.alibaba.fastjson.JSONObject;
import com.alibaba.otter.canal.client.CanalConnector;
import com.alibaba.otter.canal.client.CanalConnectors;
import com.alibaba.otter.canal.protocol.CanalEntry;
import com.alibaba.otter.canal.protocol.Message;
import com.google.protobuf.ByteString;

import java.net.InetSocketAddress;
import java.util.List;

public class CanalUtils {
    private static   CanalConnector connector; //连接器
    private final static int BATCH_SIZE = 1000;
    private JSONObject before = new JSONObject();
    private JSONObject after = new JSONObject();
    private String eventType;
    private String tableName;
    public  void connect(String url,int port,String example,String username,String password)throws Exception{
        //TODO 连接canal
        this.connector=CanalConnectors.newSingleConnector(new InetSocketAddress(url,port),example,username,password);
        //TODO 发起连接
        this.connector.connect();
    }
    public void subscribe(String tableName){
        //TODO 订阅监视的表
        if (tableName==null){
            this.connector.subscribe();
        }else {
            this.connector.subscribe(tableName);
        }

    }
    public void getEntry(int clintNum) throws  Exception{
        this.getBefore().clear();
        this.getAfter().clear();

        Message message= this.connector.get(clintNum);
       List<CanalEntry.Entry> entries= message.getEntries();

       if (entries.size()!=0){
           for (CanalEntry.Entry entry : entries) {
               //TODO 获取表名
              tableName = entry.getHeader().getTableName();
                //TODO 获取类型
               CanalEntry.EntryType entryType = entry.getEntryType();
                //TODO 获取数据
               ByteString storeValue = entry.getStoreValue();
               if (CanalEntry.EntryType.ROWDATA.equals(entryType)) {
                   //TODO 序列化数据
                   CanalEntry.RowChange rowChange = CanalEntry.RowChange.parseFrom(storeValue);
                   //TODO 获取数据
                   CanalEntry.EventType  eventType = rowChange.getEventType();
                   this.eventType=eventType.toString();
                   // TODO 获取数据集
                   List<CanalEntry.RowData> rowDataList = rowChange.getRowDatasList();
                    NoSelectRowData(rowDataList);
//                   switch (eventType.toString()){
//                       case "INSERT":
//                           System.out.println("Insert");
//                           NoSelectRowData(rowDataList);
//                           break;
//                       case "DELETE":
//                           System.out.println("DELETE");
//                           NoSelectRowData(rowDataList);
//                           break;
//                       case "SELECT":
//                           System.out.println("SELECT");
//                           NoSelectRowData(rowDataList);
//                           break;
//                       case "UPDATE":
//                           System.out.println("UPDATE");
//                           NoSelectRowData(rowDataList);
//                           break;
//                   }

               }
           }
       }
    }

    /**
     * mysql数据库只执行 修改 删除 添加 操作
     * @param rowDataList
     */
    public void NoSelectRowData(List<CanalEntry.RowData> rowDataList){

        for (CanalEntry.RowData row : rowDataList) {
            List<CanalEntry.Column> beforeColumnsList = row.getBeforeColumnsList();
            beforeColumnsList.forEach(v -> {
                before.put(v.getName(), v.getValue());
            });
            List<CanalEntry.Column> afterColumnsList = row.getAfterColumnsList();
            afterColumnsList.forEach(v -> {
                after.put(v.getName(), v.getValue());
            });
        }
    }
    public String getEventType(){
        return this.eventType;
    }
    public JSONObject getBefore(){
        return this.before;
    }
    public JSONObject getAfter(){
        return  this.after;
    }

}

在man函数中查看

     CanalUtils canalUtils = new CanalUtils();
        canalUtils.connect("localhost",11111,"example","","");
        canalUtils.subscribe(null);
         while (true){
                    canalUtils.getEntry(100);
                    if (canalUtils.getBefore().size()>0||canalUtils.getAfter().size()>0){
                        System.out.println("事件是:"+canalUtils.getEventType());
                        System.out.println("之前数据:" + canalUtils.getBefore());
                        System.out.println("之后数据:" + canalUtils.getAfter());
                    }
         }

执行结果

然后拿到之前数据和之后数据的情况，写入es或者redis、mq等