#!/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;
##########################################################################
# Setting
my $lock_file = '/var/run/setup-mysql-replication.pid';
my $runas_user = 'root';
my $config_file = '/etc/pbs/setup-mysql-replication.conf';
##########################################################################
# Globals
my ($DB_NAME_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,$M_binf,$M_binp,$DumpDir,$My_Cmt,$My_Unc);
my @Struct;
my @DB_NAME;
my @USER_HOST;
my %DB_TABLE;
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_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;
         }
      }
   }
$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)}
   }
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 {
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;
}
##########################################################################
# 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)=@_;
   if (defined ($tb) && $tb ne '') {
      printf "Dumping table [$db.$tb] From Master [$DB_HOST_Mst]: /";
      system ("mysqldump -h'$DB_HOST_Mst' -u'$DB_USER_Mst' --no-create-info $db $tb > $DumpDir/dump-$db-$tb.sql") == 0  or die "Failed: $?";
   } else {
      printf "Dumping full database [$db] From Master [$DB_HOST_Mst]: /";
      system ("mysqldump -h'$DB_HOST_Mst' -u'$DB_USER_Mst' --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;
printf "Creating $count users On Slave [$DB_HOST_Slv]: /";
foreach my $t (@USER_HOST) {
   my $Req_ = "INSERT IGNORE INTO 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 '';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=DB_connect('',$DB_HOST_Mst,$DB_USER_Mst);
my $query;
$query = "FLUSH TABLES WITH READ LOCK;";
$dbh->do($query);
Get_Status($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,'');
   }
}
$query = "UNLOCK TABLES;";
$dbh->do($query);
$dbh->disconnect ;
#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);
$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 = "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);
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                                   #
##########################################################################
