Скрипт для создания дампов БД и их ротации
05 августа 2008, 14:04Допустим, у нас две БД - PostgreSQL и MySQL, надо держать дампы за последние 10 дней и отдельно - за 1-ое число каждого месяца.
sqlservice.sh :
#!/bin/sh
date=`date +"%Y%m%d"`
backup_dir="/.backup/dumps"
backup_name="$date-mysql"
backup_pg="$date-postgre"
/usr/local/bin/sudo -u postgres /usr/local/pgsql/bin/pg_dumpall -D -h localhost > $backup_dir/${backup_pg}.sql
/usr/local/mysql/bin/mysqldump -A --host=127.0.0.1 --user=user --password=password --compatible=mysql40 > $backup_dir/${backup_name}.sql
cd $backup_dir
rm -f ${backup_name}.sql.gz
rm -f ${backup_pg}.sql.gz
gzip ${backup_name}.sql
gzip ${backup_pg}.sql
/usr/bin/perl ./rotate.pl
Ну и вот скриптик ротации дампов за последние 10 дней (дампы лежат в /.backup/dumps/, а 1-ого числа сохраняем дампы в /.backup/dumps/monthly/)
rotate.pl :
#!/usr/bin/perl -w
use Date::Calc qw(:all);
$shortdate=`date +"%Y-%m-%d"`; chop($shortdate); ($cyear, $cmonth, $cday)=split/-/,$shortdate;
$days{"$cyear$cmonth$cday"}=1;
for (-10..0) {
($year,$month,$day) = Add_Delta_Days($cyear,$cmonth,$cday, $_);
$month="0$month" if length($month)==1;
$day="0$day" if length($day)==1;
$days{"$year$month$day"}=1;
}
chdir "/.backup/dumps";
opendir(DIR,"/.backup/dumps");
@files=grep/.sql.gz$/,readdir(DIR);
closedir(DIR);
foreach $f(@files) {
($date)=$f=~/(d+)/i;
if ($date=~/01$/) {
print $date." first of month detected ";
$tmp=`mv $f monthly/$f`;
}
elsif (exists $days{"$date"}) {
print $f." kept ";
}
else {
print $date." to del ";
$tmp=`rm -f $f`;
}
}