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

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