How to improve my mysql backup cron job

stardotstar · 05-24-2006, 06:05 PM

Hi Guys,

as part of my ambitious foray into shell programming I have made a cron.daily to do the backup of my mysql database.

Basically I am going to setup a scp or rsync of this process when I have the other server properly setup.

Until that time I want to have my cron.daily do

1) a mysqldump (secure if possible - at the moment I am putting the password in in clear text = bad)
2) a bzip2 of the sql backup
3) a mail to the admin group summarising the output of these commands.

What I have so far is embarasingly simple and simply a concatenation of the two commands I have been using for this task manually!

Code:

stardotstar@helios:~$ cat /etc/cron.daily/archeli_backup
#!/bin/bash
mysqldump -u stardotstar --password=********** archeli_mysql > /home/stardotstar/backups/archeli_mysql_daily.sql
bzip2 -f --small /home/stardotstar/backups/archeli_mysql_daily.sql
stardotstar@helios:~$

I need to know how to direct any output from these commands to a file that I include in the email. So I get an email that says something like:

"Hi admingroup,

The daily backup is complete.
The output generated was:
[any errors or output here]"

Ideally I would like the backup files generated with date and time and I understand that I can get mysqldump to do this for me which will take care of the naming conventions for when I do my scp for example.

I cannot work out yet how to send mail from cron. mail admgrp[at]blahblah.com.au -s "Daily Backup" directs me to enter the message body and then break out...

I'm sure there would be a way of getting mail to send to that address and include as the body a file that is the template like above and then any output from the commands.

Can some kind guru give me some help creating this please?

In the mean time I will continue to tinker and fool around amongst man pages and google referernces L

Will

paulsm4 · 05-24-2006, 06:58 PM

Try "mail -s"

Here's a good tutorial on the mail (and other parts of your question):
http://www.4webhelp.net/tutorials/misc/cron.php

gilead · 05-24-2006, 07:02 PM

I don't have any advice for your MySQL backup, but if you want to send output using the mail command, you can just pipe the output to mail. The following example may help:

Code:

/usr/local/bin/backup 2>&1 | mail -s "Backup completed" root

Or, you can use sendmail directly - mail is probably better though:

Code:

(echo "To: A. User <username@somewhere.com>"
 echo "From: A. Sender <othername@someplaceelse.com>"
 echo "Subject: Backup completed"
 echo
 /usr/local/bin/backup
) 2>&1 | sendmail -t

stardotstar · 05-24-2006, 09:49 PM

Thanks guys, I will try that.

just one thing gilead, is the 2>&1 necessary for the piping or can i just go:

Code:

mysqldump -u stardotstar --password=********** archeli_mysql > /home/stardotstar/backups/archeli_mysql_daily.sql && bzip2 -f --small /home/stardotstar/backups/archeli_mysql_daily.sql | mail -s "Backup Completed" root

gilead · 05-24-2006, 09:57 PM

The 2>&1 just diverts the stderr data (if there is any) to the same place that stdout goes. If all you want is the output of your commands you don't need it, but if something goes wrong it can be useful.

stardotstar · 05-24-2006, 11:23 PM

So... how's this??:

Code:

mysqldump -u stardotstar --password=********** archeli_mysql > /home/stardotstar/backups/archeli_mysql_daily.sql && bzip2 -f --small /home/stardotstar/backups/archeli_mysql_daily.sql 2>&1 | mail -s "Backup Completed" root

gilead · 05-25-2006, 12:15 AM

It looks good. Having the 2>&1 after the redirect of the stdout to archeli_mysql_daily.sql means that any error messages will also go to archeli_mysql_daily.sql...

spirit receiver · 05-25-2006, 05:29 AM

You can also redirect all output to STDIN of mail, this might make a script more readable:

Code:

#! /bin/bash

# pipe fd4 to mail
exec 4> >( mail -s "L0\/\/E\$T P®lCE" root )

# redirect STDOUT and STDERR to fd4
exec 1>&4 2>&4

echo "Urgent business proposal!"
cat ~/does-not-exist  # this should cause some error printed to STDERR

spirit receiver · 05-25-2006, 06:11 AM

As for the other question:

You'll probably want to declare a variable as in TARGET="$( date +%F )-backup.sql" and use that variable as the file name. I guess you know how to bzip2 the resulting file.
The password will have to be stored somwhere. I propose that you create a file /root/.my.cnf that is accessible to root only and contains the following:
Code:
```
[mysqldump]
password = ********
```
Then you won't need to supply that password in the backup script any more.