0

I have a mysqlbackup script that I would like for it to: - backup all databases individually - log any errors to file - email that file

what I have right now is: 

\#!/bin/sh

TIMESTAMP=$(date +"%F-%H-%M")
BACKUP_DIR="/mnt/usb/mysql/"
MYSQL_USER="backup"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="*****"
MYSQLDUMP=/usr/bin/mysqldump
EMAIL=*****
RETENTION="+14"
MSG=/tmp/mysqlbackup.messages

echo "Date: $(date)">$MSG
echo "Hostname: $(hostname)" >>$MSG
echo "Backup script has run. [don't worry, it won't get far!]" >>$MSG
echo " ----- ----- ----- " >> $MSG
echo " " >> $MSG
echo " " >> $MSG

mkdir -p "$BACKUP_DIR/$TIMESTAMP"

databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`

for db in $databases; do
        $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$TIMESTAMP/$db.gz"
        echo "Backing up: $db" >> $MSG
done

echo " " >> $MSG
echo " " >> $MSG
echo " ----- ----- ----- " >> $MSG
echo " " >> $MSG
echo " " >> $MSG

find $BACKUP_DIR* -mtime $RETENTION -exec rm {} \;  >> $MSG 2>&1

mail  -s "MySQL Backup script has run" "$EMAIL" <$MSG
rm -f $MSG

Which works pretty much perfectly, however, I get these errors/warniongs to the console:

[root@linux scripts]# ./mysqldump-all-databases.sh
mysqldump: Error 1194: Table 'XXXXX' is marked as crashed and should be repaired when dumping table `XXXXX` at row: 81052
mysqldump: Error 1194: Table 'XXXXX' is marked as crashed and should be repaired when dumping table `XXXXX` at row: 68532
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

How can I log them to the $MSG file to get emailed? Even better, is it possible to change the email subject if errors are found [so it doesn't get ignored]

Sean Kimball
  • 4,506
  • 9
  • 42
  • 73
  • @BroSlow, if I try to add something like $MSG >> 2>&1 to the end of the line that actually dumps and zips the dbs then it tries to dump the zip to the $MSG body. – Sean Kimball Oct 23 '14 at 02:31
  • 1
    Something like `$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db 2>>$MSG | ...`? – Reinstate Monica Please Oct 23 '14 at 02:31
  • Or are you trying to [store stderr in a variable](http://stackoverflow.com/questions/3130375/bash-script-store-stderr-in-variable), so you can append later? Which would also solve your last problem, as you can just check whether the variable has an assigned value `[[ -n $varable ]] && dowhatever` – Reinstate Monica Please Oct 23 '14 at 02:37
  • ok - thanks, I got the 2>>$MSG to work, problem was I was putting it at the end of the line, not at the end of mysqldump. fraid I don't understand the variable bit, I need to log errors to an email [done- works now] changing the subject line if errors happened would be nice. – Sean Kimball Oct 23 '14 at 02:43
  • 1
    Use the [--log-error](http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_log-error) argument – Greg Guida Oct 23 '14 at 02:55
  • @SeanKimball For saving it in a variable. Try something like `var=$(($MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$TIMESTAMP/$db.gz") 2>&1)`. And then something like `if [[ -n $var ]]; then subject=error; else subject=no\ error; fi` If you use it multiple times or in a loop, make sure to blank out `var`, i.e. `var=` – Reinstate Monica Please Oct 23 '14 at 03:12

0 Answers0