Perl-owy skrypt do znalezienia rożnic miedzy dwoma mysqlowymi bazami

Jakiś czas temu stanęłam przed zadaniem znalezienia różnic między dwoma bazami danych, między którymi niegdyś działała replikacja master-master. Aby wyłapać wszystkie różnice, napisała mały skrypt perl-owy, którego zadaniem w pierwszej kolejności będzie sprawdzenie schematu bazy danych (tabel i kolumn), a następnie analizować wiersze w tabelach.
Główne ciało skryptu: 

#!/usr/bin/perl -w
# sudo perl -MCPAN -e 'install Date::Calc'

use strict;
use warnings;
use English;
use DBI;
use Date::Simple;
use List::MoreUtils qw/ any /;
use List::Compare;

# disabling output buffering

$| = 1;

##########################################
# DATABASE connection 
########################################## 
#$dbConnConditionFilePath = $ARGV[0] || "-";


our $server01Host = '127.0.0.1'; 
our $server01Port = '3306';
our $server01User = 'root'; 
our $server01Pass = 'root'; 
our $server01Instance = 'database01'; 


our $server02Host = '127.0.0.1'; 
our $server02Port = '3306'; 
our $server02User = 'root'; 
our $server02Pass = 'root'; 
our $server02Instance = 'database02'; 


our $checkDataForMaxRows = 1000; 
our $showFullDiffreneces = 1; 


our $server01Dbh = DBI->connect("DBI:mysql:dbname=$server01Instance;host=$server01Host;port=$server01Port", $server01User, $server01Pass, {'RaiseError' => 1});
our $server02Dbh = DBI->connect("DBI:mysql:dbname=$server02Instance;host=$server02Host;port=$server02Port", $server02User, $server02Pass, {'RaiseError' => 1}); 

our %serversData = (
'server01' => [$server01Instance, $server01Dbh],
'server02' => [$server02Instance, $server02Dbh]
);
our @tablesToVerify = ();
@tablesToVerify = dbSchemaVerification(); 
dbTablesVerification(@tablesToVerify); 
dbCheckValues(@tablesToVerify); 
$server01Dbh->disconnect(); 
$server02Dbh->disconnect();
exit;

Sprawdzenie schematu bazy danych i opcji tabel następuje poprzez sprawdzenie/wyciągnięcie informacji z tabeli information_schema.tables. Sprawdzane są następujące informację:

• nazwa tabeli 
• engine 
• collation 
• row_format

Zapytanie:

SELECT 
 TABLE_NAME, 
 TABLE_TYPE, 
 coalesce(ENGINE,''), 
 coalesce(ROW_FORMAT,''), 
 coalesce(TABLE_COLLATION,'') 
from 
 information_schema.tables 
where 
 TABLE_SCHEMA = '$dbName' 
order by TABLE_NAME ASC;

# 
# Get informationa about all tables from information_schema.tables
#
sub dbSchemaVerification {
 print "######################################\n";
 print "Table Schema verification: START\n";
 my %tables = (
  'server01' => {}, 
  'server02' => {}
 );
 my @allUniqueTablesOnServers=();
 my @tableToCheck = ();
 
 
 while( my ($serverName,@serverData) = each(%serversData)) {
  my $dbName = $serverData[0][0];
  my $dbDbh = $serverData[0][1];
  my $query = "SELECT TABLE_NAME, TABLE_TYPE, coalesce(ENGINE,''), coalesce(ROW_FORMAT,''), coalesce(TABLE_COLLATION,'') from information_schema.tables where TABLE_SCHEMA = '$dbName' order by TABLE_NAME ASC;";
  
  my $statement = $dbDbh->prepare($query);
  $statement->execute();
  my $statement_ref = $statement->fetchall_arrayref();
  foreach my $row (@$statement_ref) {
   my ($tableName, $tableType, $engine, $rowFormat, $tableCollation) = @$row;
   $tables{$serverName}{$tableName} = ([$tableName, $tableType, $engine, $rowFormat, $tableCollation]);
   if (!(any { $_ eq $tableName} @allUniqueTablesOnServers)) {
    push(@allUniqueTablesOnServers, $tableName);
   } 
  }
 }

 if(length $tables{'server01'} != length $tables{'server02'}) {
  print "There is diffrent between database schemas ! \n";
 }
 
 # check tables with them options
 for my $i (0..$#allUniqueTablesOnServers) {
  my $tableName = $allUniqueTablesOnServers[$i];
  
  # check server01
  my @server01ForTable = @{$tables{'server01'}{$tableName}};
  
  # check server02
  my @server02ForTable = @{$tables{'server02'}{$tableName}};
  
  if(!@server01ForTable) {
   print "ERROR: Table $tableName desn't exist on server01!\n";
  } elsif(!@server01ForTable) {
   print "ERROR: Table $tableName desn't exist on server02!\n";
  } else {
   my $tablePropertisServer01 = join(", ", @server01ForTable);
   #print "$tablePropertisServer01\n";
   my $tablePropertisServer02 = join(", ", @server02ForTable);
   if($tablePropertisServer01 ne $tablePropertisServer02) {
    print "ERROR: Check table schemas on servers, difference was detected:\n";
    print "Compared data: TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, TABLE_COLLATION\n";
    print "Server 01: $tablePropertisServer01\n";
    print "Server 02: $tablePropertisServer02\n";
   } else {
    print "Check for $tableName: OK! \n";
   }
   
   # store information about tables to check columns
   push(@tableToCheck, $tableName);
  }
 }
 print "Table Schema verification: DONE\n";
 #print join(",", @tableToCheck)."\n";
 return @tableToCheck;
}

W następnej funkcji sprawdzane są opcje kolumn wszystkich tabel. Te informacje są trzymane w tabeli: information_schema.columns.
Aby wyciągnąć informację na temat kolumn tabeli, wykonuję następujące zapytanie:

SELECT 
  COLUMN_NAME, 
  ORDINAL_POSITION, 
  coalesce(COLUMN_DEFAULT,''), 
  IS_NULLABLE, 
  DATA_TYPE, 
  coalesce(CHARACTER_MAXIMUM_LENGTH, ''), 
  COLUMN_TYPE, 
  COLUMN_KEY, 
   EXTRA 
 from information_schema.columns 
 where TABLE_SCHEMA = '$dbName' AND TABLE_NAME = '$tableName' 
 order by ORDINAL_POSITION ASC;

Dzięki temu będę wiedziała między innymi:

• czy kolumny są ustawione na tych samym pozycjach
• typy kolumn są takie same
• czy moją zawierać wartości null-owe

#
# check if columns are the same with the same options
#
sub dbTablesVerification {
 my @tableList = @_;
 
 print "######################################\n";
 print "Columns Schema verification: START\n";
 
 my %columns = (
  'server01' => {}, 
  'server02' => {}
 );
 
 for my $i (0..$#tableList) {
  my $tableName = $tableList[$i];
  while( my ($serverName,@serverData) = each(%serversData)) {
   my $dbName = $serverData[0][0];
   my $dbDbh = $serverData[0][1];
  
   my $query = "SELECT COLUMN_NAME, ORDINAL_POSITION, coalesce(COLUMN_DEFAULT,''), IS_NULLABLE, DATA_TYPE, coalesce(CHARACTER_MAXIMUM_LENGTH, ''), COLUMN_TYPE, COLUMN_KEY, EXTRA ".
   "from information_schema.columns ".
   "where TABLE_SCHEMA = '$dbName' AND TABLE_NAME = '$tableName' ".
   "order by ORDINAL_POSITION ASC;";
  
   my $statement = $dbDbh->prepare($query);
   $statement->execute();
   my $statement_ref = $statement->fetchall_arrayref();
   foreach my $row (@$statement_ref) {
    my ($columnName, $ordinalPosition, $columnDefault, $isNullable, $dataType, $character, $columnType, $columnKey, $extra) = @$row;
    $columns{$serverName}{$tableName}{$columnName} = ($columnName." | ".$ordinalPosition." | ".$columnDefault." | ".$isNullable." | ".$dataType." | ".$character." | ".$columnType." | ".$columnKey." | ".$extra);
   }
  }
  my $tableVeryficationServer01 = "";
  foreach my $key (keys %{$columns{'server01'}{$tableName}}) {
   $tableVeryficationServer01 .= $columns{'server01'}{$tableName}{$key};
  }
  my $tableVeryficationServer02 = "";
  foreach my $key (keys %{$columns{'server02'}{$tableName}}) {
   $tableVeryficationServer02 .= $columns{'server02'}{$tableName}{$key};
  }
  if($tableVeryficationServer01 ne $tableVeryficationServer02) {
   print "ERROR: The difference from $tableName was detected:\n";
   print "COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | COLUMN_TYPE | COLUMN_KEY | EXTRA\n";
   print "Server 01:\n";
   foreach my $key (keys %{$columns{'server01'}{$tableName}}) {
    print $columns{'server01'}{$tableName}{$key}."\n";
   }
   print "\nServer 02:\n";
   foreach my $key (keys %{$columns{'server02'}{$tableName}}) {
    print $columns{'server02'}{$tableName}{$key}."\n";
   }
   print "\n";
  } else {
   print "CHECK for $tableName: OK! \n";
  }
 }
 
 print "Columns Schema verification: DONE\n";
}

Ostatnią rzeczą jest sprawdzenie wartości w wierszach. Jeśli w tabelach przechowujemy bardzo dużą ilość danych, weryfikacja może być bardzo obciążająca dla bazy. Dlatego warto założyć jakieś ograniczenia. Ja przyjęłam, że to w ostatnich rekordach można znaleźć różnice. Dlatego przed analizą danych w tabelach, wyciągnęłam klucze główne dla każdej z nich przy pomocy zapytania:

SELECT 
  table_name, 
  COLUMN_NAME 
FROM information_schema.columns 
WHERE 
 TABLE_SCHEMA = '$dbName' and COLUMN_KEY = 'PRI';

Dodatkowo najlepiej jest porównywać ze sobą jak najmniejszą ilość danych dlatego cała zawartość wiersza zapisuje jako wartość funkcji MD5. W ten sposób ograniczy się przesyłanie zbędnej ilości danych pomiędzy serwerem a klientem.

Dla każdej tabeli powtarzam następujące kroki:

1. Sprawdzam ilość rekordów 
2. Wyciągam ostatnie 1000 wierszy (ta wartość jest konfigurowana na początku skryptu) w postaci pary: klucz główny i MD5-ka ze wszystkich danych w kolumnach. Każda wartość z każdej kolumny jest sprawdzana przez funkcję COALESCE na wypadek gdyby zawierała wartość null (MD5 z null daje null). 
3. Porównuje różnice pomiędzy wynikami drugiego kroku obydwu baz. Dodatkowo na wyjściu otrzymam także plik z danymi, dla każdej tabeli osobno, jeśli różnice istnieją oczywiście. 

sub dbCheckValues {
 my @tableList = @_;
 my %tablesWithPrimaryKeyColumns = ();
 
 print "######################################\n";
 print "Data value verification: START\n";
 
 # get pxrimary_key of table from first server
 my @server01ConnData = @{$serversData{'server01'}};
 my $server01Conn = $server01ConnData[1];
 my @server02ConnData = @{$serversData{'server02'}};
 my $server02Conn = $server02ConnData[1];
 
 my $dbName = $server01ConnData[0];
 my $query = "select table_name, COLUMN_NAME from information_schema.columns where TABLE_SCHEMA = '$dbName' and COLUMN_KEY = 'PRI';";
 my $statement = $server01Conn->prepare($query);
 $statement->execute();
 my $statement_ref = $statement->fetchall_arrayref();
 foreach my $row (@$statement_ref) {
  my ($tableName, $columnName) = @$row;
  $tablesWithPrimaryKeyColumns{$tableName} = $columnName;
 }
 
 # estimate how many rows can be in table
 # if more then 500 - check only a news rows
 # if less then 500 - check all rows
 for my $i (0..$#tableList) {
  print "\n#####################################################\n";
  my $tableName = $tableList[$i];
  print "TABLE NAME: $tableName \n";
  my $columnName = $tablesWithPrimaryKeyColumns{$tableName};
  my $query = "";
  if($columnName) {
   $query = "select count(*) from (select * from $tableName order by $columnName desc limit $checkDataForMaxRows) foo;";
  } else {
   $query = "select count(*) from (select * from $tableName limit $checkDataForMaxRows) foo;";
  }
  my $server01Count = runCountQueryOnConn($query, $server01Conn);
  my $server02Count = runCountQueryOnConn($query, $server02Conn);
  
  my @columnsToSelectArray = getArrayBySql("select column_name from information_schema.columns where table_name = '$tableName' AND TABLE_SCHEMA='$dbName'",$server01Conn);
  for my $c (0..$#columnsToSelectArray) {
   $columnsToSelectArray[$c] = "COALESCE(".$columnsToSelectArray[$c].",'')";
  }
  my $columnsToSelect = join(",", @columnsToSelectArray);
  $query = "SELECT concat($columnName,'-',md5(CONCAT($columnsToSelect))) as c FROM $tableName ORDER BY $columnName DESC";
  
  if($server01Count != $server02Count) {
   print "ERROR: Detected a difference in the number of rows in the table: $tableName: Server 01: $server01Count rows, Server 02: $server02Count rows\n";
   $query .= "";
  }elsif( ($server01Count == $server02Count) && ($server01Count > $checkDataForMaxRows) ) {
   print "WARNINGS: In the table $tableName is more then $checkDataForMaxRows rows\n";
   $query .= " LIMIT $checkDataForMaxRows";
  }elsif( ($server01Count == $server02Count) && ($server01Count <= $checkDataForMaxRows) ) 

{
   $query .= "";
  }
  #print $query."\n";
  my @dataServer01 = getArrayBySql($query, $server01Conn);
  my @dataServer02 = getArrayBySql($query, $server02Conn);
  
  my $lc = List::Compare->new(\@dataServer01, \@dataServer02);
  my @recordIdsWithDiff = ();
  my @server01Only = $lc->get_unique;
  my @server02Only = $lc->get_complement;
  my @LorRonly = $lc->get_symmetric_difference;
  if(scalar(@server01Only) > 0) {
   print "------------------------------------------------------\n";
   print "Element which exists in server01 but not in server02: \n";
   for my $e (@server01Only) {
    my $rowIdValue = substr($e, 0, index($e, '-'));
    if (!(any { $_ eq $rowIdValue} @recordIdsWithDiff)) {
     push(@recordIdsWithDiff, $rowIdValue);
    }
   }
   my $recordIdsWithDiffStr = join(",", @recordIdsWithDiff);
   print "Column name: $columnName, values: $recordIdsWithDiffStr\n";
  }
  @recordIdsWithDiff = ();
  if(scalar(@server02Only) > 0) {
   print "------------------------------------------------------\n";
   print "Element which exists in server02 but not in server01: \n";
   for my $e (@server02Only) {
    my $rowIdValue = substr($e, 0, index($e, '-'));
    if (!(any { $_ eq $rowIdValue} @recordIdsWithDiff)) {
     push(@recordIdsWithDiff, $rowIdValue);
    }
   }
   my $recordIdsWithDiffStr = join(",", @recordIdsWithDiff);
   print "Column name: $columnName, values: $recordIdsWithDiffStr\n";
  }
  @recordIdsWithDiff = ();
  if(scalar(@LorRonly) > 0) {
   print "------------------------------------------------------\n";
   print "Rows which appear at least once in either the first or the second list, but not both: \n";
   for my $e (@LorRonly) {
    my $rowIdValue = substr($e, 0, index($e, '-'));
    if (!(any { $_ eq $rowIdValue} @recordIdsWithDiff)) {
     push(@recordIdsWithDiff, $rowIdValue);
    }
   }
   my $recordIdsWithDiffStr = join(",", @recordIdsWithDiff);
   print "Column name: $columnName, values: $recordIdsWithDiffStr\n";

     my $csvFileNameS01 = "$server01Instance"."_s01_for_$tableName.csv";
     my $csvFileNameS02 = "$server02Instance"."_s02_for_$tableName.csv";
     print "GENERATE CSV file with differences. Files: $csvFileNameS01 and $csvFileNameS02\n";
     $query = "SELECT * FROM $tableName WHERE $columnName IN \($recordIdsWithDiffStr\)";
     #print $query."\n";
     system("echo '$query' |mysql -h$server01Host -p$server01Pass -u$server01User -P$server01Port -D$server01Instance > $csvFileNameS01");
     system("echo '$query' |mysql -h$server02Host -p$server02Pass -u$server02User -P$server02Port -D$server02Instance > $csvFileNameS02");
  }
  
 }
 
 print "Data value verification: DONE\n";
}

Przykładowy wynik działania skryptu:

∴ perl mysql_replication_check.pl 
######################################
Table Schema verification: START
Check for t1: OK! 
Table Schema verification: DONE
######################################
Columns Schema verification: START
CHECK for t1: OK! 
Columns Schema verification: DONE
######################################
Data value verification: START

#####################################################
TABLE NAME: t1 
ERROR: Detected a difference in the number of rows in the table: t1: Server 01: 2 rows, 

Server 02: 3 rows
------------------------------------------------------
Element which exists in server02 but not in server01: 
Column name: c1, values: 3
------------------------------------------------------
Rows which appear at least once in either the first or the second list, but not both: 
Column name: c1, values: 3
GENERATE CSV file with differences. Files: test1_s01_for_t1.csv and test2_s02_for_t1.csv
Data value verification: DONE

Skrypt wychwycił, że na serwerze 01 jest mniej wierszy niż na 02. Wszystkie różnice zostały dodatkowo przekierowane do plików:

∴ ll
total 28
drwxrwxr-x  2 ela ela  4096 Dec 23 18:23 ./
drwxr-xr-x 38 ela ela  4096 Dec 23 17:17 ../
-rw-rw-r--  1 ela ela 12133 Dec 23 18:20 mysql_replication_check.pl
-rw-rw-r--  1 ela ela     0 Dec 23 18:23 test1_s01_for_t1.csv
-rw-rw-r--  1 ela ela    38 Dec 23 18:23 test2_s02_for_t1.csv
-rw-rw-r--  1 ela ela  2009 Dec 23 18:18 test.dump

W pliku test2_s02_for_t1.csv jest zrzucony dodatkowy wiersz, którego nie ma na drugiej bazie. W ten sposób łatwo jest wykryć różnice w danych przy pomocy polecenia diff. W przypadku gdy pod tym samą wartością klucza głównego istnieją różne dane, to już my musimy zdecydować, które dane są najbardziej aktualne albo czy nie doszło do konfliktów wartości kluczy głównych. W takim przypadku wymagane będzie update kluczy głównych dla tych wierszy na jednym z serwerów, później export, a następnie import do drugiej bazy danych.


Skrypt zawiera jeszcze dwie funkcje pomocnicze, które używane są w powyższych funkcjach: 

sub runCountQueryOnConn {
  my $sql = shift;
  my $connection = shift;
  
  my ($result) = $connection->selectrow_array($sql);
  return $result;
}

sub getArrayBySql {
  my $sql = shift;
  my $connection = shift;
  my @result = ();
  
  #print $sql."\n";
  my $statement = $connection->prepare($sql);
  $statement->execute();
  my $statement_ref = $statement->fetchall_arrayref();
  foreach my $row (@$statement_ref) {
    my ($r) = @$row;
    push(@result, $r);
  }
  return @result;
}

To na pewno ułatwi znalezienie/wyrównanie danych pomiędzy dwoma bazami danych.