1

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

  • 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 – user2786484 Aug 1 '16 at 12:51
1

You probably need to capture the stderr of mysqldump

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

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.