Q As part of my day-to-day work, I need to back up MySQL databases from various servers (both local to the office and external). I have created cron jobs for each server to be backed up at night - some servers have only one or two databases, but others have hundreds. My cron jobs are simple bash scripts. They take the name of the database being backed up and append the date and time to create a unique filename, then use mysqldump to retrieve the data:
mydatabase="mydatabase 'date'.sql" filename=${mydatabase// /_}
mysqldump -h mydbaseserver1.co.uk -u username -ppassword mydatabase
> /var/backups/sqlbackup/mydbaseserver1/$filename
This all works perfectly. If a database fails to be backed up, the cron job sends the error report to me on email. However, the email does not tell me which database failed. As each cron job contains backups for more than one database, I could get a confusing email like this:
'mysqldump: Got error: 2013: Lost connection to MySQL server during query when retrieving data from server. mysqldump: Got error: 2013: Lost connection to MySQL server during query when retrieving data from server. mysqldump: Got error: 2013: Lost connection to MySQL server during query when retrieving data from server'.
The subject of the message lets me know which cron job was running, but apart from going in to the directory and looking through the files for backups that haven't been created properly (far too laborious and time- consuming!) there is no way to identify which databases failed. It does not really seem practical to create single cron jobs for every database as there are hundreds of databases. Is there a way that the error email I get from the cron job can include the database names of backup jobs that have failed?
A Yes, it is possible to configure mysqldump to give more verbose output using the -v or -verbose switch. This should report the status of individual database successes and failures. The mysqldump output should be emailed to you by cron. As an extra level of intelligence you could set up a local mail filter on your workstation or procmail on the server to parse for a keyword synonymous with a failed database backup and only bring it to your attention if a failure has occurred. To have cron mail you the results, add a MAILTO=username to the top of your /etc/crontab or an individual user's crontab. An alternative to mysqldump is mysqlhotcopy. Many administrators prefer it because of its supposedly superior locking and better reliability. You can find information on mysqlhotcopy from MySQL directly at http://dev.mysql.com/doc/mysql/en/mysqlhotcopy.html.