ClickHouse_Backup_Remote_BZ2

clickhouse_backup_remote_bz2.sh

#!/bin/bash
#==============================================================================================
# Created by svet0slav of Countess Cat Inc - https://countesscat.com
#==============================================================================================
# We use this because clickhouse-backup freezes data and new data can't insert, not good!
# clickhouse-backup not working with latest version of ClickHouse - not good. Ha-ha!
#==============================================================================================
# Insanely good compression anf quite fast!
# You can symlink bzip2, bunzip2 and bzcat to lbzip2, and gzip, gunzip, gzcat and zcat to pigz:
#
# Here is how on Ubuntu
#
# sudo apt-get install lbzip2 pigz
# cd /usr/local/bin
# ln -s /usr/bin/lbzip2 bzip2
# ln -s /usr/bin/lbzip2 bunzip2
# ln -s /usr/bin/lbzip2 bzcat
# ln -s /usr/bin/pigz gzip
# ln -s /usr/bin/pigz gunzip
# ln -s /usr/bin/pigz gzcat
# ln -s /usr/bin/pigz zcat
#
# Other things you need to use are: tar, nice, ionice and cpulimit. Install!
# sudo apt-get install cpulimit
#==============================================================================================
# USAGE: just call this script with cron after setup (fill the config data) and you're cool!
# Yes - make file executable - chmod +x /path/to/file/file.sh
#==============================================================================================
# clickhouse-client --query="SELECT * FROM table FORMAT Native" > table.native
# Native is the most efficient format 
# CSV, TabSeparated, JSONEachRow are more portable: you may import/export data to another DBMS.
#
# Dump of metadata:
# clickhouse-client --query="SHOW CREATE TABLE table" --format=TabSeparatedRaw > table.sql
#==============================================================================================
# Unarchive
# tar xjf /path/to/backup.tar.bz2
#
# Restore of metadata:
# clickhouse-client < table.sql
#
# Restore of data:
# clickhouse-client --query="INSERT INTO table FORMAT Native" < table.native
#==============================================================================
# CUSTOM SETTINGS
#==============================================================================
# Directory to put the backup files
BACKUP_DIRL='/path/to/backup/folder/on/remote/host'
# Remote host
PORT='XXXX' 
USER='XXXX'
HOST='XXXX'
REMOTE_HOST='ssh -p '$PORT' '$USER'@'$HOST
# Number of days to keep backups
DTBS='database_name'
TBLS='table_name'
KEEP_BACKUPS_FOR=5 #days

#==============================================================================
# METHODS
#==============================================================================

# YYYY-MM-DD
TIMESTAMP=$(date +%F)

function delete_old_data() {
  echo "Deleting $BACKUP_DIRL/*.tar.bz2 backups older than $KEEP_BACKUPS_FOR days..."
  nice -n 10 ionice -c2 -n 7 $REMOTE_HOST "find $BACKUP_DIRL -type f -name '*.tar.bz2' -mtime +$KEEP_BACKUPS_FOR -exec rm \"{}\" \;"
  echo "Deleting old backups done!"
}

function backup_metadata() {
  echo "Backing up..."
  nice -n 10 ionice -c2 -n 7 clickhouse-client --query="SHOW CREATE TABLE $DTBS.$TBLS" --format=TabSeparatedRaw | $REMOTE_HOST "cat > $BACKUP_DIRL/$TIMESTAMP.$DTBS.$TBLS.sql"
  echo "Backup of metadata done!"
}

function backup_table() {
  echo "Backing up..."
  nice -n 10 ionice -c2 -n 7 clickhouse-client --query="SELECT * FROM $DTBS.$TBLS FORMAT Native" | $REMOTE_HOST "cat > $BACKUP_DIRL/$TIMESTAMP.$DTBS.$TBLS.native"
  echo "Backup of data done!"
}

function create_tarbal_delete_files() {
  echo "Archiving..."
  $REMOTE_HOST "cpulimit -l 8 -- nice -n 10 ionice -c2 -n 7 tar -cjSf $BACKUP_DIRL/$TIMESTAMP.tar.bz2 -C $BACKUP_DIRL $TIMESTAMP.$DTBS.$TBLS.native $TIMESTAMP.$DTBS.$TBLS.sql --remove-files"
  echo "Archiving of data done! Original files deleted!"
}

function hr() {
  printf '=%.0s' {1..100}
  printf "\n"
}

#==============================================================================
# RUN SCRIPT
#==============================================================================
hr
delete_old_data
hr
backup_metadata
hr
backup_table
hr
create_tarbal_delete_files
hr
printf "All backed up!\n\n"