mysql 同步到 elasticsearch —— canal 1.1.4 demo

mysql 配置文件：（注释的是 canal 需要的）

[mysqld]
# 打开binlog
log-bin=mysql-bin
# 选择ROW(行)模式
binlog-format=ROW
# 配置MySQL replaction需要定义，不要和canal的slaveId重复
server_id=1
collation_server=utf8_bin
character_set_server=utf8
sql_mode = 'STRICT_TRANS_TABLES'
[client]
default-character-set=utf8  
[mysql]
default-character-set=utf8  

启动 mysql docker 容器

docker run -itd --name mysql56_canal -p 8005:3306 -v /usr/test/conf:/etc/mysql/mysql.conf.d/mysqld.cnf -e MYSQL_ROOT_PASSWORD=4396 mysql:5.6.50

mysql 设置权限

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MySQL [(none)]> show variables like 'log_bin';  # bin_log 开启
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

MySQL [(none)]> show binary logs;  # bin_log 文件列表
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     65417 |
| mysql-bin.000002 |   1410106 |
| mysql-bin.000003 |   4117641 |
| mysql-bin.000004 |       120 |
+------------------+-----------+
4 rows in set (0.00 sec)

MySQL [(none)]> show master status  # 正在写入的 bin_log文件
    -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 创建用户
MySQL [(none)]> create user 'canal'@'%' identified by 'Canal@123456';
Query OK, 0 rows affected (0.00 sec)

# 分配权限
MySQL [(none)]> grant SELECT, REPLICATION SLAVE, REPLICATION CLIENT on *.* to 'canal'@'%' identified by 'Canal@123456';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

服务端：canal docker 部署

docker pull canal/canal-server:v1.1.4

cannal docker-compose 文件

version: '3'

services:
  canal-server:
    image: canal/canal-server:v1.1.4
    container_name: canal-server
    restart: unless-stopped
    ports: 
      - 8004:11111
    environment:
      - canal.auto.scan=false
      - canal.instance.master.address=120.55.163.233:8005
      - canal.instance.dbUsername=canal
      - canal.instance.dbPassword=canal
      - canal.instance.filter.regex=.*\\..*
      - canal.destinations=test
      - canal.instance.connectionCharset=UTF-8
      - canal.instance.tsdb.enable=true
    volumes:
      - /usr/test/canal_test/log/:/home/admin/canal-server/logs/

python 客户端 canal

pip install canal-python  # 安装模块
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib  # 依赖包

官方 demo

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'', password=b'')
client.subscribe(client_id=b'1001', destination=b'test', 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()
                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()

运行客户端，测试

create database ttt;  # 创建数据库
use ttt  # 切换到数据库
CREATE TABLE `role` (   `id` int unsigned NOT NULL AUTO_INCREMENT,   `role_name` varchar(255) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;  # 创建 role 表
insert into role (id, role_name) values (10, 'admin');  # 插入数据
update role set role_name='hh' where id = 10;  # 更新数据
delete from role where id = 10;  # 删除数据

得到结果

变更一条数据，输出内容分三部分，分别是：TRANSACTIONBEGIN，ROWDATA 和 TRANSACTIONEND。然后我们比较关注的内容都在 ROWDATA 中，解析出来之后就是我们需要的，包括数据库名，表名和变更内容。

其中 event_type 字段 1 表示新增，2 表示更新，3 表示删除。

canal 服务端启动之后，在 /home/admin/canal-server/logs/test 目录下会生成两个日志文件，分别是：meta.log 和 test.log，可以查看服务是不是正常，有没有报错信息。其中 test 是启动 Docker 时 canal.destinations 设置的名称。

别人踩坑记录

问题：

解决：fetch failed by table meta:schemeName.tableName

Caused by: java.io.IOException: ErrorPacket [errorNumber=1142, fieldCount=-1, message=SHOW command denied to user

binlog 权限问题 select 权限要加上

由于我使用的是内网pypi不是最新，无法获取canal-python，将主页上源码下载后放在python环境目录下的site-package目录下即可；

无论原始数据是int还是varchar，解析出来的数据都是字符串类型；

原文链接：https://cloud.tencent.com/developer/article/1759828

原文链接：https://zhuanlan.zhihu.com/p/165428937