clickhouse单机部署及实时同步mysql数据

clickhouse20.8.3部分

示例版本：20.8.3

环境检查

1
2
grep -q sse4_2 /proc/cpuinfo && echo "SSE 4.2 supported" || echo "SSE 4.2 not supported"
显示：SSE 4.2 supported，则环境支持

下载安装（单机模式）

安装依赖

1
2
yum install -y curl
yum install -y libtool

添加Clickhouse 的yum镜像

1
curl -s https://packagecloud.io/install/repositories/altinity/clickhouse/script.rpm.sh | sudo bash

检查镜像情况

1
yum list | grep clickhouse

安装 server client

1
2
3
4
5
6
yum install -y clickhouse-server 
yum install -y clickhouse-client
安装完后
vim /etc/clickhouse-server/users.xml
            <password>alexNB</password>
给default用户设置密码
1
2
3
4
5
6
7
启动server
/etc/init.d/clickhouse-server start|restart|stop
启动client CLI
clickhouse-client
clickhouse-client -m #可以在命令窗口输入多行命令
停止
systemctl stop clickhouse-server

外网访问(可选)

1
2
3
4
vim /etc/clickhouse-server/config.xml
把 <listen_host>::</listen_host> 的注释打开，这样的话才能让ClickHouse被除本机以外的服务器访问
如果禁用了ipv6，使用下面配置
<listen_host>0.0.0.0</listen_host>
  • 修改默认目录
  • 服务端的配置目录：/etc/clickhouse-server
  • 数据文件路径：var/lib/clickhouse /var/lib/clickhouse/
  • 日志文件路径： /var/log/clickhouse-server/ 如/var/log/clickhouse-server/clickhouse-server.log
  • 存储和日志需要建议修改为非系统盘，其他默认即可

    卸载

    1
    2
    3
    4
    5
    6
    7
    8
    查看安装
    yum list installed | grep clickhouse
    clickhouse-client.noarch               21.3.3.14-2                     @repo.clickhouse.tech_rpm_stable_x86_64
    clickhouse-common-static.x86_64        21.3.3.14-2                     @repo.clickhouse.tech_rpm_stable_x86_64
    clickhouse-server.noarch               21.3.3.14-2                     @repo.clickhouse.tech_rpm_stable_x86_64
    卸载并删除目录
    yum remove -y clickhouse-common-static
    yum remove -y clickhouse-server-commonrm -rf /var/lib/clickhouserm -rf /etc/clickhouse*rm -rf /var/log/clickhouse*

    mysql_8.13

    安装完master之后,执行sql查看是否满足条件

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    mysql> show variables like '%gtid_mode%';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | gtid_mode     | ON    |
    +---------------+-------+
    1 row in set (0.00 sec)
    mysql> show variables like '%enforce_gtid_consistency%';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | enforce_gtid_consistency | ON    |
    +--------------------------+-------+
    1 row in set (0.00 sec)
    mysql> show variables like '%binlog_format%';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | binlog_format | ROW   |
    +---------------+-------+
    1 row in set (0.00 sec)

    若不满足修改配置文件并重启mysql

    1
    2
    3
    4
    5
    cat /etc/my.cnf

    gtid_mode=ON
    enforce_gtid_consistency=1
    binlog_format=ROW

    MySQL8创建用户并授权的语句

    1
    mysql>create user brady@'%' identified  by 'brady';
    授权示例
    1
    mysql>grant all privileges on *.* to brady@'%' with grant option;
    刷新权限
    1
    mysql>flush privileges;
    修改远程连接用户的加密规则
    1
    mysql>ALTER USER 'brady'@'%' IDENTIFIED WITH mysql_native_password BY 'brady';

    数据同步

    进入clickhouse数据库

    1
    clickhouse-client -u default -h <IP> --password alexNB

    开启复制通道

    1
    2
    3
    select * from system.settings where name ='allow_experimental_database_materialize_mysql';

    SET allow_experimental_database_materialize_mysql=1;

    同步数据

    1
    2
    3
    CREATE DATABASE DBNAME ENGINE = MaterializeMySQL('MYSQLIP:PORT', 'SOURCEDB', 'MYSQL_DB', 'PASSWORD');

    CREATE DATABASE data_dataTransfer_ch_update ENGINE = MaterializeMySQL('172.22.254.217:3306', 'data_dataTransfer_ch', 'brady', 'brady');
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    k8s-master :) show databases;
    SHOW DATABASES
    Query id: 7469cc9b-da21-44ad-9eb5-f32cddcadbe7
    ┌─name────────────────────────┐
    │ audit                       │
    │ data_dataTransfer_ch        │
    │ data_dataTransfer_ch_update │
    │ default                     │
    │ system                      │
    │ tstu                        │
    └─────────────────────────────┘
    6 rows in set. Elapsed: 0.002 sec. 

    k8s-master :) use data_dataTransfer_ch_update;
    USE data_dataTransfer_ch_update
    Query id: 552cecae-df70-49e1-a4c4-8bae3e7f4f7d
    Ok.
    0 rows in set. Elapsed: 0.001 sec. 

    k8s-master :) select task_id,data_val from data_dataTransfer_ch_update.t_task_check_field_data where task_id = '796D82C8-CB64-41AC-84EE-9D292880FEBC';

    SELECT
        task_id,
        data_val
    FROM data_dataTransfer_ch_update.t_task_check_field_data
    WHERE task_id = '796D82C8-CB64-41AC-84EE-9D292880FEBC'

    Query id: 676b491b-df7a-4010-84ed-acb8dec5a393

    ┌─task_id──────────────────────────────┬─data_val──────────────────────────────────────────────────────────────┐
    │ 796D82C8-CB64-41AC-84EE-9D292880FEBC │ http://110.110.110.110:8085/vul/xss/xssblind/admin_login.php            │
    │ 796D82C8-CB64-41AC-84EE-9D292880FEBC │ http://110.110.110.110:8085/vul/xss/xssblind/admin_login.php            │
    │ 796D82C8-CB64-41AC-84EE-9D292880FEBC │ http://110.110.110.110:8085//vul/xss/xsspost/post_login.php             │
    │ 796D82C8-CB64-41AC-84EE-9D292880FEBC │ http://110.110.110.110:8085/vul/sqli/sqli_iu/sqli_login.php             │
    │ 796D82C8-CB64-41AC-84EE-9D292880FEBC │ http://110.110.110.110:8085/vul/infoleak/findabc.php                    │
    至此实时同步结束
    可以在mysql对数据表进行增删改操作，在clickhouse可以看到效果

clickhouse版本更新迭代较快，老版本bug较多不稳定。
20.8这个版本bug也挺多，如果有遇到可以参考下篇文章
大佬移步这里
或者这里