This is the 16th day of my participation in the August More Text Challenge

Why backup CK

Although CK engine has a replicated table, there are also some protection measures (cannot be used with artificial remove MergeTree engine and contains more than 50 gb of data table), but is unable to avoid human error, and bad luck machine cluster fault, or the compliance requirements, etc., in order to add an insurance, you need to use as long as it is the database, There is a need for backups

Backup methods and tools from the website clickhouse. Tech/docs/useful/ope…

It is recommended to practice in a test or development environment for the first time. Do not play directly on production for the first time

The manual backup

  • Manual backup, is to use the ALTER command to implement in CK, is to use a hard link to a directory (/ var/lib/clickhouse/shadow), recovery from/var/lib/clickhouse/bakcup check records for a specified name
  • Note that manual backup and recovery do not involve the table structure, but only backup and recovery data. Therefore, relevant table structures need to be archived and created by ourselves

Create /var/lib/clickhouse/{shadow,bakcup}. Note that the permission of the two directories belongs to the clickhouse owner group

Backup example

alter table tableName freeze 
#Run directly without entering interactive mode
echo 'alter table tableName freeze ' | clickhouse-client -u xxxx --password  xxxx
Copy the code

The data in shadow needs to be copied to before recovery

Restore example Namexxx to create backup time, exist as a folder in the/var/lib/clickhouse/shadow

alter table tableName attach partition Namexxx 
#Run directly without entering interactive mode
echo 'alter table tableName attach partition Namexxx ' | clickhouse-client -u xxxx --password  xxxx

#View the recovery table data
echo 'select count() from tableName' | clickhouse-client -u xxxx --password  xxxx
Copy the code

Table structure Although manual backup table structure, but the existing table, CK will default to SQL table structure in/data/clickhouse/data/metadata/defalut/XXXX. SQL. Under this path. Just back up the directory regularly as well

Backup tool

Clickhouse-backup github.com/AlexAkulov/…

This tool can not only back up data, but also table structures, and also supports backup to third party storage such as S3(OSS).

I can download a package from Releases and use the direct RPM -ivh installation of the RPM package globally, which I used in this case as a tar package.

Decompress and view the help commands

tar xf clickhouse-backup.tar.gz
cd clickhouse-backup
./clickhouse-backup help
Copy the code

If the configuration is to be backed up to the local disk, you only need to write the account secret CK address and port into it

Back up data to the local PC

Because of the tar package I used here, you need to specify the configuration file when executing the command. If you don’t want to specify the configuration file, you can put it in /etc/clickhouse-backup/config.yml

The default backup to/var/lib/clickhouse/backup the path

If you have changed the default system data store path (), that is path/clickhouse/backup path to data directory, with trailing slash

View tables that can be backed up

./clickhouse-backup -c config.yml  tables
Copy the code

The backup command

#Backup all tables
./clickhouse-backup -c config.yml create 
#Backup all tables, and specify the name for this backup, default to the current time, in the folder way
./clickhouse-backup -c config.yml create bak_Namexxx
#Backing up specified tables
./clickhouse-backup -c config.yml create -t default.tableName bak_Namexxx
#Backup multi-table
./clickhouse-backup -c config.yml create -t default.tableName1,default.tableName2 bak_Namexxx

#Viewing the Backup List
./clickhouse-backup -c config.yml list
#Example Delete a backup
./clickhouse-backup -c config.yml delete local bak_Namexxx
Copy the code

Restore command

#Restores a backup name data and table structure
./clickhouse-backup -c config.yml restore bak_Namexxx
#Specify table restore data and table structure
./clickhouse-backup -c config.yml restore -t default.tableName bak_Namexxx
#Reduction table structure
./clickhouse-backup -c config.yml restore -s 
#Restore data
./clickhouse-backup -c config.yml restore -d 
Copy the code

Back up data to Ali Cloud OSS

To back up data to remote storage (third-party storage OSS), you need to configure s3 in the configuration file in advance

The local backup is created, and the remote is update, to see the help command, which is almost the same as create

#Creating a Remote Backup
./clickhouse-backup -c config.yml update
#Download remote backup files to the local PC
./clickhouse-backup -c config.yml download
#Restoring remote Backup
./clickhouse-backup -c config.yml restore_remote
Copy the code