备份恢复与容灾

-- 一个完整备份应包含：
-- 1. 表结构（DDL）
-- 2. 数据（Parts）
-- 3. 分区元数据
-- 4. 访问权限（users/roles/quotas/row policies）
-- 5. 集群配置（config.xml / metadata.xml，通常走配置仓库）

-- 生成全表当前 active Parts 的快照，放到 /var/lib/clickhouse/shadow/N/
ALTER TABLE events FREEZE WITH SNAPSHOT 'backup-20240901';

# 查看 shadow 快照
ls -R /var/lib/clickhouse/shadow/

ALTER TABLE events DETACH PARTITION '202405';
-- 拷贝 detached/202405 到备份目录
cp -r /var/lib/clickhouse/data/default/events/detached/202405 /backup/202405
-- 处理完后重新挂回
ALTER TABLE events ATTACH PARTITION '202405';

# 二进制安装
wget https://github.com/Altinity/clickhouse-backup/releases/latest/download/clickhouse-backup-linux-amd64.tar.gz
tar xzf clickhouse-backup-linux-amd64.tar.gz
sudo mv clickhouse-backup /usr/local/bin/
clickhouse-backup --version

general:
  remote_storage: s3
  max_file_size: 1000000000
clickhouse:
  host: localhost
  port: 9000
  username: default
  password: "***"
  secure: false
s3:
  access_key: "AKIA..."
  secret_key: "***"
  bucket: "ch-backup-bucket"
  path: "/backups"
  region: ap-east-1
  compression_format: tar
backup:
  allow_to_backup_frozen: true
  use_embedded_backup_restore: false
restore:
  allow_to_restore_frozen: true

# 备份全部数据库
clickhouse-backup create

# 只备份指定库表
clickhouse-backup create events --tables=default.events

# 按分区备份
clickhouse-backup create events_part --tables=default.events --partitions=202406

# 查看备份列表
clickhouse-backup list

# 查看备份包含的表
clickhouse-backup tables events

# 上传到对象存储
clickhouse-backup upload events

# 删除本地/远端备份
clickhouse-backup delete events
clickhouse-backup delete_remote events

# 第一次全量
clickhouse-backup create full_0901

# 第二次自动增量（仅新 Parts）
clickhouse-backup create incr_0902

# 查看大小差异
clickhouse-backup list

# 需要指定基准备份链时
clickhouse-backup restore incr_0902 --diff-from=full_0901

# 在每个分片节点上分别执行（只备份本机 local 表）
clickhouse-backup create node_backup --tables=default.events_local

-- 定义备份磁盘（config.xml 中加一块 disk）
-- <disks><backups><type>s3</type><endpoint>...</endpoint></backups></disks>

-- 备份全库（在任意节点执行，ON CLUSTER 分发）
BACKUP DATABASE default ON CLUSTER my_cluster
TO Disk('backups', 'cluster_20240901');

-- 查看备份进度与结果
SELECT status, error FROM system.backup_log
ORDER BY start_time DESC LIMIT 5;

-- 恢复
RESTORE DATABASE default ON CLUSTER my_cluster
FROM Disk('backups', 'cluster_20240901');

-- 模拟事故
DROP TABLE default.events;

# 用 clickhouse-backup 恢复
clickhouse-backup restore events --tables=default.events

-- 恢复后校验
SELECT count(), sum(rows) FROM system.parts
WHERE table = 'events' AND active = 1;
SELECT count() FROM events;

# 用 clickhouse-copier 做跨集群数据同步（官方工具）
clickhouse-copier --config /etc/clickhouse-copier/config.xml --task-id sync_task

# 每日凌晨跑备份的 cron，输出退出码与日志
0 1 * * * /usr/local/bin/clickhouse-backup create daily 2>&1 | tee /var/log/ch-backup.log

# 自动核对备份行数
clickhouse-backup tables daily
clickhouse-backup list

# 每月例行恢复演练（测试集群）
clickhouse-backup restore daily --tables=default.events
clickhouse-client --query "SELECT count() FROM default.events"