#!/bin/bash

export HOME=/root
export SHELL=/bin/bash
export PATH=/usr/local/bin:/usr/local/sbin:/opt/local/bin:/usr/bin:/usr/sbin:/bin:/sbin:/usr/libexec

_check_root() {
  if [ "$(id -u)" -eq 0 ]; then
    ionice -c2 -n7 -p $$
    renice 19 -p $$
    chmod a+w /dev/null
  else
    echo "ERROR: This script should be run as a root user"
    exit 1
  fi
  _DF_TEST=$(df -kTh / -l \
    | grep '/' \
    | sed 's/\%//g' \
    | awk '{print $6}' 2> /dev/null)
  _DF_TEST=${_DF_TEST//[^0-9]/}
  if [ ! -z "${_DF_TEST}" ] && [ "${_DF_TEST}" -gt 90 ]; then
    echo "ERROR: Your disk space is almost full !!! ${_DF_TEST}/100"
    echo "ERROR: We can not proceed until it is below 90/100"
    exit 1
  fi
}
_check_root

[ -e "/root/.proxy.cnf" ] && exit 0
[ ! -e "/root/.my.cluster_write_node.txt" ] && exit 0
[ ! -e "/root/.my.cluster_root_pwd.txt" ] && exit 0

_IS_SQLBACKUP_RUNNING=$(pgrep -f mysql_backup.sh)
if [ ! -z "${_IS_SQLBACKUP_RUNNING}" ]; then
  exit 0
fi

if [ -e "/root/.my.cluster_root_pwd.txt" ]; then
  _SQL_PSWD=$(cat /root/.my.cluster_root_pwd.txt 2>/dev/null | tr -d '\n')
fi

if [ -e "/root/.my.cluster_backup_proxysql.txt" ]; then
  _SQL_PORT="6033"
  _SQL_HOST="127.0.0.1"
else
  _SQL_PORT="3306"
  if [ -e "/root/.my.cluster_write_node.txt" ]; then
    _SQL_HOST=$(cat /root/.my.cluster_write_node.txt 2>&1)
    _SQL_HOST=$(echo -n ${_SQL_HOST} | tr -d "\n" 2>&1)
  fi
  [ -z ${_SQL_HOST} ] && _SQL_HOST="127.0.0.1" && _SQL_PORT="3306"
fi

_C_SQL="mysql --user=root --password=${_SQL_PSWD} --host=${_SQL_HOST} --port=${_SQL_PORT} --protocol=tcp"

echo "SQL --host=${_SQL_HOST} --port=${_SQL_PORT}"
_n=$((RANDOM%600+8))
echo "INFO: Waiting ${_n} seconds on $(date) before running backup..."
sleep ${_n}
echo "INFO: Starting backup on $(date)"

# shellcheck disable=SC1091
[ -e "/root/.barracuda.cnf" ] && source /root/.barracuda.cnf

# Sanitize to allow only digits and minus sign
export _B_NICE=${_B_NICE//[^0-9-]/}

# Validate and set default if necessary
if ! [[ "${_B_NICE}" =~ ^-?[0-9]+$ ]]; then
  _B_NICE=0
fi

# Clamp the value within -20 to 19
if (( _B_NICE < -20 )); then
  _B_NICE=-20
elif (( _B_NICE > 19 )); then
  _B_NICE=19
fi

renice ${_B_NICE} -p $$ &> /dev/null

_SQL_CACHE_EXC_DEF="cache_bootstrap cache_discovery cache_config"

if [ -e "/root/.my.cache.exceptions.cnf" ]; then
  _SQL_CACHE_EXC_ADD=$(cat /root/.my.cache.exceptions.cnf 2>&1)
  _SQL_CACHE_EXC="${_SQL_CACHE_EXC_DEF} ${_SQL_CACHE_EXC_ADD}"
else
  _SQL_CACHE_EXC="${_SQL_CACHE_EXC_DEF}"
fi

_BACKUPDIR=/data/disk/arch/cluster
_DATE=$(date +%y%m%d-%H%M%S)
_DOW=$(date +%u)
_hName="$(cat /etc/hostname 2>/dev/null | tr -d '\n' || hostname -f 2>/dev/null)"
_DOW=${_DOW//[^1-7]/}
_DOM=$(date +%e)
_DOM=${_DOM//[^0-9]/}
_SAVELOCATION=${_BACKUPDIR}/${_hName}-${_DATE}
if [ -e "/root/.my.optimize.cnf" ]; then
  _OPTIM=YES
else
  _OPTIM=NO
fi
_VM_TEST="$(uname -a)"
if [[ "${_VM_TEST}" =~ "-beng" ]]; then
  _VMFAMILY="VS"
else
  _VMFAMILY="XEN"
fi
touch /run/boa_sql_cluster_backup.pid

_create_locks() {
  echo "Creating locks for $1"
  touch /run/mysql_cluster_backup_running.pid
}

_remove_locks() {
  echo "Removing locks for $1"
  rm -f /run/mysql_cluster_backup_running.pid
}

_check_running() {
  _IS_PROXYSQL_RUNNING=$(pgrep -f proxysql)
  while [ -z "${_IS_PROXYSQL_RUNNING}" ] \
    || [ ! -e "/var/lib/proxysql/proxysql.pid" ]; do
    _IS_PROXYSQL_RUNNING=$(pgrep -f proxysql)
    echo "Waiting for ProxySQL availability..."
    sleep 3
  done
}

_truncate_cache_tables() {
  _check_running
  _TABLES=$(${_C_SQL} ${_DB} -e "show tables" -s | grep ^cache | uniq | sort 2>&1)
  for C in ${_TABLES}; do
    _IF_SKIP_C=
    for X in ${_SQL_CACHE_EXC}; do
      if [ "${C}" = "${X}" ]; then
        _IF_SKIP_C=SKIP
      fi
    done
    if [ -z "${_IF_SKIP_C}" ]; then
      ${_C_SQL} ${_DB}<<EOFMYSQL
TRUNCATE ${C};
EOFMYSQL
    fi
  done
}

_truncate_watchdog_tables() {
  _check_running
  _TABLES=$(${_C_SQL} ${_DB} -e "show tables" -s | grep ^watchdog$ 2>&1)
  for W in ${_TABLES}; do
${_C_SQL} ${_DB}<<EOFMYSQL
TRUNCATE ${W};
EOFMYSQL
  done
}

_truncate_accesslog_tables() {
  _check_running
  _TABLES=$(${_C_SQL} ${_DB} -e "show tables" -s | grep ^accesslog$ 2>&1)
  for A in ${_TABLES}; do
${_C_SQL} ${_DB}<<EOFMYSQL
TRUNCATE ${A};
EOFMYSQL
  done
}

_truncate_batch_tables() {
  _check_running
  _TABLES=$(mysql ${_DB} -u root -e "show tables" -s | grep ^batch$ 2>&1)
  for B in ${_TABLES}; do
${_C_SQL} ${_DB}<<EOFMYSQL
TRUNCATE ${B};
EOFMYSQL
  done
}

_truncate_queue_tables() {
  _check_running
  _TABLES=$(${_C_SQL} ${_DB} -e "show tables" -s | grep ^queue$ 2>&1)
  for Q in ${_TABLES}; do
${_C_SQL} ${_DB}<<EOFMYSQL
TRUNCATE ${Q};
EOFMYSQL
  done
}

_truncate_views_data_export() {
  _check_running
  _TABLES=$(mysql ${_DB} -u root -e "show tables" -s | grep ^views_data_export_index_ 2>&1)
  for V in ${_TABLES}; do
${_C_SQL} ${_DB}<<EOFMYSQL
DROP TABLE ${V};
EOFMYSQL
  done
${_C_SQL} ${_DB}<<EOFMYSQL
TRUNCATE views_data_export_object_cache;
EOFMYSQL
}

_repair_this_database() {
  _check_running
  mysqlcheck --host=${_SQL_HOST} --port=${_SQL_PORT} --protocol=tcp -u root --auto-repair --silent ${_DB}
}

_optimize_this_database() {
  _check_running
  _TABLES=$(${_C_SQL} ${_DB} -e "show tables" -s | uniq | sort 2>&1)
  for T in ${_TABLES}; do
${_C_SQL} ${_DB}<<EOFMYSQL
OPTIMIZE TABLE ${T};
EOFMYSQL
  done
}

_convert_to_innodb() {
  _check_running
  _TABLES=$(${_C_SQL} ${_DB} -e "show tables" -s | uniq | sort 2>&1)
  for T in ${_TABLES}; do
${_C_SQL} ${_DB}<<EOFMYSQL
ALTER TABLE ${T} ENGINE=INNODB;
EOFMYSQL
  done
}

_backup_this_database_with_mydumper() {
  _check_running
  if [ ! -d "${_SAVELOCATION}/${_DB}" ]; then
    mkdir -p ${_SAVELOCATION}/${_DB}
  fi
  mydumper \
    --database=${_DB} \
    --host=${_SQL_HOST} \
    --user=root \
    --password=${_SQL_PSWD} \
    --port=${_SQL_PORT} \
    --outputdir=${_SAVELOCATION}/${_DB}/ \
    --rows=50000 \
    --build-empty-files \
    --threads=4 \
    --less-locking \
    --long-query-guard=900 \
    --verbose=1
}

_backup_this_database_with_mysqldump() {
  _check_running
  mysqldump \
    --user=root \
    --password=${_SQL_PSWD} \
    --host=${_SQL_HOST} \
    --port=${_SQL_PORT} \
    --protocol=tcp \
    --single-transaction \
    --quick \
    --no-autocommit \
    --skip-add-locks \
    --no-tablespaces \
    --hex-blob ${_DB} \
    > ${_SAVELOCATION}/${_DB}.sql
}

_compress_backup() {
  if [ "${_MYQUICK_USE}" = "YES" ]; then
    for DbPath in `find ${_SAVELOCATION}/ -maxdepth 1 -mindepth 1 | sort`; do
      if [ -e "${DbPath}/metadata" ]; then
        DbName=$(echo ${DbPath} | cut -d'/' -f7 | awk '{ print $1}' 2>&1)
        cd ${_SAVELOCATION}
        tar -c -p -I zstd -f ${DbName}-${_DATE}.tar.zst ${DbName} &> /dev/null
        rm -f -r ${DbName}
      fi
    done
    chmod 600 ${_SAVELOCATION}/*
    chmod 700 ${_SAVELOCATION}
    chmod 700 /data/disk/arch
    echo "INFO: Permissions fixed"
  else
    gzip ${_SAVELOCATION}/*.sql
    chmod 600 ${_BACKUPDIR}/*/*
    chmod 700 ${_BACKUPDIR}/*
    chmod 700 ${_BACKUPDIR}
    chmod 700 /data/disk/arch
    echo "INFO: Permissions fixed"
  fi
}

[ ! -a ${_SAVELOCATION} ] && mkdir -p ${_SAVELOCATION};

_check_mysql_version() {
  _DBS_TEST=$(which mysql 2>&1)
  if [ ! -z "${_DBS_TEST}" ]; then
    _DB_SERVER_TEST=$(mysql -V 2>&1)
  fi
  if [[ "${_DB_SERVER_TEST}" =~ "Ver 8.4." ]]; then
    _DB_V=8.4
  elif [[ "${_DB_SERVER_TEST}" =~ "Ver 8.3." ]]; then
    _DB_V=8.3
  elif [[ "${_DB_SERVER_TEST}" =~ "Ver 8.0." ]]; then
    _DB_V=8.0
  elif [[ "${_DB_SERVER_TEST}" =~ "Distrib 5.7." ]]; then
    _DB_V=5.7
  fi
  if [ ! -z "${_DB_V}" ]; then
    ${_C_SQL} -e "SET GLOBAL innodb_max_dirty_pages_pct = 0;" &> /dev/null
    ${_C_SQL} -e "SET GLOBAL innodb_change_buffering = 'none';" &> /dev/null
    ${_C_SQL} -e "SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;" &> /dev/null
    ${_C_SQL} -e "SET GLOBAL innodb_io_capacity = 2000;" &> /dev/null
    ${_C_SQL} -e "SET GLOBAL innodb_io_capacity_max = 4000;" &> /dev/null
    if [ "${_DB_V}" = "5.7" ]; then
      ${_C_SQL} -e "SET GLOBAL innodb_buffer_pool_dump_pct = 100;" &> /dev/null
      ${_C_SQL} -e "SET GLOBAL innodb_buffer_pool_dump_now = ON;" &> /dev/null
    fi
    ${_C_SQL} -e -e "SET GLOBAL innodb_fast_shutdown = 1;" &> /dev/null
  fi
}

_check_running
_check_mysql_version

_MYQUICK_USE=NO
if [ -x "/usr/local/bin/mydumper" ]; then
  _MYQUICK_ITD=$(mydumper -V 2>&1 \
    | tr -d "\n" \
    | tr -d "," \
    | tr -d "v" \
    | cut -d" " -f2 \
    | awk '{ print $1}' 2>&1)
  _DB_V=$(mysql -V 2>&1 \
    | tr -d "\n" \
    | cut -d" " -f6 \
    | awk '{ print $1}' \
    | cut -d"-" -f1 \
    | awk '{ print $1}' \
    | sed "s/[\,']//g" 2>&1)
  if [ "${_DB_V}" = "Linux" ]; then
    _DB_V=$(mysql -V 2>&1 \
      | tr -d "\n" \
      | cut -d" " -f4 \
      | awk '{ print $1}' \
      | cut -d"-" -f1 \
      | awk '{ print $1}' \
      | sed "s/[\,']//g" 2>&1)
  fi
  _MD_V=$(mydumper --version 2>&1 \
    | tr -d "\n" \
    | cut -d" " -f6 \
    | awk '{ print $1}' \
    | cut -d"-" -f1 \
    | awk '{ print $1}' \
    | sed "s/[\,']//g" 2>&1)
  if [ ! -e "/root/.mysql.force.legacy.backup.cnf" ]; then
    _MYQUICK_USE=YES
    echo "INFO: Installed MyQuick ${_MYQUICK_ITD} for ${_MD_V} (${_DB_V})"
  fi
fi

for _DB in `${_C_SQL} -e "show databases" -s | uniq | sort`; do
  if [ "${_DB}" != "Database" ] \
    && [ "${_DB}" != "information_schema" ] \
    && [ "${_DB}" != "performance_schema" ]; then
    _check_running
    _create_locks ${_DB}
    if [ "${_DB}" != "mysql" ]; then
      _IS_GB=$(${_C_SQL} --skip-column-names --silent -e "SELECT table_name 'Table Name', round(((data_length + index_length)/1024/1024),0)
'Table Size (MB)' FROM information_schema.TABLES WHERE table_schema = '${_DB}' AND table_name ='watchdog';" | cut -d'/' -f1 | awk '{ print $2}' | sed "s/[\/\s+]//g" | bc 2>&1)
      _IS_GB=${_IS_GB//[^0-9]/}
      _SQL_MAX_LIMIT="1024"
      if [ ! -z "${_IS_GB}" ]; then
        if [ "${_IS_GB}" -gt "${_SQL_MAX_LIMIT}" ]; then
          _truncate_watchdog_tables &> /dev/null
          echo "INFO: Truncated giant ${_IS_GB} watchdog in ${_DB}"
        fi
      fi
      # _truncate_accesslog_tables &> /dev/null
      # echo "Truncated not used accesslog in ${_DB}"
      # _truncate_queue_tables &> /dev/null
      # echo "Truncated queue table in ${_DB}"
      _CACHE_CLEANUP=NONE
      # if [ "${_DOW}" = "6" ] && [ -e "/root/.my.batch_innodb.cnf" ]; then
      #   _repair_this_database &> /dev/null
      #   echo "Repair task for ${_DB} completed"
      #   _truncate_cache_tables &> /dev/null
      #   echo "All cache tables in ${_DB} truncated"
      #   _convert_to_innodb &> /dev/null
      #   echo "InnoDB conversion task for ${_DB} completed"
      #   _CACHE_CLEANUP=DONE
      # fi
      # if [ "${_OPTIM}" = "YES" ] \
      #   && [ "${_DOW}" = "7" ] \
      #   && [ "${_DOM}" -ge 24 ] \
      #   && [ "${_DOM}" -lt 31 ]; then
      #   _repair_this_database &> /dev/null
      #   echo "Repair task for ${_DB} completed"
      #   _truncate_cache_tables &> /dev/null
      #   echo "All cache tables in ${_DB} truncated"
      #   _optimize_this_database &> /dev/null
      #   echo "Optimize task for ${_DB} completed"
      #   _CACHE_CLEANUP=DONE
      # fi
      if [ "${_CACHE_CLEANUP}" != "DONE" ]; then
        _truncate_cache_tables &> /dev/null
        echo "INFO: All cache tables in ${_DB} truncated"
      fi
    fi
    if [ "${_MYQUICK_USE}" = "YES" ]; then
      _backup_this_database_with_mydumper &> /dev/null
    else
      _backup_this_database_with_mysqldump &> /dev/null
    fi
    _remove_locks ${_DB}
    echo "INFO: Backup completed for ${_DB}"
    echo
  fi
done

echo "INFO: Completing all dbs backups on $(date)"
rm -f /run/boa_sql_cluster_backup.pid
touch /var/log/boa/last-run-cluster-backup

echo "INFO: Starting dbs backup compress on $(date)"
_compress_backup &> /dev/null
echo "INFO: Completing dbs backup compress on $(date)"

echo "INFO: Starting dbs backup cleanup on $(date)"
_DB_BACKUPS_TTL=${_DB_BACKUPS_TTL//[^0-9]/}
if [ -z "${_DB_BACKUPS_TTL}" ]; then
  _DB_BACKUPS_TTL="30"
fi
find ${_BACKUPDIR} -mtime +${_DB_BACKUPS_TTL} -type d -exec rm -rf {} \;
echo "INFO: Backups older than ${_DB_BACKUPS_TTL} days deleted"

echo "INFO: ALL TASKS COMPLETED, BYE!"
exit 0

