Auto MySQL Backup?

[Anon]

Is there a way to backup the DB (MySQL) every night and have the server (Linux) mail it to my address? I'm using sendmail.

Thanks for any help!

JC

[jcleary]

I am currently working on something like this. I think you'll need to create a cron job which runs a script. The script will to call the mysqldump command, the syntax for which can be found at www.mysql.com, but looks something like

/usr/local/mysql/bin/mysqldump --user=root --pass=your_password database_name > /tmp/database_name.sql

Then you'll need to read about the "mail" command and see how to attach it. Sorry if this isnt much help

John

[Anon]

Try something like the following (there's quite a few bugs .. but the idea is here .. thanks in part to John Cleary's suggestion above)

#!/usr/local/bin/perl -w

$sendmail = '/usr/lib/sendmail' # to find out where your sendmail is to a /usr/ucb/whereis sendmail
$mysqldump = '/usr/local/mysql/bin/mysqldump' # location of mysqldump
$mysqluser = 'janmike' # replace with your root username
$mysqlpass = 'password' # replace with appropriate password
$mysqlhost = 'janmike' # replace with your hostname
$tmpfilename = '/tmp/output.sql'
$recipient = '[e-mail]'

build command here

$command = $mysqldump . " --user=$mysqluser --pass=$mysqlpass > $tmpfilename";

run mysqldump command

empty array

@systemoutput = ();
@systemoutput = system("$command");

check systemoutput

write your checks here to make sure that system output was properly executed

assuming everything went well, open sendmail library

my $body = "";
open(FILE, "$tmpfilename") or die ("Cannot open $tmpfilename: $!");
if (!open(MAIL, "| $sendmail $recipient")) {
&error("Could not start mail program");
}
while (<FILE>) {

build body messege

$body = $_ . $body . "\n";
}
print(MAIL "To: $recipient\n");
print(MAIL $fromline . "\n");
print(MAIL "\n");
print(MAIL "$body");
close(MAIL);
close(FILE);

exit;

[Anon]

I had to add the -A to include all databases, but you could use --databases db1 db2 db(n)....

this will create a dump on a daily basis when you add it to crontab

i.e.
59 11 * /path/to/this/file/msyql_dump.pl >/dev/null 2>&1

#!/usr/local/bin/perl -w
my($sendmail,$mysqldump,$mysqluser,$mysqlpass,$mysqlhost,$tmpfilename,$recipient,$command,@systemoutput,$body);

$sendmail = '/usr/lib/sendmail'; # to find out where your sendmail is to a /usr/ucb/whereis sendmail
$mysqldump = '/usr/local/mysql/bin/mysqldump -A'; # location of mysqldump
$mysqluser = 'mysql_user_here'; # replace with your root username
$mysqlpass = 'mysql_password_here'; # replace with appropriate password
$mysqlhost = 'localhost'; # replace with your hostname
$tmpfilename = &create_filename();
$recipient = '[e-mail]';

build command here

$command = $mysqldump . " --user=$mysqluser --pass=$mysqlpass > $tmpfilename";

run mysqldump command

empty array

@systemoutput = ();
@systemoutput = system("$command");

check systemoutput

write your checks here to make sure that system output was properly executed

assuming everything went well, open sendmail library

my $body = "";
open(FILE, "$tmpfilename") or die ("Cannot open $tmpfilename: $!");
if (!open(MAIL, "| $sendmail $recipient")) {
&error("Could not start mail program");
}
while (<FILE>) {

build body messege

$body = $_ . $body . "\n";
}
print(MAIL "To: $recipient\n");
print(MAIL "\n");
print(MAIL "$body");
close(MAIL);
close(FILE);

sub create_filename
{
my($date, $filename);
chomp ($date = date '+%d');
$filename = "/directory_path_here/mysqlDump_$date.sql";
return $filename;

}

exit;