#!/usr/bin/perl

#########################################
# check_mysql.pl v0.1
#  by Emmanuel Doguet
#
# Check status of MySql server and Slave
# Can output perf-data of Number of Query
#
#
# v0.1:		first version
#########################################

use strict;
use Getopt::Long;
use DBD::mysql;
use POSIX qw(strftime);

# Use Own lib
use lib "/app/scripts/nagios";
use nagios_libs;


my $DEBUG = 1;
my $perfdata_file=undef;
my $perfdata = undef;

########
# Aide #
########
sub Usage {
	my ( $s ) = @_;

        if( defined( $s ) )
        {
                print "$s";
        }


        print "check_mysql.pl\n";
        print "  -h                mysql host\n";
        print "  -P                port\n";
#        print "  -w, -c         Critical, Warning check\n";
        print "  -u                user\n";
	print "  -p                password\n";
	print "  --perfdata        perf-data : Queries per second\n";
	print "  -s<warn>,<crit>   slave mode (x and y are warning and critical value for delay between master & slave)\n";
        print "\n";


	exit(-1);
}

#############
# Arguments #
#############
my $o_host="localhost";
my $o_port=3306;
my $o_user="root";
my $o_password="";
my $o_slave;
my $o_perfdata;
my $o_help;

sub check_options {
    Getopt::Long::Configure ("bundling");
    GetOptions( 
        'h:s'   => \$o_host,            'hostname:s'    => \$o_host,
        'P:i'   => \$o_port,            'port:i'        => \$o_port,
	'u:s'	=> \$o_user,
	'p:s'	=> \$o_password,
	's:s'	=> \$o_slave,
	'perfdata' => \$o_perfdata,
	'--help'   => \$o_help,
	);
}


	check_options();

	if( defined( $o_help ) )
	{
		Usage();
	}

	# Check options
	my @slave_delay;
	if( defined( $o_slave ) )
	{
		@slave_delay = split( ',', $o_slave );

		#foreach ( @slave_delay ) {
		#  print "$_\n";
		#}

		if( $slave_delay[0] > $slave_delay[1] )
		{
			Usage( "ERROR\nWith -s<warn>,<crit> : Warning must be < Critical !\n\n" );
		}
	}
	
	# Set defaults datas
	$perfdata_file = "/tmp/check_mysql-$o_host.tmp";


	###############
	# Connexion ! #
	###############
	my $dbh = DBI->connect("DBI:mysql:mysql:host=$o_host:$o_port", $o_user, $o_password, 
		{ PrintError=> 0,
		  RaiseError=>0
		} ); 

	if( $dbh == undef ) {
		NAGIOS_Add( "Error: " . $DBI::errstr );
		NAGIOS_SetStatus( $STATES{'CRITICAL'}, "Connect failed!" );
		NAGIOS_Send();
	        exit( $NAGIOS_STATUS );
	};


	#################
	# Server Status #
	#################
	my %mysql_status= ( "Uptime" => -1, "Slow_queries" => -1, "Queries" => 0 );

	my $sth = $dbh->prepare("show status");
	
	if( $sth->execute == undef )
	{
		NAGIOS_Add( "$N_COLOR_RED SQL Error: " .  $sth->errstr  );
		NAGIOS_SetStatus( $STATES{'CRITICAL'}, "SQL Error!" );
		NAGIOS_Send();
	        exit( $NAGIOS_STATUS );
	}

	# Get Values
	my $ref = $sth->fetchrow_hashref();
	my $slow_queries = $ref->{'Uptime'};


	# Parse results
	while ( my $row = $sth->fetchrow_hashref ) 
	{
	     foreach my $field ( sort keys %{ $row } ) {

		if( !( $row->{ $field } =~ /^(\d+\.?\d*|\.\d+)$/)  )
		{
			my $r =  $row->{ $field };
			if( exists( $mysql_status{ $r } ) )
			{
				$mysql_status{ $r } = $row->{ 'Value' } ;
			}
		}
     		}
  	} 



	NAGIOS_Add( "\n$N_COLOR_GREEN  Uptime : " . $mysql_status{ 'Uptime' } . "\n" );
       	NAGIOS_SetStatus( $STATES{'OK'} );

	NAGIOS_Add( "$N_COLOR_GREEN  Slow Queries: " . $mysql_status{ 'Slow_queries' } . "\n" );
       	NAGIOS_SetStatus( $STATES{'OK'} );

	if( defined( $o_perfdata ) )
	{
		my $cur_time=time();
		my @query;
		my $query_avg = "not yet";

		if( -e $perfdata_file )
		{
			
			open(FP,"< $perfdata_file");
			my @fContent = <FP>;
			close( FP );

			@query = split( ":" , $fContent[0] );

			$query_avg = ($mysql_status{ 'Queries' } - $query[1]) / ($cur_time - $query[0]);
		}

		open( FP, "> $perfdata_file" );
		print FP "$cur_time:" . $mysql_status{ 'Queries' }  . "\n";
		close( FP );
		
		$perfdata .= "queries_per_sec=" . sprintf( "%d", $query_avg ) . " ";

		#print "Time: $cur_time - " . $query[0] . "\n";
		#print "Query: " . $mysql_status{ 'Queries' }  . " - " . $query[1] .  "\n";	

		
	}

	################
	# Slave Status #
	################
	if( defined( $o_slave ) )
	{
		NAGIOS_Add( "\n<b>Slave Status:</b>\n" );

		my $sth = $dbh->prepare("show slave status") or die("??");
	
		if( $sth->execute() == undef )
		{
			NAGIOS_Add( "$N_COLOR_RED SQL Error: " .  $sth->errstr  );
			NAGIOS_SetStatus( $STATES{'CRITICAL'}, "SQL Error!" );
			NAGIOS_Send();
		        exit( $NAGIOS_STATUS );
		}

		# Get Values
		my $ref = $sth->fetchrow_hashref();
		my $slave_io_running = $ref->{'Slave_IO_Running'};
		my $slave_io_state = $ref->{'Slave_IO_State'};
		my $seconds_behind_master = $ref->{'Seconds_Behind_Master'};
		my $slave_sql = $ref->{'Slave_SQL_Running'};
		my $last_error = $ref->{'Last_Error'};

		$sth->finish;
		$dbh->disconnect;

	
		####################
		# Slave IO Running #
		####################
		if ($slave_io_running eq "No") 
		{
                	NAGIOS_Add( "$N_COLOR_RED  Slave IO Running:  $slave_io_running\n" );
                	NAGIOS_SetStatus( $STATES{'CRITICAL'}, "not running" );
		}
		else
		{
                	NAGIOS_Add( "$N_COLOR_GREEN  Slave IO Running:  $slave_io_running\n" );
                	NAGIOS_SetStatus( $STATES{'OK'}, "running" );
		}


		##################
		# Slave IO State #
		##################
		if( $slave_io_state eq "No" ) 
		{
			NAGIOS_Add( "$N_COLOR_YELLOW  Slave IO State = $slave_io_state\¬" );
			NAGIOS_SetStatus( $STATES{'WARNING'}, "IO warning" );
		}
		else
		{
			NAGIOS_Add( "$N_COLOR_GREEN  Slave IO State = $slave_io_state\n" );
			NAGIOS_SetStatus( $STATES{'OK'}, "IO ok" );
		}


		#####################
		# Slave SQL Running #
		#####################
		if ($slave_sql eq "No") 
		{
                	NAGIOS_Add( "$N_COLOR_RED  Slave SQL Running:  $slave_io_running\n" );
                	NAGIOS_SetStatus( $STATES{'CRITICAL'}, "sql not running" );
		}
		else
		{
                	NAGIOS_Add( "$N_COLOR_GREEN  Slave SQL Running:  $slave_io_running\n" );
                	NAGIOS_SetStatus( $STATES{'OK'}, "sql running" );
		}

		##########################
		# Secondes Behind Master #
		##########################
		if ($slave_delay[0] != 0 && $seconds_behind_master >  $slave_delay[0]  && $seconds_behind_master <  $slave_delay[1]  ) 
		{
			NAGIOS_Add( "$N_COLOR_YELLOW  Seconds Behind Master = $seconds_behind_master\n" );
			NAGIOS_SetStatus( $STATES{'WARNING'}, "Delay warn" );
		}
		elsif ( $slave_delay[1]!= 0 && $seconds_behind_master >=  $slave_delay[1]  ) 
		{
			NAGIOS_Add( "$N_COLOR_RED  Seconds Behind Master = $seconds_behind_master\n" );
			NAGIOS_SetStatus( $STATES{'CRITICAL'}, "Delay nok" );
		}
		else
		{
			NAGIOS_Add( "$N_COLOR_GREEN  Seconds Behind Master = $seconds_behind_master\n" );
			NAGIOS_SetStatus( $STATES{'OK'}, "Delay ok" );
		}


	}


	NAGIOS_Send( $perfdata );
      	exit( $NAGIOS_STATUS );