mysql 如何进行benlog_canal 入门（基于docker）&& mysql install

第一步：安装MySQL：(可以参考:https://my.oschina.net/amhuman/blog/1941540)

命令：

sudo docker run -it -d --restart unless-stopped -p 13306:3306 -h mysql --name mysql -e MYSQL_ROOT_PASSWORD=root alanpeng/mysql5.7-replication-docker

查看mysql 容器的id

sudo docker inspect [容器id]

开启MySQL 的binlog

(如果不是在docker中 vi /etc/mysql/my.cnf)

vi   /etc/mysql/mysql.conf.d/mysqld.cnf

添加：

cd /etc/mysql

[mysqld]

log-bin=mysql-bin #添加这一行就ok

binlog-format=ROW #选择row模式

server_id=1 #配置mysql replaction需要定义，不能和canal的slaveId重复

#配置表名字不区分大小写

lower_case_table_names=1

解决  ＃sql 的模式//可以不操作

方案一：修改mysqld

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重新更新这个数据，取消这个only_full_group_by

set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';set @@session.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,

授权root 用户可以远程访问(运行sql)

grant all privileges on *.* to root@'%' identified by "root";

flush privileges;

第二步：安装canal

2.1 命令：

sudo docker run -it -d --restart unless-stopped -p 11111:11111 canal/canal-server

2.2 进入容器

sudo docker exec -it  [id]

2.3修改配置修改

应用参数：(参照：https://github.com/alibaba/canal/wiki/QuickStart)

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 = .\..

#################################################

第三步：写一个java springboot项目，消费benlogpom依赖：

com.alibaba.otter

canal.client

1.1.0

写一个Test 方法：

package com.icil.canal;

import java.net.InetSocketAddress;

import java.util.List;

import org.springframework.core.env.SystemEnvironmentPropertySource;

import com.alibaba.otter.canal.client.CanalConnector;

import com.alibaba.otter.canal.client.CanalConnectors;

import com.alibaba.otter.canal.common.utils.AddressUtils;

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;

import com.alibaba.otter.canal.protocol.Message;public classSimpleCanalClientExample {public static voidmain(String args[]) {//创建链接//CanalConnector connector = CanalConnectors.newSingleConnector(new InetSocketAddress(AddressUtils.getHostIp(),11111), "example", "", "");

CanalConnector connector = CanalConnectors.newSingleConnector(new InetSocketAddress(AddressUtils.getHostIp(),11111), "seatest", "", "");

System.err.println(AddressUtils.getHostIp());int batchSize = 1000;int emptyCount = 0;try{

connector.connect();

connector.subscribe(".*\\..*");

connector.rollback();int totalEmptyCount = 120;while (emptyCount

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(Listentrys) {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);

}//获取时间类型//update insert delete

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(Listcolumns) {for(Column column : columns) {

System.out.println(column.getName() + ":" + column.getValue() + "update=" +column.getUpdated());

}

}

}

View Code

配置多个instans : (复制 example ,删除 h2.mv.db 和 meta,dat ,避免出现找不到 初始位置问题)