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

clickhouse20.8.3部分

示例版本：20.8.3

环境检查

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

下载安装（单机模式）

安装依赖

yum install -y curl
yum install -y libtool

添加Clickhouse 的yum镜像

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

检查镜像情况

yum list | grep clickhouse

安装 server client

yum install -y clickhouse-server 
yum install -y clickhouse-client
安装完后
vim /etc/clickhouse-server/users.xml
            <password>alexNB</password>
给default用户设置密码

启动server
/etc/init.d/clickhouse-server start|restart|stop
启动client CLI
clickhouse-client
clickhouse-client -m #可以在命令窗口输入多行命令
停止
systemctl stop clickhouse-server

外网访问(可选)

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
• 存储和日志需要建议修改为非系统盘，其他默认即可

  卸载

  查看安装
  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查看是否满足条件

  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

  cat /etc/my.cnf
  
  gtid_mode=ON
  enforce_gtid_consistency=1
  binlog_format=ROW

  MySQL8创建用户并授权的语句

  mysql>create user brady@'%' identified  by 'brady';

  授权示例

  mysql>grant all privileges on *.* to brady@'%' with grant option;

  刷新权限

  mysql>flush privileges;

  修改远程连接用户的加密规则

  mysql>ALTER USER 'brady'@'%' IDENTIFIED WITH mysql_native_password BY 'brady';

  数据同步

  进入clickhouse数据库

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

  开启复制通道

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

  同步数据

  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');

  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也挺多，如果有遇到可以参考下篇文章
大佬移步这里
或者这里