Clickhouse備份與恢復(fù)+異機(jī)遠(yuǎn)程備份腳本
作者：運(yùn)維小弟
Clickhouse 的數(shù)據(jù)備份有多種方式，其官方網(wǎng)站上就提供了多種備份方式可以參考，但是不同公司有不同的可用資源和業(yè)務(wù)需求，因此不存在一個(gè)通用的解決方案可以應(yīng)對各種情況下的ClickHouse備份和恢復(fù)。
Clickhouse 的數(shù)據(jù)備份有多種方式，其官方網(wǎng)站上就提供了多種備份方式可以參考，但是不同公司有不同的可用資源和業(yè)務(wù)需求，因此不存在一個(gè)通用的解決方案可以應(yīng)對各種情況下的ClickHouse備份和恢復(fù)。 適用于 1GB 數(shù)據(jù)的方案可能并不適用于幾十 PB 數(shù)據(jù)的情況。 有多種具備各自優(yōu)缺點(diǎn)的可能方法，將在下面對其進(jìn)行討論。最好使用幾種方法而不是僅僅使用一種方法來彌補(bǔ)它們的各種缺點(diǎn)。
背景：
某項(xiàng)目clickhouse 數(shù)據(jù)庫遷移，數(shù)據(jù)量大約9億條單表，版本為clickhouse20，遷移前我先在本地做了一些列的備份測試，下面我把我在生產(chǎn)中選用的備份方法和遇到的種種小插曲和大家分享下
實(shí)驗(yàn)環(huán)境：
官網(wǎng)下載的demo數(shù)據(jù)，大約有8G的測試數(shù)據(jù)
clickhouse 20.4.6
一、 文本文件導(dǎo)入導(dǎo)出
#說白了就是手動(dòng)的把數(shù)據(jù)庫里的數(shù)據(jù)導(dǎo)出成特定的格式，再導(dǎo)入;這種方式!!不推薦!!!!除非你的數(shù)據(jù)庫真的很少，我當(dāng)時(shí)從官網(wǎng)下載了一個(gè)demo 數(shù)據(jù)，手動(dòng)導(dǎo)入導(dǎo)出，差點(diǎn)把我累死
導(dǎo)出：
- clickhouse-client --password helloword --query="select * from iot_dev.t_indicator_record FORMAT CSV" > iot_dev.t_indicator_record.csv
導(dǎo)入: 注意FORMAT后面大寫
- cat iot_dev.t_indicator_record.csv | clickhouse-client --port 9008 --password helloword --query="INSERT INTO iot_dev.t_indicator_record FORMAT CSV"
二、 拷貝數(shù)據(jù)目錄
#此方案和mysql 的冷數(shù)據(jù)恢復(fù)是一個(gè)方案，直接拷貝走clickhouse 的數(shù)據(jù)到另一臺機(jī)器上，修改下相關(guān)配置就可以直接啟動(dòng)了，仔細(xì)的觀察一下 ClickHouse 在文件系統(tǒng)上的目錄結(jié)構(gòu)(配置文件
/ect/clickhouse-server/config.xml 里面配置的
- .
- ├── data
- │ ├── default
- │ ├── system
- │ │ ├── asynchronous_metric_log
- │ │ ├── metric_log
- │ │ ├── query_log
- │ │ ├── query_thread_log
- │ │ └── trace_log
- ├── metadata
- │ ├── default
- │ │ └── v_table_size.sql
- │ ├── default.sql
- │ ├── system
- │ │ ├── asynchronous_metric_log.sql
- │ │ ├── metric_log.sql
- │ │ ├── query_log.sql
- │ │ ├── query_thread_log.sql
- │ │ └── trace_log.sql
- data 目錄里保存的是數(shù)據(jù)，每個(gè)數(shù)據(jù)庫一個(gè)目錄，內(nèi)部每個(gè)表一個(gè)子目錄。
- metadata 目錄里保存的是元數(shù)據(jù)，即數(shù)據(jù)庫和表結(jié)構(gòu)。其中
- <database>.sql 是 創(chuàng)建數(shù)據(jù)庫的 DDL（ATTACH DATABASE default ENGINE = Ordinary）
- <database>/<table>.sql 是建表的 DDL (ATTACH TABLE ...)
基于這個(gè)信息，直接把data和metadata目錄(要排除 system 庫)復(fù)制到新集群，即可實(shí)現(xiàn)數(shù)據(jù)遷移
實(shí)操步驟：
- 1、停止原先的clickhouse數(shù)據(jù)庫，并打包好 對應(yīng)數(shù)據(jù)庫或表的 data 和 metadata 數(shù)據(jù)
- 2、拷貝到目標(biāo)clickhouse數(shù)據(jù)庫對應(yīng)的目錄，比如/var/lib/clickhouse 目錄下
- 3、給clickhouse 賦予權(quán)限, chown -Rf clickhouse:clickhouse /var/lib/clickhouse/*
- chown -Rf clickhouse:clickhouse /var/lib/clickhouse
- 4、重啟目標(biāo)clickhouse數(shù)據(jù)庫
- 5、驗(yàn)證數(shù)據(jù)
- select count(1) form iot_dev.t_indicator_record;
三、 使用第三方工具，clickhouse-backup
此方案，是我在測試的過程中使用最方便的備份與恢復(fù)工具，因此我會(huì)詳細(xì)的說下此工具的使用方法及需要注意的地方。
clickhouse-backup 是社區(qū)開源的一個(gè) ClickHouse 備份工具，可用于實(shí)現(xiàn)數(shù)據(jù)遷移。其原理是先創(chuàng)建一個(gè)備份，然后從備份導(dǎo)入數(shù)據(jù)，類似 MySQL 的 mysqldump + SOURCE。這個(gè)工具可以作為常規(guī)的異地冷備方案
# 使用限制：
- 支持1.1.54390以上的ClickHouse
- 僅MergeTree系列表引擎
- 不支持備份Tiered storage或storage_policy
- 云存儲上的最大備份大小為5TB
- AWS S3上的parts數(shù)最大為10,000
(1)、下載clickhouse-backup 軟件包
官方提供了二進(jìn)制版本和rpm包的方式，我這里采用了二進(jìn)制安裝
- github地址： https://github.com/AlexAkulov/clickhouse-backup
- 下載地址： https://github.com/AlexAkulov/clickhouse-backup/releases/download/v1.0.0/clickhouse-backup.tar.gz
- #解壓軟件包
- tar -xf clickhouse-backup.tar.gz -C /root
- #復(fù)制二進(jìn)制文件到系統(tǒng)中
- cp /root/clickhouse-backup /usr/bin
- #創(chuàng)建clickhouse-backup 配置文件目錄
- mkdir -p /etc/clickhouse-backup
- #拷貝模板配置文件到clickhouse-backup 配置文件目錄下
- cp /root/config.yml /etc/clickhouse-backup/
(2)、修改clickhouse-backup 配置文件config.yml
# 根據(jù)clickhouse自身的配置來修改 此配置文件，比如 clickhouse的數(shù)據(jù)目錄，數(shù)據(jù)庫密碼，監(jiān)控地址及端口
官方的配置說明：
clickhouse-backup 除了備份到本機(jī)，此外還支持遠(yuǎn)程備份的方式，備份到s3 上【對象存儲】，ftp，sftp 上，還支持 使用 api 接口 訪問
- general:
- remote_storage: none # REMOTE_STORAGE，遠(yuǎn)程備份的方式，對應(yīng)下面的【azblo，s3，gcs，api，ftp，sftp】
- max_file_size: 107374182400 # MAX_FILE_SIZE
- disable_progress_bar: false # DISABLE_PROGRESS_BAR
- backups_to_keep_local: 0 # BACKUPS_TO_KEEP_LOCAL， 本地備份保留個(gè)數(shù) ，0為不限制
- backups_to_keep_remote: 0 # BACKUPS_TO_KEEP_REMOTE，遠(yuǎn)程的備份保留個(gè)數(shù)，0為不限制
- log_level: info # LOG_LEVEL，日志等級
- allow_empty_backups: false # ALLOW_EMPTY_BACKUPS
- clickhouse:
- username: default # CLICKHOUSE_USERNAME，數(shù)據(jù)庫用戶名，默認(rèn)
- password: "" # CLICKHOUSE_PASSWORD，數(shù)據(jù)庫密碼
- host: localhost # CLICKHOUSE_HOST，數(shù)據(jù)庫實(shí)例地址
- port: 9000 # CLICKHOUSE_PORT，數(shù)據(jù)庫連接端口
- disk_mapping: {} # CLICKHOUSE_DISK_MAPPING
- skip_tables: # CLICKHOUSE_SKIP_TABLES ，備份時(shí)，需要忽略的庫表
- - system.*
- timeout: 5m # CLICKHOUSE_TIMEOUT
- freeze_by_part: false # CLICKHOUSE_FREEZE_BY_PART
- secure: false # CLICKHOUSE_SECURE
- skip_verify: false # CLICKHOUSE_SKIP_VERIFY
- sync_replicated_tables: true # CLICKHOUSE_SYNC_REPLICATED_TABLES
- log_sql_queries: true # CLICKHOUSE_LOG_SQL_QUERIES
- config_dir: "/etc/clickhouse-server" # CLICKHOUSE_CONFIG_DIR
- restart_command: "systemctl restart clickhouse-server" # CLICKHOUSE_RESTART_COMMAND
- azblob:
- endpoint_suffix: "core.windows.net" # AZBLOB_ENDPOINT_SUFFIX
- account_name: "" # AZBLOB_ACCOUNT_NAME
- account_key: "" # AZBLOB_ACCOUNT_KEY
- sas: "" # AZBLOB_SAS
- container: "" # AZBLOB_CONTAINER
- path: "" # AZBLOB_PATH
- compression_level: 1 # AZBLOB_COMPRESSION_LEVEL
- compression_format: tar # AZBLOB_COMPRESSION_FORMAT
- sse_key: "" # AZBLOB_SSE_KEY
- s3:
- access_key: "" # S3_ACCESS_KEY，<AWS訪問密鑰>
- secret_key: "" # S3_SECRET_KEY
- bucket: "" # S3_BUCKET，<存儲桶BUCKET名稱>
- endpoint: "" # S3_ENDPOINT
- region: us-east-1 # S3_REGION
- acl: private # S3_ACL
- force_path_style: false # S3_FORCE_PATH_STYLE
- path: "" # S3_PATH ， <備份路徑>
- disable_ssl: false # S3_DISABLE_SSL
- compression_level: 1 # S3_COMPRESSION_LEVEL
- # supports 'tar', 'gzip', 'zstd', 'brotli'
- compression_format: tar # S3_COMPRESSION_FORMAT
- # empty (default), AES256, or aws:kms
- sse: AES256 # S3_SSE
- disable_cert_verification: false # S3_DISABLE_CERT_VERIFICATION
- storage_class: STANDARD # S3_STORAGE_CLASS
- debug: false # S3_DEBUG
- gcs:
- credentials_file: "" # GCS_CREDENTIALS_FILE
- credentials_json: "" # GCS_CREDENTIALS_JSON
- bucket: "" # GCS_BUCKET
- path: "" # GCS_PATH
- compression_level: 1 # GCS_COMPRESSION_LEVEL
- compression_format: tar # GCS_COMPRESSION_FORMAT
- cos:
- url: "" # COS_URL
- timeout: 2m # COS_TIMEOUT
- secret_id: "" # COS_SECRET_ID
- secret_key: "" # COS_SECRET_KEY
- path: "" # COS_PATH
- compression_format: tar # COS_COMPRESSION_FORMAT
- compression_level: 1 # COS_COMPRESSION_LEVEL
- api:
- listen: "localhost:7171" # API_LISTEN
- enable_metrics: true # API_ENABLE_METRICS
- enable_pprof: false # API_ENABLE_PPROF
- username: "" # API_USERNAME
- password: "" # API_PASSWORD
- secure: false # API_SECURE
- certificate_file: "" # API_CERTIFICATE_FILE
- private_key_file: "" # API_PRIVATE_KEY_FILE
- create_integration_tables: false # API_CREATE_INTEGRATION_TABLES
- ftp:
- address: "" # FTP_ADDRESS
- timeout: 2m # FTP_TIMEOUT
- username: "" # FTP_USERNAME
- password: "" # FTP_PASSWORD
- tls: false # FTP_TLS
- path: "" # FTP_PATH
- compression_format: tar # FTP_COMPRESSION_FORMAT
- compression_level: 1 # FTP_COMPRESSION_LEVEL
- debug: false # FTP_DEBUG
- sftp:
- address: "" # SFTP_ADDRESS
- username: "" # SFTP_USERNAME
- password: "" # SFTP_PASSWORD
- key: "" # SFTP_KEY
- path: "" # SFTP_PATH
- compression_format: tar # SFTP_COMPRESSION_FORMAT
- compression_level: 1 # SFTP_COMPRESSION_LEVEL
我本次使用的配置文件，就是最簡單的配置，直接本地備份，然后通過執(zhí)行腳本的方式去推送備份文件到備份主機(jī)上
- [root@localhost clickhouse-backup]# cat config.yml
- general:
- max_file_size: 1099511627776
- disable_progress_bar: false
- backups_to_keep_local: 0
- backups_to_keep_remote: 0
- log_level: info
- allow_empty_backups: false
- clickhouse:
- username: default
- password: ""
- host: localhost
- port: 9000
- disk_mapping: {}
- skip_tables:
- - system.*
- - default.*
- timeout: 5m
- freeze_by_part: false
- secure: false
- skip_verify: false
- sync_replicated_tables: true
- skip_sync_replica_timeouts: true
- log_sql_queries: false
(3)、查看clickhouse-backup 相關(guān)命令
- NAME:
- clickhouse-backup - Tool for easy backup of ClickHouse with cloud support
- USAGE:
- clickhouse-backup <command> [-t, --tables=<db>.<table>] <backup_name>
- VERSION:
- 1.0.0
- DESCRIPTION:
- Run as 'root' or 'clickhouse' user
- COMMANDS:
- tables Print list of tables
- create Create new backup
- create_remote Create and upload
- upload Upload backup to remote storage
- list Print list of backups
- download Download backup from remote storage
- restore Create schema and restore data from backup
- restore_remote Download and restore
- delete Delete specific backup
- default-config Print default config
- server Run API server
- help, h Shows a list of commands or help for one command
- GLOBAL OPTIONS:
- --config FILE, -c FILE Config FILE name. (default: "/etc/clickhouse-backup/config.yml") [$CLICKHOUSE_BACKUP_CONFIG]
- --help, -h show help
- --version, -v print the version
1、 查看全部默認(rèn)的配置項(xiàng)
- clickhouse-backup default-config
2、 查看可備份的表【已在配置文件中過濾掉system和default 庫下面的所有表】
- [root@localhost clickhouse-backup]# clickhouse-backup tablesbrdatasets.hits_v1 1.50GiB default
3、 創(chuàng)建備份
#全庫備份
- clickhouse-backup create
- 2021/09/06 10:03:23 info done backup=2021-09-06T14-03-23 operation=create table=datasets.hits_v1
- 2021/09/06 10:03:23 info done backup=2021-09-06T14-03-23 operation=create
備份存儲在中 $data_path/backup 下，備份名稱默認(rèn)為時(shí)間戳，可手動(dòng)指定備份名稱
- clickhouse-backup create fxkjnj_0906
備份包含兩個(gè)目錄：
- metadata目錄: 包含重新創(chuàng)建所需的DDL SQL
- shadow目錄: 包含作為ALTER TABLE ... FREEZE操作結(jié)果的數(shù)據(jù)
- [root@localhost backup]# ll /var/lib/clickhouse/backup/2021-09-06T14-03-23/
- total 4
- drwxr-x--- 3 clickhouse clickhouse 22 Sep 6 10:03 metadata
- -rw-r----- 1 clickhouse clickhouse 865 Sep 6 10:03 metadata.json
- drwxr-x--- 3 clickhouse clickhouse 22 Sep 6 10:03 shadow
#單表備份
- clickhouse-backup create [-t, --tables=<db>.<table>] <backup_name>
備份表datasets.hits_v1
- clickhouse-backup create -t datasets.hits_v1
備份多個(gè)表datasets.hits_v1， datasets.hits_v2
- clickhouse-backup create -t datasets.hits_v1,datasets.hits_v2
4、查看備份記錄
- [root@localhost datasets]# clickhouse-backup list
- 2021-09-06T14-03-23 1.50GiB 06/09/2021 14:03:23 local
- 2021/09/06 10:10:50 error InvalidParameter: 1 validation error(s) found.
- - minimum field size of 1, ListObjectsV2Input.Bucket.
5、刪除備份文件
- [root@localhost datasets]# clickhouse-backup delete local 2021-09-06T14-03-23
(4)、數(shù)據(jù)恢復(fù)
語法：
clickhouse-backup restore 備份名
- [root@localhost shadow]# clickhouse-backup restore --help
- NAME:
- clickhouse-backup restore - Create schema and restore data from backup
- USAGE:
- clickhouse-backup restore [-t, --tables=<db>.<table>] [-s, --schema] [-d, --data] [--rm, --drop] <backup_name>
- OPTIONS:
- --config FILE, -c FILE Config FILE name. (default: "/etc/clickhouse-backup/config.yml") [$CLICKHOUSE_BACKUP_CONFIG]
- --table value, --tables value, -t value
- --schema, -s Restore schema only
- --data, -d Restore data only
- --rm, --drop Drop table before restore
參數(shù)：
- --table 只恢復(fù)特定表，可使用正則。如針對特定的數(shù)據(jù)庫：--table=dbname.*
- --schema 只還原表結(jié)構(gòu)
- --data 只還原數(shù)據(jù)
- --rm 數(shù)據(jù)恢復(fù)之前，先刪除需要恢復(fù)的表
四、使用clickhouse-backup備份與恢復(fù)數(shù)據(jù)-實(shí)戰(zhàn)
4.1 、本機(jī)備份與恢復(fù)
1、備份前，先查看數(shù)據(jù)庫里的數(shù)據(jù)量，一共是17747796條數(shù)據(jù)
- 1、備份前，先查看數(shù)據(jù)庫里的數(shù)據(jù)量，一共是17747796條數(shù)據(jù)
- [root@localhost ~]# clickhouse-client
- ClickHouse client version 20.4.6.53 (official build).
- Connecting to localhost:9000 as user default.
- Connected to ClickHouse server version 20.4.6 revision 54434.
- localhost :)
- localhost :) select count(1) from datasets.hits_v1;
- SELECT count(1)
- FROM datasets.hits_v1
- ┌─count(1)─┐
- │ 17747796 │
- └──────────┘
- 1 rows in set. Elapsed: 0.016 sec.
- localhost :) exit
- 2、創(chuàng)建clickhouse-backup配置文件，本地備份文件最多保留7個(gè)
- [root@localhost ~]# vim /etc/clickhouse-backup/config.yml
- general:
- max_file_size: 1099511627776
- disable_progress_bar: false
- backups_to_keep_local: 7
- log_level: info
- allow_empty_backups: false
- clickhouse:
- username: default
- password: "helloword"
- host: localhost
- port: 9000
- disk_mapping: {}
- skip_tables:
- - system.*
- - default.*
- timeout: 5m
- freeze_by_part: false
- secure: false
- skip_verify: false
- sync_replicated_tables: true
- skip_sync_replica_timeouts: true
- log_sql_queries: false
- 3、查看可備份的表
- [root@localhost backup]# clickhouse-backup tables
- datasets.hits_v1 1.50GiB default
- 4、使用clickhouse-backup 做個(gè)備份
- [root@localhost clickhouse]# clickhouse-backup create
- 2021/09/06 10:50:42 info done backup=2021-09-06T14-50-42 operation=create table=datasets.hits_v1
- 2021/09/06 10:50:42 info done backup=2021-09-06T14-50-42 operation=create
- 5、模擬進(jìn)行數(shù)據(jù)破壞，?。?！生產(chǎn)環(huán)境勿用?。?！此處為測試
- [root@localhost clickhouse]# clickhouse-client
- ClickHouse client version 20.4.6.53 (official build).
- Connecting to localhost:9000 as user default.
- Connected to ClickHouse server version 20.4.6 revision 54434.
- localhost :) drop database datasets;
- DROP DATABASE datasets
- Ok.
- 0 rows in set. Elapsed: 0.002 sec.
- SHOW DATABASES
- ┌─name───────────────────────────┐
- │ _temporary_and_external_tables │
- │ default │
- │ system
- │ test
- └────────────────────────────────┘
- 3 rows in set. Elapsed: 0.002 sec.
- 6、恢復(fù)數(shù)據(jù)庫
- [root@localhost backup]# clickhouse-backup restore 2021-09-06T14-50-42 -s -d --rm
- 2021/09/06 10:57:04 info done backup=2021-09-06T14-50-42 operation=restore table=datasets.hits_v1
- 2021/09/06 10:57:04 info done backup=2021-09-06T14-50-42 operation=restore
- 7、驗(yàn)證數(shù)據(jù)庫，查看數(shù)據(jù)是否復(fù)原，數(shù)據(jù)量是否匹配
- [root@localhost backup]# clickhouse-client
- ClickHouse client version 20.4.6.53 (official build).
- Connecting to localhost:9000 as user default.
- Connected to ClickHouse server version 20.4.6 revision 54434.
- localhost :) show databases;
- SHOW DATABASES
- ┌─name───────────────────────────┐
- │ _temporary_and_external_tables │
- │ datasets │
- │ default │
- │ system │
- │ test │
- └────────────────────────────────┘
- 5 rows in set. Elapsed: 0.002 sec.
- localhost :) select count(1) from datasets.hits_v1;
- SELECT count(1)
- FROM datasets.hits_v1
- ┌─count(1)─┐
- │ 17747796 │
- └──────────┘
- 1 rows in set. Elapsed: 0.020 sec.
- localhost :)
4.2、異機(jī)遠(yuǎn)程備份與恢復(fù)
所謂異機(jī)遠(yuǎn)程備份，就是指將備份好的文件，同步傳輸?shù)竭h(yuǎn)程 專業(yè)的存儲服務(wù)器，往往這些存儲服務(wù)器都是大存儲，可以備份存儲1年甚至幾年的數(shù)據(jù)庫備份都沒問題
環(huán)境：
clickhouse 數(shù)據(jù)庫 192.168.99.102
存儲服務(wù)器 192.168.99.101
- 1、備份前，先查看數(shù)據(jù)庫里的數(shù)據(jù)量，一共是17747796條數(shù)據(jù)
- [root@localhost ~]# clickhouse-client
- ClickHouse client version 20.4.6.53 (official build).
- Connecting to localhost:9000 as user default.
- Connected to ClickHouse server version 20.4.6 revision 54434.
- localhost :)
- localhost :) select count(1) from datasets.hits_v1;
- SELECT count(1)
- FROM datasets.hits_v1
- ┌─count(1)─┐
- │ 17747796 │
- └──────────┘
- 1 rows in set. Elapsed: 0.016 sec.
- localhost :) exit
- 2、創(chuàng)建clickhouse-backup配置文件，本地備份文件最多保留7個(gè)
- [root@localhost ~]# vim /etc/clickhouse-backup/config.yml
- general:
- max_file_size: 1099511627776
- disable_progress_bar: false
- backups_to_keep_local: 7
- log_level: info
- allow_empty_backups: false
- clickhouse:
- username: default
- password: "helloword"
- host: localhost
- port: 9000
- disk_mapping: {}
- skip_tables:
- - system.*
- - default.*
- timeout: 5m
- freeze_by_part: false
- secure: false
- skip_verify: false
- sync_replicated_tables: true
- skip_sync_replica_timeouts: true
- log_sql_queries: false
- 3、查看可備份的表
- [root@localhost backup]# clickhouse-backup tables
- datasets.hits_v1 1.50GiB default
- 4、使用clickhouse-backup 做個(gè)備份
- [root@localhost clickhouse]# clickhouse-backup create
- 2021/09/06 10:50:42 info done backup=2021-09-06T14-50-42 operation=create table=datasets.hits_v1
- 2021/09/06 10:50:42 info done backup=2021-09-06T14-50-42 operation=create
- 5、使用SCP 將備份文件，傳輸?shù)酱鎯Ψ?wù)器上
- [root@localhost clickhouse]# scp -rp /var/lib/clickhouse/backup/2021-09-06T14-50-42 fxkjnj@192.168.99.101:/data/clickhouse-backup/
- 6、登錄到存儲服務(wù)器 ，查看備份數(shù)據(jù)是否存在
- [root@localhost clickhouse]# ll /data/clickhouse-backup/2021-09-06T14-50-42
- total 4
- drwxr-x--- 3 clickhouse clickhouse 22 Sep 6 10:50 metadata
- -rw-r----- 1 clickhouse clickhouse 865 Sep 6 10:50 metadata.json
- drwxr-x--- 3 clickhouse clickhouse 22 Sep 6 10:50 shadow
五、使用腳本定期異機(jī)遠(yuǎn)程備份
環(huán)境： clickhouse 數(shù)據(jù)庫 192.168.99.102 存儲服務(wù)器 192.168.99.101
條件：
- 存儲服務(wù)器 建立備份目錄，/data/clickhouse-back
- clickhouse 數(shù)據(jù)庫 可以免密到 存儲服務(wù)器 上，免密傳輸備份文件
- vim /var/lib/clickhouse/clickhouse-backup.sh
- #!/bin/bash
- #Author fxkjnj.com
- ####################################################
- ##
- ## clickhouse-back script
- ## backup data at remote host
- ## you should config ssh trust
- ##
- ####################################################
- MSNAME=yz
- BAKFILE=$MSNAME-`date +%Y%m%d%H%M%S`
- LOCAL_BAKDIR=/var/lib/clickhouse/backup
- REMOTE_BAKDIR=/data/clickhouse-back
- REMOTE_HOST=root@192.168.99.101
- #備份到本地
- /usr/bin/clickhouse-backup create $BAKFILE
- if [[ $? != 0 ]]; then
- echo "clickhouse-backup Create FAILED" > /var/log/clickhouse-backup.log
- exit
- else
- #SCP備份到遠(yuǎn)程主機(jī)
- scp -rp $LOCAL_BAKDIR/$BAKFILE $REMOTE_HOST:$REMOTE_BAKDIR/
- if [[ $? != 0 ]]; then
- echo "clickhouse-backup FAILED" > /var/log/clickhouse-backup.log
- else
- echo "clickhouse-backup successful" > /var/log/clickhouse-backup.log
- fi
- fi
- #定期刪除遠(yuǎn)程備份文件
- ssh $REMOTE_HOST "find $REMOTE_BAKDIR/yz* -maxdepth 0 -mtime +30 -type d | xargs rm -rf {}"
六、常見問題
1、問題現(xiàn)象：使用clickhouse-backup 恢復(fù)數(shù)據(jù)時(shí)，提示UUID 問題
- clickhouse-backup restore 2021-08-21T06-35-10 -s -d --rm
- 2021/08/21 14:40:51 error can't create table `default`.`t`: code: 57, message:
- Directory for table data store/c57/c5780d8a-7d5a-47a3-8578-0d8a7d5a37a3/ already exists after 1 times, please check your schema depencncies
解決方法：
- 去掉 備份文件中 ${backup_path}/2021-08-21T06-35-10/metadata/default/t.json 中的UUID
- UUID '80ea6411-9c37-4d47-80ea-64119c374d47'
- 再次執(zhí)行恢復(fù)
- clickhouse-backup restore 2021-08-21T06-35-10 -s -d --rm
- SELECT count(1)
- FROM datasets.hits_v1
- ┌─count(1)─┐
- │ 17747796 │
- └──────────┘
- 1 rows in set. Elapsed: 0.016 sec.
- localhost :) exit
責(zé)任編輯：姜華
來源：
今日頭條
