vADC Docs

Writing a custom Stingray Health Monitor in C

by sphillips on ‎03-13-2013 06:50 AM (1,980 Views)

k_and_r_and_mysql.jpgStingray provides a module to help you write custom monitors in Perl. However Perl is not the only way to write a custom monitor - you can use any programming or scripting language that is supported on the system hosting the Stingray software. And if you want to avoid installing a large number of libraries, CPAN modules (or other scripting languages) you can write your custom monitors in C.

In this article I walk-through a few examples of simple custom monitors written in C that query a MySQL database.  Hopefully you will be able to use the example code as the basis of your own monitors.

MySQL

The purpose of this article is to document using C to write a custom monitor. I chose to write monitors for MySQL because it is a commonly used server with a well-known (and simple) C API. Hopefully the concepts (and some of the code here) will be transferable to other custom monitors. And, of course, someone who is looking to provide a more thorough monitor for their MySQL pool should have a good idea of where to start after reading this.

I am using MySQL v4.1 (ver 14.7). Depending on backwards compatibility, the code samples should work with more recent releases of MySQL.

A ping monitor

story_of_ping.jpgLet's start with a MySQL ping monitor.

mysql_ping checks whether the connection to the server is working. If the connection has gone down, an automatic reconnection is attempted. It should be a little more thorough than the standard built-in ping monitor plus it has the advantage of keeping the connection to the server open.

mysql_ping.c


#include <stdlib.h>


#include <stdio.h>


#include <mysql/mysql.h>



MYSQL mysql;



int main(void)


{


   mysql_init(&mysql);



   mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,MONITORNAME);



   /* connect */


   if (!mysql_real_connect(&mysql,HOST,USER,PASSWD,DATABASE,0,NULL,0)) {


       fprintf(stderr, "Failed to connect to database: Error: %s\n",


             mysql_error(&mysql));


       exit(EXIT_FAILURE);


   }


  


   /* try a ping */


   if (mysql_ping(&mysql)) {


       fprintf(stderr, "Cannot ping database: Error: %s\n",


             mysql_error(&mysql));


       exit(EXIT_FAILURE);


   }



   exit(EXIT_SUCCESS);


}


Compiling the monitor

A zip archive of all the c code and Makefiles to build these monitors is included at the end of this article.

The database details are hard-coded into this monitor so you will need to change them before you build it. Edit the Makefile and enter your hostname, username password and the name of the database you want to ping.

Type make. If all is well you should now have a monitor called mysql_ping.

Problems?

If it didn't build you will probably need to install the mysqlclient-dev libraries. On my Debian/Ubuntu system, this was sufficient:


$ sudo apt-get install libmysqlclient-dev


Of course, your milage may differ. Once you've istalled them, try typing mysql_config --cflags. This should return the include directory for the MySQL client library.

Testing the monitor

Test the monitor by running it from the shell:


$ ./mysql_ping


If all is well you should see nothing! If there is any problem connecting with the database (or if any of your settings are wrong) you should get an appropriate error message.

Installing the monitor in Stingray

Copy the built monitor to your Stingray and place it in your monitors directory $ZEUSHOME/zxtm/monitors. Ensure it has the correct user, group and executable permissions.  You can also upload it via the Extra Files section of the catalog.

In the Stingray admin interface, Create a virtual server and pool to manage MySQL traffic. MySQL is a generic server-first protocol that uses port 3036.

Now you can create your custom monitor. In the Monitors Catalog create a new external program monitor. Enter mysql_ping in the Program box.

Go to your MySQL pool and set add the monitor to it. (It's easy to forget this step.)

If all is well nothing should happen. To see something more interesting, try stopping the MySQL server (or recompiling the monitor with some bogus settings). You can also edit the settings and turn verbose mode on. You should then see some output from the monitor.

A more sophisticated monitor

A MySQL ping monitor is only midly more useful than a generic ping monitor. It would be much more useful if we could connect to the server, run a query and check the result.

This monitor does exactly that. To demonstrate this I have created a database with a simple table that stores name-value pairs.

I have a table called vars that looks like this:


+-------+-------------+------+-----+---------+-------+


| Field | Type        | Null | Key | Default | Extra |


+-------+-------------+------+-----+---------+-------+


| name  | varchar(32) |      |     |         |       |


| value | tinytext    | YES  |     | NULL    |       |


+-------+-------------+------+-----+---------+-------+


... and I have an entry in vars like this:


+-------------------+-------+


| name              | value |


+-------------------+-------+


| database_is_happy | yes   |


+-------------------+-------+


The monitor queries this database table to check that this value is correct. i.e


SELECT value FROM vars where name='database_is_happy'"


If it can't connect, ping or query the database, or if value returned by the query isn't 'yes' the monitor fails.


#include <stdlib.h>


#include <stdio.h>


#include <string.h>


#include <mysql/mysql.h>



MYSQL mysql;


MYSQL_RES *res;


MYSQL_ROW row;



int main(void)


{


   mysql_init(&mysql);



   mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,MONITORNAME);



   /* connect */


   if (!mysql_real_connect(&mysql,HOST,USER,PASSWD,DATABASE,0,NULL,0)) {


       fprintf(stderr, "Cannot connect to database: Error: %s.\n",


             mysql_error(&mysql));


       exit(EXIT_FAILURE);


   }


  


   /* try a ping */


   if (mysql_ping(&mysql)) {


       fprintf(stderr, "Cannot ping database: Error: %s.\n",


             mysql_error(&mysql));


       exit(EXIT_FAILURE);


   }



   /* try a query */


   if (mysql_query(&mysql,"SELECT value FROM vars where name='database_is_happy'"))  {


       fprintf(stderr, "Cannot query database: Error: %s.\n",


             mysql_error(&mysql));


       exit(EXIT_FAILURE);


  }



   /* get the result */


   if (!(res = mysql_store_result(&mysql)))


   {


       fprintf(stderr, "Cannot store database result: Error: %s.\n",


               mysql_error(&mysql));


       exit(EXIT_FAILURE);


   }



   /* check result */  


   row = mysql_fetch_row(res);



   if(strcmp(row[0],"yes")) {


     fprintf(stderr, "Unexpected data (\'%s\') returned by database.\n",


             row[0]);


     exit(EXIT_FAILURE);


   }



   exit(EXIT_SUCCESS);


}


Now if you change the entry in the table to 'no' you should see your pool fail. And then if you change it back everything should go green once again.

Full example with optional parameters

It would be even more useful if we didn't have to compile our settings into the monitor. Stingray will pass arguments to your monitor, plus it will provide it with the ipaddress, port number to test (along with other options).

In this example I have used getopt() to parse the options and use them to decide which MySQL server to connect to. Plus the query and expected result can be passed too.


#include <stdlib.h>


#include <stdio.h>


#include <string.h>


#include <getopt.h>


#include <mysql/mysql.h>



/* options sent by Stingray */


char* ipaddr = "";



/* load defaults from Makefile */


/* can be overrided by options */


char* host = HOST;


char* user = USER;


char* passwd = PASSWD;


char* database = DATABASE;


char* query = QUERY;


char* result = RESULT;



void parseArguments(int argc, char **argv)


{


  int c;


 


  while (1) {


   


    static struct option long_options[] =


    {


       {"verbose",       no_argument,        0,  'v'},


       {"ipaddr",        required_argument,  0,  'i'},


       {"port",          required_argument,  0,  'o'},


       {"failures_left", required_argument,  0,  'f'},


       {"host",          required_argument,  0,  'h'},


       {"user",          required_argument,  0,  'u'},


       {"passwd",        required_argument,  0,  'p'},


       {"database",      required_argument,  0,  'd'},


       {"query",         required_argument,  0,  'q'},


       {"result",        required_argument,  0,  'r'},


       {0, 0, 0, 0}


    };


   


    /* getopt_long stores the option index here. */


    int option_index = 0;


   


    c = getopt_long (argc, argv, "h:uSmiley Tongue:d:q:r:",


      long_options, &option_index);


   


    /* Detect the end of the options. */


    if (c == -1)


      break;


   


    switch (c) {


   


    case 'o':


    case 'f':


    case 'v':


      /* ignore */


      break;




    case 'i':


      ipaddr = optarg;


      break;




    case 'h':


      host = optarg;


      break;


     


    case 'u':


      user = optarg;


      break;


     


    case 'p':


      passwd = optarg;


      break;


     


    case 'd':


      database = optarg;


      break;


     


    case 'q':


      query = optarg;


      break;


     


    case 'r':


      result = optarg;


      break;


     


    default:     


      exit (EXIT_FAILURE);


    }


  }


}



MYSQL mysql;


MYSQL_RES *res;


MYSQL_ROW row;



int main(int argc, char **argv)


{


  parseArguments(argc,argv);


 


  if (*ipaddr) /* ipaddr overrides host when live */


    host = ipaddr;



  mysql_init(&mysql);


 


  mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,MONITORNAME);


 


  /* connect */


  if (!mysql_real_connect(&mysql,host,user,passwd,database,0,NULL,0)) {


    fprintf(stderr, "Cannot connect to database: Error: %s.\n",


     mysql_error(&mysql));


    exit(EXIT_FAILURE);


  }


 


  /* try a ping -- ping zero if ok */


  if (mysql_ping(&mysql)) {


    fprintf(stderr, "Cannot ping database: Error: %s.\n",


     mysql_error(&mysql));


    exit(EXIT_FAILURE);


  }


 


  /* try a query */


  if (mysql_query(&mysql,query))  {


    fprintf(stderr, "Cannot query database: Error: %s.\n",


     mysql_error(&mysql));


    exit(EXIT_FAILURE);


  }


 


  /* get the result */


  if (!(res = mysql_store_result(&mysql))) {


    fprintf(stderr, "Cannot store database result: Error: %s.\n",


     mysql_error(&mysql));


    exit(EXIT_FAILURE);


  }


 


  /* check result */  


  row = mysql_fetch_row(res);


 


  if(strcmp(row[0],result)) {


    fprintf(stderr, "Unexpected data (\'%s\') returned by database.\n",


     row[0]);


    exit(EXIT_FAILURE);


  }


 


  exit(EXIT_SUCCESS);


}


To test it, create an external program monitor with these arguments:-

mysql_monitor_args.png

... and these settings

mysql_monitor_settings.png

(obviously you should enter the full query: select value from vars where name='database_is_happy')

You can, of course, pass: username, password, hostname and database this way too.

Static Builds

To avoid installing the mysql client libraries onto your Stingray (or if you are using a Stingray Virtual Appliance) you will need to make static builds of these monitors.

The Make files included have a make static option.

However, please note that this just blindly builds everything statically including libc, libz, etc which are already present on your Stingray. This may not be the best idea for your setup - especially if you can build on an identical architecture - and may produce linker warnings as recent versions of libc complain, eg:


/usr/lib/libmysqlclient.a(libmysql.o)(.text+0xb2): In function `mysql_server_init':: warning: Using 'getservbyname' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking


For a production system you are better to only statically link in any libraries not present on the Stingray (in this case just libmysqlclient) and dynamically link the others. But, if you have to compile for a 32 bit chip and run on a 64 bit chip (which is what I had to do to test this monitor) you will need to statically link everything (and the linker may complain that you still need to copy the 32 bit libc to the Stingray).

If you are using Stingray software on your own machine then you are, of course, free to install any library you wish which does away with all this faff.


This article was originally written by Sam Phillips in February 2006


Comments
by ebrandsberg
on ‎06-05-2014 02:01 PM

Here is an alternative and extremely simple mysql monitor using Bash instead of C:

#!/bin/bash

# script to use the mysql cli to test the health of a mysql database server

# arguments for the script should be "user" and "password" to enable loging in

# only permissions enough to do "select 1" are needed here

# please insure the command "mysql" is installed and in the standard path

# Process the health monitor arguments

args=( $@ );

for (( i=0; $i < $# ; i++ ))

do

  [[ "${args[$i]}" =~ --ipaddr= ]] && ipaddr=${args[$i]#*=} && continue

  [[ "${args[$i]}" =~ --port= ]]  && port=${args[$i]#*=} && continue

  [[ "${args[$i]}" =~ --node= ]]  && node=${args[$i]#*=} && continue

  [[ "${args[$i]}" =~ --user= ]]  && user=${args[$i]#*=} && continue

  [[ "${args[$i]}" =~ --password= ]]  && password=${args[$i]#*=} && continue

  [[ "${args[$i]}" =~ --failures_left= ]] && failures_left=${args[$i]#*=} && continue

done

# issue the command, timing out in one second

mysql --host=$ipaddr --user=$user --password=$password --port=$port --connect-timeout=1 --execute="select 1"

# reply with the exit status of mysql--a value of 0 flags that the monitor was a success, anything else

# is a failure

exit $?