下载地址https://mirrors.aliyun.com/clickhouse/rpm/lts/https://packages.clickhouse.com/rpm/lts/初始化ulimit-SHn65535catEOF/etc/security/limits.conf# 末尾添加如下内容* soft nofile65536* hard nofile131072* soft nproc65535* hard nproc655350* soft memlock unlimited * hard memlock unlimited EOF#增加打印执行命令时间catEOF/etc/profile#命令历史记录的条数exportHISTSIZE1000#显示时间和用户exportHISTTIMEFORMAT%F %Twhoami#忽略mysql*命令user*开头的历史#export HISTIGNOREmysql*:user*:clickhouse*:redis-client*:mongo*:passwd*:echo*EOFsource/etc/profilecatEOF/etc/hosts172.16.5.156 int-ywvm-vm-ltjy-ckdb03172.16.5.155 int-ywvm-vm-ltjy-ckdb02172.16.5.154 int-ywvm-vm-ltjy-ckdb01 EOF安装部署ckrpm-ivhclickhouse-common-static-23.8.9.54.x86_64.rpm clickhouse-server-23.8.9.54.x86_64.rpm clickhouse-client-23.8.9.54.x86_64.rpm clickhouse-common-static-dbg-23.8.9.54.x86_64.rpmmkdir/data/clickhouse/{ckdata,conf,logs}-pchown-Rclickhouse:clickhouse /data/clickhousecp/etc/clickhouse-* /data/clickhouse/conf/-rcd/data/clickhouse/rm-rf/var/lib/clickhouserm-rf/etc/clickhouse-*rm-rf/var/log/clickhouse-serverln-s/data/clickhouse/ckdata /var/lib/clickhouseln-s/data/clickhouse/logs /var/log/clickhouse-serverln-s/data/clickhouse/conf/clickhouse-client /etc/clickhouse-clientln-s/data/clickhouse/conf/clickhouse-keeper /etc/clickhouse-keeperln-s/data/clickhouse/conf/clickhouse-server /etc/clickhouse-server#systemctl start clickhouse-serversystemctlenableclickhouse-server#systemctl status clickhouse-serverclickhouse-keepermkdir/data/clickhouse/{keeperdata,keeperlogs}-pchown-Rclickhouse:clickhouse /data/clickhouseln-s/data/clickhouse/keeperlogs /var/log/clickhouse-keeperln-s/data/clickhouse/keeperdata /var/lib/clickhouse-keepercp/data/clickhouse/conf/clickhouse-keeper/keeper_config.xml /data/clickhouse/conf/clickhouse-keeper/keeper_config.xml.bak# ck1catEOF/data/clickhouse/conf/clickhouse-keeper/keeper_config.xmlclickhouseloggerlevelwarning/levellog/var/log/clickhouse-keeper/clickhouse-keeper.log/logerrorlog/var/log/clickhouse-keeper/clickhouse-keeper.err.log/errorlog/loggerlisten_host0.0.0.0/listen_hostkeeper_servertcp_port9181/tcp_portserver_id1/server_idlog_storage_path/var/lib/clickhouse-keeper/coordination/log/log_storage_pathsnapshot_storage_path/var/lib/clickhouse-keeper/coordination/snapshots/snapshot_storage_pathcoordination_settingsoperation_timeout_ms10000/operation_timeout_mssession_timeout_ms30000/session_timeout_msraft_logs_levelwarning/raft_logs_level/coordination_settingsraft_configurationserverid1/idhostnameint-ywvm-vm-ltjy-ckdb01/hostnameport9234/port/serverserverid2/idhostnameint-ywvm-vm-ltjy-ckdb02/hostnameport9234/port/serverserverid3/idhostnameint-ywvm-vm-ltjy-ckdb03/hostnameport9234/port/server/raft_configuration/keeper_server/clickhouseEOF# ck2catEOF/data/clickhouse/conf/clickhouse-keeper/keeper_config.xmlclickhouseloggerlevelwarning/levellog/var/log/clickhouse-keeper/clickhouse-keeper.log/logerrorlog/var/log/clickhouse-keeper/clickhouse-keeper.err.log/errorlog/loggerlisten_host0.0.0.0/listen_hostkeeper_servertcp_port9181/tcp_portserver_id2/server_idlog_storage_path/var/lib/clickhouse-keeper/coordination/log/log_storage_pathsnapshot_storage_path/var/lib/clickhouse-keeper/coordination/snapshots/snapshot_storage_pathcoordination_settingsoperation_timeout_ms10000/operation_timeout_mssession_timeout_ms30000/session_timeout_msraft_logs_levelwarning/raft_logs_level/coordination_settingsraft_configurationserverid1/idhostnameint-ywvm-vm-ltjy-ckdb01/hostnameport9234/port/serverserverid2/idhostnameint-ywvm-vm-ltjy-ckdb02/hostnameport9234/port/serverserverid3/idhostnameint-ywvm-vm-ltjy-ckdb03/hostnameport9234/port/server/raft_configuration/keeper_server/clickhouseEOF# ck3catEOF/data/clickhouse/conf/clickhouse-keeper/keeper_config.xmlclickhouseloggerlevelwarning/levellog/var/log/clickhouse-keeper/clickhouse-keeper.log/logerrorlog/var/log/clickhouse-keeper/clickhouse-keeper.err.log/errorlog/loggerlisten_host0.0.0.0/listen_hostkeeper_servertcp_port9181/tcp_portserver_id3/server_idlog_storage_path/var/lib/clickhouse-keeper/coordination/log/log_storage_pathsnapshot_storage_path/var/lib/clickhouse-keeper/coordination/snapshots/snapshot_storage_pathcoordination_settingsoperation_timeout_ms10000/operation_timeout_mssession_timeout_ms30000/session_timeout_msraft_logs_levelwarning/raft_logs_level/coordination_settingsraft_configurationserverid1/idhostnameint-ywvm-vm-ltjy-ckdb01/hostnameport9234/port/serverserverid2/idhostnameint-ywvm-vm-ltjy-ckdb02/hostnameport9234/port/serverserverid3/idhostnameint-ywvm-vm-ltjy-ckdb03/hostnameport9234/port/server/raft_configuration/keeper_server/clickhouseEOFmkdir-p/var/lib/clickhouse-keeper/coordination/{log,snapshots}/var/log/clickhouse-keeperchown-Rclickhouse:clickhouse /data/clickhouse systemctl start clickhouse-keeper systemctlenableclickhouse-keeper systemctl status clickhouse-keeper# 健康检查看 leader/followerechomntr|nck19181|grepzk_server_statecurltelnet://int-ywvm-vm-ltjy-ckdb01:9181mntr|grepzk_server_statecurltelnet://int-ywvm-vm-ltjy-ckdb02:9181mntr|grepzk_server_statecurltelnet://int-ywvm-vm-ltjy-ckdb03:9181mntr|grepzk_server_state配置集群配置1分片2副本# ckdb01 ckdb02catEOF/etc/clickhouse-server/config.d/keeper.xmlclickhousezookeepernodehostint-ywvm-vm-ltjy-ckdb01/hostport9181/port/nodenodehostint-ywvm-vm-ltjy-ckdb02/hostport9181/port/nodenodehostint-ywvm-vm-ltjy-ckdb03/hostport9181/port/nodesession_timeout_ms30000/session_timeout_msoperation_timeout_ms10000/operation_timeout_ms/zookeeperlisten_host0.0.0.0/listen_host/clickhouseEOFcatEOF/etc/clickhouse-server/config.d/cluster.xmlclickhouseremote_serversck_hashardinternal_replicationtrue/internal_replicationreplicahostint-ywvm-vm-ltjy-ckdb01/hostport9000/port/replicareplicahostint-ywvm-vm-ltjy-ckdb02/hostport9000/port/replica/shard/ck_ha/remote_servers/clickhouseEOF# ckdb01catEOF/etc/clickhouse-server/config.d/macros.xmlclickhousemacrosshard01/shardreplicaint-ywvm-vm-ltjy-ckdb01/replica/macros/clickhouseEOF#ckdb02catEOF/etc/clickhouse-server/config.d/macros.xmlclickhousemacrosshard01/shardreplicaint-ywvm-vm-ltjy-ckdb02/replica/macros/clickhouseEOF systemctlenableclickhouse-server systemctl start clickhouse-server systemctl status clickhouse-server验证# 验证clickhouse-client SELECT cluster, shard_num, replica_num, host_name FROM system.clusters WHEREclusterck_ha;-- 查看复制队列状态在任意节点执行 SELECT database, table, is_leader, is_readonly, parts_to_check, queue_size, inserts_in_queue, merges_in_queue FROM system.replicas;-- 副本状态 SELECT database, table, is_leader, is_readonly, absolute_delay FROM system.replicas WHEREtableevents;在任意 CK 上执行ON CLUSTER 会自动两节点建表CREATE TABLE test.events ON CLUSTER ck_ha(idUInt64, ts DateTime, val String)ENGINEReplicatedMergeTree(/clickhouse/tables/{shard}/test.events,{replica})ORDER BY(ts,id);写入 ck1INSERT INTO test.events VALUES(1, now(),a);在 ck2 查SELECT * FROM test.events;能查到即复制正常。副本状态SELECT database, table, is_leader, is_readonly, absolute_delay FROM system.replicas WHEREtableevents;备份安装clickhouse-backup1、下载安装包cd /server/tools wget https://github.com/Altinity/clickhouse-backup/releases/download/v2.4.33/clickhouse-backup-linux-amd64.tar.gz2、创建存放clickhouse-backup的目录并解压mkdir -pv /data/clickhouse-backup tar xvf clickhouse-backup-linux-amd64.tar.gz -C /data/clickhouse-backup/3、创建软连接ln -sv /data/clickhouse-backup/build/linux/amd64/clickhouse-backup /usr/local/bin4、测试clickhouse-backup命令clickhouse-backup -v5、创建配置文件#注意下面的配置文件是按本地标准来的配置文件中默认data_path:“/var/lib/clickhouse”,如果clickhouse-server存储数据的路径变了这个地方也需要加上data_pathmkdir -p /etc/clickhouse-backup/ cd /etc/clickhouse-backup/ vim config.yml general: remote_storage: none backups_to_keep_local: 7 # 本地备份保留个数默认0表示不自动做备份清理 backups_to_keep_remote: 31 # 远程备份保留个数 clickhouse: username: default password: XXXXXX host: localhost port: 9000 #data_path: /var/lib/clickhouse二、自动备份数据1、查看可备份的表clickhouse-backup tables2、全库备份clickhouse-backup create备份存储在中 $data_path/backup 下(默认在/var/lib/clickhouse/backup)备份名称默认为时间戳可手动指定备份名称。例如clickhouse-backup create ch_bk_20240308备份包含两个目录metadata’目录: 包含重新创建所需的DDL SQLshadow’目录: 包含作为ALTER TABLE … FREEZE操作结果的数据。3、单表备份语法clickhouse-backup create [-t, --tablesdb.table] backup_name备份test库中的t_order表clickhouse-backup create -t test.t_order ch_t_order_202403084、备份多表备份test库中的t_order表和t_new表两个表用,隔开clickhouse-backup create -t test.t_order,test.t_new ch_two_bak_202403085、备份到远程对象存储例如s31修改配置文件#vim /etc/clickhouse-backup/config.yml #添加下面内容参数根据自己情况填写s3: access_key: AWS访问密钥 secret_key: AWS SECRET KEY bucket: 存储桶BUCKET名称 region: us-east-1 path: /some/path/in/bucket 备份路径2上传备份uploadclickhouse-backup upload 2024-03-08T07-32-37**注意**使用clickhouse-backup upload 上传备份可以使用参数 --diff-from将文件与以前的本地备份进行比较仅上载新的/更改的文件。必须保留先前的备份以便从新备份中进行还原3下载备份downloadclickhouse-backup download 2024-03-08T07-32-376、查看备份文件clickhouse-backup list #2024-03-08T07-32-37 244.00MiB 08/03/2024 07:32:37 local7、删除备份文件注意删除时要考虑一下clickhouse-backup delete local 2024-03-08T07-32-378、删除shadow下的临时备份文件注意删除时要考虑一下clickhouse-backup clean三、(自动备份数据)数据恢复1、语法clickhouse-backup restore 备份名NAME: clickhouse-backup restore - Create schema and restore data from backup USAGE: clickhouse-backup restore [-t, --tablesdb.table] [-m, --restore-database-mappingoriginDB:targetDB[,...]] [--partitionspartitions_names] [-s, --schema] [-d, --data] [--rm, --drop] [-i, --ignore-dependencies] [--rbac] [--configs] backup_name OPTIONS: --config value, -c value Config FILE name. (default: /etc/clickhouse-backup/config.yml) [$CLICKHOUSE_BACKUP_CONFIG] --table value, --tables value, -t value Restore only database and objects which matched with table name patterns, separated by comma, allow ? and * as wildcard --restore-database-mapping value, -m value Define the rule to restore data. For the database not defined in this struct, the program will not deal with it. --partitions partition_id Restore backup only for selected partition names, separated by comma If PARTITION BY clause returns numeric not hashed values for partition_id field in system.parts table, then use --partitionspartition_id1,partition_id2 format If PARTITION BY clause returns hashed string values, then use --partitions(non_numeric_field_value_for_part1),(non_numeric_field_value_for_part2) format If PARTITION BY clause returns tuple with multiple fields, then use --partitions(numeric_value1,string_value1,date_or_datetime_value),(...) format Values depends on field types in your table, use single quotes for String and Date/DateTime related types Look at the system.parts partition and partition_id fields for details https://clickhouse.com/docs/en/operations/system-tables/parts/ --schema, -s Restore schema only --data, -d Restore data only --rm, --drop Drop exists schema objects before restore -i, --ignore-dependencies Ignore dependencies when drop exists schema objects --rbac, --restore-rbac, --do-restore-rbac Restore RBAC related objects --configs, --restore-configs, --do-restore-configs Restore clickhouse-server CONFIG related files --rbac-only Restore RBAC related objects only, will skip backup data, will backup schema only if --schema added --configs-only Restore clickhouse-server configuration files only, will skip backup data, will backup schema only if --schema added重要选项table 只恢复特定表可以用正则如还原指定数据库–tabledbname.*schema 只还原表结构data 只还原数据2、全库恢复clickhouse-backup restore /var/lib/clickhouse/backup/备份名 #需要保证原来的库完全被删除否则可能会报错,默认会去/var/lib/clickhouse/backup路径下去找备份文件夹注意恢复过程可能会存在这个报错warn can’t create table ‘test.t_new’: code: 57, message: Directory for table data store/05c/05cbcdec-fa1c-4cf9-98d6-98868b16fa38/ already exists, will try again backup2024-03-11T02-06-53 operationrestore解决方法需要删除/var/lib/clickhouse/backup/2024-03-11T02-06-53/metadata/test/t_new.json中的UUID 05cbcdec-fa1c-4cf9-98d6-98868b16fa38’删除掉,重新执行clickhouse-backup restore /var/lib/clickhouse/backup/备份名。#vim /var/lib/clickhouse/backup/2024-03-11T02-06-53/metadata/test/t_new.json { table: t_new, database: test, parts: { default: [ { name: 202103_1_1_0 }, { name: 202203_2_2_0 } ] }, query: CREATE TABLE test.t_new (id Int64 COMMENT 订单id, datetime DateTime COMMENT 订单日期, name String COMMENT 商品名称, price Decimal(9, 2) COMMENT 商品价格, user_id Int64 COMMENT 用户id) ENGINE MergeTree PARTITION BY toYYYYMM(datetime) ORDER BY id SETTINGS index_granularity 8192, size: { default: 2248 }, total_bytes: 1328, metadata_only: false }3、只恢复表结构#使用 --schema 恢复表的表结构 clickhouse-backup restore 备份名 --table 库名.表名 --schema4、只恢复数据注意:由于是ATTACH PARTITION操作如果执行2次的话数据会翻倍#用 --data 恢复表中数据注意:由于是ATTACH PARTITION操作如果执行2次的话数据会翻倍 clickhouse-backup restore 备份名 --table 库名.表名 --data 还原测试数据可参考 #创建库 create database if not exists test; #创建表 create table if not exists test.t_order ( id Int64 COMMENT 订单id, datetime DateTime COMMENT 订单日期, name String COMMENT 手办名称, price Decimal32(2) COMMENT 手办价格, user_id Int64 COMMENT 用户id ) engine MergeTree partition by toYYYYMM(datetime) order by id ; #插入数据 insert into t_order values (1, 2022-03-08 12:40:00, 欧阳锋, 999.99, 202203080001) insert into t_order values (2, 2023-03-08 12:44:00, 梅超风, 1111, 202303080002) insert into t_order values (3, 2022-03-08 12:45:00, 黄蓉, 8989, 202203080001) #用全库备份先预留一份再进行下面操作 #删除数据 alter table t_order delete where id 2; #清空表 truncate table t_order; #删除表 drop table t_order; #删除库 drop database test;四、手动备份数据ClickHouse通过其 ALTER TABLE…FREEZE 功能提供了对即时时间点备份的本地支持。1、确保/var/lib/clickhouse/shadow/目录为空默认目录ls /var/lib/clickhouse/shadow/2、执行备份表命令echo -n alter table 库名.表名 freeze | clickhouse-client --password XXXXXX3、保存备份cd /var/lib/clickhouse/ mkdir backup cp -r shadow/ backup/表名_202403084、清空shadow目录rm -rf /var/lib/clickhouse/shadow/*5、注意备份表结构手动备份数据的时候只恢复数据不恢复表结构所以也需要提前将表结构备份出来。cp -r /var/lib/clickhouse/metadata/库名/表名.sql /var/lib/clickhouse/backup vim /var/lib/clickhouse/backup/表名.sql #需要进行两处修改 #1、需要将ATTACH替换成CREATE #2、需要将 _ UUID 2fbd6c71-1ccc-47eb-b767-e6580bf18e47替换成表名五、手动备份数据数据恢复1、表被删除的话创建表#执行上面备份的sql文件clickhouse-client --passwordXXXXX --database库名 -m -n --query$(cat /var/lib/clickhouse/backup/表名.sql)2、将backup/表名_20240308备份复制到表的“ detached”目录中cp -r /var/lib/clickhouse/backup/表名_20240308/1/store/b75/b7506b44-4286-4cd8-9925-d17fa0e0fa0b/* /var/lib/clickhouse/data/库名/表名/detached/3、重新给/var/lib/clickhouse授权chown -R clickhouse.clickhouse /var/lib/clickhouse4、附上分离的零件#注意这里需要选择一下分区分区的选择可以根据手动备份的目录名进行获取例如/var/lib/clickhouse/backup/表名_20240308/1/store/b75/b7506b44-4286-4cd8-9925-d17fa0e0fa0b/202103_1_1_0,就是在202103分区手动备份是按照分区来进行恢复的并不是一次性都恢复。 echo alter table 库名.表名 attach partition 202103 | clickhouse-client --passwordXXXXXX5、确认数据已还原echo select count() from 库名.表名 | clickhouse-client --passwordXXXXXX六、编写shell脚本设置定时任务1、自动备份脚本#创建存放脚本的目录 mkdir -pv /var/lib/clickhouse/script #编写自动备份脚本 #vim /var/lib/clickhouse/script/ch_full_backup.sh #!/bin/bash /usr/local/bin/clickhouse-backup create ch_full_backup_date %Y-%m-%dT%H-%M-%S2、设置定时任务#每天凌晨1点进行备份 0 1 * * * sh /var/lib/clickhouse/script/ch_full_backup.sh原文链接https://blog.csdn.net/qq_43510111/article/details/136570850