开启mysql bin-log
[mysqld]
log-bin=mysql-bin # 开启 binlog
binlog-format=ROW # 选择 ROW 模式
server_id=1 # 配置 MySQL replaction 需要定义，不要和 canal 的 slaveId 重复
创建canal账号
CREATE USER canal IDENTIFIED BY 'canal';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
FLUSH PRIVILEGES;
下载
下载最新版deployer和adapter包（支持jdk8）
https://github.com/alibaba/canal/releases?page=1
配置deployer
conf/canal.properties不用动
编辑conf/example/instance.properties
这里改为源mysql地址
这里配置要监听的库
启动deployer：bin/startup.sh
监控日志：tail -f /home/dev/canal/deployer/logs/canal/canal.log
2022-10-20 15:18:33.831 [canal-instance-scan-0] INFO com.alibaba.otter.canal.deployer.CanalController - auto notify start example successful.
2022-10-20 15:18:33.831 [canal-instance-scan-0] INFO com.alibaba.otter.canal.deployer.CanalController - auto notify reload example successful.
2022-10-20 15:18:45.309 [Thread-6] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## stop the canal server
2022-10-20 15:18:45.884 [Thread-6] INFO com.alibaba.otter.canal.deployer.CanalController - ## stop the canal server[172.26.0.1(172.26.0.1):11111]
2022-10-20 15:18:45.909 [Thread-6] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## canal server is down.
2022-10-20 15:18:49.796 [main] INFO com.alibaba.otter.canal.deployer.CanalLauncher - ## set default uncaught exception handler
2022-10-20 15:18:49.801 [main] INFO com.alibaba.otter.canal.deployer.CanalLauncher - ## load canal configurations
2022-10-20 15:18:49.807 [main] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## start the canal server.
2022-10-20 15:18:49.838 [main] INFO com.alibaba.otter.canal.deployer.CanalController - ## start the canal server[172.26.0.1(172.26.0.1):11111]
2022-10-20 15:18:51.344 [main] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## the canal server is running now ......
配置adapter
编辑conf/application.yml
在defaultDS里配置源mysql，properties里配置目标mysql
编辑conf/rdb/mytest_user.yml
database为源库，table为源表，targetTable为目标表
如果目标表是联合主键，在targetPK下写多个即可，但是要注意，源表中要有与之映射的字段。
如果使用的是mysql8，启动报错，将lib目录下的mysql jar包替换为高版本即可
https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.29
替换后将权限改为：rwsrwsrwt
启动adapter：bin/startup.sh
监控日志：tail -f /home/dev/canal/adapter/logs/adapter/adapter.log
2022-10-20 15:30:02.064 [main] INFO c.a.o.canal.adapter.launcher.loader.CanalAdapterLoader - Start adapter for canal-client mq topic: example-g1 succeed
2022-10-20 15:30:02.064 [Thread-4] INFO c.a.otter.canal.adapter.launcher.loader.AdapterProcessor - =============> Start to connect destination: example <=============
2022-10-20 15:30:02.064 [main] INFO c.a.o.canal.adapter.launcher.loader.CanalAdapterService - ## the canal client adapters are running now ......
2022-10-20 15:30:02.070 [main] INFO org.apache.coyote.http11.Http11NioProtocol - Starting ProtocolHandler ["http-nio-8081"]
2022-10-20 15:30:02.073 [main] INFO org.apache.tomcat.util.net.NioSelectorPool - Using a shared selector for servlet write/read
2022-10-20 15:30:02.090 [main] INFO o.s.boot.web.embedded.tomcat.TomcatWebServer - Tomcat started on port(s): 8081 (http) with context path ''
2022-10-20 15:30:02.092 [main] INFO c.a.otter.canal.adapter.launcher.CanalAdapterApplication - Started CanalAdapterApplication in 2.491 seconds (JVM running for 2.859)
2022-10-20 15:30:02.112 [Thread-4] INFO c.a.otter.canal.adapter.launcher.loader.AdapterProcessor - =============> Subscribe destination: example succeed <=============
如果报错找不到库，可以执行如下sql
-- MySQL dump 10.13 Distrib 5.7.38, for Linux (x86_64)
--
-- Host: 192.168.55.127 Database: canal_manager
-- ------------------------------------------------------
-- Server version 8.0.31-0ubuntu0.20.04.1
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `canal_adapter_config`
--
DROP TABLE IF EXISTS `canal_adapter_config`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `canal_adapter_config` (
`id` bigint NOT NULL AUTO_INCREMENT,
`category` varchar(45) NOT NULL,
`name` varchar(45) NOT NULL,
`status` varchar(45) DEFAULT NULL,
`content` text NOT NULL,
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `canal_adapter_config`
--
LOCK TABLES `canal_adapter_config` WRITE;
/*!40000 ALTER TABLE `canal_adapter_config` DISABLE KEYS */;
/*!40000 ALTER TABLE `canal_adapter_config` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `canal_cluster`
--
DROP TABLE IF EXISTS `canal_cluster`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `canal_cluster` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(63) NOT NULL,
`zk_hosts` varchar(255) NOT NULL,
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `canal_cluster`
--
LOCK TABLES `canal_cluster` WRITE;
/*!40000 ALTER TABLE `canal_cluster` DISABLE KEYS */;
/*!40000 ALTER TABLE `canal_cluster` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `canal_config`
--
DROP TABLE IF EXISTS `canal_config`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `canal_config` (
`id` bigint NOT NULL AUTO_INCREMENT,
`cluster_id` bigint DEFAULT NULL,
`server_id` bigint DEFAULT NULL,
`name` varchar(45) NOT NULL,
`status` varchar(45) DEFAULT NULL,
`content` text NOT NULL,
`content_md5` varchar(128) NOT NULL,
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `sid_UNIQUE` (`server_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `canal_config`
--
LOCK TABLES `canal_config` WRITE;
/*!40000 ALTER TABLE `canal_config` DISABLE KEYS */;
/*!40000 ALTER TABLE `canal_config` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `canal_instance_config`
--
DROP TABLE IF EXISTS `canal_instance_config`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `canal_instance_config` (
`id` bigint NOT NULL AUTO_INCREMENT,
`cluster_id` bigint DEFAULT NULL,
`server_id` bigint DEFAULT NULL,
`name` varchar(45) NOT NULL,
`status` varchar(45) DEFAULT NULL,
`content` text NOT NULL,
`content_md5` varchar(128) DEFAULT NULL,
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `canal_instance_config`
--
LOCK TABLES `canal_instance_config` WRITE;
/*!40000 ALTER TABLE `canal_instance_config` DISABLE KEYS */;
/*!40000 ALTER TABLE `canal_instance_config` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `canal_node_server`
--
DROP TABLE IF EXISTS `canal_node_server`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `canal_node_server` (
`id` bigint NOT NULL AUTO_INCREMENT,
`cluster_id` bigint DEFAULT NULL,
`name` varchar(63) NOT NULL,
`ip` varchar(63) NOT NULL,
`admin_port` int DEFAULT NULL,
`tcp_port` int DEFAULT NULL,
`metric_port` int DEFAULT NULL,
`status` varchar(45) DEFAULT NULL,
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `canal_node_server`
--
LOCK TABLES `canal_node_server` WRITE;
/*!40000 ALTER TABLE `canal_node_server` DISABLE KEYS */;
/*!40000 ALTER TABLE `canal_node_server` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `canal_user`
--
DROP TABLE IF EXISTS `canal_user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `canal_user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`username` varchar(31) NOT NULL,
`password` varchar(128) NOT NULL,
`name` varchar(31) NOT NULL,
`roles` varchar(31) NOT NULL,
`introduction` varchar(255) DEFAULT NULL,
`avatar` varchar(255) DEFAULT NULL,
`creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `canal_user`
--
LOCK TABLES `canal_user` WRITE;
/*!40000 ALTER TABLE `canal_user` DISABLE KEYS */;
/*!40000 ALTER TABLE `canal_user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-11-10 17:21:16
测试
向源mysql的mytest.user中插入一条数据：insert into user(name) value("6666666666");
adapter中有如下日志打印
2022-10-20 15:56:01.476 [pool-7-thread-1] INFO c.a.o.canal.client.adapter.logger.LoggerAdapterExample - DML: {"data":[{"id":12,"name":"6666666666"}],"database":"mytest","destination":"example","es":1666252561000,"groupId":"g1","isDdl":false,"old":null,"pkNames":["id"],"sql":"","table":"user","ts":1666252561475,"type":"INSERT"}
2022-10-20 15:56:01.523 [pool-1-thread-1] DEBUG c.a.o.canal.client.adapter.rdb.service.RdbSyncService - DML: {"data":{"id":12,"name":"6666666666"},"database":"mytest","destination":"example","old":null,"table":"user","type":"INSERT"}
此时目标mysql的mytest.user已经同步
mysql> select * from user;
+----+------------+
| id | name |
+----+------------+
| 11 | ccc |
| 12 | 6666666666 |
+----+------------+
2 rows in set (0.00 sec)
也可以手动全量同步，如果只是修改了yml，其他都为默认，只需要修改以下命令中的price_config-bj（表映射文件）即可
curl "localhost:8081/etl/rdb/mysql1/price_config-bj.yml" -X POST
{"succeeded":true,"resultMessage":"导入RDB 数据：11 条"}root@ts-OptiPlex-7070:/home/ts/canal/adapter/conf# curl "localhost:8081/etl/rdb/mysql1/price_config-zj.yml" -X POST
附录
mysql8的docker-compose
version: '3'
services:
mysql:
privileged: true
restart: always
image: mysql:8.0.29
container_name: mysql
ports:
- 3306:3306
environment:
TZ: Asia/Shanghai
MYSQL_ROOT_PASSWORD: 123456
command:
--default-authentication-plugin=mysql_native_password
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
volumes:
- ./conf:/etc/mysql/conf.d
- ./logs:/logs
- ./data:/var/lib/mysql
完整的mysql配置文件
###### [mysql]配置模块 ######
[mysql]
# 设置MySQL客户端默认字符集
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock
###### [mysqld]配置模块 ######
[mysqld]
port=3306
#user=mysql
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
#datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# MySQL8 的密码认证插件
default_authentication_plugin=mysql_native_password
# 禁用符号链接以防止各种安全风险
symbolic-links=0
# 允许最大连接数
max_connections=1000
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
lower_case_table_names=0
max_allowed_packet=16M
# 设置时区
#default-time_zone='+8:00'
# binlog 配置
# 二进制文件存放路径，非必须，mysql8后默认存放/var/lib/mysql
log-bin=mysql-bin
# 日志存储方式
binlog_format=row
# 监控的库，如果不写，对所有库开启监控
#binlog-do-db=test
#binlog-do-db=test2
expire-logs-days = 90
max-binlog-size = 500M
# 集群id
server-id=1
###### [client]配置模块 ######
[client]
default-character-set=utf8mb4
