#!/bin/bash

MYHST=127.0.0.1
MYUSR=root
MYPWD=pass

PREFIX=bkdb
SUFFIX=`date +%Y%m%d`.sql
DESTDIR=/bak

#----------------------------------------------------------------------------
if [ ! -e "${DESTDIR}" ]
then
  echo "fatal error : ${DESTDIR} not exists!"
  exit 1
fi

#----------------------------------------------------------------------------
find "${DESTDIR}" -name "${PREFIX}-*.zst" -mtime +60 -exec rm {} \;

#----------------------------------------------------------------------------
echo "begin ..."
for DB in $(mysql -h${MYHST} -u${MYUSR} -p${MYPWD} -N -e 'show databases' 2>/dev/null)
do
  for SYSDB in information_schema performance_schema sys
  do
    if [[ ${SYSDB} == ${DB} ]]; then continue 2; fi
  done

  echo "mysqldump database : ${DB} ..."
  mysqldump -h${MYHST} -u${MYUSR} -p${MYPWD} --single-transaction --events --routines -d -B ${DB} | zstd -f -T0 > ${DESTDIR}/${PREFIX}-${DB}_d_${SUFFIX}.zst  2>/dev/null
  mysqldump -h${MYHST} -u${MYUSR} -p${MYPWD} --single-transaction --events --routines    -B ${DB} | zstd -f -T0 > ${DESTDIR}/${PREFIX}-${DB}_${SUFFIX}.zst    2>/dev/null
done
echo "end."