SpringBoot整合Canal实现数据同步

由于公司最近业务的发展，需要将部分业务数据实时同步至异地数据库进行备份。笔者基于公司大佬调研的canal实现的这块功能，同时也记录下来分享给各位童鞋。
canal是应阿里巴巴跨机房同步业务的需求而提出的，canal基于数据库的日志分析，获取变更进行增量订阅&消费的业务。无论是canal实验需要还是为了实现数据同步，主从同步和恢复，都是需要开启mysql-binlog日志。

canal工作原理

canal模拟mysql slave的交互协议，伪装自己为mysql salve，向mysql master发送dump协议，mysql master收到dump请求，开始推送binary log给slave(即canal)

一、配置mysql环境

在这一步，笔者认为大家已经安装了mysql，如果未安装，请先安装mysql。检查binlog功能是否开启，如果显示状态为OFF表示该功能未开启，需要开启binlog功能

1. 查询mysql binlog状态

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)

2. 修改 mysql 的配置文件 my.cnf

vim /etc/my.cnf， 追加如下内容：

log-bin=mysql-bin #binlog文件名
binlog_format=ROW #选择row模式
server_id=1 #mysql实例id,不能和canal的slaveId重复

3. 重启 mysql

 service mysql restart

4. 登录 mysql 客户端，查看 log_bin 变量，binlog功能已经开启成功。

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

二、Linux下载安装Canal服务

下载地址：https://github.com/alibaba/canal/releases

1. 下载之后，放到目录中，解压文件

cd /usr/local/
canal.deployer-1.1.4.tar.gz
tar zxvf canal.deployer-1.1.4.tar.gz

2. 修改配置文件
   vi conf/example/instance.properties

#需要改成自己的数据库信息
canal.instance.master.address=127.0.1:3306
#需要改成自己的数据库用户名与密码
canal.instance.dbUsername=root
canal.instance.dbPassword=
#需要改成同步的数据库表规则
  #1、同步所有的表
canal.instance.filter.regex=.*\\..*
  #2、需要同步的那个库中的那个表
#canal.instance.filter.regex=guli_ucenter.ucenter_member

3. 进入bin目录下启动

./startup.sh

4. 查看 instance 的日志，启动成功
   

三、初步监听实验

1. 引入相关依赖

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

<dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	<optional>true</optional>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-test</artifactId>
	<scope>test</scope>
</dependency>

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

<dependency>
	<groupId>log4j</groupId>
	<artifactId>log4j</artifactId>
	<version>1.2.17</version>
</dependency>

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
</dependency>

<dependency>
	<groupId>commons-dbutils</groupId>
	<artifactId>commons-dbutils</artifactId>
	<version>1.7</version>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

2、修改YML配置文件

server:
  port: 8899
# 服务名
spring:
  application:
    name: byh-data-synchronization
  datasource:
    #配置hikari连接池
    url: jdbc:mysql://xxxxx1:3306/test01?useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: root
    driver-class-name: com.mysql.jdbc.Driver
    hikari:
      minimum-idle: 4
      maximum-pool-size: 16
      connection-timeout: 10000
      idle-timeout: 30000
      connection-init-sql: set names utf8mb4
  	
db:
  ip: xxxxxxxx  #canal所在服务器
canal:
  name: example

tables: canal.test //监听的数据库表

3、创建Canal配置类自动监听的代码

• 启动服务时候自动读取Canal服务器ip、监听的数据库表等

@Component
@Slf4j
public class EventConfig implements ApplicationListener<ApplicationStartedEvent> {
    @Override
    public void onApplicationEvent(ApplicationStartedEvent event) {
        EnvironmentAlone.createInstance(event.getApplicationContext().getEnvironment());
        String dbIp =EnvironmentAlone.getInstance().getEnvironmentParam("db.ip");
        String tables =EnvironmentAlone.getInstance().getEnvironmentParam("tables");
        String canalName =EnvironmentAlone.getInstance().getEnvironmentParam("canal.name");
        CanalUtils.getInstance().attachChangeHandler(ChangeHandleManager.getChangeHandler()).connServer(dbIp,tables,canalName);
    }
}

public class EnvironmentAlone {
    private static  EnvironmentAlone environmentAlone;
    private ConfigurableEnvironment environment;
    private Map<String, String> propertyMap;

    private EnvironmentAlone(ConfigurableEnvironment env) {
        this.environment = env;
    }

    private EnvironmentAlone(Map<String, String> propertyMap) {
        this.propertyMap = propertyMap;
    }

    public static void createInstance(ConfigurableEnvironment env) {
        synchronized (EnvironmentAlone.class) {
            environmentAlone = new EnvironmentAlone(env);
        }
    }

    public static void createInstance(Map<String, String> propertyMap) {
        synchronized (EnvironmentAlone.class) {
            environmentAlone = new EnvironmentAlone(propertyMap);
        }
    }

    public static EnvironmentAlone getInstance() {
        if (environmentAlone != null) {
            return environmentAlone;
        }

        throw new RuntimeException("Environment isn't created");
    }

    public String getEnvironmentParam(String paramName) {
        if(environment != null) {
            return this.environment.getProperty(paramName);
        }
        return propertyMap.get(paramName);
    }
}

• 客户端连接canal，并实时监听

public class CanalUtils {

    private static List<IChangeHandler> changeHandlers;

    private static volatile CanalUtils instance;

    public CanalUtils attachChangeHandler(List<IChangeHandler> handlers){changeHandlers = handlers; return this;}

    public static CanalUtils getInstance(){
        if(instance == null){
            synchronized (CanalUtils.class){
                if(instance == null){
                    instance = new CanalUtils();
                }
            }
        }
        return instance;
    }

    public void connServer(String dataBaseIp,String tables,String canalName) {
        CanalConnector connector = CanalConnectors.newSingleConnector(new InetSocketAddress(dataBaseIp, 11111),
                canalName, "", "");
        int batchSize = 1000;
        try {
            connector.connect();
            connector.subscribe(tables);
            connector.rollback();
            while (true) {

                Long batchId = null;
                try {
                    // 获取指定数量的数据
                    Message message = connector.getWithoutAck(batchSize);
                    batchId = message.getId();
                    if (!(batchId == -1 || message.getEntries().size() == 0)) {
                        doSync(message.getEntries());
                    }
                    // 提交确认
                    connector.ack(batchId);
                }catch (Exception e){
                    e.printStackTrace();
                    connector.ack(batchId);
                }

            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            connector.disconnect();
        }
    }

    private void doSync(@NotNull List<CanalEntry.Entry> entries) {
        if(!CollectionUtils.isEmpty(changeHandlers)){
            for (IChangeHandler changeHandler : changeHandlers) {
                changeHandler.handleChange(entries);
            }
        }
    }
}

• 最后一步，处理canal监听到的数据

public interface IChangeHandler {

    void handleChange(List<CanalEntry.Entry> entryList);
}

public interface ITableChangeHandler extends IChangeHandler {

    Logger logger = Logger.getLogger(ITableChangeHandler.class);

    @Override
    default void handleChange(@NotNull List<CanalEntry.Entry> entryList) {
        for (CanalEntry.Entry entry : entryList) {
            if (entry.getEntryType() == CanalEntry.EntryType.TRANSACTIONBEGIN || entry.getEntryType() == CanalEntry.EntryType.TRANSACTIONEND) {
                continue;
            }
            CanalEntry.RowChange rowChange = null;
            try {
                rowChange = CanalEntry.RowChange.parseFrom(entry.getStoreValue());
            } catch (Exception e) {
                logger.error("## ERROR ##, data : " + entry.toString(), e);
            }

            CanalEntry.EventType eventType = rowChange.getEventType();
            logger.info(String.format("================> binlog[%s:%s] , name[%s,%s] , eventType : %s",
                    entry.getHeader().getLogfileName(), entry.getHeader().getLogfileOffset(),
                    entry.getHeader().getSchemaName(), entry.getHeader().getTableName(),
                    eventType));

            for (CanalEntry.RowData rowData : rowChange.getRowDatasList()) {
                if (eventType == CanalEntry.EventType.DELETE) {
                    delete(rowData.getBeforeColumnsList(),entry.getHeader().getSchemaName(), entry.getHeader().getTableName());
                } else if (eventType == CanalEntry.EventType.INSERT) {
                    insert(rowData.getAfterColumnsList(),entry.getHeader().getSchemaName(), entry.getHeader().getTableName());
                } else if (eventType == CanalEntry.EventType.UPDATE) {
                    update(rowData.getBeforeColumnsList(),rowData.getAfterColumnsList(),entry.getHeader().getSchemaName(), entry.getHeader().getTableName());
                }
            }
        }
    }

    void delete(List<CanalEntry.Column> beforeList, String db, String table);
    void insert(List<CanalEntry.Column> afterList, String db, String table);
    void update(List<CanalEntry.Column> beforeList, List<CanalEntry.Column> afterList, String db, String table);

}

@Slf4j
public class ChangeHandleManager {

    public static List<IChangeHandler> list = new ArrayList<>();

    static {
        Map<String, ITableChangeHandler> result = SpringUtils.getApplicationContext().getBeansOfType(ITableChangeHandler.class);
        try {
            for (Map.Entry<String, ITableChangeHandler> entry : result.entrySet()) {
                registerChangeHandler(SpringUtils.getBean(entry.getValue().getClass()));
            }
        } catch (Exception e) {
            log.error("ChangeHandleManager fail ! e=", e);
        }
    }

    public static List<IChangeHandler> getChangeHandler() {
        return list;
    }

    private static void registerChangeHandler(IChangeHandler changeHandler) {
        if (changeHandler != null) {
            list.add(changeHandler);
        }
    }
}

@Slf4j
@Component
public class DataSync implements ITableChangeHandler {

    @Resource
    private DataSource dataSource;

    @Override
    public void delete(List<CanalEntry.Column> beforeList, String db, String table) {


        StringBuffer sql = new StringBuffer("delete from " + table + " where ");
        for (CanalEntry.Column column : beforeList) {
            if (column.getIsKey()) {
                sql.append(column.getName() + "=" + column.getValue());
                break;
            }
        }

        this.execute(sql.toString());
    }

    @Override
    public void insert(List<CanalEntry.Column> afterList, String db, String table) {

        StringBuffer sql = new StringBuffer("insert into " + table + " (");
        for (int i = 0; i < afterList.size(); i++) {
            sql.append(afterList.get(i).getName());
            if (i != afterList.size() - 1) {
                sql.append(",");
            }
        }
        sql.append(") VALUES (");
        for (int i = 0; i < afterList.size(); i++) {
            sql.append("'" + afterList.get(i).getValue() + "'");
            if (i != afterList.size() - 1) {
                sql.append(",");
            }
        }
        sql.append(")");
        this.execute(sql.toString());

    }

    @Override
    public void update(List<CanalEntry.Column> beforeList, List<CanalEntry.Column> afterList, String db, String table) {
        StringBuffer sql = new StringBuffer("update " + table + " set ");

        for (int i = 0; i < afterList.size(); i++) {
            sql.append(" " + afterList.get(i).getName()
                    + " = '" + afterList.get(i).getValue() + "'");
            if (i != afterList.size() - 1) {
                sql.append(",");
            }
        }

        sql.append(" where ");
        for (CanalEntry.Column column : beforeList) {
            if (column.getIsKey()) {
                sql.append(column.getName() + "=" + column.getValue());
                break;
            }
        }
        this.execute(sql.toString());
    }

    public void execute(String sql) {
        Connection con = null;
        try {
            if(null == sql) return;
            con = dataSource.getConnection();
            QueryRunner qr = new QueryRunner();
            qr.execute(con, sql, new ArrayListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(con);
        }
    }
}

四、总结

通过上文的学习，再经过自己的实践，相信读者就轻松可以掌握canal同步数据。如有问题，也积极欢迎和笔者交流。