一、环境介绍
canal是阿里开源的中间件,主要用于同步mysql数据库变更。具体参见:https://github.com/alibaba/canal/releases
搭建环境:Centos6.5 64位
二、部署canal server
(参考:https://github.com/alibaba/canal/wiki/QuickStart)
1.下载canal server
https://github.com/alibaba/canal/releases/download/canal-1.1.4/canal.deployer-1.1.4.tar.gz
我下载的是canal.deployer-1.1.4.tar.gz,下载完成后解压缩:
mkdir /opt/canal
tar -zxvf canal.deployer-1.1.4.tar.gz -C /opt/canal
2.查看binlog相关数据库命令:
2.1 是否启用了日志
mysql>show variables like 'log_bin';
2.2 查看详细的日志配置信息
mysql>SHOW GLOBAL VARIABLES LIKE '%log%';
2.3 mysql数据存储目录
mysql>SHOW VARIABLES LIKE '%dir%';
2.4 查看mysql binlog模式
mysql> show variables like 'binlog_format';
2.5 获取binlog文件列表
mysql> show binary logs;
2.6 查看当前正在写入的binlog文件
mysql>show master status
2.7 查看指定binlog文件的内容
mysql>show binlog events in 'mysql-bin.000008';
3.开启binlog
如果log_bin关闭,需要在etc下面找到my.cnf,开启binlog:
log-bin=mysql-bin
binlog-format=ROW
server_id=1
然后重启mysql服务。
4.添加canal mysql数据库账号
CREATE USER canal IDENTIFIED BY 'canal';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
FLUSH PRIVILEGES;
5.配置canal实例,设置本地数据库信息
vi conf/example/instance.properties
#################################################
## mysql serverId , v1.0.26+ will autoGen
# canal.instance.mysql.slaveId=0
# enable gtid use true/false
canal.instance.gtidon=false
# position info
canal.instance.master.address=127.0.0.1:3306 #监听数据库的ip:port
canal.instance.master.journal.name=mysql-bin.000008 #2.6的binlog文件
canal.instance.master.position=170612 #2.6的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=canal #监听数据库的用户名
canal.instance.dbPassword=canal #监听数据库的密码
canal.instance.connectionCharset = UTF-8
canal.instance.defaultDatabaseName =testcanal #监听的schema
# enable druid Decrypt database password
canal.instance.enableDruid=false
#canal.instance.pwdPublicKey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALK4BUxdDltRRE5/zXpVEVPUgunvscYFtEip3pmLlhrWpacX7y7GCMo2/JM6LeHmiiNdH1FWgGCpUfircSwlWKUCAwEAAQ==
# table regex
canal.instance.filter.regex=.*\\..* #监听的schema下的所有表
# table black regex
canal.instance.filter.black.regex=
# table field filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)
#canal.instance.filter.field=test1.t_product:id/subject/keywords,test2.t_company:id/name/contact/ch
# table field black filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)
#canal.instance.filter.black.field=test1.t_product:subject/product_image,test2.t_company:id/name/contact/ch
# mq config
canal.mq.topic=example
# dynamic topic route by schema or table regex
#canal.mq.dynamicTopic=mytest1.user,mytest2\\..*,.*\\..*
canal.mq.partition=0
# hash partition config
#canal.mq.partitionsNum=3
#canal.mq.partitionHash=test.table:id^name,.*\\..*
#################################################
6.启动canal
sh bin/startup.sh
7.查看日志
cat /opt/canal/logs/canal/canal.log
2020-12-31 10:52:37.784 [main] INFO com.alibaba.otter.canal.deployer.CanalLauncher - ## set default uncaught exception handler
2020-12-31 10:52:37.820 [main] INFO com.alibaba.otter.canal.deployer.CanalLauncher - ## load canal configurations
2020-12-31 10:52:37.830 [main] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## start the canal server.
2020-12-31 10:52:37.858 [main] INFO com.alibaba.otter.canal.deployer.CanalController - ## start the canal server[xxx.xxx.xxx.xxx(xxx.xxx.xxx.xxx):11111]
2020-12-31 10:52:39.169 [main] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## the canal server is running now ......
cat /opt/canal/logs/example/example.log
2020-12-31 10:52:38.158 [main] INFO c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [canal.properties]
2020-12-31 10:52:38.162 [main] INFO c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [example/instance.properties]
2020-12-31 10:52:38.298 [main] WARN o.s.beans.GenericTypeAwarePropertyDescriptor - Invalid JavaBean property 'connectionCharset' being accessed! Ambiguous write methods found next to actually used [public void com.alibaba.otter.canal.parse.inbound.mysql.AbstractMysqlEventParser.setConnectionCharset(java.nio.charset.Charset)]: [public void com.alibaba.otter.canal.parse.inbound.mysql.AbstractMysqlEventParser.setConnectionCharset(java.lang.String)]
2020-12-31 10:52:38.334 [main] INFO c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [canal.properties]
2020-12-31 10:52:38.334 [main] INFO c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [example/instance.properties]
2020-12-31 10:52:39.117 [main] INFO c.a.otter.canal.instance.spring.CanalInstanceWithSpring - start CannalInstance for 1-example
2020-12-31 10:52:39.124 [main] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^.*\..*$
2020-12-31 10:52:39.124 [main] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table black filter :
2020-12-31 10:52:39.131 [main] INFO c.a.otter.canal.instance.core.AbstractCanalInstance - start successful....
三、canal lient demo
1.pom文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.cetiti</groupId>
<artifactId>demoCannal</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<name>demoCannal</name>
<description>DataRest project for Spring Boot</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<repositories>
<repository>
<id>maven.d.com</id>
<name>maven.d.com</name>
<url>http://maven.d.com/repository/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>com.alibaba.otter</groupId>
<artifactId>canal.client</artifactId>
<version>1.1.4</version>
</dependency>
<!--SpringBook所需的jar包 默认不需要写版本号 继承父类的版本号-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--Mysql依赖 springboot 2.2.0 好像默认需要一个数据库连接 不写会报错-->
<!-- MySQL 连接驱动依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.42</version>
</dependency>
</dependencies>
<!--spirng 打包成war包-->
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
2.客户端代码
import java.net.InetSocketAddress;
import java.util.List;
import com.alibaba.otter.canal.client.CanalConnector;
import com.alibaba.otter.canal.client.CanalConnectors;
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;
import com.cetiti.io.dao.DataDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
@Service
public class CanalTest {
@Autowired
private DataDao dataDao;
@Async
public void getMysqlMessage() throws Exception {
CanalConnector connector = CanalConnectors.newSingleConnector(new InetSocketAddress("10.0.40.105", 11111), "example", "canal", "canal");
connector.connect();
connector.subscribe(".*\\..*");
connector.rollback();
while (true) {
Message message = connector.getWithoutAck(100); // 获取指定数量的数据
long batchId = message.getId();
if (batchId == -1 || message.getEntries().isEmpty()) {
Thread.sleep(1000);
continue;
}
// System.out.println(message.getEntries());
printEntries(message.getEntries());
connector.ack(batchId);// 提交确认,消费成功,通知server删除数据
// connector.rollback(batchId);// 处理失败, 回滚数据,后续重新获取数据
}
}
private void printEntries(List<Entry> entries) throws Exception {
for (Entry entry : entries) {
if (entry.getEntryType() != EntryType.ROWDATA) {
continue;
}
RowChange rowChange = RowChange.parseFrom(entry.getStoreValue());
EventType eventType = rowChange.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 : rowChange.getRowDatasList()) {
getStringSql(rowData.getBeforeColumnsList(),entry,rowChange);
}
}
}
private void getStringSql(List<Column> columns,Entry entry,RowChange rowChange) {
switch (rowChange.getEventType()) {
case INSERT:
System.out.println("INSERT ");
for (Column column : columns) {
System.out.println(column.getName() + " : " + column.getValue() + " update=" + column.getUpdated());
}
break;
case UPDATE:
System.out.println("UPDATE ");
for (Column column : columns) {
System.out.println(column.getName() + " : " + column.getValue() + " update=" + column.getUpdated());
}
break;
case DELETE:
System.out.println("DELETE ");
StringBuffer sql= new StringBuffer("DELETE FROM "+ entry.getHeader().getSchemaName()+"."+entry.getHeader().getTableName()+" WHERE 1=1 ");
for (Column column : columns) {
System.out.println(column.getName() + " : " + column.getValue() + " update=" + column.getUpdated());
if(column.getMysqlType().equals("date")) {
sql.append(" and ").append(column.getName()).append("=").append("'").append(column.getValue()).append("'");
}
else if(column.getMysqlType().contains("varchar"))
{
sql.append(" and ").append(column.getName()).append("=").append("'").append(column.getValue()).append("'");
}
else {
sql.append(" and ").append(column.getName()).append("=").append(column.getValue());
}
}
System.out.println(sql.toString());
dataDao.method(sql.toString());
break;
default:
break;
}
}
}
3.建立数据库连接,进行delete等数据库操作
源库删除name=13
目标库删除name=13
四、注意事项
关闭防火墙
service iptables stop