基于Binlog和canal的异库同步

下载canal服务端的安装包：https://github.com/alibaba/canal
为 MySQL/MariaDB 开启 Binlog 功能 ：https://yq.aliyun.com/articles/655864

被监听端

1、开启binlog服务，修改vi /etc/my.cnf

log-bin：binlog日志存放位置
server-id：服务id

2、上传canal服务端安装包，并解压。配置过程如下：

修改vi conf/canal.properties

#################################################
######### 		common argument		############# 
#################################################
canal.id= 1
canal.ip=
canal.port= 11111
canal.zkServers=
# flush data to zk
canal.zookeeper.flush.period = 1000
# flush meta cursor/parse position to file
canal.file.data.dir = ${canal.conf.dir}
canal.file.flush.period = 1000
## memory store RingBuffer size, should be Math.pow(2,n)
canal.instance.memory.buffer.size = 16384
## memory store RingBuffer used memory unit size , default 1kb
canal.instance.memory.buffer.memunit = 1024 
## meory store gets mode used MEMSIZE or ITEMSIZE
canal.instance.memory.batch.mode = MEMSIZE

## detecing config
canal.instance.detecting.enable = false
#canal.instance.detecting.sql = insert into retl.xdual values(1,now()) on duplicate key update x=now()
canal.instance.detecting.sql = select 1
canal.instance.detecting.interval.time = 3
canal.instance.detecting.retry.threshold = 3
canal.instance.detecting.heartbeatHaEnable = false

# support maximum transaction size, more than the size of the transaction will be cut into multiple transactions delivery
canal.instance.transaction.size =  1024
# mysql fallback connected to new master should fallback times
canal.instance.fallbackIntervalInSeconds = 60

# network config
canal.instance.network.receiveBufferSize = 16384
canal.instance.network.sendBufferSize = 16384
canal.instance.network.soTimeout = 30

# binlog filter config
canal.instance.filter.query.dcl = false
canal.instance.filter.query.dml = false
canal.instance.filter.query.ddl = false
canal.instance.filter.table.error = false
canal.instance.filter.rows = false

# binlog format/image check
canal.instance.binlog.format = ROW,STATEMENT,MIXED 
canal.instance.binlog.image = FULL,MINIMAL,NOBLOB

# binlog ddl isolation
canal.instance.get.ddl.isolation = false

#################################################
######### 		destinations		############# 
#################################################
canal.destinations= example1,example2
# 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

canal.instance.global.mode = spring 
canal.instance.global.lazy = false
#canal.instance.global.manager.address = 127.0.0.1:1099
#canal.instance.global.spring.xml = classpath:spring/memory-instance.xml
canal.instance.global.spring.xml = classpath:spring/file-instance.xml
#canal.instance.global.spring.xml = classpath:spring/default-instance.xml

其中canal.destinations= example1,example2，改配置项为被监听实例，一个实例对应一个数据库，即：

4、配置实例

创建用户并授权：

授权 canal 链接 MySQL 账号具有作为 MySQL slave 的权限, 如果已有账户可直接 grant
CREATE USER canal IDENTIFIED BY 'canal';  GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
FLUSH PRIVILEGES;

修改 instance.properties文件（注意，每次修改配置，建议把meta.dat删除），配置如下：

其中，mysql配置项数据，需要通过mysql 系统配置查找：
show master status;

下面是一些有关于binlog的mysql命令：

binlog还原数据库
/usr/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000001 |mysql -u canal -p canalinfo

（1）直接删除
找到binlog所在目录，用rm binglog名 直接删除
例：rm mysql-bin.010
（2）通过mysql提供的工具来删除
删除之前可以先看一下purge的用法：help purge;
删除举例：
RESET MASTER;//删除所有binlog日志，新日志编号从头开始
PURGE MASTER LOGS TO 'mysql-bin.010';//删除mysql-bin.010之前所有日志
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';// 删除2003-04-02 22:46:26之前产生的所有日志

监听端

在pom里面添加：

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

直接粘代码：

    public static void main(String[] args) throws Exception {
        String dbname = "db_otl_canal";
        // 异库连接信息
        url = "jdbc:jyf://192.168.11.160:8066/" + dbname + "?useServerPstmts=true&user=" + dbname + "&password=g5IpeOcnHM";
        connection =BaseTest.getConnection(url);
        // 监听 实例名
        listenDataBase("example1",connection);
    }

    public static void listenDataBase(String destination,Connection connection) throws Exception {
        // 源库连接信息
        CanalConnector connector = CanalConnectors.newSingleConnector(
                new InetSocketAddress("192.168.11.203", 11111), destination, "", "");
        connector.connect();
        connector.subscribe(".*\\..*");
        connector.rollback();
        int batchSize = 5 * 1024;
        while (true) {
            Message message = connector.getWithoutAck(batchSize); // 获取指定数量的数据
            long batchId = message.getId();
            int size = message.getEntries().size();
            if (batchId == -1 || size == 0) {
                // try {
                // Thread.sleep(1000);
                // } catch (InterruptedException e) {
                // }
            } else {
                synchronizedData(message.getEntries(), connection);
            }
            connector.ack(batchId); // 提交确认
            // connector.rollback(batchId); // 处理失败, 回滚数据
        }
    }

    /**
     * 同步数据
     * @param entries
     * @throws Exception
     */
    private static void synchronizedData(List<CanalEntry.Entry> entries,Connection connection) throws Exception {
        for (CanalEntry.Entry entry : entries) {
            if (entry.getEntryType() != CanalEntry.EntryType.ROWDATA) {
                continue;
            }

            CanalEntry.RowChange rowChange = CanalEntry.RowChange.parseFrom(entry.getStoreValue());
            String tableName = entry.getHeader().getTableName();
            for (CanalEntry.RowData rowData : rowChange.getRowDatasList()) {
                String sql = getSql(rowChange.getEventType(),tableName,rowData,connection);
                System.out.println(sql);
                // TODO 执行sql语句
            }
        }
    }

    /**
     * 获取增删改的sql
     * @param eventType
     * @param tableName
     * @param rowData
     * @return
     */
    private static String getSql(CanalEntry.EventType eventType, String tableName, CanalEntry.RowData rowData,Connection connection){
        String sql = null;
        switch (eventType) {
            case INSERT:
                sql = getInsertSql(tableName,rowData.getAfterColumnsList());
                try {
                    BaseTest.insert(sql, connection);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                break;
            case UPDATE:
                sql = getUpdateSql(tableName,rowData.getAfterColumnsList());
                break;
            case DELETE:
                sql = getDeleteSql(tableName,rowData.getBeforeColumnsList());
                break;
            case QUERY:
                System.out.println(tableName);break;
            default:
                break;
        }

        return sql;
    }

    private static String getInsertSql(String tableName,List<CanalEntry.Column> columns){
        if(columns.size() == 0 || StringUtils.isBlank(tableName)){
            return null;
        }
        String keys = "";
        String values = "";
        for(int i=0;i<columns.size();i++){
            if(i != 0) {
                keys += ",";
                values += ",";
            }
            keys += columns.get(i).getName();
            values += getValue(columns.get(i));
        }
        String format = "INSERT INTO %s (%s) VALUES (%s)";
        return String.format(format,tableName,keys,values);
    }

    private static String getUpdateSql(String tableName,List<CanalEntry.Column> columns){
        if(columns.size() == 0 || StringUtils.isBlank(tableName)){
            return null;
        }
        String sets = "";
        String where = "";
        for(CanalEntry.Column column : columns){
            if(column.getIsKey()){
                where = column.getName() + "=" + getValue(column);
                continue;
            }
            if(!StringUtils.isBlank(sets)) {
                sets += ",";
            }
            sets += column.getName() + "=" + getValue(column);
        }
        String format = "UPDATE %s SET %s WHERE %s";
        return String.format(format,tableName,sets,where);
    }

    private static String getDeleteSql(String tableName,List<CanalEntry.Column> columns){
        if(columns.size() == 0 || StringUtils.isBlank(tableName)){
            return null;
        }
        String where = "";
        for(CanalEntry.Column column : columns){
            if(column.getIsKey()){
                where = column.getName() + "=" + getValue(column);
                continue;
            }
        }
        String format = "DELETE FROM %s WHERE %s";
        return String.format(format,tableName,where);
    }

    private static String getValue(CanalEntry.Column column){
        if(column.getIsNull()){
            return "null";
        }
        return String.format("'%s'",column.getValue());
    }