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	#!/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'});