plugins/check_mysql.c

   1 /*****************************************************************************
   2 *
   3 * Monitoring check_mysql plugin
   4 *
   5 * License: GPL
   6 * Copyright (c) 1999 Didi Rieder (adrieder@sbox.tu-graz.ac.at)
   7 * Copyright (c) 2000 Karl DeBisschop (kdebisschop@users.sourceforge.net)
   8 * Copyright (c) 1999-2011 Monitoring Plugins Development Team
   9 *
  10 * Description:
  11 *
  12 * This file contains the check_mysql plugin
  13 *
  14 * This program tests connections to a mysql server
  15 *
  16 *
  17 * This program is free software: you can redistribute it and/or modify
  18 * it under the terms of the GNU General Public License as published by
  19 * the Free Software Foundation, either version 3 of the License, or
  20 * (at your option) any later version.
  21 *
  22 * This program is distributed in the hope that it will be useful,
  23 * but WITHOUT ANY WARRANTY; without even the implied warranty of
  24 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  25 * GNU General Public License for more details.
  26 *
  27 * You should have received a copy of the GNU General Public License
  28 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
  29 *
  30 *
  31 *****************************************************************************/
  32
  33 const char *progname = "check_mysql";
  34 const char *copyright = "1999-2011";
  35 const char *email = "devel@monitoring-plugins.org";
  36
  37 #define SLAVERESULTSIZE 70
  38
  39 #include "common.h"
  40 #include "utils.h"
  41 #include "utils_base.h"
  42 #include "netutils.h"
  43
  44 #include <mysql.h>
  45 #include <mysqld_error.h>
  46 #include <errmsg.h>
  47
  48 char *db_user = NULL;
  49 char *db_host = NULL;
  50 char *db_socket = NULL;
  51 char *db_pass = NULL;
  52 char *db = NULL;
  53 char *ca_cert = NULL;
  54 char *ca_dir = NULL;
  55 char *cert = NULL;
  56 char *key = NULL;
  57 char *ciphers = NULL;
  58 bool ssl = false;
  59 char *opt_file = NULL;
  60 char *opt_group = NULL;
  61 unsigned int db_port = MYSQL_PORT;
  62 int check_slave = 0, warn_sec = 0, crit_sec = 0;
  63 int ignore_auth = 0;
  64 int verbose = 0;
  65
  66 static double warning_time = 0;
  67 static double critical_time = 0;
  68
  69 #define LENGTH_METRIC_UNIT 6
  70 static const char *metric_unit[LENGTH_METRIC_UNIT] = {
  71         "Open_files",
  72         "Open_tables",
  73         "Qcache_free_memory",
  74         "Qcache_queries_in_cache",
  75         "Threads_connected",
  76         "Threads_running"
  77 };
  78
  79 #define LENGTH_METRIC_COUNTER 9
  80 static const char *metric_counter[LENGTH_METRIC_COUNTER] = {
  81         "Connections",
  82         "Qcache_hits",
  83         "Qcache_inserts",
  84         "Qcache_lowmem_prunes",
  85         "Qcache_not_cached",
  86         "Queries",
  87         "Questions",
  88         "Table_locks_waited",
  89         "Uptime"
  90 };
  91
  92 thresholds *my_threshold = NULL;
  93
  94 int process_arguments (int, char **);
  95 int validate_arguments (void);
  96 void print_help (void);
  97 void print_usage (void);
  98
  99 int
 100 main (int argc, char **argv)
 101 {
 102
 103         MYSQL mysql;
 104         MYSQL_RES *res;
 105         MYSQL_ROW row;
 106
 107         /* should be status */
 108
 109         char *result = NULL;
 110         char *error = NULL;
 111         char slaveresult[SLAVERESULTSIZE];
 112         char* perf;
 113
 114         perf = strdup ("");
 115
 116         setlocale (LC_ALL, "");
 117         bindtextdomain (PACKAGE, LOCALEDIR);
 118         textdomain (PACKAGE);
 119
 120         /* Parse extra opts if any */
 121         argv=np_extra_opts (&argc, argv, progname);
 122
 123         if (process_arguments (argc, argv) == ERROR)
 124                 usage4 (_("Could not parse arguments"));
 125
 126         /* initialize mysql  */
 127         mysql_init (&mysql);
 128
 129         if (opt_file != NULL)
 130                 mysql_options(&mysql,MYSQL_READ_DEFAULT_FILE,opt_file);
 131
 132         if (opt_group != NULL)
 133                 mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,opt_group);
 134         else
 135                 mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"client");
 136
 137         if (ssl)
 138                 mysql_ssl_set(&mysql,key,cert,ca_cert,ca_dir,ciphers);
 139         /* establish a connection to the server and error checking */
 140         if (!mysql_real_connect(&mysql,db_host,db_user,db_pass,db,db_port,db_socket,0)) {
 141                 if (ignore_auth && mysql_errno (&mysql) == ER_ACCESS_DENIED_ERROR)
 142                 {
 143                         printf("MySQL OK - Version: %s (protocol %d)\n",
 144                                 mysql_get_server_info(&mysql),
 145                                 mysql_get_proto_info(&mysql)
 146                         );
 147                         mysql_close (&mysql);
 148                         return STATE_OK;
 149                 }
 150                 else if (mysql_errno (&mysql) == CR_UNKNOWN_HOST)
 151                         die (STATE_WARNING, "%s\n", mysql_error (&mysql));
 152                 else if (mysql_errno (&mysql) == CR_VERSION_ERROR)
 153                         die (STATE_WARNING, "%s\n", mysql_error (&mysql));
 154                 else if (mysql_errno (&mysql) == CR_OUT_OF_MEMORY)
 155                         die (STATE_WARNING, "%s\n", mysql_error (&mysql));
 156                 else if (mysql_errno (&mysql) == CR_IPSOCK_ERROR)
 157                         die (STATE_WARNING, "%s\n", mysql_error (&mysql));
 158                 else if (mysql_errno (&mysql) == CR_SOCKET_CREATE_ERROR)
 159                         die (STATE_WARNING, "%s\n", mysql_error (&mysql));
 160                 else
 161                         die (STATE_CRITICAL, "%s\n", mysql_error (&mysql));
 162         }
 163
 164         /* get the server stats */
 165         result = strdup (mysql_stat (&mysql));
 166
 167         /* error checking once more */
 168         if (mysql_error (&mysql)) {
 169                 if (mysql_errno (&mysql) == CR_SERVER_GONE_ERROR)
 170                         die (STATE_CRITICAL, "%s\n", mysql_error (&mysql));
 171                 else if (mysql_errno (&mysql) == CR_SERVER_LOST)
 172                         die (STATE_CRITICAL, "%s\n", mysql_error (&mysql));
 173                 else if (mysql_errno (&mysql) == CR_UNKNOWN_ERROR)
 174                         die (STATE_CRITICAL, "%s\n", mysql_error (&mysql));
 175         }
 176
 177         /* try to fetch some perf data */
 178         if (mysql_query (&mysql, "show global status") == 0) {
 179                 if ( (res = mysql_store_result (&mysql)) == NULL) {
 180                         error = strdup(mysql_error(&mysql));
 181                         mysql_close (&mysql);
 182                         die (STATE_CRITICAL, _("status store_result error: %s\n"), error);
 183                 }
 184
 185                 while ( (row = mysql_fetch_row (res)) != NULL) {
 186                         int i;
 187
 188                         for(i = 0; i < LENGTH_METRIC_UNIT; i++) {
 189                                 if (strcmp(row[0], metric_unit[i]) == 0) {
 190                                         xasprintf(&perf, "%s%s ", perf, perfdata(metric_unit[i],
 191                                                 atol(row[1]), "", FALSE, 0, FALSE, 0, FALSE, 0, FALSE, 0));
 192                                         continue;
 193                                 }
 194                         }
 195                         for(i = 0; i < LENGTH_METRIC_COUNTER; i++) {
 196                                 if (strcmp(row[0], metric_counter[i]) == 0) {
 197                                         xasprintf(&perf, "%s%s ", perf, perfdata(metric_counter[i],
 198                                                 atol(row[1]), "c", FALSE, 0, FALSE, 0, FALSE, 0, FALSE, 0));
 199                                         continue;
 200                                 }
 201                         }
 202                 }
 203                 /* remove trailing space */
 204                 if (strlen(perf) > 0)
 205                     perf[strlen(perf) - 1] = '\0';
 206         }
 207
 208         if(check_slave) {
 209                 /* check the slave status */
 210                 if (mysql_query (&mysql, "show slave status") != 0) {
 211                         error = strdup(mysql_error(&mysql));
 212                         mysql_close (&mysql);
 213                         die (STATE_CRITICAL, _("slave query error: %s\n"), error);
 214                 }
 215
 216                 /* store the result */
 217                 if ( (res = mysql_store_result (&mysql)) == NULL) {
 218                         error = strdup(mysql_error(&mysql));
 219                         mysql_close (&mysql);
 220                         die (STATE_CRITICAL, _("slave store_result error: %s\n"), error);
 221                 }
 222
 223                 /* Check there is some data */
 224                 if (mysql_num_rows(res) == 0) {
 225                         mysql_close(&mysql);
 226                         die (STATE_WARNING, "%s\n", _("No slaves defined"));
 227                 }
 228
 229                 /* fetch the first row */
 230                 if ( (row = mysql_fetch_row (res)) == NULL) {
 231                         error = strdup(mysql_error(&mysql));
 232                         mysql_free_result (res);
 233                         mysql_close (&mysql);
 234                         die (STATE_CRITICAL, _("slave fetch row error: %s\n"), error);
 235                 }
 236
 237                 if (mysql_field_count (&mysql) == 12) {
 238                         /* mysql 3.23.x */
 239                         snprintf (slaveresult, SLAVERESULTSIZE, _("Slave running: %s"), row[6]);
 240                         if (strcmp (row[6], "Yes") != 0) {
 241                                 mysql_free_result (res);
 242                                 mysql_close (&mysql);
 243                                 die (STATE_CRITICAL, "%s\n", slaveresult);
 244                         }
 245
 246                 } else {
 247                         /* mysql 4.x.x and mysql 5.x.x */
 248                         int slave_io_field = -1 , slave_sql_field = -1, seconds_behind_field = -1, i, num_fields;
 249                         MYSQL_FIELD* fields;
 250
 251                         num_fields = mysql_num_fields(res);
 252                         fields = mysql_fetch_fields(res);
 253                         for(i = 0; i < num_fields; i++) {
 254                                 if (strcmp(fields[i].name, "Slave_IO_Running") == 0) {
 255                                         slave_io_field = i;
 256                                         continue;
 257                                 }
 258                                 if (strcmp(fields[i].name, "Slave_SQL_Running") == 0) {
 259                                         slave_sql_field = i;
 260                                         continue;
 261                                 }
 262                                 if (strcmp(fields[i].name, "Seconds_Behind_Master") == 0) {
 263                                         seconds_behind_field = i;
 264                                         continue;
 265                                 }
 266                         }
 267
 268                         /* Check if slave status is available */
 269                         if ((slave_io_field < 0) || (slave_sql_field < 0) || (num_fields == 0)) {
 270                                 mysql_free_result (res);
 271                                 mysql_close (&mysql);
 272                                 die (STATE_CRITICAL, "Slave status unavailable\n");
 273                         }
 274
 275                         /* Save slave status in slaveresult */
 276                         snprintf (slaveresult, SLAVERESULTSIZE, "Slave IO: %s Slave SQL: %s Seconds Behind Master: %s", row[slave_io_field], row[slave_sql_field], seconds_behind_field!=-1?row[seconds_behind_field]:"Unknown");
 277
 278                         /* Raise critical error if SQL THREAD or IO THREAD are stopped */
 279                         if (strcmp (row[slave_io_field], "Yes") != 0 || strcmp (row[slave_sql_field], "Yes") != 0) {
 280                                 mysql_free_result (res);
 281                                 mysql_close (&mysql);
 282                                 die (STATE_CRITICAL, "%s\n", slaveresult);
 283                         }
 284
 285                         if (verbose >=3) {
 286                                 if (seconds_behind_field == -1) {
 287                                         printf("seconds_behind_field not found\n");
 288                                 } else {
 289                                         printf ("seconds_behind_field(index %d)=%s\n", seconds_behind_field, row[seconds_behind_field]);
 290                                 }
 291                         }
 292
 293                         /* Check Seconds Behind against threshold */
 294                         if ((seconds_behind_field != -1) && (strcmp (row[seconds_behind_field], "NULL") != 0)) {
 295                                 double value = atof(row[seconds_behind_field]);
 296                                 int status;
 297
 298                                 status = get_status(value, my_threshold);
 299
 300                                 xasprintf (&perf, "%s %s", perf, fperfdata ("seconds behind master", value, "s",
 301                                         TRUE, (double) warning_time,
 302                                         TRUE, (double) critical_time,
 303                                         FALSE, 0,
 304                                         FALSE, 0));
 305
 306                                 if (status == STATE_WARNING) {
 307                                         printf("SLOW_SLAVE %s: %s|%s\n", _("WARNING"), slaveresult, perf);
 308                                         exit(STATE_WARNING);
 309                                 } else if (status == STATE_CRITICAL) {
 310                                         printf("SLOW_SLAVE %s: %s|%s\n", _("CRITICAL"), slaveresult, perf);
 311                                         exit(STATE_CRITICAL);
 312                                 }
 313                         }
 314                 }
 315
 316                 /* free the result */
 317                 mysql_free_result (res);
 318         }
 319
 320         /* close the connection */
 321         mysql_close (&mysql);
 322
 323         /* print out the result of stats */
 324         if (check_slave) {
 325                 printf ("%s %s|%s\n", result, slaveresult, perf);
 326         } else {
 327                 printf ("%s|%s\n", result, perf);
 328         }
 329
 330         return STATE_OK;
 331 }
 332
 333
 334 /* process command-line arguments */
 335 int
 336 process_arguments (int argc, char **argv)
 337 {
 338         int c;
 339         char *warning = NULL;
 340         char *critical = NULL;
 341
 342         int option = 0;
 343         static struct option longopts[] = {
 344                 {"hostname", required_argument, 0, 'H'},
 345                 {"socket", required_argument, 0, 's'},
 346                 {"database", required_argument, 0, 'd'},
 347                 {"username", required_argument, 0, 'u'},
 348                 {"password", required_argument, 0, 'p'},
 349                 {"file", required_argument, 0, 'f'},
 350                 {"group", required_argument, 0, 'g'},
 351                 {"port", required_argument, 0, 'P'},
 352                 {"critical", required_argument, 0, 'c'},
 353                 {"warning", required_argument, 0, 'w'},
 354                 {"check-slave", no_argument, 0, 'S'},
 355                 {"ignore-auth", no_argument, 0, 'n'},
 356                 {"verbose", no_argument, 0, 'v'},
 357                 {"version", no_argument, 0, 'V'},
 358                 {"help", no_argument, 0, 'h'},
 359                 {"ssl", no_argument, 0, 'l'},
 360                 {"ca-cert", optional_argument, 0, 'C'},
 361                 {"key", required_argument,0,'k'},
 362                 {"cert", required_argument,0,'a'},
 363                 {"ca-dir", required_argument, 0, 'D'},
 364                 {"ciphers", required_argument, 0, 'L'},
 365                 {0, 0, 0, 0}
 366         };
 367
 368         if (argc < 1)
 369                 return ERROR;
 370
 371         while (1) {
 372                 c = getopt_long (argc, argv, "hlvVnSP:p:u:d:H:s:c:w:a:k:C:D:L:f:g:", longopts, &option);
 373
 374                 if (c == -1 || c == EOF)
 375                         break;
 376
 377                 switch (c) {
 378                 case 'H':                                                                       /* hostname */
 379                         if (is_host (optarg)) {
 380                                 db_host = optarg;
 381                         }
 382                         else {
 383                                 usage2 (_("Invalid hostname/address"), optarg);
 384                         }
 385                         break;
 386                 case 's':                                                                       /* socket */
 387                         db_socket = optarg;
 388                         break;
 389                 case 'd':                                                                       /* database */
 390                         db = optarg;
 391                         break;
 392                 case 'l':
 393                         ssl = true;
 394                         break;
 395                 case 'C':
 396                         ca_cert = optarg;
 397                         break;
 398                 case 'a':
 399                         cert = optarg;
 400                         break;
 401                 case 'k':
 402                         key = optarg;
 403                         break;
 404                 case 'D':
 405                         ca_dir = optarg;
 406                         break;
 407                 case 'L':
 408                         ciphers = optarg;
 409                         break;
 410                 case 'u':                                                                       /* username */
 411                         db_user = optarg;
 412                         break;
 413                 case 'p':                                                                       /* authentication information: password */
 414                         db_pass = strdup(optarg);
 415
 416                         /* Delete the password from process list */
 417                         while (*optarg != '\0') {
 418                                 *optarg = 'X';
 419                                 optarg++;
 420                         }
 421                         break;
 422                 case 'f':                                                                       /* client options file */
 423                         opt_file = optarg;
 424                         break;
 425                 case 'g':                                                                       /* client options group */
 426                         opt_group = optarg;
 427                         break;
 428                 case 'P':                                                                       /* critical time threshold */
 429                         db_port = atoi (optarg);
 430                         break;
 431                 case 'S':
 432                         check_slave = 1;                                                        /* check-slave */
 433                         break;
 434                 case 'n':
 435                         ignore_auth = 1;                                                        /* ignore-auth */
 436                         break;
 437                 case 'w':
 438                         warning = optarg;
 439                         warning_time = strtod (warning, NULL);
 440                         break;
 441                 case 'c':
 442                         critical = optarg;
 443                         critical_time = strtod (critical, NULL);
 444                         break;
 445                 case 'V':                                                                       /* version */
 446                         print_revision (progname, NP_VERSION);
 447                         exit (STATE_UNKNOWN);
 448                 case 'h':                                                                       /* help */
 449                         print_help ();
 450                         exit (STATE_UNKNOWN);
 451                 case 'v':
 452                         verbose++;
 453                         break;
 454                 case '?':                                                                       /* help */
 455                         usage5 ();
 456                 }
 457         }
 458
 459         c = optind;
 460
 461         set_thresholds(&my_threshold, warning, critical);
 462
 463         while ( argc > c ) {
 464
 465                 if (db_host == NULL)
 466                         if (is_host (argv[c])) {
 467                                 db_host = argv[c++];
 468                         }
 469                         else {
 470                                 usage2 (_("Invalid hostname/address"), argv[c]);
 471                         }
 472                 else if (db_user == NULL)
 473                         db_user = argv[c++];
 474                 else if (db_pass == NULL)
 475                         db_pass = argv[c++];
 476                 else if (db == NULL)
 477                         db = argv[c++];
 478                 else if (is_intnonneg (argv[c]))
 479                         db_port = atoi (argv[c++]);
 480                 else
 481                         break;
 482         }
 483
 484         return validate_arguments ();
 485 }
 486
 487
 488 int
 489 validate_arguments (void)
 490 {
 491         if (db_user == NULL)
 492                 db_user = strdup("");
 493
 494         if (db_host == NULL)
 495                 db_host = strdup("");
 496
 497         if (db == NULL)
 498                 db = strdup("");
 499
 500         return OK;
 501 }
 502
 503
 504 void
 505 print_help (void)
 506 {
 507         char *myport;
 508         xasprintf (&myport, "%d", MYSQL_PORT);
 509
 510         print_revision (progname, NP_VERSION);
 511
 512         printf (_(COPYRIGHT), copyright, email);
 513
 514         printf ("%s\n", _("This program tests connections to a MySQL server"));
 515
 516   printf ("\n\n");
 517
 518         print_usage ();
 519
 520   printf (UT_HELP_VRSN);
 521         printf (UT_EXTRA_OPTS);
 522
 523   printf (UT_HOST_PORT, 'P', myport);
 524   printf (" %s\n", "-n, --ignore-auth");
 525   printf ("    %s\n", _("Ignore authentication failure and check for mysql connectivity only"));
 526
 527   printf (" %s\n", "-s, --socket=STRING");
 528   printf ("    %s\n", _("Use the specified socket (has no effect if -H is used)"));
 529
 530   printf (" %s\n", "-d, --database=STRING");
 531   printf ("    %s\n", _("Check database with indicated name"));
 532   printf (" %s\n", "-f, --file=STRING");
 533   printf ("    %s\n", _("Read from the specified client options file"));
 534   printf (" %s\n", "-g, --group=STRING");
 535   printf ("    %s\n", _("Use a client options group"));
 536   printf (" %s\n", "-u, --username=STRING");
 537   printf ("    %s\n", _("Connect using the indicated username"));
 538   printf (" %s\n", "-p, --password=STRING");
 539   printf ("    %s\n", _("Use the indicated password to authenticate the connection"));
 540   printf ("    ==> %s <==\n", _("IMPORTANT: THIS FORM OF AUTHENTICATION IS NOT SECURE!!!"));
 541   printf ("    %s\n", _("Your clear-text password could be visible as a process table entry"));
 542   printf (" %s\n", "-S, --check-slave");
 543   printf ("    %s\n", _("Check if the slave thread is running properly."));
 544   printf (" %s\n", "-w, --warning");
 545   printf ("    %s\n", _("Exit with WARNING status if slave server is more than INTEGER seconds"));
 546   printf ("    %s\n", _("behind master"));
 547   printf (" %s\n", "-c, --critical");
 548   printf ("    %s\n", _("Exit with CRITICAL status if slave server is more then INTEGER seconds"));
 549   printf ("    %s\n", _("behind master"));
 550   printf (" %s\n", "-l, --ssl");
 551   printf ("    %s\n", _("Use ssl encryptation"));
 552   printf (" %s\n", "-C, --ca-cert=STRING");
 553   printf ("    %s\n", _("Path to CA signing the cert"));
 554   printf (" %s\n", "-a, --cert=STRING");
 555   printf ("    %s\n", _("Path to SSL certificate"));
 556   printf (" %s\n", "-k, --key=STRING");
 557   printf ("    %s\n", _("Path to private SSL key"));
 558   printf (" %s\n", "-D, --ca-dir=STRING");
 559   printf ("    %s\n", _("Path to CA directory"));
 560   printf (" %s\n", "-L, --ciphers=STRING");
 561   printf ("    %s\n", _("List of valid SSL ciphers"));
 562
 563
 564   printf ("\n");
 565   printf (" %s\n", _("There are no required arguments. By default, the local database is checked"));
 566   printf (" %s\n", _("using the default unix socket. You can force TCP on localhost by using an"));
 567   printf (" %s\n", _("IP address or FQDN ('localhost' will use the socket as well)."));
 568
 569         printf ("\n");
 570         printf ("%s\n", _("Notes:"));
 571         printf (" %s\n", _("You must specify -p with an empty string to force an empty password,"));
 572         printf (" %s\n", _("overriding any my.cnf settings."));
 573
 574         printf (UT_SUPPORT);
 575 }
 576
 577
 578 void
 579 print_usage (void)
 580 {
 581         printf ("%s\n", _("Usage:"));
 582   printf (" %s [-d database] [-H host] [-P port] [-s socket]\n",progname);
 583   printf ("       [-u user] [-p password] [-S] [-l] [-a cert] [-k key]\n");
 584   printf ("       [-C ca-cert] [-D ca-dir] [-L ciphers] [-f optfile] [-g group]\n");
 585 }