#!/usr/bin/perl 
##########################################################################
#                                                                        #
# Perl Script for check mysql replication                                #
# Switzernet(c)2011                                                      #
#                                                                        #
##########################################################################
# my modules
# cpan > Install Date::Manip
# cpan > Install MIME::Lite
# cpan > Install Config::IniFiles
# aptitude install libterm-progressbar-perl
use strict;
use warnings;
use Sys::Syslog;
use POSIX;
use Config::IniFiles;
use Switch;
use DBI;
use Date::Manip;
use Time::HiRes qw/usleep/;
use threads;
use File::Copy;
##########################################################################
# Setting
my $lock_file = '/var/run/setup-mysql-replication.pid';
my $runas_user = 'root';
my $config_file = '/etc/dba/setup-mysql-replication.conf';
##########################################################################
# Globals
my ($DB_NAME_Mst,$DB_MASTER_Mst,$DB_HOST_Mst,$DB_USER_Mst);
my ($DB_NAME_Slv,$DB_HOST_Slv,$DB_USER_Slv,$DB_PASS_Slv);
my ($fus,$DB_USER_Loc,$DB_DBA_Loc,$M_binf,$M_binp,$DumpDir,$My_Cmt,$My_Unc);
my @Struct;
my @DB_NAME;
my @USER_HOST;
my @DBA_HOST;
my %DB_TABLE;
my ($dumptype,$dumpfrom,$dumpuser,$dumppass);
my @My_Par;
my $Subject="Check MySQL replication started on ".format_date_sql(time);
my $STDROUT="- $Subject";
##########################################################################
#                             Initialization                             #
##########################################################################
# Check if already running
if( -e $lock_file ) {
   open(PID,$lock_file);
   my $pid=<PID>;
   close PID;
   chomp $pid;
   if( !-e "/proc/$pid" ) {
      print STDERR "Lock file present, but no process with pid=$pid.\n";
      die "Can't delete lock file $lock_file\n" if !unlink $lock_file;
      print STDERR "Lock file has been removed.\n";
   } else {
      die "Lockfile present, another copy is punning pid=$pid\n";
   }
}
my ($name, $passwd, $uid, $gid) = getpwnam($runas_user) or die "$runas_user not in passwd file";;
##########################################################################
# set C locale
POSIX::setlocale(LC_ALL, "C");
openlog('setup-mysql-replication', 'cons,pid', 'daemon');
syslog('notice', "Setup:Setup MySQL replication started");
##########################################################################
# Install signall handler
$SIG{INT} = \&safe_exit;
$SIG{QUIT} = \&safe_exit;
$SIG{TERM} = \&safe_exit;
$SIG{HUP} = \&load_config;
##########################################################################
# Drop privileges
setuid($uid);
$< = $uid;
$> = $uid;
##########################################################################
# Signal Handlers
sub safe_exit {
   my($sig) = @_;
   syslog('crit', "Setup: Caught a SIG $sig - shutting down") if ($sig);
   unlink $lock_file or syslog('crit', "Setup: Unable to create lockfile $lock_file\n");
   closelog();     
   exit;
}
##########################################################################
#                                FUNCTION                                #
##########################################################################
# sample '2004-12-11 09:16:05'
sub format_date_sql {
   my ($date) = @_;
   if ($date eq 0) {
        return "NULL";
   } else {
        return strftime("%Y-%m-%d %H:%M:%S", localtime($date));
   }
}
##########################################################################
# Remove leading and trailing white space from a string
sub chomp_plus {
   my $string=shift;
   $string =~ s/^\s+//;$string =~ s/\s+$//;
   chomp $string;
   return $string;
}
##########################################################################
# Get tables name
sub Get_tables{
   my $db=shift;
   my @VR;
   my @row;
   my $query="show tables";
   my $dbh=DB_connect($db,$DB_HOST_Slv,$DB_USER_Slv,$DB_PASS_Slv);
   my $sqlQuery  = $dbh->prepare($query) or die "Can't prepare $query: $dbh->errstr\n";
   $sqlQuery->execute or die "can't execute the query: $sqlQuery->errstr";
   push( @VR, $row[0]) while (@row= $sqlQuery->fetchrow_array());
   $sqlQuery->finish;
   $dbh->disconnect ;
   return \@VR;
}
##########################################################################
# Set config on my.cnf (mysql config)
sub Set_conf {
   print "Update configuration in [/etc/mysql/my.cnf]: /";
   my @My_Cmt;
   @My_Cmt=split(',', $My_Cmt) if (defined $My_Cmt);
   my @My_Unc;
   @My_Unc=split(',', $My_Unc) if (defined $My_Unc);
   my @tofile;
   foreach my $t (@My_Cmt) {$t=chomp_plus($t)}
   foreach my $t (@My_Unc) {$t=chomp_plus($t)}
   foreach my $t (@My_Par) {$t=chomp_plus($t)}
   my $infile = '/etc/mysql/my.cnf';
   open(FILE,"<$infile") or die"open: $!";
   my ($line,$tofile)=('',''); 
   while( defined( $line = <FILE> ) ) {
      $line=chomp_plus($line);
      foreach my $t (@My_Unc) {
         $line =~ s/^\#[\t \#]*($t.*)/$1/i;
      }
      if ($line =~ /^[a-zA-Z]/) {
         foreach my $t (@My_Cmt) {
            $line =~ s/^($t.*)/\#$1/i;
         }
      }
      push(@tofile,$line);
   }
   close(FILE);
   my $count=@tofile;
   my @toadd;
   foreach my $t (@My_Par) {
      my $yes=0;
      my ($p1,$p2);
      my @T=split('=', $t);
      if ( @T == 1 ) {
         $p1=chomp_plus($T[0]);
      } elsif  ( @T == 2 ){
         $p1=chomp_plus($T[0]);
         $p2=chomp_plus($T[1]);
      }
      for(my $i=0;$i<$count;$i++) {
         if ($p2) {
            if ($tofile[$i]  =~ s/^[\t ]*$p1[\t ]*=[\t ]*$p2.*$/$p1 = $p2/i) {
               $yes=1;
            }
         } else {
            if ($tofile[$i]  =~ s/^[\t ]*$p1[\t ]*$/$p1/i) {
               $yes=1;
            }
         }
      }
      if ($p2) {
         push(@toadd,"$p1 = $p2") if (!$yes);
      } else {
         push(@toadd,"$p1") if (!$yes);
      }
   }
   open(FILE,">$infile") or die"open: $!";
   foreach my $tt (@tofile) {
      print FILE $tt."\n";
      if ($tt eq "[mysqld]"){
         foreach my $pp (@toadd) {print FILE $pp."\n"}
      }
   }
   close(FILE);
   system("/etc/init.d/mysql restart >> /dev/null 2>&1") == 0  or print "Failed: $?";
}
##########################################################################
# Load config from mysql.conf
sub load_config {
   print "Loading config file [$config_file]: ";
   my $conf=Config::IniFiles->new(-file => $config_file);
   return 0 if !defined $conf ;
   @My_Par = $conf->val('MYSQL','Parameter');
   $My_Cmt = $conf->val('MYSQL','Comment');
   $My_Unc = $conf->val('MYSQL','Uncomment');
   my $Structs = $conf->val('GLOBAL','Struct');
   @Struct=split(',', $Structs);
   foreach my $k (@Struct) {
      $k=chomp_plus($k);
   }
   $DB_MASTER_Mst = $conf->val('MASTER','Master');
   $DB_HOST_Mst = $conf->val('MASTER','Host');
   $DB_USER_Mst = $conf->val('MASTER','User');
   $DB_HOST_Slv = $conf->val('SLAVE','Host');
   $DB_USER_Slv = $conf->val('SLAVE','User');
   $DB_PASS_Slv = $conf->val('SLAVE','Pass');
   my $DBs_NAME = $conf->val('DUMP','Data-bases');
   @DB_NAME=split(',', $DBs_NAME);
   foreach my $k (@DB_NAME) {
      $k=chomp_plus($k);
      if ($k) {
         my $tmp1 = $conf->val('DUMP',"Tables[$k]");
         if ($tmp1){
            my @REF=split(',', $tmp1);
            foreach my $t (@REF) {$t=chomp_plus($t);}
            $DB_TABLE{$k}=\@REF;
         }
      }
   }
   $dumpfrom=$conf->val('DUMP','Host');
   if ($dumpfrom =~ m/[\d]{1,3}\.[\d]{1,3}\.[\d]{1,3}\.[\d]{1,3}/) {
      if ($dumpfrom eq $DB_MASTER_Mst) {
        $dumptype = 0; #Dump from Master
        $dumpuser = $DB_USER_Mst;
      } else {
        if ($DB_HOST_Mst eq $DB_MASTER_Mst) {
          $dumptype = 1; #Dump from Sec Master or DBA but then use Billing Master as Master
        } else {
          $dumptype = 3; #Dump from Sec Master or DBA and use that server as Master
        }
        $dumpuser = $conf->val('DUMP','User');
        $dumppass = $conf->val('DUMP','Pass') if (defined($conf->val('DUMP','Pass') && $conf->val('DUMP','Pass') ne ''));
      }  
   } else {
      $dumptype = 2; #Dump from file
   }
   $DB_USER_Loc = $conf->val('USERS','User');
   my $USERs_Hosts = $conf->val('USERS','Host');
   $fus=0;
   $fus=1 if ($DB_USER_Loc && $USERs_Hosts);
   if ($fus){
      @USER_HOST=split(',', $USERs_Hosts);
      foreach my $t (@USER_HOST) {$t=chomp_plus($t)}
   }
   my @ifconfigresult = `ifconfig|grep "addr.*Bcast" -oE`;
   $ifconfigresult[0] =~ m/addr:([^ ]+)[ ]*Bcast/;
   my $ipaddress      = $1;
   $DB_DBA_Loc        = $conf->val('DBA','User');
   my $DBAs_Hosts     = $conf->val('DBA','Host');
   $fus=0;
   $fus=1 if ($DB_DBA_Loc && $DBAs_Hosts);
   if ($fus){
      @DBA_HOST=split(',', $DBAs_Hosts);
      my $i = 0;
      foreach my $t (@DBA_HOST) {
         $t=chomp_plus($t);
         delete $DBA_HOST[$i] if ($DBA_HOST[$i] eq $ipaddress);
         $i++;
      }
   }
   print "OK\n";
   return 1;
}
##########################################################################
# DB connect 
sub DB_connect {
   my($DB_NAME,$DB_HOST,$DB_USER,$DB_PASS) = @_;
   my $cop=0;
   my $tdbh;
   while( 1 ) {
      $cop++;
      $tdbh = DBI->connect_cached("DBI:mysql:$DB_NAME;host=$DB_HOST", $DB_USER, $DB_PASS);
      if( $tdbh ) {
         syslog('info', 'Setup: Connected to DB '.$DB_NAME);
         return $tdbh;
      } else {
         syslog('err', 'Setup: Could not connect to DB '.$DB_NAME.'!');
         syslog('info', "Setup: Connection to DB ".$DB_NAME." -> SLEEP $cop");
         return 0 if ($cop>=3);
      }
      sleep $cop;
   }
}
##########################################################################
# Get master & slave status
sub Get_Status_in_Master {
   my $dbh=shift;
   my @row;
   my $query = "show master status";
   my $sqlQuery  = $dbh->prepare($query) or die "Can't prepare $query: $dbh->errstr\n";
   $sqlQuery->execute or die "can't execute the query: $sqlQuery->errstr";
   @row= $sqlQuery->fetchrow_array();
   $M_binf=$row[0];
   $M_binp=$row[1];
   $sqlQuery->finish;
}
##########################################################################
# Get master & slave status
sub Get_Status_from_Slave {
   my $dbh=shift;
   my @row;
   my $query = "show slave status";
   my $sqlQuery  = $dbh->prepare($query) or die "Can't prepare $query: $dbh->errstr\n";
   $sqlQuery->execute or die "can't execute the query: $sqlQuery->errstr";
   @row= $sqlQuery->fetchrow_array();
   $M_binf=$row[9];
   $M_binp=$row[21];
   $sqlQuery->finish;
}
##########################################################################
# Set structure
sub Set_str {
   my $k=shift;
   print "Execute SQL file [$k] On Slave [$DB_HOST_Slv]: /";
   system("mysql -h'$DB_HOST_Slv' -u'$DB_USER_Slv' -p'$DB_PASS_Slv' < $k") == 0  or die "Failed: $?";
}
##########################################################################
# Get data
sub Get_data {
   my ($db,$tb)=@_;
   my $passstring = '';
   $passstring = '-p '.$dumppass if (defined($dumppass) && $dumppass ne '');
   if (defined ($tb) && $tb ne '') {
      printf "Dumping table [$db.$tb] From [$dumpfrom]: /";
      system ("mysqldump -h'$dumpfrom' -u'$dumpuser' $passstring --no-create-info $db $tb > $DumpDir/dump-$db-$tb.sql") == 0  or die "Failed: $?";
   } else {
      printf "Dumping full database [$db] From [$dumpfrom]: /";
      system ("mysqldump -h'$dumpfrom' -u'$dumpuser' $passstring --no-create-info $db > $DumpDir/dump-$db.sql") == 0  or die "Failed: $?";
   }
}
##########################################################################
# Set data
sub Set_data {
   my ($db,$tb)=@_;
   if (defined ($tb) && $tb ne '') {
      printf "Invoke Dump file [dump-$db-$tb.sql] On slave [$DB_HOST_Slv]: /";
      system ("mysql -h'$DB_HOST_Slv' -u'$DB_USER_Slv' -p'$DB_PASS_Slv' $db < $DumpDir/dump-$db-$tb.sql") == 0  or die "Failed: $?";
   } else {
      printf "Invoke Dump file [dump-$db.sql] On slave [$DB_HOST_Slv]: /";
      system ("mysql -h'$DB_HOST_Slv' -u'$DB_USER_Slv' -p'$DB_PASS_Slv' $db < $DumpDir/dump-$db.sql") == 0  or die "Failed: $?";
   }
}
##########################################################################
# Set users
sub Set_users {
   my $count=@USER_HOST+@DBA_HOST;
   my ($t, $Req_);
   printf "Creating $count users (@USER_HOST Astrads and @DBA_HOST DBAs) On Slave [$DB_HOST_Slv]: /";
   foreach $t (@USER_HOST) {
      $Req_ = "INSERT IGNORE INTO mysql.user (Host,User,Password) VALUES ('$t','$DB_USER_Loc','');FLUSH PRIVILEGES;GRANT PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD,LOCK TABLES  ON *.* TO '$DB_USER_Loc'\@'$t' IDENTIFIED BY '';GRANT all privileges ON astrad.* TO '$DB_USER_Loc'\@'$t' IDENTIFIED BY '';GRANT all privileges ON asterisk.* TO '$DB_USER_Loc'\@'$t' IDENTIFIED BY '';GRANT SELECT ON \\`porta-billing\\`.* TO '$DB_USER_Loc'\@'$t' IDENTIFIED BY '';FLUSH PRIVILEGES;";
      system("mysql -h'$DB_HOST_Slv' -u'$DB_USER_Slv' -p'$DB_PASS_Slv' mysql -e \"$Req_\" ") == 0  or print "Failed: $?";
   }
   foreach $t (@DBA_HOST) {
      if (defined $t) {
         $Req_ = "INSERT IGNORE INTO mysql.user (Host,User,Password) VALUES ('$t','$DB_DBA_Loc','');FLUSH PRIVILEGES;GRANT PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD,LOCK TABLES ON *.* TO '$DB_DBA_Loc'\@'$t' IDENTIFIED BY '';GRANT all privileges ON *.* TO '$DB_DBA_Loc'\@'$t' IDENTIFIED BY '';FLUSH PRIVILEGES;";
         system("mysql -h'$DB_HOST_Slv' -u'$DB_USER_Slv' -p'$DB_PASS_Slv' mysql -e \"$Req_\" ") == 0  or print "Failed: $?";
      }
   }
}
##########################################################################
# Print status
sub Print_status {
my ($SB,$P1,$P2)=@_;
my $index=0;
my $thr;
my @spinners = ("-", "\\", "|", "/");
if ($P1 && $P2) {
   $thr = threads->new(\&$SB,$P1,$P2);
   }
   else {
      if ($P1) {
         $thr = threads->new(\&$SB,$P1);
      } else {
         $thr = threads->new(\&$SB);
      }
   }
$thr->detach;
while($thr->is_running())
   {
   print "\b$spinners[$index++ % @spinners]";
   usleep(500);
   }
print "\bOK\n";
}
##########################################################################
# bzip2
sub bzip2 {
print "Creating dump archive [$DumpDir.bzip2]: /";
system("tar -cjvf $DumpDir.bzip2 $DumpDir/ >> /dev/null 2>&1") == 0  or print "Failed: $?";
system("rm -R $DumpDir/ >> /dev/null 2>&1") == 0  or print "Failed: $?";
}
sub debug {
  my $message=shift;
  print "Debug:".$message."\n";
}

##########################################################################
#                                 main()                                 #
##########################################################################
$| = 1;
load_config();
#Dumping Master DB
my $dbh;
my $query;
if ($dumptype == 0 || $dumptype == 1 || $dumptype == 3) {
   if (defined($dumppass)) {
      $dbh=DB_connect('',$dumpfrom,$dumpuser,$dumppass);
   } else {
      $dbh=DB_connect('',$dumpfrom,$dumpuser);
   }
   if ($dumptype == 1 || $dumptype == 3) {
      $query = "STOP SLAVE;";
      $dbh->do($query);
      $query = "FLUSH TABLES;";
      $dbh->do($query);
   } else {
      $query = "FLUSH TABLES WITH READ LOCK;";
      $dbh->do($query);
   }
   if ($dumptype == 0) {
      print "Reading replication cursor from Master\n";
      Get_Status_in_Master($dbh);
   } else {
      if ($dumptype == 1) {
        print "Reading replication cursor from Slave $dumpfrom\n";
        Get_Status_from_Slave($dbh);
      } else {
        print "Reading replication cursor from Slave $dumpfrom which will be this machine master\n";
        Get_Status_in_Master($dbh);
      }
   }
   $DumpDir="dump-".strftime("%Y-%m-%d", localtime(time)).sprintf("-%05d",rand(10)*10000);
   mkdir $DumpDir;
   foreach my $db (@DB_NAME) {
      if (defined($DB_TABLE{$db})) {
         my $REF=$DB_TABLE{$db};
         foreach my $tb (@$REF) {
            Print_status("Get_data",$db,$tb);
         }
      } else {
         Print_status("Get_data",$db,'');
      }
   }
   if ($dumptype == 0) {
      $query = "UNLOCK TABLES;";
      $dbh->do($query);
   } else {
      $query = "START SLAVE;";
      $dbh->do($query);
   }
   $dbh->disconnect;
} else {
#Open dumps from file
   $dumpfrom =~ m/.*\/([^\/]+)\.bzip2$/;
   $DumpDir = $1;
   safe_exit() unless (-e $dumpfrom);
   print "Reading replication cursor from file $dumpfrom\n";
   system("cp $dumpfrom /root");
   print "Using SQL dumps in file $dumpfrom\n";
   system("cd /root;tar -xjvf /root/$DumpDir.bzip2 >> /dev/null 2>&1") == 0 or print "Failed: $?";
   my $grep = `cat /root/$DumpDir/BEGIN.txt|grep "Master Log File"`;
   $grep =~ m/: (.*)$/;
   $M_binf=$1;
   $grep = `cat /root/$DumpDir/BEGIN.txt|grep "Master Log Pos"`;
   $grep =~ m/: (.*)$/;
   $M_binp=$1;
}
#Importing Master DB to Slave and setting replication
Print_status("Set_conf");
$dbh=DB_connect('',$DB_HOST_Slv,$DB_USER_Slv,$DB_PASS_Slv);
$query = "STOP SLAVE;";
$dbh->do($query);
$query = "SET unique_checks=0;";
$dbh->do($query);
$query = "SET foreign_key_checks=0;";
$dbh->do($query);
$dbh->disconnect ;
foreach my $k (@Struct) {
   Print_status("Set_str",$k);
}
foreach my $db (@DB_NAME) {
   if (defined($DB_TABLE{$db})) {
      my $REF=$DB_TABLE{$db};
      foreach my $tb (@$REF) {
         Print_status("Set_data",$db,$tb);
      }
   } else {
      Print_status("Set_data",$db,'');
   }
}
print "Update Master log file (name & position) and Restarting Slave [$DB_HOST_Slv]:  ";
$dbh=DB_connect('',$DB_HOST_Slv,$DB_USER_Slv,$DB_PASS_Slv);
$query = "SET unique_checks=1;";
$dbh->do($query);
$query = "SET foreign_key_checks=1;";
$dbh->do($query);
$query = "STOP SLAVE;";
$dbh->do($query);
$query = "CHANGE MASTER TO MASTER_HOST='$DB_HOST_Mst', MASTER_USER='$DB_USER_Mst', MASTER_PASSWORD='', MASTER_LOG_FILE='$M_binf', MASTER_LOG_POS=$M_binp;";
$dbh->do($query);
$query = "START SLAVE;";
$dbh->do($query);
$dbh->disconnect ;
print "\bOK\n";
Print_status("Set_users") if ($fus);
if ($dumptype == 0 || $dumptype == 1 || $dumptype == 3) {
   open(README,">$DumpDir/BEGIN.txt") or die ("Creation Readme.txt failed ") ;
   print README "\nDate           : ".format_date_sql(time);
   print README "\nMaster Host    : $DB_HOST_Mst";
   print README "\nMaster Log File: $M_binf";
   print README "\nMaster Log Pos : $M_binp";
   print README "\nSlave Host     : $DB_HOST_Slv\n";
   close(README);
}
Print_status("bzip2");
safe_exit();
##########################################################################
#                                  END                                   #
##########################################################################