MySQL rotating backup script - plesk

Question

I have a MySQL backup script I picked up somewhere (can't recall where). It works when I run it manually, but run on cron as root it doesn't run for some reason.

There may be a cron log I can check somewhere, but I'm curious if anyone has any ideas as to why it's not creating my daily backups on cron.

#!/bin/bash

# Set the datestamp, login credentials and backup directory
export date=$(date +\%Y\%m\%d)
export creds="-uadmin -p`sudo cat /etc/psa/.psa.shadow`"
export backupdir="/root/backups-mydomain.com-mysql"

# delete week old files
find ${backupdir}/ -regex '.*.dump.gz' -mtime +4 -exec rm {} \;

# dump databases to the backupdir
echo "show databases;" | mysql ${creds} | egrep -v ^Database$ | \
        awk '{print "mysqldump ${creds} "$1" | \
        gzip > ${backupdir}/db-"$1"-${date}.dump.gz"}' | \
        sh

I have tried the following cron entries, neither of which work:

10      0       *       *       *       /root/_backups-generate-mysql.sh

10      0       *       *       *       /bin/sh /root/_backups-generate-mysql.sh

I'm getting this error with the suggestion given below:

/bin/sh: /root/_backups-generate-mysql.sh: Permission denied

Answer 1

Try the script with your actual Plesk admin password hard coded into your backup script, instead of relying on sudo cat /etc/psa/.psa.shadow to get the password for you -- it's no less secure than having your script sudo cat the password file without a password, and removes one layer of abstraction.

Better yet, create a user with LOCK TABLES, RELOAD, and SELECT privileges for all databases, and use it for your backup purposes.

If you're looking for a replacement script, I suggest AutoMySQLBackup. I've been using it for years on several Plesk implementations.

Answer 2

Also see reasons why cron does not run.

Without the redirections alvosu suggests, cron output will go to email, so the user running those cron jobs might have some errors in their mailbox.

The error message says to me that the user cron is running as (which is whoever owns that crontab file) does not have rights to execute the script. You'll want to change permissions on the script.