LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 05-24-2006, 07:05 PM   #1
stardotstar
Member
 
Registered: Nov 2002
Location: /au/qld/bne/4157
Distribution: Gentoo mactel-linux
Posts: 238

Rep: Reputation: 30
Question How to improve my mysql backup cron job


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
 
Old 05-24-2006, 07:58 PM   #2
paulsm4
Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
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

Last edited by paulsm4; 05-24-2006 at 08:04 PM.
 
Old 05-24-2006, 08:02 PM   #3
gilead
Senior Member
 
Registered: Dec 2005
Location: Brisbane, Australia
Distribution: Slackware64 14.0
Posts: 4,123

Rep: Reputation: 162Reputation: 162
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
 
Old 05-24-2006, 10:49 PM   #4
stardotstar
Member
 
Registered: Nov 2002
Location: /au/qld/bne/4157
Distribution: Gentoo mactel-linux
Posts: 238

Original Poster
Rep: Reputation: 30
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
 
Old 05-24-2006, 10:57 PM   #5
gilead
Senior Member
 
Registered: Dec 2005
Location: Brisbane, Australia
Distribution: Slackware64 14.0
Posts: 4,123

Rep: Reputation: 162Reputation: 162
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.
 
Old 05-25-2006, 12:23 AM   #6
stardotstar
Member
 
Registered: Nov 2002
Location: /au/qld/bne/4157
Distribution: Gentoo mactel-linux
Posts: 238

Original Poster
Rep: Reputation: 30
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
 
Old 05-25-2006, 01:15 AM   #7
gilead
Senior Member
 
Registered: Dec 2005
Location: Brisbane, Australia
Distribution: Slackware64 14.0
Posts: 4,123

Rep: Reputation: 162Reputation: 162
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...
 
Old 05-25-2006, 06:29 AM   #8
spirit receiver
Member
 
Registered: May 2006
Location: Frankfurt, Germany
Distribution: SUSE 10.2
Posts: 424

Rep: Reputation: 33
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
 
Old 05-25-2006, 07:11 AM   #9
spirit receiver
Member
 
Registered: May 2006
Location: Frankfurt, Germany
Distribution: SUSE 10.2
Posts: 424

Rep: Reputation: 33
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Optimize MySQL Database Cron Job Help br00tal Linux - General 3 10-03-2005 05:45 PM
cron backup job not running dsschanze Linux - General 9 07-29-2005 09:47 PM
Mysql dumps via Cron job. Thrifty Linux - Software 2 07-28-2004 03:13 PM
Cron Job to Repair MySQL tables? Shinjuku Linux - Software 0 07-13-2003 09:34 AM
cron job - backup medamnit Linux - Newbie 4 05-24-2002 04:37 AM


All times are GMT -5. The time now is 03:02 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration