trunk/plugins/check_galera_nodes.pl

#!/usr/bin/perl

# http://nagios.sourceforge.net/docs/3_0/embeddedperl.html
# nagios: -epn

# (C) 2006-2015 by Oli Sennhauser <oli.sennhauser@fromdual.com>
# FromDual: Neutral and vendor independent consulting for MySQL,
# MariaDB and Percona Server www.fromdual.com
#
# This program is free software; you can redistribute it and/or modify it
# under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street - Fifth Floor, Boston, MA
# 02110-1301, USA.

use strict;
use warnings "all";

use DBI;
use Getopt::Long;
use File::Basename;

my $lMyName = basename($0);

my %lError = (
  'OK'       => 0
, 'Warning'  => 1
, 'Critical' => 2
, 'Unknown'  => 3
);

my $pHelp          = 0;
my $pUser          = 'check_db';
my $pPassword      = '';
my $pHost          = 'localhost';
my $lDefaultPort   = '3306';
my $lDefaultSocket = '/var/run/mysqld/mysqld.sock';
my $pNodes         = 3;

sub Usage
{
        print <<EOF;

SYNOPSIS

  $lMyName flags parameters

DESCRIPTION

  Nagios/Icinga plugin to check if a Galera Cluster for MySQL is up and running...

  For security reasons use a user with as little privileges as possible. For
  example a user called $pUser:

  GRANT USAGE ON *.* TO '$pUser'\@'$pHost' IDENTIFIED BY 'secret';

FLAGS

  --help, -?           Display this help and exit.
  --host=name, -h      Host where database to check is located (default $pHost).
  --password=name, -p  Password of user $pUser to use when connecting to server
                       $pHost (default '$pPassword').
  --port=#, -P         Port number where database listens to (default $lDefaultPort).
  --socket=name, -S    Socket file to use for connection (default
                       $lDefaultSocket).
  --user=name, -u      Check user for connecting to the database (default
                       $pUser).
  --nodes=#            Number of expected nodes (default $pNodes).

PARAMETERS

  none

EXAMPLE

  $lMyName --user=$pUser --password=secret --host=$pHost --port=$lDefaultPort --nodes=$pNodes

EOF
}

my ($pPort, $pSocket);

my $rc = GetOptions(
  'help|?'       => \$pHelp
, 'user|u=s'     => \$pUser
, 'password|p=s' => \$pPassword
, 'host|h=s'     => \$pHost
, 'port|P=i'     => \$pPort
, 'socket|S=s'   => \$pSocket
, 'nodes|n=i'    => \$pNodes
);

# On Unix, MySQL programs treat the host name localhost specially. For connec-
# tions to localhost, MySQL programs attempt to connect to the local server by
# using a Unix socket file. This occurs even if a --port or -P  option is given
# to specify a port number. To ensure that the client makes a TCP/IP connection
# to the local server, use --host or -h to specify a host name value of
# 127.0.0.1, or the IP address or name of the local server.
#
# Lit: refman-5.6-en.html-chapter/programs.html

my $lConnectionMethod = 'socket';

if ( ! defined($pHost) || $pHost eq '' ) {
        $pHost = 'localhost';
}
if ( $pHost eq 'localhost' ) {
        if ( defined($pPort) && $pPort ne '' ) {
                print("Port is overspecified when using host=localhost.");
                exit($lError{'Warning'});
        }
        if ( ! defined($pSocket) || $pSocket eq '' ) {
                $pSocket = $lDefaultSocket;
        }
        $lConnectionMethod = 'socket';
}
# host != localhost
else {
        if ( defined($pSocket) && $pSocket ne '' ) {
                print("Socket is overspecified when using host=localhost.");
                exit($lError{'Warning'});
        }
        if ( ! defined($pPort) || $pPort eq '' ) {
                $pPort = $lDefaultPort;
        }
        $lConnectionMethod = 'port';
}

# Nodes not specfied or values out of range (<2 > 64)

if ( ($pNodes < 2) || ($pNodes > 128) ) {
        print "Number for Galera Cluster nodes is out of expected range (2...128): $pNodes\n";
        exit($lError{'Warning'});
}

if ( $pHelp ) {
        &Usage();
        exit($lError{'OK'});
}

if ( ! $rc ) {
        print("Error in parameters. User = $pUser, Password=hidden, Host = $pHost, Port = $pPort, Socket = $pSocket");
        exit($lError{'Unknown'});
}

if ( @ARGV != 0 ) {
        print("Error in parameters. To many arguments: @ARGV");
        exit($lError{'Unknown'});
}

# Start here
# ----------

my ($dbh, $sql, $sth);

my $lTimeout = 10;
my $dsn;
if ( $lConnectionMethod eq 'socket' ) {
        $dsn = "DBI:mysql::mysql_socket=$pSocket;mysql_connect_timeout=$lTimeout";
}
else {
        $dsn = "DBI:mysql::host=$pHost:port=$pPort;mysql_connect_timeout=$lTimeout";
}
$dbh = DBI->connect($dsn, $pUser, $pPassword
        , { RaiseError => 0
        , PrintError => 0
        , AutoCommit => 0
                }
);

if ( DBI::err ) {

        if ( DBI::errstr =~ m/Can't connect to/ ) {
                print("Error during connection: " . DBI::errstr);
                exit($lError{'Critical'});
        }

        if ( DBI::errstr =~ m/Access denied for user/ ) {
                print("User does not have access privileges to database: " . DBI::errstr);
                exit($lError{'Warning'});
        }

        print("Error during connection: " . DBI::errstr);
        exit($lError{'Critical'});
}

$sql = "
        SHOW GLOBAL STATUS WHERE variable_name in ('wsrep_cluster_size', 'wsrep_cluster_status')
";

$sth = $dbh->prepare( $sql );
if ( DBI::err ) {
        print("Error in preparing: " . DBI::errstr);
        $dbh->disconnect;
        exit($lError{'Critical'});
}

$sth->execute();
if ( $sth->err ) {
        print("Error in executing: " . $sth->errstr);
        $dbh->disconnect;
        exit($lError{'Critical'});
}

my ($lKey, $lValue);
$sth->bind_columns(undef, \$lKey, \$lValue);
if ( $sth->err ) {
        print("Error in binding: " . $sth->errstr);
        $sth->finish;
        $dbh->disconnect;
        exit($lError{'Critical'});
}

my %aStatus;
while ( $sth->fetchrow_arrayref() ) {
        $aStatus{$lKey} = $lValue;
}
if ( $sth->err ) {
        print("Error in fetchting:" . $sth->err);
        $sth->finish;
        $dbh->disconnect;
        exit($lError{'Critical'});
}
$sth->finish;

if ( ! defined($aStatus{'wsrep_cluster_status'}) ) {
        print "Caution: It looks like this is NOT a Galera cluster. The variable wsrep_cluster_status is not defined.\n";
        $dbh->disconnect;
        exit($lError{'Warning'});
}

if ( $aStatus{'wsrep_cluster_status'} eq "Primary" ) {

        if ( $aStatus{'wsrep_cluster_size'} == $pNodes ) {
                print "OK wsrep_cluster_size: " . $aStatus{'wsrep_cluster_size'} . ", wsrep_cluster_status: " . $aStatus{'wsrep_cluster_status'} . "\n";
                $dbh->disconnect;
                exit($lError{'OK'});
        }
        else {
                print "Caution: wsrep_cluster_size: " . $aStatus{'wsrep_cluster_size'} . ", wsrep_cluster_status: " . $aStatus{'wsrep_cluster_status'} . "\n";
                $dbh->disconnect;
                exit($lError{'Warning'});
        }
}
else {
        print "Caution: wsrep_cluster_status: " . $aStatus{'wsrep_cluster_status'} . "\n";
        $dbh->disconnect;
        exit($lError{'Critical'});
}

print "Galera Cluster for MySQL seems OK...";
exit($lError{'OK'});
1	racvision	205	#!/usr/bin/perl
2
3			# http://nagios.sourceforge.net/docs/3_0/embeddedperl.html
4			# nagios: -epn
5
6			# (C) 2006-2015 by Oli Sennhauser <oli.sennhauser@fromdual.com>
7			# FromDual: Neutral and vendor independent consulting for MySQL,
8			# MariaDB and Percona Server www.fromdual.com
9			#
10			# This program is free software; you can redistribute it and/or modify it
11			# under the terms of the GNU General Public License as published by
12			# the Free Software Foundation; version 2 of the License.
13			#
14			# This program is distributed in the hope that it will be useful, but
15			# WITHOUT ANY WARRANTY; without even the implied warranty of
16			# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
17			# General Public License for more details.
18			#
19			# You should have received a copy of the GNU General Public License
20			# along with this program; if not, write to the Free Software
21			# Foundation, Inc., 51 Franklin Street - Fifth Floor, Boston, MA
22			# 02110-1301, USA.
23
24			use strict;
25			use warnings "all";
26
27			use DBI;
28			use Getopt::Long;
29			use File::Basename;
30
31			my $lMyName = basename($0);
32
33			my %lError = (
34			'OK' => 0
35			, 'Warning' => 1
36			, 'Critical' => 2
37			, 'Unknown' => 3
38			);
39
40			my $pHelp = 0;
41			my $pUser = 'check_db';
42			my $pPassword = '';
43			my $pHost = 'localhost';
44			my $lDefaultPort = '3306';
45			my $lDefaultSocket = '/var/run/mysqld/mysqld.sock';
46			my $pNodes = 3;
47
48			sub Usage
49			{
50			print <<EOF;
51
52			SYNOPSIS
53
54			$lMyName flags parameters
55
56			DESCRIPTION
57
58			Nagios/Icinga plugin to check if a Galera Cluster for MySQL is up and running...
59
60			For security reasons use a user with as little privileges as possible. For
61			example a user called $pUser:
62
63			GRANT USAGE ON . TO '$pUser'\@'$pHost' IDENTIFIED BY 'secret';
64
65			FLAGS
66
67			--help, -? Display this help and exit.
68			--host=name, -h Host where database to check is located (default $pHost).
69			--password=name, -p Password of user $pUser to use when connecting to server
70			$pHost (default '$pPassword').
71			--port=#, -P Port number where database listens to (default $lDefaultPort).
72			--socket=name, -S Socket file to use for connection (default
73			$lDefaultSocket).
74			--user=name, -u Check user for connecting to the database (default
75			$pUser).
76			--nodes=# Number of expected nodes (default $pNodes).
77
78			PARAMETERS
79
80			none
81
82			EXAMPLE
83
84			$lMyName --user=$pUser --password=secret --host=$pHost --port=$lDefaultPort --nodes=$pNodes
85
86			EOF
87			}
88
89			my ($pPort, $pSocket);
90
91			my $rc = GetOptions(
92			'help\|?' => \$pHelp
93			, 'user\|u=s' => \$pUser
94			, 'password\|p=s' => \$pPassword
95			, 'host\|h=s' => \$pHost
96			, 'port\|P=i' => \$pPort
97			, 'socket\|S=s' => \$pSocket
98			, 'nodes\|n=i' => \$pNodes
99			);
100
101			# On Unix, MySQL programs treat the host name localhost specially. For connec-
102			# tions to localhost, MySQL programs attempt to connect to the local server by
103			# using a Unix socket file. This occurs even if a --port or -P option is given
104			# to specify a port number. To ensure that the client makes a TCP/IP connection
105			# to the local server, use --host or -h to specify a host name value of
106			# 127.0.0.1, or the IP address or name of the local server.
107			#
108			# Lit: refman-5.6-en.html-chapter/programs.html
109
110			my $lConnectionMethod = 'socket';
111
112			if ( ! defined($pHost) \|\| $pHost eq '' ) {
113			$pHost = 'localhost';
114			}
115			if ( $pHost eq 'localhost' ) {
116			if ( defined($pPort) && $pPort ne '' ) {
117			print("Port is overspecified when using host=localhost.");
118			exit($lError{'Warning'});
119			}
120			if ( ! defined($pSocket) \|\| $pSocket eq '' ) {
121			$pSocket = $lDefaultSocket;
122			}
123			$lConnectionMethod = 'socket';
124			}
125			# host != localhost
126			else {
127			if ( defined($pSocket) && $pSocket ne '' ) {
128			print("Socket is overspecified when using host=localhost.");
129			exit($lError{'Warning'});
130			}
131			if ( ! defined($pPort) \|\| $pPort eq '' ) {
132			$pPort = $lDefaultPort;
133			}
134			$lConnectionMethod = 'port';
135			}
136
137			# Nodes not specfied or values out of range (<2 > 64)
138
139			if ( ($pNodes < 2) \|\| ($pNodes > 128) ) {
140			print "Number for Galera Cluster nodes is out of expected range (2...128): $pNodes\n";
141			exit($lError{'Warning'});
142			}
143
144			if ( $pHelp ) {
145			&Usage();
146			exit($lError{'OK'});
147			}
148
149			if ( ! $rc ) {
150			print("Error in parameters. User = $pUser, Password=hidden, Host = $pHost, Port = $pPort, Socket = $pSocket");
151			exit($lError{'Unknown'});
152			}
153
154			if ( @ARGV != 0 ) {
155			print("Error in parameters. To many arguments: @ARGV");
156			exit($lError{'Unknown'});
157			}
158
159			# Start here
160			# ----------
161
162			my ($dbh, $sql, $sth);
163
164			my $lTimeout = 10;
165			my $dsn;
166			if ( $lConnectionMethod eq 'socket' ) {
167			$dsn = "DBI:mysql::mysql_socket=$pSocket;mysql_connect_timeout=$lTimeout";
168			}
169			else {
170			$dsn = "DBI:mysql::host=$pHost:port=$pPort;mysql_connect_timeout=$lTimeout";
171			}
172			$dbh = DBI->connect($dsn, $pUser, $pPassword
173			, { RaiseError => 0
174			, PrintError => 0
175			, AutoCommit => 0
176			}
177			);
178
179			if ( DBI::err ) {
180
181			if ( DBI::errstr =~ m/Can't connect to/ ) {
182			print("Error during connection: " . DBI::errstr);
183			exit($lError{'Critical'});
184			}
185
186			if ( DBI::errstr =~ m/Access denied for user/ ) {
187			print("User does not have access privileges to database: " . DBI::errstr);
188			exit($lError{'Warning'});
189			}
190
191			print("Error during connection: " . DBI::errstr);
192			exit($lError{'Critical'});
193			}
194
195			$sql = "
196			SHOW GLOBAL STATUS WHERE variable_name in ('wsrep_cluster_size', 'wsrep_cluster_status')
197			";
198
199			$sth = $dbh->prepare( $sql );
200			if ( DBI::err ) {
201			print("Error in preparing: " . DBI::errstr);
202			$dbh->disconnect;
203			exit($lError{'Critical'});
204			}
205
206			$sth->execute();
207			if ( $sth->err ) {
208			print("Error in executing: " . $sth->errstr);
209			$dbh->disconnect;
210			exit($lError{'Critical'});
211			}
212
213			my ($lKey, $lValue);
214			$sth->bind_columns(undef, \$lKey, \$lValue);
215			if ( $sth->err ) {
216			print("Error in binding: " . $sth->errstr);
217			$sth->finish;
218			$dbh->disconnect;
219			exit($lError{'Critical'});
220			}
221
222			my %aStatus;
223			while ( $sth->fetchrow_arrayref() ) {
224			$aStatus{$lKey} = $lValue;
225			}
226			if ( $sth->err ) {
227			print("Error in fetchting:" . $sth->err);
228			$sth->finish;
229			$dbh->disconnect;
230			exit($lError{'Critical'});
231			}
232			$sth->finish;
233
234			if ( ! defined($aStatus{'wsrep_cluster_status'}) ) {
235			print "Caution: It looks like this is NOT a Galera cluster. The variable wsrep_cluster_status is not defined.\n";
236			$dbh->disconnect;
237			exit($lError{'Warning'});
238			}
239
240			if ( $aStatus{'wsrep_cluster_status'} eq "Primary" ) {
241
242			if ( $aStatus{'wsrep_cluster_size'} == $pNodes ) {
243			print "OK wsrep_cluster_size: " . $aStatus{'wsrep_cluster_size'} . ", wsrep_cluster_status: " . $aStatus{'wsrep_cluster_status'} . "\n";
244			$dbh->disconnect;
245			exit($lError{'OK'});
246			}
247			else {
248			print "Caution: wsrep_cluster_size: " . $aStatus{'wsrep_cluster_size'} . ", wsrep_cluster_status: " . $aStatus{'wsrep_cluster_status'} . "\n";
249			$dbh->disconnect;
250			exit($lError{'Warning'});
251			}
252			}
253			else {
254			print "Caution: wsrep_cluster_status: " . $aStatus{'wsrep_cluster_status'} . "\n";
255			$dbh->disconnect;
256			exit($lError{'Critical'});
257			}
258
259			print "Galera Cluster for MySQL seems OK...";
260			exit($lError{'OK'});