After creating a usable MySQL database backup script and having tested it throughly, I put it in the /etc/cron.daily directory where I had expected it to run automatically. I had even tested it from that directory manually with success. After some time had past, I realized that the script was running as it read in the logs; however had only run for a second. The script didn’t do anything.
Looking for a resolution to an unrelated issue, I came across something that turned out to be the solution to this issue.
The cron environment is not the same as the environment of the root or the user. To verify the environment path of your working environment a simple command be issued.
echo $PATH
To check the environment path of cron, you must first emulate the cron envionment.
env -i /bin/bash --noprofile --norc
Then run the same echo $PATH command as above and see that the path is likely not the same. This was true for my MySQL script. The mysql and mysqldump commands were in a directory not part of the cron path environment, however, was part of mine.
The solution was to add the path of the binaries, in this case, MySQL binaries to the /etc/cron.daily script. like so.
PATH=$PATH;/path/to/mysql/bin
Now for the script including the modified PATH environment.
#!/bin/bash
# Do NOT forget to modify the path, the cron environment will NOT be all inclusive.
PATH=$PATH:/opt/app/mysql/bin
TIMESTAMP=$(date +"%F")
BACKUP_DIR="/opt/mysql-backups"
MYSQL_USER="mybackupuser"
MYSQL_PASSWORD=mypassword
# Create a directory for the daily backups, if it doesn't already exist.
[ -d $BACKUP_DIR/$TIMESTAMP ] || mkdir -p $BACKUP_DIR/$TIMESTAMP
# Get a list of all user created databases
databases=$(mysql --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)")
for db in $databases; do
mysqldump --user=$MYSQL_USER -p$MYSQL_PASSWORD --events --opt --single-transaction $db | gzip > "$BACKUP_DIR/$TIMESTAMP/$db.gz"
done
# Remove backups older than 10 days (DANGEROUS!! BE CAREFUL!!)
find $BACKUP_DIR/ -maxdepth 1 -type d -mtime +10 -exec rm -rf {} \;
Done. By the way, this was tested on a CentOS 6.6 Linux distribution.
Source(s)
http://stackoverflow.com/questions/2135478/how-to-simulate-the-environment-cron-executes-a-script-with
http://unix.stackexchange.com/questions/26047/how-to-correctly-add-a-path-to-path
http://dev.mensfeld.pl/2013/04/backup-mysql-dump-all-your-mysql-databases-in-separate-files
http://www.linuxbrigade.com/back-up-all-of-your-mysql-databases-nightly
