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

Форум для размещения материалов по реализации различных схем использования ПО, решению частых проблем и предупреждению частых ошибок
Davion
Сообщения: 267
Зарегистрирован: Чт дек 01, 2005 13:36

Сообщение Davion »

:?: а почему эта строка# $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");
:roll:

или есть какойто нюанс :shock:

GMS
Сообщения: 6
Зарегистрирован: Пт сен 02, 2011 09:22

Сообщение GMS »

Потому что если основная база и архив хранятся на разных дисковых разделах, то простое переименование не работает. Проверено на горьком опыте.

Davion
Сообщения: 267
Зарегистрирован: Чт дек 01, 2005 13:36

Сообщение Davion »

:o тоесть если на одном в одной базе то проблем быть не должно?

amin
Сообщения: 8
Зарегистрирован: Вт ноя 29, 2011 12:59

Сообщение amin »

Я предлагаю несколько иное решение (у нас один диск под базу).

1). Архивация происходит на момент запуска скрипта.
2). Максимально быстро
3). Чистый SQL
4). При наличии адекватных индексов по дате и не сильно запущенной БД можно делать архивацию, не останавливая биллинг (таблица будет недоступна буквально доли секунды). Если индексов по дате нет, а таблица - 20 Гб и 100 млн записей - то в столь запущенном случае лучше биллинг остановить и запускать ночью.

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

USE UTM5;   -- База биллинга UTM-5 сборка .009		// 2011-09-06, Amin

-- Дата (YYYY-MM-DD), до которой включительно будут архивированы данные
    SELECT CONCAT('-- Start script: ', CURRENT_DATE(), ' ', CURRENT_TIME());
SET @CUTDATE=replace(CURRENT_DATE(),"-","");   -- для таблиц - убрать тире из имён

-- Определим начальное время для каждой части архива
    SELECT CONCAT('   Search MIN() dates started... ', CURRENT_DATE(), ' ', CURRENT_TIME());
SELECT @bd1 := MIN(discount_date) , CURRENT_DATE(), ' ', CURRENT_TIME() FROM discount_transactions_all ;
SELECT @bd2 := MIN(discount_date) , CURRENT_DATE(), ' ', CURRENT_TIME() FROM discount_transactions_iptraffic_all ;
SELECT @bd3 := MIN(actual_date) , CURRENT_DATE(), ' ', CURRENT_TIME() FROM payment_transactions ;

-- Старые большие таблицы просто переименуем
  SELECT CONCAT('   Rename BIG Tables: ', CURRENT_DATE(), ' ', CURRENT_TIME());

SET @q1 = CONCAT("ALTER TABLE discount_transactions_all RENAME discount_transactions_all_", @CUTDATE);
SET @q2 = CONCAT("ALTER TABLE discount_transactions_iptraffic_all RENAME discount_transactions_iptraffic_all_", @CUTDATE);
SET @q3 = CONCAT("ALTER TABLE payment_transactions RENAME payment_transactions_", @CUTDATE);

PREPARE q1 FROM @q1;
EXECUTE q1;
    SELECT CONCAT('     T1 Renamed ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q2 FROM @q2;
EXECUTE q2;
    SELECT CONCAT('     T2 Renamed ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q3 FROM @q3;
EXECUTE q3;
    SELECT CONCAT('     T3 Renamed ', CURRENT_DATE(), ' ', CURRENT_TIME());

-- Определим конечное время
SET @edts = UNIX_TIMESTAMP(CONCAT( CURRENT_DATE(), " ", CURRENT_TIME() ) );

-- Создадим новые таблицы только для новых данных

    SELECT CONCAT('   Create NEW tables ... ', CURRENT_DATE(), ' ', CURRENT_TIME());
SET @q11 = CONCAT("CREATE TABLE discount_transactions_all LIKE discount_transactions_all_", @CUTDATE);
SET @q21 = CONCAT("CREATE TABLE discount_transactions_iptraffic_all LIKE discount_transactions_iptraffic_all_", @CUTDATE);
SET @q31 = CONCAT("CREATE TABLE payment_transactions LIKE payment_transactions_", @CUTDATE);

PREPARE q11 FROM @q11;
EXECUTE q11;
    SELECT CONCAT('     New T1 created ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q21 FROM @q21;
EXECUTE q21;
    SELECT CONCAT('     New T2 created ', CURRENT_DATE(), ' ', CURRENT_TIME());
 
PREPARE q31 FROM @q31;
EXECUTE q31;
    SELECT CONCAT('     New T3 created ', CURRENT_DATE(), ' ', CURRENT_TIME());

-- Search AUTOINCREMENT

    SELECT CONCAT('   Search AUTOINCREMENT ... ', CURRENT_DATE(), ' ', CURRENT_TIME());
SET @q111 = CONCAT("SELECT @ai1 := MAX(id)+10 FROM discount_transactions_all_", @CUTDATE);
SET @q211 = CONCAT("SELECT @ai2 := MAX(id)+10 FROM discount_transactions_iptraffic_all_", @CUTDATE);
SET @q311 = CONCAT("SELECT @ai3 := MAX(id)+10 FROM payment_transactions_", @CUTDATE);

PREPARE q111 FROM @q111;
EXECUTE q111;
    SELECT CONCAT('     T1 AutoInc Found ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q211 FROM @q211;
EXECUTE q211;
    SELECT CONCAT('     T2 AutoInc Found ', CURRENT_DATE(), ' ', CURRENT_TIME());
 
PREPARE q311 FROM @q311;
EXECUTE q311;
    SELECT CONCAT('     T3 AutoInc Found ', CURRENT_DATE(), ' ', CURRENT_TIME());

-- Fix AUTOINCREMENT
  SELECT CONCAT('   Fix AUTOINCREMENT : ', CURRENT_DATE(), ' ', CURRENT_TIME());

SET @q10 = CONCAT("ALTER TABLE discount_transactions_all AUTO_INCREMENT=", @ai1);
SET @q20 = CONCAT("ALTER TABLE discount_transactions_iptraffic_all AUTO_INCREMENT=", @ai2);
SET @q30 = CONCAT("ALTER TABLE payment_transactions AUTO_INCREMENT=", @ai3);

PREPARE q10 FROM @q10;
EXECUTE q10;
    SELECT CONCAT('     T1 AutoInc Fixed  ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q20 FROM @q20;
EXECUTE q20;
    SELECT CONCAT('     T2 AutoInc Fixed ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q30 FROM @q30;
EXECUTE q30;
    SELECT CONCAT('     T3 AutoInc Fixed ', CURRENT_DATE(), ' ', CURRENT_TIME());


-- Создадим записи в таблице архивов
    SELECT CONCAT('   Make archive records ...', CURRENT_DATE(), ' ', CURRENT_TIME());
SET @q13 = CONCAT("INSERT INTO archives (archive_id, table_type, table_name, start_date, end_date) VALUES ('", @edts, "', '1', 'discount_transactions_all_", @CUTDATE,"', '", @bd1, "', '", @edts, "')");
SET @q23 = CONCAT("INSERT INTO archives (archive_id, table_type, table_name, start_date, end_date) VALUES ('", @edts, "', '2', 'discount_transactions_iptraffic_all_", @CUTDATE,"', '", @bd2, "', '", @edts, "')");
SET @q33 = CONCAT("INSERT INTO archives (archive_id, table_type, table_name, start_date, end_date) VALUES ('", @edts, "', '7', 'payment_transactions_", @CUTDATE,"', '", @bd3, "', '", @edts, "')");

PREPARE q13 FROM @q13;
EXECUTE q13;

PREPARE q23 FROM @q23;
EXECUTE q23;

PREPARE q33 FROM @q33;
EXECUTE q33;

    SELECT CONCAT('     Archive records created. ', CURRENT_DATE(), ' ', CURRENT_TIME());


Puzan
Сообщения: 58
Зарегистрирован: Ср окт 31, 2007 09:58

Сообщение Puzan »

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`)
);
Данный скрипт для 008 подходит ?

JAO
Сообщения: 1153
Зарегистрирован: Вт дек 11, 2007 08:17

Сообщение JAO »

Если он для семи таблиц, то подходит.

Cramac
Сообщения: 454
Зарегистрирован: Сб июл 01, 2006 17:59

Сообщение Cramac »

а есть вариант архивации на 005?

JAO
Сообщения: 1153
Зарегистрирован: Вт дек 11, 2007 08:17

Сообщение JAO »

Нет, для 005 только агрегация списаний (так называемая оптимизация).

Cramac
Сообщения: 454
Зарегистрирован: Сб июл 01, 2006 17:59

Сообщение Cramac »

жаль, придется обновляться...

Аватара пользователя
Davy_Jones
Сообщения: 36
Зарегистрирован: Ср июл 27, 2011 12:50

Сообщение Davy_Jones »

простите. чушь сморозил

Vlad83
Сообщения: 11
Зарегистрирован: Пт май 15, 2009 12:09

Сообщение Vlad83 »

Здравствуйте!
Запустил данный скрипт на 5.2.1-008.

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

#!/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 = "";
my $Password = "";
my @tables = &#40;"discount_transactions_all","discount_transactions_iptraffic_all","tel_sessions_log","tel_sessions_detail","dhs_sessions_log","dhs_sessions_detail","payment_transactions"&#41;;
my @tables_date = &#40;"discount_date","discount_date","recv_date","recv_date","recv_date","recv_date","payment_enter_date"&#41;;
my @tables_type = &#40;1,2,3,4,5,6,7&#41;;

my $archive_id=0;
База весит больше 10 Гб ,и из-за малого значения innodb_buffer_pool_size скрипт не отработал до конца ругнувшись на малый размер буфера.После изменил значение буфера и попробовал вновь запустить, скрипт выдал ошибку :

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

END DATE for archive&#58; 1328036400 ,201201&#41;
Archive ID&#58; 1
Movie discount_transactions_all to discount_transactions_all_bak

DBD&#58;&#58;mysql&#58;&#58;db do failed&#58; Table 'discount_transactions_all_bak' already exists at ./utm5db_arch.pl line 84.
DBD&#58;&#58;mysql&#58;&#58;db do failed&#58; Table 'discount_transactions_all_bak' already exists at ./utm5db_arch.pl line 84.
Подскажите пожалуйста,можно ли как-нибудь это обойти без удаления discount_transactions_all_bak т.к. там уже присутствуют данные с прошлого запуска.

JAO
Сообщения: 1153
Зарегистрирован: Вт дек 11, 2007 08:17

Сообщение JAO »

Добавил в архив еще один скрипт (см. мой пост выше со ссылочкой)

RuffiAn
Сообщения: 107
Зарегистрирован: Пн янв 24, 2011 16:11

Сообщение RuffiAn »

Добрый день! Решил попробывать сделать архивацию, скрипт отработал без ошибок, но после этого в админке пропали отчеты по платежам и по сессиям. Подскажите что не так сделал. Скрин таблиц http://s54.radikal.ru/i143/1207/78/669dcd1d177a.jpg

Pei0t
Сообщения: 258
Зарегистрирован: Чт дек 13, 2007 20:48

Сообщение Pei0t »

Смотреть в сторону таблицы архивс.

RuffiAn
Сообщения: 107
Зарегистрирован: Пн янв 24, 2011 16:11

Сообщение RuffiAn »

Таблице archives присутствуют 2 записи discount_transactions_iptraffic_all_06July2012
discount_transactions_all_06July2012

скрин http://s1.ipicture.ru/uploads/20120711/fRGLhhJg.png

Ответить