Backup databases mysql and postgres from docker’s

Опубликовано в Автор: human

Backup databases mysql and postgres in containers.
Add to crontabs like this

00 00  * * * bash /docker-compose/scripts/backup_db.sh  "ARG_BACKUP_ROOT_DIR=/docker-compose/bup" "ARG_DB_STACK_POSTGRES=docker" "ARG_DB_STACK_MYSQL=docker" "ARG_DAYS_TO_STORE_BACKUP=30" "ARG_DOW_FOR_VACUUM=6"

#!/bin/bash
#bash /docker-compose/scripts/backup_db.sh  "ARG_BACKUP_ROOT_DIR=/docker-compose/bup" "ARG_DB_STACK_POSTGRES=docker" "ARG_DB_STACK_MYSQL=docker" "ARG_DAYS_TO_STORE_BACKUP=30" "ARG_DOW_FOR_VACUUM=6"
DATE_BACKUP=$(date +"%Y-%m-%d")
DOW=$(date +%u)
##Вход переменная backup dir
ARG_BACKUP_ROOT_DIR="$1"
BACKUP_ROOT_DIR=$(echo "${ARG_BACKUP_ROOT_DIR}" | cut -d\= -f2)
##Вход переменная DB_STACK_POSTGRES - native or docker
ARG_DB_STACK_POSTGRES="$2"
DB_STACK_POSTGRES=$(echo "${ARG_DB_STACK_POSTGRES}" | cut -d\= -f2)
##Вход переменная DB_STACK_MYSQL - native or docker
ARG_DB_STACK_MYSQL="$3"
DB_STACK_MYSQL=$(echo "${ARG_DB_STACK_MYSQL}" | cut -d\= -f2)
##Сколько дней ротация бэкапов
ARG_DAYS_TO_STORE_BACKUP="$4"
DAYS_TO_STORE_BACKUP=$(echo "${ARG_DAYS_TO_STORE_BACKUP}" | cut -d\= -f2)
##Когда делаем ваккум - 6 - суббота
ARG_DOW_FOR_VACUUM="$5"
DOW_FOR_VACUUM=$(echo "${ARG_DOW_FOR_VACUUM}" | cut -d\= -f2)
##Каталоги для бэкапов
BACKUP_DIR_MYSQL="${BACKUP_ROOT_DIR}/mysql_bases"
BACKUP_DIR_PSQL="${BACKUP_ROOT_DIR}/pg_bases"
##Включен ли бэкап mysql
DB_STACK_MYSQL_BACKUP_ENABLED="true"
##Включен ли бэкап postgres
DB_STACK_POSTGRES_BACKUP_ENABLED="true"
#sudo bash /docker-compose/scripts/backup_db.sh  "ARG_BACKUP_ROOT_DIR=/bup" "ARG_DB_STACK_POSTGRES=docker" "ARG_DB_STACK_MYSQL=docker" "ARG_DAYS_TO_STORE_BACKUP=30" "ARG_DOW_FOR_VACUUM=3"
HOST_VPN_IP=$(ip a | grep 10.26 | awk '{print $2}' | cut -d\/ -f1)
echo "DB_STACK_MYSQL -is ${DB_STACK_MYSQL}"
echo "DB_STACK_POSTGRES -is ${DB_STACK_POSTGRES}"
sleep 5;
##faq
echo "sudo bash /docker-compose/scripts/backup_db.sh  \"ARG_BACKUP_ROOT_DIR=/bup\" \"ARG_DB_STACK_POSTGRES=docker\" \"ARG_DB_STACK_MYSQL=docker\" \"ARG_DAYS_TO_STORE_BACKUP=30\" \"ARG_DOW_FOR_VACUUM=3\""
echo ""
##BEGIN
if [[ -z "${BACKUP_ROOT_DIR}" ]]
then
    echo "BACKUP_ROOT_DIR is not defined. exit"
    exit
fi
precheck_postgres () {
    if [[ ! -d "${BACKUP_DIR_PSQL}" ]]
    then
        mkdir -p "${BACKUP_DIR_PSQL}"
    fi
    chmod 777 -R "${BACKUP_DIR_PSQL}"
    echo "Precheck postgres backup vars"
    if [[ "${DB_STACK_POSTGRES}" == "" ]]
    then
        echo "DB_STACK_POSTGRES variable is not defined - exit"
        exit
    fi
    if [[ "${DB_STACK_POSTGRES}" == "docker" ]];  then
        echo "Precheck postgres backup vars - ok DB_STACK_POSTGRES is ${DB_STACK_POSTGRES}"
    elif [[ "${DB_STACK_POSTGRES}" == "native" ]]; then
        echo "Precheck postgres backup vars - ok DB_STACK_POSTGRES is ${DB_STACK_POSTGRES}"
    else
        echo "DB_STACK_POSTGRES variable is not  docker or native.Current DB_STACK_POSTGRES is ${DB_STACK_POSTGRES}"
        exit
    fi
}
precheck_mysql () {
    if [[ ! -d "${BACKUP_DIR_MYSQL}" ]]
    then
        mkdir -p "${BACKUP_DIR_MYSQL}"
        chmod 777 -R "${BACKUP_DIR_MYSQL}"
    fi
    echo "Precheck mysql backup vars"
    if [[ "${DB_STACK_MYSQL}" == "" ]]
    then
        echo "DB_STACK_MYSQL variable is not defined - exit"
        exit
    fi
    if [[ "${DB_STACK_MYSQL}" == "docker" ]];  then
        echo "Precheck postgres backup vars - ok DB_STACK_MYSQL is ${DB_STACK_MYSQL}"
    elif [[ "${DB_STACK_MYSQL}" == "native" ]]; then
        echo "Precheck mysql backup vars - ok DB_STACK_MYSQL is ${DB_STACK_MYSQL}"
    else
        echo "DB_STACK_MYSQL variable is not  docker or native.Current DB_STACK_MYSQL is ${DB_STACK_MYSQL}"
        exit
    fi
}
postcheck_backup_postgres() {
if [ $? -eq 0 ]; then
  echo "Database backup successfully completed"
  #delete_old_backups_postgres
else
  echo "Error found during backup"
  send_message_rocket_chat "crons_backupdb" "Backup on $(hostname) with ip ${HOST_VPN_IP} exit with error on function ${FUNCNAME[ 0 ]}"
fi
}
postcheck_backup_mysql() {
if [ $? -eq 0 ]; then
  echo "Database backup successfully completed"
  #delete_old_backups_mysql
else
  echo "Error found during backup"
  send_message_rocket_chat "crons_backupdb" "Backup on $(hostname) with ip ${HOST_VPN_IP} exit with error on function ${FUNCNAME[ 0 ]}"
fi
}
get_docker_mysql_vars () {
echo "get_docker_mysql_vars"
unset -v mysql_container
unset -v mysql_root_password
unset -v mysql_bases
mysql_containers=$(docker ps | grep mysql | grep -ve 'exporter' | awk '{print $NF}')
mysql_containers_count=$(docker ps | grep mysql | grep -ve 'exporter' | awk '{print $NF}' | wc -l )
if [[ "${mysql_containers_count}" == "0" ]]; then
status_mysql_container="container mysql not found"
echo "${status_mysql_container} on $(hostname) not found"
send_message_rocket_chat "crons_backupdb" "${status_mysql_container} on $(hostname) with ip ${HOST_VPN_IP} not found"
fi
}
get_native_mysql_vars () {
#unset -v mysql_root_password
#unset -v mysql_bases
mysql_root_password=""
mysql_bases=$(echo "show databases;" |  mysql -u root | awk -F\| '{print $1}' | tail -n+2 | grep -v "information_schema" | grep -v "performance_schema")
}
get_docker_postgres_vars () {
#unset -v psql_bases
#unset -v psql_container
psql_containers=$(docker ps | grep 'postgres\|timescale' | grep -ve 'exporter\|adapter' | awk '{print $NF}')
psql_containers_count=$(docker ps | grep 'postgres\|timescale' | grep -ve 'exporter\|adapter' | awk '{print $NF}' | wc -l)
if [[ "${psql_containers_count}"  == "0" ]]; then
status_psql_container="container postgres not found"
echo "${status_psql_container} on $(hostname) not found"
send_message_rocket_chat "crons_backupdb" "${status_psql_container} on $(hostname) with ip ${HOST_VPN_IP} not found"
fi
}
get_native_postgres_vars () {
unset -v psql_bases
psql_bases=$(echo '\l' | su postgres -c 'psql 2>/dev/null'  | awk -F\| '{print $1}' | awk '{print $1}' | tail -n+4 |  grep -v "^(" | grep -v template | grep -v postgres | awk "NF")
}
delete_old_backups_mysql () {
old_backups_mysqls_count=$(ls ${BACKUP_DIR_MYSQL}/ | wc -l )
if [[ "${old_backups_mysqls_count}" -gt "10" ]];then
echo "Удаляем старые базы данных mysql"
/usr/bin/find "${BACKUP_DIR_MYSQL}" -type f -name '*.sql.*' -mtime +${DAYS_TO_STORE_BACKUP} -exec rm {} \;
fi
}
delete_old_backups_postgres () {
old_backups_postgres_count=$(ls ${BACKUP_DIR_PSQL}/ | wc -l )
if [[ "${old_backups_postgres_count}" -gt "10" ]];then
echo "Удаляем старые базы данных postgres"
/usr/bin/find "${BACKUP_DIR_PSQL}" -type f -name '*.dump' -mtime +${DAYS_TO_STORE_BACKUP} -exec rm {} \;
fi
}
dump_db_mysql () {
######DUMP DOCKERMYSQL
if [[ "${DB_STACK_MYSQL}" == "docker" ]]
then
get_docker_mysql_vars
echo "1 ${DB_STACK_MYSQL} dump all mysql databases - ${mysql_bases} - ${DB_STACK_MYSQL}"
echo "docker"
sleep 5;
for mysql_container in ${mysql_containers}
do
mysql_root_password=$(docker exec -i "${mysql_container}" sh -c 'export' | grep MYSQL_ROOT | awk '{print $2}' | cut -d\= -f2 | tr -d \" | tr -d \')
mysql_bases=$(echo "show databases" |  /usr/bin/docker exec -i ${mysql_container} mysql -u root -p${mysql_root_password} | awk -F\| '{print $1}' | tail -n+2 | grep -v "information_schema" | grep -v "performance_schema")
for mysql_db in ${mysql_bases}
do
    echo "Backup  $mysql_db"
if [[ ! -f "${BACKUP_DIR_MYSQL}/${DATE_BACKUP}_${mysql_db}.sql.gz" ]]
    then
    echo "dump db ${mysql_db} with root password ${mysql_root_password}"
    /usr/bin/docker exec "${mysql_container}" /usr/bin/mysqldump -u root -p${mysql_root_password} "${mysql_db}" | gzip > "${BACKUP_DIR_MYSQL}"/"${DATE_BACKUP}"_"${mysql_db}".sql.gz
    postcheck_backup_mysql
    else
    echo "Файл ${BACKUP_DIR_MYSQL}/${DATE_BACKUP}_${mysql_db}.sql.gz уже существует"
fi
done
done
fi
######DUMP NATIVE MYSQL
if [[ "${DB_STACK_MYSQL}" == "native" ]]
then
get_native_mysql_vars
echo "1 ${DB_STACK_MYSQL} dump all mysql databases - ${mysql_bases} - ${DB_STACK_MYSQL}"
echo "native"
sleep 5;
for mysql_db in ${mysql_bases}
do
        echo "Backup  $mysql_db"
if [[ ! -f "${BACKUP_DIR_MYSQL}/${DATE_BACKUP}_${mysql_db}.sql.gz" ]]
        then
        /usr/bin/mysqldump -u root "${mysql_db}" | gzip > "${BACKUP_DIR_MYSQL}"/"${DATE_BACKUP}"_"${mysql_db}".sql.gz
        postcheck_backup_mysql
        else
        echo "Файл ${BACKUP_DIR_MYSQL}/${DATE_BACKUP}_${mysql_db}.sql.gz уже существует"
fi
done
fi
}
dump_db_psql () {
if [[ "${DB_STACK_POSTGRES}" == "docker" ]]
then
get_docker_postgres_vars
echo "1 ${DB_STACK_POSTGRES} dump all psql databases - ${psql_bases} - ${DB_STACK_POSTGRES}"
echo "native"
sleep 5;
for psql_container in ${psql_containers}
do
psql_bases=$(echo '\l' | /usr/bin/docker exec -i -u postgres "${psql_container}" psql 2>/dev/null  | awk -F\| '{print $1}' | awk '{print $1}' | tail -n+4 |  grep -v "^(" | grep -v template | grep -v postgres | awk "NF")
for PGBASE in ${psql_bases}
do
    echo "Backup  $PGBASE"
    if [[ ! -f "${BACKUP_DIR_PSQL}/${DATE_BACKUP}_${PGBASE}.dump" ]]
    then
    /usr/bin/docker exec  -u postgres "${psql_container}" pg_dump -Fc  --no-owner "${PGBASE}" > "${BACKUP_DIR_PSQL}"/"${DATE_BACKUP}"_"${PGBASE}".dump 2>/dev/null
    postcheck_backup_postgres
    else
    echo "Файл ${BACKUP_DIR_PSQL}/${DATE_BACKUP}_${PGBASE}.dump уже существует "
    fi
done
done
fi
if [[ "${DB_STACK_POSTGRES}" == "native" ]]
then
get_native_postgres_vars
echo "1 ${DB_STACK_POSTGRES} dump all psql databases - ${psql_bases} - ${DB_STACK_POSTGRES}"
echo "native"
sleep 5;
for PGBASE in ${psql_bases}
do
    echo "Backup  $PGBASE"
    if [[ ! -f "${BACKUP_DIR_PSQL}/${DATE_BACKUP}_${PGBASE}.dump" ]]
    then
    su postgres -c "pg_dump -Fc  --no-owner \"${PGBASE}\" > \"${BACKUP_DIR_PSQL}\"/\"${DATE_BACKUP}\"_\"${PGBASE}\".dump 2>/dev/null"
    postcheck_backup_postgres
    else
    echo "Файл ${BACKUP_DIR_PSQL}/${DATE_BACKUP}_${PGBASE}.dump уже существует "
    fi
done
fi
}
prewarm_psql () {
echo "prewarm_psql DB_STACK_POSTGRES is ${DB_STACK_POSTGRES}"
if [[ "${DB_STACK_POSTGRES}" == "docker" ]]
then
echo "DB_STACK_POSTGRES is docker"
sleep 2;
get_docker_postgres_vars
for PGBASE in ${psql_bases}
do
    echo "Make postgres warmup  $PGBASE ${DB_STACK_POSTGRES} STACK DB "
    /usr/bin/docker exec -i -u postgres "${psql_container}" sh -c "if [ -f /pgscripts/05-prewarm-postgres.sh ]; then /bin/bash /pgscripts/05-prewarm-postgres.sh \"${PGBASE}\" ;else /bin/bash /var/lib/postgresql/data/pgscripts/05-prewarm-postgres.sh \"${PGBASE}\";fi"
done
fi
if [[ "${DB_STACK_POSTGRES}" == "native" ]]
then
echo "DB_STACK_POSTGRES is native"
get_native_postgres_vars
echo "DB_STACK_POSTGRES is native"
sleep 2;
for PGBASE in ${psql_bases}
do
    echo "Make postgres warmup  $PGBASE - ${DB_STACK_POSTGRES} STACK DB "
    su postgres -c "/bin/bash /var/lib/postgresql/data/pgscripts/05-prewarm-postgres.sh \"${PGBASE}\""
done
fi
}
vacuum_psql () {
echo "Make postgres vacuum if day of week = ${DOW_FOR_VACUUM}"
if [[ "${DB_STACK_POSTGRES}" == "docker" ]]
then
get_docker_postgres_vars
if [[ "${DOW_FOR_VACUUM}" != "" ]]
then
    if [[ "${DOW}" == "${DOW_FOR_VACUUM}" ]]
    then
    for PGBASE in ${psql_bases}
    do
        echo "Make postgres vacuum  $PGBASE - ${DB_STACK_POSTGRES} STACK DB "
        /usr/bin/docker  exec -i -u postgres "${psql_container}" sh -c "if [ -f /pgscripts/06-vacuum-full-anazize-postgres.sh ]; then /bin/bash /pgscripts/06-vacuum-full-anazize-postgres.sh  \"${PGBASE}\"; else /bin/bash /var/lib/postgresql/data/pgscripts/06-vacuum-full-anazize-postgres.sh \"${PGBASE}\";fi"
    done
    else
    echo "Сегодня не день недели  № ${DOW_FOR_VACUUM} для запуска этого задания - выходим"
    fi
fi
fi
if [[ "${DB_STACK_POSTGRES}" == "native" ]]
then
get_native_postgres_vars
if [[ "${DOW_FOR_VACUUM}" != "" ]]
then
    if [[ "${DOW}" == "${DOW_FOR_VACUUM}" ]]
    then
    for PGBASE in ${psql_bases}
    do
        echo "Make postgres vacuum  $PGBASE - ${DB_STACK_POSTGRES} STACK DB "
         su postgres -c "/bin/bash /var/lib/postgresql/data/pgscripts/06-vacuum-full-anazize-postgres.sh \"${PGBASE}\""
    done
    else
    echo "Сегодня не день недели  № ${DOW_FOR_VACUUM} для запуска этого задания - выходим"
    fi
fi
fi
}
send_message_rocket_chat () {
channel="$1"
text="$2"
webhook_url="ВАШ_WEB_HOOK_IP"
if [[ "$webhook_url" == "" ]]
then
        echo "No webhook_url specified"
        exit 1
fi
# ------------
shift
if [[ "$channel" == "" ]]
then
        echo "No channel specified"
        exit 1
fi
# ------------
shift
username=$(hostname)
if [[ "${username}" == "" ]]
then
        echo "No username specified"
        username="unknown host"
fi
# ------------
shift
#text=test
if [[ "$text" == "" ]]
then
        echo "No text specified"
        exit 1
fi
escapedText=$(echo "$text" | sed 's/"/\"/g' | sed "s/'/\'/g" )
json="{\"channel\": \"$channel\", \"username\":\"$username\", \"icon_emoji\":\"ghost\", \"attachments\":[{\"color\":\"danger\" , \"text\": \"$escapedText\"}]}"
curl -s -d "payload=$json" "$webhook_url"
}
backup_postgres () {
#prepare || send_message_rocket_chat "crons" "Error during creating backup folder"
if [[ "${DB_STACK_POSTGRES_BACKUP_ENABLED}" == "true" ]]
then
precheck_postgres
dump_db_psql
delete_old_backups_postgres
vacuum_psql
prewarm_psql
else
    echo "Бэкап бд postgres отключен"
fi
}
backup_mysql () {
#prepare || send_message_rocket_chat "crons" "Error during creating backup folder"
if [[ "${DB_STACK_MYSQL_BACKUP_ENABLED}" == "true" ]]
then
precheck_mysql
dump_db_mysql
delete_old_backups_mysql
else
    echo "Бэкап бд mysql отключен"
fi
}
main () {
    backup_mysql
    backup_postgres
}
main

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

 

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.