Clickhouse 的备份

Clickhouse 的备份有全库备份和单表备份

全库备份需借助第三方工具， clickhouse-backup
单表备份直接使用官方命令。

clickhouse-backup

1 install
可以直接下载安装包或者去 github 上下载

https://github.com/AlexAkulov/clickhouse-backup

2 修改配置文件
/etc/clickhouse-backup/config.yml

  general:
  remote_storage: none
  max_file_size: 1073741824
  disable_progress_bar: true
  backups_to_keep_local: 7
  backups_to_keep_remote: 0
  log_level: debug
  allow_empty_backups: false
  download_concurrency: 1
  upload_concurrency: 1
  restore_schema_on_cluster: ""
  upload_by_part: true
  download_by_part: true
clickhouse:
  username: chuser
  password: "YouPassword"
  host: 192.168.1.203
  port: 9000
  skip_tables:
  - system.*
  - INFORMATION_SCHEMA.*
  - information_schema.*
  timeout: 5m
  freeze_by_part: false
  secure: false
  skip_verify: false
  sync_replicated_tables: false
  log_sql_queries: false
  ignore_not_exists_error_during_freeze: true
  debug: false

3 如果 clickhouse 是 docker 形式运行，需要创建一个 softlink ，否则备份时会报错

2023/03/16 11:06:30.246750 error stat /var/lib/clickhouse/: no such file or directory

cd /var/lib/
ln -s /dataDisk/clickhouse_database clickhouse

4 backup or restore

clickhouse-backup  create  -c /etc/clickhouse-backup/config.yml -t pre1.*  202303161155

clickhouse-backup restore -c /etc/clickhouse-backup/config.yml -t clickhouse_prod.* 20230309120501

单表备份

参考： https://clickhouse.com/docs/en/operations/backup/
1 配置更改
/dataDisk/clickhouse-server/config.d/backup_disk.xml

<clickhouse>
    <storage_configuration>
        <disks>
            <backups>
                <type>local</type>
        <path>/dataDisk/backups/</path>
            </backups>
        </disks>
    </storage_configuration>
    <backups>
        <allowed_disk>backups</allowed_disk>
        <allowed_path>/dataDisk/backups</allowed_path>
    </backups>
</clickhouse>

2 确保 docker 启动时此目录可读写， docker-compose 例子

version: "3"
services:
    clickhouse:
        image: clickhouse/clickhouse-server:22.8.5
        hostname: clickhouse
        container_name: clickhouse
        ports:
            - 9000:9000
            - 8123:8123
            - 9009:9009
            - 9363:9363
        volumes:
                - /dataDisk/clickhouse_database:/var/lib/clickhouse
                - /dataDisk/clickhouse-client:/etc/clickhouse-client
                - /dataDisk/clickhouse-server:/etc/clickhouse-server
                - /dataDisk/clickhouse-log:/var/log/clickhouse-server
                - /dataDisk/backups:/dataDisk/backups
        ulimits:
            nofile:
                soft: 262144
                hard: 262144

3 重启一下 CH 确保此配置生效

4 备份单表

BACKUP TABLE pre1.user_behavior_log TO Disk('backups', 'user_behavior_log.zip')

BACKUP TABLE pre1.user_behavior_log TO Disk('backups', 'user_behavior_log.zip')

Query id: 60cdafb7-ad36-48ce-8805-e6c75e36864d

┌─id───────────────────────────────────┬─status─────────┐
│ 843831e3-7a63-43b2-87ef-31e82e0cbb2b │ BACKUP_CREATED │
└──────────────────────────────────────┴────────────────┘

1 rows in set. Elapsed: 0.059 sec.

SELECT
    *
FROM system.backups
where id='843831e3-7a63-43b2-87ef-31e82e0cbb2b'
FORMAT Vertical

Query id: 2c3e56cc-634e-45ec-9b7d-38aeb5ac5a01

Row 1:
──────
id:                843831e3-7a63-43b2-87ef-31e82e0cbb2b
name:              Disk('backups', 'user_behavior_log.zip')
status:            BACKUP_CREATED
num_files:         581
uncompressed_size: 476752
compressed_size:   324725
error:
start_time:        2023-03-16 12:07:01
end_time:          2023-03-16 12:07:01

1 rows in set. Elapsed: 0.002 sec.

5 restore 单表

RESTORE TABLE pre1.user_behavior_log AS pre1.user_behavior_log FROM Disk('backups', 'user_behavior_log.zip')

restore 到另外一个新表

RESTORE TABLE pre1.user_behavior_log AS pre1.user_behavior_log_1 FROM Disk('backups', 'user_behavior_log.zip')

clickhouse :) select count(*) from user_behavior_log;

SELECT count(*)
FROM user_behavior_log

Query id: 18502e7b-ca3f-4c59-aedd-bc35e8ea8522

┌─count()─┐
│    2003 │
└─────────┘

1 rows in set. Elapsed: 0.002 sec.

clickhouse :) select count(*) from user_behavior_log_1;

SELECT count(*)
FROM user_behavior_log_1

Query id: e76fc2fa-db1b-4ad3-a70d-694b24828f2f

┌─count()─┐
│    2003 │
└─────────┘

1 rows in set. Elapsed: 0.002 sec.

「真诚赞赏，手留余香」

Clickhouse 的备份

clickhouse-backup

单表备份

CATALOG

FEATURED TAGS

FRIENDS