Архивация списаний

[JAO]

Уже почти год использую у себя перловый скрипт для архивации списаний. Идеи заимствованы в основном у Magnum72. Отличие этого скрипта в том, что он требует минимального дописывания при вводе новых типов таблиц в структуру архивов.

Скрипт может быть повешен на крон. Выполняться он должен раз в месяц. Это важно, скрипт построен именно с таким расчетом. У меня выполняется в два часа ночи первого числа.

Из таблиц выделяются данные за прошедший месяц и переливаются в архив, данные же начиная с первого числа текущего месяца и до момента запуска скрипта переливаются в текущие таблицы, которые при этом уменьшаются в размере. Для скорости архив переводится в формат MyISAM. Архив хранится в отдельной базе данных MySQL. Плюсы - дамп базы UTM снимается очень быстро.

Работает на версии MySQL не ниже 5.0, из-за наличия запроса CREATE TABLE ... LIKE. Также в случае использования движка InnoDB есть смысл включить опцию innodb_file_per_table, иначе архивация не даст выигрыша в скорости.

Исправления и доработки приветствуются.

[JAO]

Скрипт для версии 5.2.1-006

Код: Выделить всё

#!/usr/bin/perl

use DBI;

$db_host = 'localhost';
$db_user = 'root';
$db_pass = '';
$db_name = 'UTM5';
$db_arc_name = 'UTM5arc';

$tbl_count = 2;
@tbl_names = ('discount_transactions_all','discount_transactions_iptraffic_all');
@tbl_types = (1,2);
@tbl_datefields = ('discount_date','discount_date');
@tbl_shortnames = ('dta','dti');

$dbh = DBI->connect("DBI:mysql:$db_name:$db_host",$db_user,$db_pass,{ RaiseError => 1}) or die "\nConnection failed...\nError: $DBI::errstr\n"; 
$sth = $dbh->prepare ("SELECT YEAR(NOW())");
$sth->execute ();
$y = ($sth->fetchrow_array ())[0];
$sth = $dbh->prepare ("SELECT MONTH(NOW())");
$sth->execute ();
$m = ($sth->fetchrow_array ())[0];
$sth = $dbh->prepare ("SELECT UNIX_TIMESTAMP('$y-$m-01 0:00:00')");
$sth->execute ();
$end_date = ($sth->fetchrow_array ())[0];
$m--;
if ($m == 0)
{ $m = 12;
  $y--;
}
$suffix = sprintf ("%04d_%02d",$y,$m);
$sth = $dbh->prepare ("SELECT UNIX_TIMESTAMP('$y-$m-01 0:00:00')");
$sth->execute ();
$start_date = ($sth->fetchrow_array ())[0];
$sth = $dbh->prepare ("SELECT MAX(archive_id) FROM archives");
$sth->execute ();
$arc_id = ($sth->fetchrow_array ())[0] + 1;
undef $sth;
for &#40;$j = 0;$j < $tbl_count;$j++&#41;
&#123; $tbl_orig = $tbl_names&#91;$j&#93;;
  $tbl_shortname = $tbl_shortnames&#91;$j&#93;;
  $tbl_bkp = 'arc_' . $tbl_shortname;
  $datefield = $tbl_datefields&#91;$j&#93;;
  $tbl_type = $tbl_types&#91;$j&#93;;
  $full_arcname = $db_arc_name . "." . $tbl_shortname . "_" . $suffix;
  print "Processing $tbl_orig\n";
  $dbh->do &#40;"ALTER TABLE $tbl_orig RENAME TO $tbl_bkp"&#41;;
  $dbh->do &#40;"CREATE TABLE $tbl_orig LIKE $tbl_bkp"&#41;;
  $dbh->do &#40;"INSERT INTO $tbl_orig SELECT * FROM $tbl_bkp WHERE $datefield>=$end_date"&#41;;
  $dbh->do &#40;"DELETE FROM $tbl_bkp WHERE $datefield>=$end_date"&#41;;
  $dbh->do &#40;"ALTER TABLE $tbl_bkp ENGINE=MyISAM"&#41;;
  $dbh->do &#40;"CREATE TABLE $full_arcname LIKE $tbl_bkp"&#41;;
  $dbh->do &#40;"ALTER TABLE $full_arcname ENGINE=MyISAM"&#41;;
  $dbh->do &#40;"INSERT INTO $full_arcname SELECT * FROM $tbl_bkp"&#41;;
  $dbh->do &#40;"DROP TABLE $tbl_bkp"&#41;;
  $end_date--;
  $dbh->do &#40;"INSERT INTO archives &#40;archive_id,table_type,table_name,start_date,end_date&#41; VALUES &#40;'$arc_id','$tbl_type','$full_arcname','$start_date','$end_date'&#41;"&#41;;
  $end_date++;
&#125;
$dbh->disconnect &#40;&#41;;

[JAO]

Скрипт для версии 5.2.1-007 (должен подойти и для 5.2.1-008, судя по документации)

Код: Выделить всё

#!/usr/bin/perl

use DBI;

$db_host = 'localhost';
$db_user = 'root';
$db_pass = '';
$db_name = 'UTM5';
$db_arc_name = 'UTM5arc';

$tbl_count = 7;
@tbl_names = ('discount_transactions_all','discount_transactions_iptraffic_all',	      'tel_sessions_log','tel_sessions_detail','dhs_sessions_log','dhs_sessions_detail','payment_transactions');
@tbl_types = (1,2,3,4,5,6,7);
@tbl_datefields = ('discount_date','discount_date','recv_date','recv_date','recv_date','recv_date','payment_enter_date');
@tbl_shortnames = ('dta','dti','tsl','tsd','dsl','dsd','ptr');

$dbh = DBI->connect("DBI:mysql:$db_name:$db_host",$db_user,$db_pass,{ RaiseError => 1}) or die "\nConnection failed...\nError: $DBI::errstr\n"; 
$sth = $dbh->prepare ("SELECT YEAR(NOW())");
$sth->execute ();
$y = ($sth->fetchrow_array ())[0];
$sth = $dbh->prepare ("SELECT MONTH(NOW())");
$sth->execute ();
$m = ($sth->fetchrow_array ())[0];
$sth = $dbh->prepare ("SELECT UNIX_TIMESTAMP('$y-$m-01 0:00:00')");
$sth->execute ();
$end_date = ($sth->fetchrow_array ())[0];
$m--;
if ($m == 0)
{ $m = 12;
  $y--;
}
$suffix = sprintf ("%04d_%02d",$y,$m);
$sth = $dbh->prepare ("SELECT UNIX_TIMESTAMP('$y-$m-01 0:00:00')");
$sth->execute ();
$start_date = ($sth->fetchrow_array ())[0];
$sth = $dbh->prepare ("SELECT MAX(archive_id) FROM archives");
$sth->execute ();
$arc_id = ($sth->fetchrow_array ())[0] + 1;
undef $sth;
for &#40;$j = 0;$j < $tbl_count;$j++&#41;
&#123; $tbl_orig = $tbl_names&#91;$j&#93;;
  $tbl_shortname = $tbl_shortnames&#91;$j&#93;;
  $tbl_bkp = 'arc_' . $tbl_shortname;
  $datefield = $tbl_datefields&#91;$j&#93;;
  $tbl_type = $tbl_types&#91;$j&#93;;
  $full_arcname = $db_arc_name . "." . $tbl_shortname . "_" . $suffix;
  print "Processing $tbl_orig\n";
  $dbh->do &#40;"ALTER TABLE $tbl_orig RENAME TO $tbl_bkp"&#41;;
  $dbh->do &#40;"CREATE TABLE $tbl_orig LIKE $tbl_bkp"&#41;;
  $dbh->do &#40;"INSERT INTO $tbl_orig SELECT * FROM $tbl_bkp WHERE $datefield>=$end_date"&#41;;
  $dbh->do &#40;"DELETE FROM $tbl_bkp WHERE $datefield>=$end_date"&#41;;
  $dbh->do &#40;"ALTER TABLE $tbl_bkp ENGINE=MyISAM"&#41;;
  $dbh->do &#40;"CREATE TABLE $full_arcname LIKE $tbl_bkp"&#41;;
  $dbh->do &#40;"ALTER TABLE $full_arcname ENGINE=MyISAM"&#41;;
  $dbh->do &#40;"INSERT INTO $full_arcname SELECT * FROM $tbl_bkp"&#41;;
  $dbh->do &#40;"DROP TABLE $tbl_bkp"&#41;;
  $end_date--;
  $dbh->do &#40;"INSERT INTO archives &#40;archive_id,table_type,table_name,start_date,end_date&#41; VALUES &#40;'$arc_id','$tbl_type','$full_arcname','$start_date','$end_date'&#41;"&#41;;
  $end_date++;
&#125;
$dbh->disconnect &#40;&#41;;

[JAO]

Назначение переменных:

$tbl_count - число архивируемых таблиц
@tbl_names - имена архивируемых таблиц
@tbl_types - типы архивируемых таблиц (порядок должен соответствовать порядку в массиве @tbl_names), эта информация запишется в соответствующее поле таблицы archives
@tbl_datefields - названия полей с датой в архивируемых таблицах (здесь порядок тоже должен соответствовать порядку в массиве @tbl_names)
@tbl_shortnames - короткие имена для архивных таблиц, к которым допишется значение архивируемого месяца и года (результат может быть вида dta_2010_06)

[drag0mir]

приветствую.
делаю архивацию первый раз, очень долго выполняется так как таблицы по 12 гигов, особенно долго перегоняется в myisam
до сих пор идет обработка первой таблицы discount_transactions_all, уже 16 часов.
сейчас на стадии
ALTER TABLE arc_dta ENGINE=MyISAM

версия 5.1.2-007
собственно есть несколько вопросов
1. В связи с таким длительным временем всей операции, хотелось бы не останавливать ядро биллинга при запуске скрипта, не приведет ли это к плохим последствиям?
2. Почему то сбилось время на сервере, заметил правда не сразу, но по моему это произошло при запуске скрипта 16 с лишним часов назад

Код: Выделить всё

# date
Сбт Янв  1 16:27:58 MSK 2011

3. Почему сначала делается бекапная таблица в оригинальной базе, а затем её переносим в другую таблицу? Делая лишние движения.

Код: Выделить всё

  $dbh->do ("ALTER TABLE $tbl_orig RENAME TO $tbl_bkp");
  $dbh->do ("CREATE TABLE $tbl_orig LIKE $tbl_bkp");
  $dbh->do ("INSERT INTO $tbl_orig SELECT * FROM $tbl_bkp WHERE $datefield>=$end_date");
  $dbh->do ("DELETE FROM $tbl_bkp WHERE $datefield>=$end_date");
  $dbh->do ("ALTER TABLE $tbl_bkp ENGINE=MyISAM");
  $dbh->do ("CREATE TABLE $full_arcname LIKE $tbl_bkp");
  $dbh->do ("ALTER TABLE $full_arcname ENGINE=MyISAM");
  $dbh->do ("INSERT INTO $full_arcname SELECT * FROM $tbl_bkp");
  $dbh->do ("DROP TABLE $tbl_bkp"); 

Нельзя ли сразу делать архивную таблицу в отдельной базе?
примерно таким образом:

Код: Выделить всё

  $dbh->do ("ALTER TABLE $tbl_orig RENAME TO $full_arcname");
  $dbh->do ("CREATE TABLE $tbl_orig LIKE $full_arcname");
  $dbh->do ("INSERT INTO $tbl_orig SELECT * FROM $full_arcname WHERE $datefield>=$end_date");
  $dbh->do ("DELETE FROM $full_arcname WHERE $datefield>=$end_date");
  $dbh->do ("ALTER TABLE $full_arcname ENGINE=MyISAM");

В чем тут подводные камни?

[JAO]

Перед тем, как гонять скрипт, было бы неплохо собрать архив вручную за периоды до текущего месяца (это плюс по скорости)

Если первый раз запускаете, то по завершении руками поправьте поле start_date в таблице archives, иначе показывать будет только прошедший месяц, что не есть хорошо. Для этого надо посмотреть archive_id в таблице и ее имя, затем вытащить из нее минимальное значение поля времени и поставить его в start_date для этой таблицы. И так для всех таблиц в архиве.

Насчет создания архивной таблицы сразу... попробуйте. Может быть и можно. Я писал его быстренько, пользуясь только проверенными движениями. Может они где и лишние. При регулярной ежемесячной архивации это не столь существенный вопрос.

Почему сбилось время, не знаю. Скрипт только показания часов берет, да и те через MySQL.

Наличие полного бэкапа очередной архивируемой таблицы до конца отработки всех операций по ней выручает в случае завала скрипта. Тогда данные остаются на месте и их можно рассортировать вручную.

Надо будет написать скрипт для первого запуска, который раскладывал бы сам это всё по месяцам. Работа хотя и однократная, но раз ее можно сделать быстрее, почему бы нет.

[drag0mir]

Перед тем, как гонять скрипт, было бы неплохо собрать архив вручную за периоды до текущего месяца (это плюс по скорости)

ага, согласен ))

Если первый раз запускаете, то по завершении руками поправьте поле start_date в таблице archives, иначе показывать будет только прошедший месяц, что не есть хорошо. Для этого надо посмотреть archive_id в таблице и ее имя, затем вытащить из нее минимальное значение поля времени и поставить его в start_date для этой таблицы. И так для всех таблиц в архиве.

ага, я после завершения сразу залез в таблицу archives посмотрел параметр start_date и понял что он равен началу предыдущего месяца, поправил его ручками и всё стало КРАСИВО )) (так, к слову: при первом запуске скрипт у меня отрабатывал около 2 суток )))

Насчет создания архивной таблицы сразу... попробуйте. Может быть и можно. Я писал его быстренько, пользуясь только проверенными движениями. Может они где и лишние. При регулярной ежемесячной архивации это не столь существенный вопрос.

да времени на эксперименты особо нет, сроки поджимают, начальнег скорее хочет обновленный биллинг (я архивацию одновременно с обновлением до 007 тестирую) впринципе согласен, что если делать каждый месяц регулярно, то не критично лишние движения проделать для надежности )

Почему сбилось время, не знаю. Скрипт только показания часов берет, да и те через MySQL.

с этим разобрался, косяк не скрипта был ))

Надо будет написать скрипт для первого запуска, который раскладывал бы сам это всё по месяцам. Работа хотя и однократная, но раз ее можно сделать быстрее, почему бы нет.

я думаю было бы не плохо, он бы немного облегчил жизнь уже другим людям))
согласен, что работа однократная и не охото этим заниматься, но если добавится подобный скриптик к этим двум, то че тут скажешь... кроме респектов больших к JAO никаких других эмоций не возникает )))

ЗЫЖ Ах, да, ну и что на счет остановки ядра биллинга во время выполнения скрипта? насколько это необходимо? мне кажется нет нужды в этом большой

[JAO]

Можно и выключить, конечно. Особенно если сильно давит. Но с другой стороны, двое суток простоя - это очень значительно.

[JAO]

Завершено тестирование новых скриптов архивации.

Что нового:

1) скрипт миграции архива с версии 006 на версию 007

utm521_arc_006_to_007

Автоматически создает в каждом архиве недостающие таблицы и раскладывает по ним данные в соответствии с границами архивов

2) переписанные скрипты архивации, умеющие создавать архив с нуля

utm521006_arc
utm521007_arc

3) скрипт очистки старых архивных данных (по умолчанию старше трех лет)

utm521_arc_clear_old

При первом запуске происходит формирование архивов размером в один месяц до полного исчерпания данных. Так, если база не архивировалась и там есть данные за три года, будет создано 36 архивов. В основных таблицах остаются данные только за текущий месяц. Этими же скриптами можно выполнять ежемесячную архивацию данных.

Ссылка на скрипт удалена автором за ненадобностью. Смотрите документацию к новым версиям UTM 5

[GMS]

У меня архивация базы ~16Г занимает около 2 минут. Все таблицы MySAM. Скрипт, правда, несколько отличается от приведенного.

[gadmin]

Спасибо за скрипт сделал с 006 на 007 , все прошло супешно) а то уже расстроился что придется самому писать:) хорошо что есть добрые люди!) респект и уважуха автору!

[Davion]

А можно срипт запускать при рабочем билинге?

[Davion]

GMS можеш поделиться скриптом? а то база жутко большая гигов 80....

[GMS]

Скрипт и запускается на рабочем биллинге.

#!/usr/bin/perl

use DBI;
use strict;
sub move_table_tmp;
sub copy_delta;
sub move_to_archiv;

my $ArchiveDBname = "UTM5ARCHIVE";
my $DBhost = "localhost";
my $DBname = "UTM5";
my $Login = "xxxxxxx";
my $Password = "xxxxxxxx";
my @tables = ("discount_transactions_all","discount_transactions_iptraffic_all","tel_sessions_log","tel_sessions_detail","dhs_sessions_log","dhs_sessions_detail","payment_transactions");
my @tables_date = ("discount_date","discount_date","recv_date","recv_date","recv_date","recv_date","payment_enter_date");
my @tables_type = (1,2,3,4,5,6,7);

my $archive_id=0;

my $i=0;
my $hc_locks="";
for($i=0;$i<=$#tables;$i++){
$hc_locks.="$tables[$i] WRITE, ";
}
$hc_locks =~ s/..$//;

my $dbh = DBI->connect( "DBI:mysql:$DBname:$DBhost", $Login, $Password, {RaiseError=>1} ) or die "\nConnection failed...\nError: $DBI::errstr\n";

#get end_date of period to archive
my $sth = $dbh->prepare( "select UNIX_TIMESTAMP(concat(YEAR(now()),'-',MONTH(now()),'-1 0:0:0')),from_unixtime(UNIX_TIMESTAMP(DATE_ADD(now(), INTERVAL -1 MONTH)),'%Y%m')" );
$sth->execute() or die "DB Error: $DBI::errstr\n";
my ($enddate,$date_for_name)=$sth->fetchrow_array;
print "END DATE for archive: $enddate ,$date_for_name)\n";
# exit;
############################################
# MAIN START
############################################

$sth = $dbh->prepare("SELECT MAX(archive_id) FROM archives");
$sth -> execute() or die "DB Error: $DBI::errstr\n";
$archive_id=$sth->fetchrow_array;
$archive_id++;
print("Archive ID: $archive_id\n");
############################################
# TRANSACTION
############################################
$dbh->do("USE $DBname");
$dbh->do("LOCK TABLES $hc_locks");
$dbh->do("START TRANSACTION");
for($i=0;$i<=$#tables;$i++){
&move_table_tmp ($tables[$i]);
}
$dbh->do("UNLOCK TABLES");
$dbh->do("COMMIT");
############################################
# END TRANSACTION
############################################
#exit;
############################################
# Copy to archive
############################################
for($i=0;$i<=$#tables;$i++){
&copy_delta($tables[$i], $tables_date[$i], $enddate);
&move_to_archiv($ArchiveDBname, $tables[$i], "_$date_for_name", $tables_date[$i],$tables_type[$i]);
}
############################################
# END Copy to archive
############################################
$sth->finish;
$dbh->disconnect;
############################################
# MAIN END
############################################

sub move_table_tmp {
my $t_name = shift;
my $SQL = "SHOW CREATE TABLE $t_name";
$sth = $dbh->prepare($SQL);
$sth->execute() or die "DB Error: $DBI::errstr\n";
my ($t_name,$t_conf_sql)=$sth->fetchrow_array;
print "Movie $t_name to $t_name" . "_bak\n\n";
# print "SQL: $t_conf_sql\n\n";
$SQL = "RENAME TABLE `".$DBname."`.`".$t_name."` TO `".$DBname."`.`".$t_name."_bak`";
$dbh->do($SQL);
$dbh->do($t_conf_sql);
}

sub copy_delta {
my $t_name = shift;
my $t_col = shift;
my $e_date = shift;
print "Copy delta: $t_name\n";
$dbh->do("INSERT INTO $t_name SELECT * FROM $t_name"."_bak WHERE $t_col>=$e_date");
$dbh->do("DELETE FROM $t_name"."_bak WHERE $t_col>=$e_date");
}

sub move_to_archiv {
my $a_DB_name = shift;
my $t_name = shift;
my $t_prefix = shift;
my $t_col = shift;
my $table_type = shift;
my $a_t_name = $a_DB_name . "." . $t_name.$t_prefix;
print "Copy: $t_name to archive $a_t_name\n";
# $dbh->do("RENAME TABLE $t_name"."_bak TO $a_t_name");
$dbh->do("CREATE TABLE $a_t_name LIKE $t_name");
$dbh->do("INSERT INTO $a_t_name SELECT * FROM $t_name"."_bak");
$dbh->do("DROP TABLE $t_name"."_bak");
$sth = $dbh->prepare("SELECT min($t_col),max($t_col) from $a_t_name");
$sth -> execute() or die "DB Error: $DBI::errstr\n";
my ($start_date,$end_date)=$sth->fetchrow_array;
if($start_date eq ""){$start_date=1;$end_date=2};

# print("INSERT INTO archives (archive_id,table_type,table_name,start_date,end_date) VALUES ($archive_id,'$table_type','$a_t_name',$start_date,$end_date)\n");
$dbh->do("INSERT INTO archives (archive_id,table_type,table_name,start_date,end_date) VALUES ($archive_id,'$table_type','$a_t_name',$start_date,$end_date)");
}
------------------------------------------------------------------------------
Только не забудь перед этим в базе UTM5
CREATE TABLE `archives` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`archive_id` int(11) NOT NULL,
`table_type` int(11) NOT NULL,
`table_name` varchar(255) NOT NULL,
`start_date` int(11) NOT NULL,
`end_date` int(11) NOT NULL,
PRIMARY KEY (`id`)
);

[Davion]

спасибо оттестирую отпишусь!