MYSQL同步数据到CLICKHOUSE

第一步：创建目录

mkdir -p ~/debezium-sync/{config,logs,data}
cd ~/debezium-sync

第二步：创建安装文件

# vim docker-compose.yml 
version: '3.8'
services:
  zookeeper:
    image: confluentinc/cp-zookeeper:7.4.0
    container_name: zookeeper
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
      ZOOKEEPER_TICK_TIME: 2000
    ports:
      - "127.0.0.1:2181:2181"
    networks:
      - debezium-net

  kafka:
    image: confluentinc/cp-kafka:7.4.0
    container_name: kafka
    depends_on:
      - zookeeper
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:9092,PLAINTEXT_HOST://localhost:29092
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: PLAINTEXT:PLAINTEXT,PLAINTEXT_HOST:PLAINTEXT
      KAFKA_INTER_BROKER_LISTENER_NAME: PLAINTEXT
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
      KAFKA_AUTO_CREATE_TOPICS_ENABLE: "true"
    ports:
      - "127.0.0.1:9092:9092"
      - "127.0.0.1:29092:29092"
    networks:
      - debezium-net

  kafka-connect:
    image: debezium/connect:2.3
    container_name: kafka-connect
    depends_on:
      - kafka
    environment:
      BOOTSTRAP_SERVERS: kafka:9092
      GROUP_ID: 1
      CONFIG_STORAGE_TOPIC: connect_configs
      OFFSET_STORAGE_TOPIC: connect_offsets
      STATUS_STORAGE_TOPIC: connect_status
      KEY_CONVERTER: org.apache.kafka.connect.json.JsonConverter
      VALUE_CONVERTER: org.apache.kafka.connect.json.JsonConverter
      KEY_CONVERTER_SCHEMAS_ENABLE: "false"
      VALUE_CONVERTER_SCHEMAS_ENABLE: "false"
    ports:
      - "127.0.0.1:8083:8083"
    volumes:
      #- ./config:/kafka/config
      - ./logs:/kafka/logs
    networks:
      - debezium-net
    restart: unless-stopped

  kafdrop:
    image: obsidiandynamics/kafdrop:3.31.0
    container_name: kafdrop
    depends_on:
      - kafka
    environment:
      KAFKA_BROKERCONNECT: kafka:9092
      JVM_OPTS: "-Xms32M -Xmx64M"
    ports:
      - "127.0.0.1:19000:9000"
    networks:
      - debezium-net
    restart: unless-stopped

networks:
  debezium-net:
    driver: bridge

第三步：启动服务

# 1. 先只启动Zookeeper
docker-compose up -d zookeeper
sleep 10

# 2. 启动Kafka
docker-compose up -d kafka  
sleep 30  # Kafka启动较慢

# 3. 启动Kafka Connect
docker-compose up -d kafka-connect
sleep 20

# 4. 最后启动Kafdrop
docker-compose up -d kafdrop

第四步： 创建mysql用户

-- 这里的ip是kafka-connect容器的ip
CREATE USER 'debezium'@'172.18.0.4' IDENTIFIED BY 'G@Eb76KSc&za';
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'debezium'@'172.18.0.4';
FLUSH PRIVILEGES;

第五步：创建kafka连接配置文件

# cat config/mysql-connector.json 
{
  "name": "mysql-connector-local",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "database.hostname": "172.18.0.1", # 这里注意，这是宿主机的ip
    "database.port": "3306",
    "database.user": "debezium", # 连接用户名
    "database.password": "G@Eb76KSc&za", # 连接密码
    "database.server.id": "184054",
    "database.server.name": "mysql-server-local",
    
    "database.include.list": "db_ai_waifu",
    "table.include.list": "db_ai_waifu.*",
    
    "database.history.connector.url": "jdbc:mysql://172.18.0.1:3306?useSSL=false&allowPublicKeyRetrieval=true&sessionVariables=binlog_format=ROW", # 这里注意，这是宿主机的ip
    "database.history.kafka.bootstrap.servers": "kafka:9092",
    "database.history.kafka.topic": "dbhistory.mydb",
    
    "include.schema.changes": "true",
    "snapshot.mode": "initial",
    "snapshot.locking.mode": "minimal",
    
    "transforms": "unwrap",
    "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
    "transforms.unwrap.drop.tombstones": "false",
    
    "key.converter": "org.apache.kafka.connect.json.JsonConverter",
    "value.converter": "org.apache.kafka.connect.json.JsonConverter",
    "key.converter.schemas.enable": "false",
    "value.converter.schemas.enable": "false",
    
    "topic.prefix": "mysql-server-local",
    "decimal.handling.mode": "double",
    "time.precision.mode": "connect",
    "max.batch.size": "20480",
    "max.queue.size": "40960"
  }
}

第六步：注册

# 重启连接器（让Debezium重新连接）
curl -X POST http://localhost:8083/connectors/mysql-connector-local/restart

# 或者删除并重新注册
curl -X DELETE http://localhost:8083/connectors/mysql-connector-local
curl -X POST -H "Content-Type: application/json" \
  http://localhost:8083/connectors \
  -d @config/mysql-connector.json

第七步： 查看服务状态

# 检查连接器详细状态
curl -s http://localhost:8083/connectors/mysql-connector-local/status | jq

# 检查任务状态
curl -s http://localhost:8083/connectors/mysql-connector-local/tasks | jq

# 检查所有连接器
curl -s http://localhost:8083/connectors | jq