create MySQL8 working dictionary sample

docker-compose.yaml

version: "3"
services:
  mysql8:
    image: mysql:${MYSQL_VERSION:-latest}
    environment:
      - MYSQL_ALLOW_EMPTY_PASSWORD=1
    volumes:
      - ./mysql8_table.sql:/docker-entrypoint-initdb.d/mysql8_table.sql
      - ./mysql8_log_queries.conf:/etc/mysql/conf.d/mysql8_log_queries.conf
  clickhouse:
    image: yandex/clickhouse-server:${CLICKHOUSE_VERSION:-latest}
    volumes:
      - ./mysql8_dictionary.sql:/docker-entrypoint-initdb.d/mysql8_dictionary.sql

mysql8_dictionary.sql

DROP DICTIONARY IF EXISTS default.mysql_dictionary;
CREATE DICTIONARY default.mysql_dictionary(
    id UInt64,
    value String
)
PRIMARY KEY id
LIFETIME(60)
LAYOUT(HASHED())
SOURCE(MYSQL(
    host 'mysql8'
    port 3306
    user 'clickhouse'
    password 'clickhouse'
    db 'test'
    table 'test_table'
));

mysql8_log_queries.conf

[mysqld]
information_schema_stats_expiry=0
general_log=1
general_log_file=/var/lib/mysql/all_queries.log

mysql8_table.sql

CREATE USER IF NOT EXISTS 'clickhouse'@'%' IDENTIFIED WITH mysql_native_password BY 'clickhouse';
CREATE DATABASE IF NOT EXISTS test;
GRANT ALL PRIVILEGES ON test.* TO 'clickhouse'@'%';
CREATE TABLE IF NOT EXISTS test.test_table (id BIGINT UNSIGNED NOT NULL PRIMARY KEY, value VARCHAR(255));
INSERT INTO test.test_table VALUES(1,'test1'),(2,'test2');

SET GLOBAL information_schema_stats_expiry=0;