BackupPC MySQL on Plesk server

mysqldumpbk.sh

#!/bin/bash
TXTDEST="/home/<username>/mysqlbackup/dblist.txt"
BKPDEST="/home/<username>/mysqlbackup"
PLESK="/usr/sbin/plesk"
DATEYMD=`date +"%Y-%m-%d"`

# Create Lockfile
LOCKFILE=/tmp/myBkup.lock
if [ -f $LOCKFILE ]; then
echo "Lockfile $LOCKFILE exists, exiting!"
exit 1
fi
touch $LOCKFILE

#create directory for today backup
mkdir ${BKPDEST}/${DATEYMD}

#delete files older than 10 days
echo "== Deleting files older than 10 days =="
find ${BKPDEST} -type d -name '*' -mtime +10 -exec rm -rf {} \;
echo "== Files deleted =="

#start databases dump
echo "== MySQL Dump Starting $(date) =="
${PLESK} db -e "show databases" | grep -v -E "^Database|information_schema|performance_schema|phpmyadmin" > ${TXTDEST}
cat ${TXTDEST} | while read i; do ${PLESK} db dump "$i" > ${BKPDEST}/${DATEYMD}/"$i".sql; done
echo "== MySQL Dump Ended $(date) =="
rm $LOCKFILE

usage.md

This script is intended to be used with BackupPC to dump all databases before running a backup task on a Plesk host using CentOS 7. The <username> needs to be a high privilege one.

1. create mysqlbackup directory and cd:
   1. mkdir /home/<username>/mysqlbackup
   2. cd /home/<username>/mysqlbackup
2. create file on host:
   vi mysqldumpbk.sh
3. paste mysqldumpbk.sh content:
4. make the script executable:
   chmod +x /home/<username>/mysqlbackup/mysqldumpbk.sh
5. set $Conf{DumpPreUserCmd} on BackupPC with:
   $sshPath -q -x -l <username> $host sudo /home/<username>/mysqlbackup/mysqldumpbk.sh
6. add visudo rule
   1. root login onto host
   2. visudo
   3. paste on end of file
      #let user <username> use custom mysql backup script without password
<username> ALL=NOPASSWD: /home/<username>/mysqlbackup/mysqldumpbk.sh