Last active
July 2, 2023 16:46
-
-
Save sv3t0sl4v/a11102c48ff68f180c75a272283cb036 to your computer and use it in GitHub Desktop.
ClickHouse_Backup_Remote_BZ2
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment