canal+Java实现mysql数据库数据变化的监听

一 开启mysql数据库bin_log

打开数据库my.ini配置文件添加以下代码重启mysql

log_bin=mysql-bin
binlog-format=ROW
server-id=1

在https://github.com/下载Canla

deployer部署包，canal是源码包
解压打开conf/example/instance.properties配置文件

# position info
# 设置要监听的mysql服务器地址
canal.instance.master.address=127.0.0.1:3306
canal.instance.master.journal.name=
canal.instance.master.position=
canal.instance.master.timestamp=
canal.instance.master.gtid=

# rds oss binlog
canal.instance.rds.accesskey=
canal.instance.rds.secretkey=
canal.instance.rds.instanceId=

# table meta tsdb info
canal.instance.tsdb.enable=true
#canal.instance.tsdb.url=jdbc:mysql://127.0.0.1:3306/canal_tsdb
#canal.instance.tsdb.dbUsername=canal
#canal.instance.tsdb.dbPassword=canal

#canal.instance.standby.address =
#canal.instance.standby.journal.name =
#canal.instance.standby.position = 
#canal.instance.standby.timestamp =
#canal.instance.standby.gtid=

# username/password
#连接数据库的用户名和密码
canal.instance.dbUsername=root
canal.instance.dbPassword=root
canal.instance.connectionCharset=UTF-8

# table regex
#监听服务器的哪些数据库和哪些表
canal.instance.filter.regex=.*\\..*

canal返回的数据对象

​

Entry=====> RowChange=====> Column
 
​
 
Entry
    Header
        logfileName [binlog文件名]
        logfileOffset [binlog position]
        executeTime [发生的变更]
        schemaName 
        tableName
        eventType [insert/update/delete类型]
    entryType   [事务头BEGIN/事务尾END/数据ROWDATA]
    storeValue  [byte数据,可展开，对应的类型为RowChange]    
 
 
RowChange
    isDdl       [是否是ddl变更操作，比如create table/drop table]
    sql     [具体的ddl sql]
    rowDatas    [具体insert/update/delete的变更数据，可为多条，1个binlog event事件可对应多条变更，比如批处理]
        beforeColumns [Column类型的数组]
        afterColumns [Column类型的数组]  
 
    
Column 
    index       
    sqlType     [jdbc type]
    name        [column name]
    isKey       [是否为主键]
    updated     [是否发生过变更]
    isNull      [值是否为null]

————————————————

创建mavne项目在pom.xml文件里添加

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

连接服务器代码

public void canalClient(){
	
	/**
	 * 连接canal服务器
	 */
	CanalConnector conn=CanalConnectors.newSingleConnector(new InetSocketAddress("127.0.0.1", 11111),"example","","");
	
	int current=0;
	
	int sumEnd=120;
	
	//一次获取多少数据
	int totalnum=1000;
	
	try {
		/**
		 * 与canalServer建立连接
		 */
		conn.connect();
		
		/**
		 * 拦截数据库表
		 */
		conn.subscribe("kylwsp.user");
		
		conn.rollback();
		
		
		while(current<totalnum){
			
			/**
			 * 获取返回得消息
			 */
			Message message = conn.getWithoutAck(totalnum);
			
			/**
			 * 获取该批信息得批号
			 */
			long id = message.getId();
			
			/**
			 * 获取数据变化的数据集合
			 */
			List<Entry> entries = message.getEntries();
			
			int size = entries.size();
			
			if(id==-1 || size==0){
				
				current++;
				
				System.out.println("current"+current+"\n");
				try {
					Thread.sleep(5000);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
			}else{
				current=0;
				List<ResultData> entryToData = HanderToData.entryToData(entries);
				System.out.println(JSON.toJSONString(entryToData));
			}
			/**
			 * 提交
			 */
			conn.ack(id);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		conn.disconnect();
	}
	
}


public static List<ResultData> entryToData(List<Entry> entrys){
	List<ResultData> resultDatas=new ArrayList<ResultData>();
	for (Entry entry : entrys) {
		/**
		 * 拦截事务头和事务尾的数据不参加解析
		 */
		if(entry.getEntryType()==EntryType.TRANSACTIONBEGIN || entry.getEntryType()==EntryType.TRANSACTIONEND){
			continue;
		}
		
		/**
		 * 获取数据变化的表名
		 */
		String tableName = entry.getHeader().getTableName();
		
		/**
		 * 获取数据变化的类型(insert,update,delete)
		 */
		EventType eventType = entry.getHeader().getEventType();
		ResultData doChangeData =null;
		
		try {
			/**
			 * 获取具体变化的行
			 */
			RowChange parseFrom = RowChange.parseFrom(entry.getStoreValue());
			
			/**
			 * 获取具体变化的行集合
			 */
			List<RowData> rowDatasList = parseFrom.getRowDatasList();
			
			/**
			 * 遍历处理每行数据
			 */
			for (RowData rowData : rowDatasList) {
				
				if(eventType==EventType.DELETE){
					/**
					 * 处理删除数据
					 */
					 doChangeData = doChangeData(tableName,"delete",rowData.getBeforeColumnsList());
				}else if(eventType==EventType.INSERT){
					
					/**
					 * 处理新增的数据
					 */
					 doChangeData = doChangeData(tableName,"insert",rowData.getAfterColumnsList());
				}else{
					/**
					 * 处理更新的数据
					 */
					doChangeData=doChangeData(tableName,"update",rowData.getBeforeColumnsList());
				}
				resultDatas.add(doChangeData);
			}
			
			
		} catch (InvalidProtocolBufferException e) {
			e.printStackTrace();
		}
		
	}
	return resultDatas;
}

/**
 * 
 * 方法概述: 处理变化的数据
 * @param tableName 表名
 * @param evenType 数据操作类型
 * @param columns 数据列集合
 * @date: 下午4:40:37
 * @author: wl
 * @version
 * @returnvoid
 * @throws
 */
public static ResultData doChangeData(String tableName,String evenType,List<Column> columns){
	Class<?> clazz=null;
	String className="com.baidu.entity."+StringUtil.firstToUp(tableName);
	ResultData resultData=new ResultData();
	try {
		clazz=Class.forName(className);
		Object object = clazz.newInstance();
		Field[] fields = clazz.getDeclaredFields();
		
		for (Field field : fields) {
			for (Column column : columns) {
				if(StringUtil.columnToClass(column.getName()).equals(field.getName())){
					field.setAccessible(true);
					
					if(column.getSqlType()==12||column.getSqlType()==1||column.getSqlType()==-1){
						field.set(object, column.getValue());
					}
					if(column.getSqlType()==4||column.getSqlType()==5||column.getSqlType()==-6){
						field.set(object, Integer.parseInt(column.getValue()));
					}
					field.setAccessible(false);
				}
			}
		}
		resultData.setClassName(className);
		resultData.setObj(object);
		resultData.setOperate(evenType);
	} catch (Exception e) {
		e.printStackTrace();
	}
	return resultData;
}