Clickhouse备份恢复_Docker环境下的clickhouse如何备份恢复

总结：
Docker环境的下的clickhouse备份，不能使用clickhouse-backup，因为clickhouse-client只能备份Docker环境下的clickhouse的元数据
Docker环境的下的clickhouse备份，可以使用TCP的clickhouse-client的9000或HTTP的8123连接clickhouse服务器后使用backup\restore命令来备份\恢复



[dailachdbud005 ~]# docker ps
CONTAINER ID   IMAGE                                             COMMAND                  CREATED         STATUS      PORTS                                                                                                NAMES  
197a50985f73   clickhouse/clickhouse-server:22.9.3               "/entrypoint.sh"         16 months ago   Up 9 days   0.0.0.0:8123->8123/tcp, :::8123->8123/tcp, 0.0.0.0:9000->9000/tcp, :::9000->9000/tcp, 9009/tcp       root-clickhouse-1
[dailachdbud005 ~]# ps -ef|grep click|grep -v grep
101        2330   2260  6 Jun02 ?        14:52:20 /usr/bin/clickhouse-server --config-file=/etc/clickhouse-server/config.xml

[dailachdbud005 ~]# clickhouse-client -h dailachdbud005 -u onescore --password '123456' --port 9000
select name,path,type from system.disks;
197a50985f73 :) select name,path,type from system.disks;
   ┌─name────┬─path─────────────────┬─type──┐
1. │ backups │ /backups/            │ local │
2. │ default │ /var/lib/clickhouse/ │ local │
   └─────────┴──────────────────────┴───────┘
查看到/backups对应的宿主服务器目录为/var/lib/docker/volumes/root_clickhouse_backup/_data
[dailachdbud005 ~]# docker inspect 197a50985f73 | grep backups -B 3 -A 3
                {
                    "Type": "volume",
                    "Source": "root_clickhouse_backup",
                    "Target": "/backups",
                    "VolumeOptions": {}
                }
            ],
--
                "Type": "volume",
                "Name": "root_clickhouse_backup",
                "Source": "/var/lib/docker/volumes/root_clickhouse_backup/_data",
                "Destination": "/backups",
                "Driver": "local",
                "Mode": "z",
                "RW": true,
--
            "Cmd": null,
            "Image": "clickhouse/clickhouse-server:22.9.3",
            "Volumes": {
                "/backups": {},
                "/etc/clickhouse-server/config.d": {},
                "/etc/clickhouse-server/users.d": {},
                "/var/lib/clickhouse": {}



clickhouse-backup
实践过只能备份元数据，因为clickhouse-backup远程备份，只能备份元数据
[odsonescoredev3 /]# cat /etc/clickhouse-backup/config.yml
general:
  remote_storage: none
  max_file_size: 1099511627776
  disable_progress_bar: false
  backups_to_keep_local: 3
  backups_to_keep_remote: 15
  log_level: info
  allow_empty_backups: false
clickhouse:
  username: onescore
  password: "123456"
  host: localhost
  port: 9000
  disk_mapping: {}
  skip_tables:
  - system.*
  - default.*
  - information_schema.*
  - INFORMATION_SCHEMA.*
  timeout: 5m
  freeze_by_part: false
  secure: false
  skip_verify: false
  sync_replicated_tables: true
  skip_sync_replica_timeouts: true
  log_sql_queries: false
sftp:
  address: "127.0.0.1"
  username: "root"
  password: "D123"
  port: 22
  key: ""
  path: "/mnt/datadomaindir/clickhouse_backup/Dev/ODS1SCHFBDMDEV"
  concurrency: 1
  compression_format: none
  debug: false



[odsonescoredev3 /]# df -h
Filesystem                                                                                          Size  Used Avail Use% Mounted on
devtmpfs                                                                                            7.8G     0  7.8G   0% /dev
tmpfs                                                                                               7.8G   22M  7.8G   1% /dev/shm
tmpfs                                                                                               7.8G  755M  7.1G  10% /run
tmpfs                                                                                               7.8G     0  7.8G   0% /sys/fs/cgroup
/dev/mapper/centos-root                                                                             253G   82G  171G  33% /
/dev/sda1                                                                                           3.8G  233M  3.5G   7% /boot
overlay                                                                                             253G   82G  171G  33% /var/lib/docker/overlay2/d3d9d9a71c2c9553ba3646b987d7b4e30363c2288e8098ace3d8e35aabfd2cd5/merged
overlay                                                                                             253G   82G  171G  33% /var/lib/docker/overlay2/f2bc5bec233a9f6341729d53db0473ed605a72a80ad6b8bd7ffb54b004c81803/merged
overlay                                                                                             253G   82G  171G  33% /var/lib/docker/overlay2/8f4af2f381fc2ed01f9ec3cf7d0c5637d1016f7b0d91cd0f6a84d6e8fb01d40e/merged
overlay                                                                                             253G   82G  171G  33% /var/lib/docker/overlay2/b78e207587d76ebb9fd3969bfb120faf257572d241948e5d5a1632c3efc04349/merged
overlay                                                                                             253G   82G  171G  33% /var/lib/docker/overlay2/b9a729c56ab4c25055feb2d19f1dedeb6921cfa37e7194d5da68b4aaab7230a4/merged
tmpfs                                                                                               1.6G     0  1.6G   0% /run/user/0

[odsonescoredev3 /]# ll /var/lib/clickhouse
total 8
drwxr-x--- 2 101 101 116 Jul 12  2022 access
drwxr-x--- 3 101 101  22 May 13 23:19 backup
drwxr-x--- 4 101 101  35 Jul 12  2022 data
drwxr-x--- 2 101 101   6 Jul 12  2022 dictionaries_lib
drwxr-x--- 2 101 101   6 Jul 12  2022 flags
drwxr-xr-x 2 101 101   6 Jul 12  2022 format_schemas
drwxr-x--- 4 101 101 184 Jul 12  2022 metadata
drwxr-x--- 2 101 101