Date: Tue, 30 Sep 2008 16:38:32 -0700 From: Fred Condo <fcondo@quinn.com> To: John Almberg <jalmberg@identry.com> Cc: freebsd-questions@freebsd.org Subject: Re: Best way to back up mysql database Message-ID: <55B7D764-CCC4-458C-8D68-5D2509EAFFC9@quinn.com> In-Reply-To: <A2DC4770-11AA-49EF-A4AC-9BA03B27ABC0@identry.com> References: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com> <7F7FCA1B-E46C-4A44-9569-D6638A550FCE@hughes.net> <A2DC4770-11AA-49EF-A4AC-9BA03B27ABC0@identry.com>
next in thread | previous in thread | raw e-mail | index | archive | help
I run a script from root's crontab (not /etc/crontab) and keep the
login credentials in /root/.my.cnf so they don't have to be embedded
in the script. Not that $gzip is defined as /bin/cat because I move
copies offsite via rsync and disk space is abundant. This script keeps
30 daily backups (configurable).
Crontab entry:
13 20 * * * cd /bak/databases && /root/db_backup
"db_backup" perl script:
#! /usr/bin/perl
use strict;
my $maxbackups = 30;
my $gz='gz';
my $mysqldump = '/usr/local/bin/mysqldump';
my $gzip = '/bin/cat';
my $newfile;
my $filename = 'all_databases.sql';
my $curfile = $filename . ".$maxbackups";
unlink $curfile if -f $curfile;
my ($i, $j);
for ($i = $maxbackups - 2; $i >= 0; $i--) {
$j = $i + 1;
$curfile = $filename . '.' . $i;
$newfile = $filename . '.' . $j;
rename $curfile, $newfile if -f $curfile;
}
$curfile = $filename . '.' . '0';
my $command = "$mysqldump --opt --all-databases | $gzip > $curfile";
my $result;
$result = system $command and warn "$result";
On Sep 30, 2008, at 4:22 PM, John Almberg wrote:
>>
>> DATE=`date +%a`
>> #
>> echo $DATE
>> #
>> echo Backup Mysql database
>> mysqldump -h localhost -u YOURSQLUSERID -pYOURPASSWORD YOURDATABASE
>> >/usr/somedirectory/somefile_$DATE.backup
>> gzip -f /usr/somedirectory/somefile_$DATE.backup
>> /usr/bin/at -f /usr/somedirectory/mysqlbackup.sh midnight
>
> Ah, a much simpler solution than my ruby script. I hadn't thought to
> zip up the file before transferring it. That's an improvement I must
> add.
>
> Thanks: John
> _______________________________________________
> freebsd-questions@freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-questions
> To unsubscribe, send any mail to "freebsd-questions-unsubscribe@freebsd.org
> "
>
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?55B7D764-CCC4-458C-8D68-5D2509EAFFC9>