实验的环境是virtulbox虚拟机一台，操作系统为centos7，源库为mariadb，oceanbase监听地址为127.0.0.1,源库和目的库都在这个虚拟及上，canal也安装部署在同一虚拟机上。
1 下载canal.deployer
[root@localhost /]# wget https://github.com/alibaba/canal/releases/download/canal-1.1.5/canal.deployer-1.1.5.tar.gz
2 MySQL数据库操作
CREATE USER canal IDENTIFIED BY 'canal';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO 'canal'@'%';
-- GRANT ALL PRIVILEGES ON . TO 'canal'@'%' ;
FLUSH PRIVILEGES;
3 部署 Canal Deployer
将canal deployer解压到 /usr/local/canal 目录下,canal 目录需要提前创建
tar -zxvf canal.deployer-1.1.5.tar.gz -C /usr/local/canal
使用手动部署，编辑canal deployer 解压目录(/usr/local/canal)下conf/example/instance.properties文件
[root@localhost example]# vi instance.properties
编辑完的文件如下
[root@localhost example]# cat 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
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=canal
canal.instance.dbPassword=canal
canal.instance.connectionCharset = UTF-8
# enable druid Decrypt database password
canal.instance.enableDruid=false
#canal.instance.pwdPublicKey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALK4BUxdDltRRE5/zXpVEVPUgunvscYFtEip3pmLlhrWpacX7y7GCMo2/JM6LeHmiiNdH1FWgGCpUfircSwlWKUCAwEAAQ==
# table regex
canal.instance.filter.regex=.\..
# table black regex
canal.instance.filter.black.regex=mysql\.slave_.*
# 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,.\..
#canal.mq.dynamicTopicPartitionNum=test.*:4,mycanal:6
#################################################
这个文件比较长，如果使用缺省的example实例，可以不对实例属性文件进行更改，只在源数据库创建用户canal，密码为canal，授予必要的权限即可。
启动canal deployer，进入canal解压目录
[root@localhost canal]# sh bin/startup.sh
4 部署canal adapter
下载canal adapter 解压至目录/usr/local/canal-adapter 目录下，编辑application.yml配置文件，注意冒号后面要有空格
[root@localhost conf]# pwd
/usr/local/canal-adapter/conf
[root@localhost conf]# cat application.yml
server:
port: 8081
spring:
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8
default-property-inclusion: non_null
canal.conf:
mode: tcp #tcp kafka rocketMQ rabbitMQ
flatMessage: true
zookeeperHosts:
syncBatchSize: 1000
retries: 0
timeout:
accessKey:
secretKey:
consumerProperties:
# canal tcp consumer
canal.tcp.server.host: 127.0.0.1:11111
canal.tcp.zookeeper.hosts:
canal.tcp.batch.size: 500
canal.tcp.username: canal ##这里填入登陆源库用户名
canal.tcp.password: canal ##这里填入登陆源库的用户密码
canalAdapters:
- instance: example # canal 实例名
groups:
- groupId: g1
outerAdapters:
- name: logger
- name: rdb
key: mysql1
properties:
jdbc.driverClassName: com.mysql.jdbc.Driver ##使用mysql官方驱动
jdbc.url: jdbc:mysql://127.0.0.1:2883/test?useUnicode=true
jdbc.username: root@my_tenant#obcluster ##登陆oceanbase数据库的用户名，格式为用户名@租户名#集群名称
jdbc.password: ##密码，oceanbase里my_tenant租户root用户没有设置密码，生产环境不能为空
mytest_user.yml文件，这里选择的镜像数据库方式
[root@localhost rdb]# pwd
/usr/local/canal-adapter/conf/rdb
[root@localhost rdb]# vi mytest_user.yml
## Mirror schema synchronize config
dataSourceKey: defaultDS
destination: example ##这里canal server 的实例名
groupId: g1
outerAdapterKey: mysql1 ##填入application.yml的outerAdapter key
concurrent: true
dbMapping:
mirrorDb: true
database: test ##要镜像的数据库
5 测试数据实时迁移效果
5.1 数据插入
源库操作
MariaDB [test]> INSERT INTO user
-> (name, age, address, sex)
-> VALUES
-> ('zhangsan', 21, 'jiangxi', 0),
-> ('lisi', 22, 'hubei', 0),
-> ('wangwu', 23, 'hunan', 0),
-> ('lilei', 24, 'henan', 1),
-> ('hanmeimei', 25, 'hebei', 1),
-> ('xiaoming', 26, 'shandong', 1),
-> ('xiaoli', 27, 'shanxi', 1) ;
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
MariaDB [test]> select * from user;
+----+-----------+------+----------+------+
| id | name | age | address | sex |
+----+-----------+------+----------+------+
| 1 | zhangsan | 21 | jiangxi | 0 |
| 2 | lisi | 22 | hubei | 0 |
| 3 | wangwu | 23 | hunan | 0 |
| 4 | lilei | 24 | henan | 1 |
| 5 | hanmeimei | 25 | hebei | 1 |
| 6 | xiaoming | 26 | shandong | 1 |
| 7 | xiaoli | 27 | shanxi | 1 |
+----+-----------+------+----------+------+
7 rows in set (0.00 sec)
oceanbase目标库查询
MySQL [test]> select * from user;
+----+-----------+------+----------+------+
| id | name | age | address | sex |
+----+-----------+------+----------+------+
| 1 | zhangsan | 21 | jiangxi | 0 |
| 2 | lisi | 22 | hubei | 0 |
| 3 | wangwu | 23 | hunan | 0 |
| 4 | lilei | 24 | henan | 1 |
| 5 | hanmeimei | 25 | hebei | 1 |
| 6 | xiaoming | 26 | shandong | 1 |
| 7 | xiaoli | 27 | shanxi | 1 |
+----+-----------+------+----------+------+
7 rows in set (0.003 sec)
5.2 创建表
源库操作
MariaDB [test]> CREATE TABLE student_score(sid INT PRIMARY KEY NOT NULL, sname VARCHAR(30), sage INT, ssex VARCHAR(8), score INT(11));
Query OK, 0 rows affected (0.07 sec)
MariaDB [test]> show tables like 'student_score';
+--------------------------------+
| Tables_in_test (student_score) |
+--------------------------------+
| student_score |
+--------------------------------+
1 row in set (0.00 sec)
目标库查询
MySQL [test]> show tables like 'stu%';
+-----------------------+
| Tables_in_test (stu%) |
+-----------------------+
| student_score |
+-----------------------+
1 row in set (0.006 sec)
5.3 给表添加列
源库操作
MariaDB [test]> alter table student_score add column class varchar(20);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> desc student_score;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid | int(11) | NO | PRI | NULL | |
| sname | varchar(30) | YES | | NULL | |
| sage | int(11) | YES | | NULL | |
| ssex | varchar(8) | YES | | NULL | |
| score | int(11) | YES | | NULL | |
| class | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
目标库操作
MySQL [test]> desc student_score;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid | int(11) | NO | PRI | NULL | |
| sname | varchar(30) | YES | | NULL | |
| sage | int(11) | YES | | NULL | |
| ssex | varchar(8) | YES | | NULL | |
| score | int(11) | YES | | NULL | |
| class | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.011 sec)
6 总结及注意事项
canal安装配置还是比较简单的，测试中发现create table as select 只能同步表定义，数据则没有同步过去。
