2

Backing up 30 DB's with .sh script using mysqldump. 27 DB's backups succeed and are the same size each day but 3 DB's do not and seem to just stop randomly part way through the dump with no errors.

Here is the script

#!/bin/bash

appname=myapp

dbname=mydb

dbuser=myuser

dbpass=mypass

datestamp=`date +%d%m%y`

rm -f /var/backups/* > /dev/null 2>&1
mysqldump -u$dbuser -p$dbpass $dbname > /var/backups/$dbname-$datestamp.sql && gzip /var/backups/$dbname-$datestamp.sql

tar -zcf /var/backups/data-$datestamp.tar.gz /var/www/data > /dev/null 2>&1

tar -zcf /var/myapp-backups/myapp-$datestamp.tar.gz /var/www/myapp > /dev/null 2>&1

I have checked the failing DB's with Mysqlcheck and everything seems fine so now I am not sure where to look for errors

There is no errors my the var/log/mysql and there is an error.log written today at 06.32 which would be around the right time the cron runs but its empty

Any ideas Thanks

1
  • Mysqldump works on the DB every-time I run it on the fly with no errors?? Not sure why at 2.00am, it fails when scheduled with the cron on the above script Aug 1, 2016 at 12:51

2 Answers 2

2

To get errors logs while you're doing dumps, add -v and 2> log.txt:

mysqldump -v -u -p databasename > databasesqlfile.sql 2> log.txt

You can also enable detail logging in mysql configuraion and check those logs if you get error while doing dumps. Start mysql and run:

show variables like '%log%;

Also, in config file, enable following logs:

[mysqld]

log-bin

log

log-error

log-slow-queries

and restart mysql to apply changes.

1
  • Odd worked perfectly 6 times in a row and wrote to file with no errors and the full DB backed up ???????? Thanks Aug 1, 2016 at 12:44
1

You probably need to capture the stderr of mysqldump

mysqldump ... 2>/var/log/mysqldump.err
2
  • Yes you are right but I am having issues working out how to capture this with my current output to .sql files Aug 1, 2016 at 10:30
  • I showed you how to do it in my answer - 2>file is standard notation for redirecting stderr to a file.
    – user9517
    Aug 1, 2016 at 10:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .