i formulate infinity and store it deep inside of me

Powered by Astir.News engine

Скрипт для создания дампов БД и их ротации

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`;
}

}

Tags: db , mysql , postgresql