ClickHouse的备份和还原

 

首先要指定备份路劲。可以备份在本地，NFS挂载路径，甚至备份在S3上。
配置文件：

?

1	/etc/clickhouse-server/config.d/backup.xml

例如：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

其中/clickHouse/backups/是备份的路径，你可以指定任何路径。这里将备份路径命名为backups。
配置好xml之后，需要重启clickhouse实例

 

重启后检查：

?

1 2 3 4 5 6 7 8 9 10 11 12 13

clickhouse :) SELECT     name,     path,     formatReadableSize(free_space) AS free,     formatReadableSize(total_space) AS total,     formatReadableSize(keep_free_space) AS reserved FROM system.disks   ┌─name────┬─path─────────────────┬─free──────┬─total─────┬─reserved─┐ │ backups │ /clickHouse/backups/ │ 67.54 GiB │ 31.67 GiB │ 0.00 B   │ │ default │ /var/lib/clickhouse/ │ 67.54 GiB │ 31.67 GiB │ 0.00 B   │ └─────────┴──────────────────────┴───────────┴───────────┴──────────┘

 

配置备份到S3：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

<clickhouse>   <storage_configuration>     <disks>       <s3>         <type>s3</type>         <endpoint>https://your_buckets_URL_address</endpoint>         <access_key_id>X.........X</access_key_id>         <secret_access_key>Y...........Y</secret_access_key>       </s3>     </disks>   </storage_configuration>   <backups>     <allowed_disk>s3</allowed_disk>     <allowed_path>/backups/</allowed_path>   </backups> </clickhouse>

 

配置后手动备份：

?

1 2 3

BACKUP TABLE|DATABASE table_name|database_name TO Disk('<backup_location>', 'backupName.zip');   RESTORE TABLE|DATABASE table_name|database_name FROM Disk('<backup_location>', 'backupName.zip'

 

备份参数
·async：异步地备份和还原。默认是同步的
·partitions：还原的分区列表。
·settings：比如compression_method、compression_level、密码、base_backup(增量备份使用到)、structure_only、storage_policy、s3_storage_class

查看备份信息

?

1 2 3

SELECT * FROM system.backups FORMAT Vertical

不过这个表是动态表，数据没有持久化。