阿里canal部署和應用

水逸冰發表於2020-07-19

阿里canal概述

canal是阿里巴巴mysql資料庫binlog的增量訂閱&消費元件。

應用場景

canal-python 作為Canal的客戶端,其應用場景就是Canal的應用場景。關於應用場景在Canal介紹一節已有概述。舉一些實際的使用例子:

1.代替使用輪詢資料庫方式來監控資料庫變更,有效改善輪詢耗費資料庫資源。

2.根據資料庫的變更實時更新搜尋引擎,比如電商場景下商品資訊發生變更,實時同步到商品搜尋引擎 Elasticsearch、solr等

3.根據資料庫的變更實時更新快取,比如電商場景下商品價格、庫存發生變更實時同步到redis

4.資料庫異地備份、資料同步

5.根據資料庫變更觸發某種業務,比如電商場景下,建立訂單超過xx時間未支付被自動取消,我們獲取到這條訂單資料的狀態變更即可向使用者推送訊息。

6.將資料庫變更整理成自己的資料格式傳送到kafka等訊息佇列,供訊息佇列的消費者進行消費。

安裝Canal

tar -zxvf canal.deployer-1.1.4.tar.gz
[root@mdb01 canal]# ll

total 4
drwxr-xr-x 2 root root   93 Jul 19 15:18 bin
drwxr-xr-x 5 root root  123 Jul 19 14:25 conf
drwxr-xr-x 2 root root 4096 Jul 19 14:25 lib
drwxrwxrwx 4 root root   34 Jul 19 14:29 logs

配置檔案在conf/example/instance.properties

[root@mdb01 example]# ll

total 176
-rw-r--r-- 1 root root 172032 Jul 19 15:19 h2.mv.db
-rwxrwxrwx 1 root root   2041 Jul 19 14:34 instance.properties
-rw-r--r-- 1 root root    342 Jul 19 21:11 meta.dat
[root@mdb01 example]# more instance.properties |grep -v '^#'

canal.instance.gtidon=false

canal.instance.master.address=192.168.61.16:3306
canal.instance.master.journal.name=
canal.instance.master.position=
canal.instance.master.timestamp=
canal.instance.master.gtid=

canal.instance.rds.accesskey=
canal.instance.rds.secretkey=
canal.instance.rds.instanceId=

canal.instance.tsdb.enable=true


canal.instance.dbUsername=canal
canal.instance.dbPassword=oracle
canal.instance.connectionCharset = UTF-8
canal.instance.enableDruid=false

canal.instance.filter.regex=.*\\..*
canal.instance.filter.black.regex=

canal.mq.topic=example
canal.mq.partition=0

建立資料庫使用者

CREATE USER canal IDENTIFIED BY 'oracle';  

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
FLUSH PRIVILEGES;


啟停指令碼在bin目錄下,sh restart.sh

[root@mdb01 canal]# cd bin

[root@mdb01 bin]# ll
total 20
-rw-r--r-- 1 root root    7 Jul 19 15:18 canal.pid
-rwxr-xr-x 1 root root   58 Sep  2  2019 restart.sh
-rwxr-xr-x 1 root root 1181 Sep  2  2019 startup.bat
-rwxr-xr-x 1 root root 3167 Sep  2  2019 startup.sh
-rwxr-xr-x 1 root root 1356 Sep  2  2019 stop.sh

日誌在logs/example目錄下。

[root@mdb01 example]# pwd

/u01/canal/logs/example
[root@mdb01 example]# ll
total 28
-rw-r--r-- 1 root root 21582 Jul 19 21:11 example.log
-rw-r--r-- 1 root root  2090 Jul 19 21:11 meta.log

成功啟動後日志輸出:

2020-07-19 15:18:18.596 [main] INFO  c.a.otter.canal.instance.spring.CanalInstanceWithSpring - start CannalInstance for 1-example 

2020-07-19 15:18:18.605 [main] WARN  c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^.*\..*$
2020-07-19 15:18:18.606 [main] WARN  c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table black filter :
2020-07-19 15:18:18.616 [main] INFO  c.a.otter.canal.instance.core.AbstractCanalInstance - start successful....
2020-07-19 15:18:18.842 [destination = example , address = /192.168.61.16:3306 , EventParser] WARN  c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - --->
begin to find start position, it will be long time for reset or first position
2020-07-19 15:18:18.842 [destination = example , address = /192.168.61.16:3306 , EventParser] WARN  c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - prep
are to find start position just show master status
2020-07-19 15:18:37.804 [destination = example , address = /192.168.61.16:3306 , EventParser] WARN  c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - --->
find start position successfully, EntryPosition[included=false,journalName=mysql-bin.000019,position=4,serverId=1573854809,gtid=<null>,timestamp=1595139828000]
cost : 18915ms , the next step is binlog dump

Canal Python客戶端

canal-python 是 Canal 的 python 客戶端,它與 Canal 是採用的Socket來進行通訊的,傳輸協議是TCP,互動協議採用的是 Google Protocol Buffer 3.0。github地址:

https://github.com/haozi3156666/canal-python

github中的給出的例子是不對的,不能正確顯示出update的前值。下面是修正過的:

import time


from canal.client import Client
from canal.protocol import EntryProtocol_pb2
from canal.protocol import CanalProtocol_pb2

client = Client()
client.connect(host='127.0.0.1', port=11111)
client.check_valid(username=b'root', password=b'oracle')
client.subscribe(client_id=b'1001', destination=b'example', filter=b'.*\\..*')

while True:
   message = client.get(100)
   entries = message['entries']
   for entry in entries:
       entry_type = entry.entryType
       if entry_type in [EntryProtocol_pb2.EntryType.TRANSACTIONBEGIN, EntryProtocol_pb2.EntryType.TRANSACTIONEND]:
           continue
       row_change = EntryProtocol_pb2.RowChange()
       row_change.MergeFromString(entry.storeValue)
       event_type = row_change.eventType
       header = entry.header
       database = header.schemaName
       table = header.tableName
       event_type = header.eventType
       for row in row_change.rowDatas:
           format_data = dict()
           if event_type == EntryProtocol_pb2.EventType.DELETE:
               for column in row.beforeColumns:
                   format_data = {
                       column.name: column.value
                   }
           elif event_type == EntryProtocol_pb2.EventType.INSERT:
               for column in row.afterColumns:
                   format_data = {
                       column.name: column.value
                   }
           else:
               #format_data['before'] = format_data['after'] = dict()
               format_data['before'] = dict()
               format_data['after'] = dict()

               for column in row.beforeColumns:
                   format_data['before'][column.name] = column.value
               for column in row.afterColumns:
                   format_data['after'][column.name] = column.value
           data = dict(
               db=database,
               table=table,
               event_type=event_type,
               data=format_data,
           )
           print(data)
   time.sleep(1)

client.disconnect()

運算元據庫

mysql> insert into t1 select 1;

Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update t1 set a=2 where a=1;
Query OK, 1 row affected (0.17 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from t1 where a=2;
Query OK, 1 row affected (0.02 sec)

輸出:

connected to 127.0.0.1:11111

Auth succed
Subscribe succed
{'db': 'ming', 'table': 't1', 'event_type': 1, 'data': {'a': '1'}}
{'db': 'ming', 'table': 't1', 'event_type': 2, 'data': {'before': {'a': '1'}, 'after': {'a': '2'}}}
{'db': 'ming', 'table': 't1', 'event_type': 3, 'data': {'a': '2'}}



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31480688/viewspace-2705493/,如需轉載,請註明出處,否則將追究法律責任。

相關文章