Writing a custom monitor for MySQL in C

ZXTM 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. 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. To get you started, I've included a zip archive of all the C code and Makefiles to build these monitors at the end of this article.

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). I beleive the API has changed a little in v5.0 so please check the MySQL v5.0 C API Documentation

A ping monitor

Let'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>_01_01
#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.

Have you installed the MySQL client libraries?
If it didn't build you will probably need to install the mysqlclient-dev libraries. On my Debian system I just typed sudo apt-get install libmysqlclient14-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 ZXTM

Copy the built monitor to your ZXTM and place it in your monitors directory $ZEUSHOME/zxtm/monitors. Ensure it has the correct user, group and executable permissions.

In the ZXTM 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. ZXTM 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 zxtm */
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:u:p: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:-

and these settings

(obviously you should enter the full query: select value from vars where name='database_is_happy' - we must make this box bigger *cough*)

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

Static Builds

To avoid installing the mysql client libraries onto your ZXTM (or if you are using a ZXTM 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 ZXTM. 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 ZXTM (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 ZXTM).

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

Source Code
Full source code with Makefiles can be found in this Zip Archive.

Sambeau [Zeus Dev Team] 27 February 2006