Tuesday May 28, 2013

The Dangers in Changing Default Character Sets on Tables

The ALTER TABLE command can change the default character set for the table in two ways. The DEFAULT CHARSET method changes the default on the table, but leaves the character columns in the older character set. The CONVERT method converts the character columns to the new character set and sets the default on the table.[Read More]

Wednesday Oct 10, 2012

MySQL Syslog Audit Plugin

This post shows the construction process of the Syslog Audit plugin that was presented at MySQL Connect 2012. It is based on an environment that has the appropriate development tools enabled including gcc,g++ and cmake. It also assumes you have downloaded the MySQL source code (5.5.16 or higher) and have compiled and installed the system into the /usr/local/mysql directory ready for use. 

The information provided below is designed to show the different components that make up a plugin, and specifically an audit type plugin, and how it comes together to be used within the MySQL service. The MySQL Reference Manual contains information regarding the plugin API and how it can be used, so please refer there for more detailed information. The code in this post is designed to give the simplest information necessary, so handling every return code, managing race conditions etc is not part of this example code.

Let's start by looking at the most basic implementation of our plugin code as seen below:

   Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved.
   Author:  Jonathon Coombes
   Licence: GPL
   Description: An auditing plugin that logs to syslog and
                can adjust the loglevel via the system variables.

#include <stdio.h>
#include <string.h>
#include <mysql/plugin_audit.h>
#include <syslog.h>

There is a commented header detailing copyright/licencing and meta-data information and then the include headers. The two important include statements for our plugin are the syslog.h plugin, which gives us the structures for syslog, and the plugin_audit.h include which has details regarding the audit specific plugin api. Note that we do not need to include the general plugin header plugin.h, as this is done within the plugin_audit.h file already.

To implement our plugin within the current implementation we need to add it into our source code and compile.

> cd /usr/local/src/mysql-5.5.28/plugin
> mkdir audit_syslog
> cd audit_syslog

A simple CMakeLists.txt file is created to manage the plugin compilation:
	MYSQL_ADD_PLUGIN(audit_syslog audit_syslog.cc

Run the cmake  command at the top level of the source and then you can compile the plugin using the 'make' command. This results in a compiled audit_syslog.so library, but currently it is not much use to MySQL as there is no level of api defined to communicate with the MySQL service.

Now we need to define the general plugin structure that enables MySQL to recognise the library as a plugin and be able to install/uninstall it and have it show up in the system. The structure is defined in the plugin.h file in the MySQL source code. 

   Plugin library descriptor
  MYSQL_AUDIT_PLUGIN,           /* plugin type                     */
  &audit_syslog_descriptor,     /* descriptor handle               */
  "audit_syslog",               /* plugin name                     */
  "Author Name",                /* author                          */
  "Simple Syslog Audit",        /* description                     */
  PLUGIN_LICENSE_GPL,           /* licence                         */
  audit_syslog_init,            /* init function                   */
  audit_syslog_deinit,          /* deinit function                 */
  0x0001,                       /* plugin version                  */
  NULL,                         /* status variables                */
  NULL,                         /* system variables                */
  NULL,                         /* no reserves                     */
  0,                            /* no flags                        */

The general plugin descriptor above is standard for all plugin types in MySQL.

The plugin type is defined along with the init/deinit functions and interface methods into the system for sharing information, and various other metadata information. The descriptors have an internally recognised version number so that plugins can be matched against the api on the running server.

The other details are usually related to the type-specific methods and structures to implement the plugin. Each plugin has a type-specific descriptor as well which details how the plugin is implemented for the specific purpose of that plugin type.

   Plugin type-specific descriptor
static struct st_mysql_audit audit_syslog_descriptor=
  MYSQL_AUDIT_INTERFACE_VERSION,                        /* interface version    */
  NULL,                                                 /* release_thd function */
  audit_syslog_notify,                                  /* notify function      */
                    MYSQL_AUDIT_CONNECTION_CLASSMASK }  /* class mask           */

In this particular case, the release_thd function has not been defined as it is not required. The important method for auditing is the notify function which is activated when an event occurs on the system. The notify function is designed to activate on an event and the implementation will determine how it is handled. For the audit_syslog plugin, the use of the syslog feature sends all events to the syslog for recording. The class mask allows us to determine what type of events are being seen by the notify function. There are currently two major types of event:

1. General Events: This includes general logging, errors, status and result type events. This is the main one for tracking the queries and operations on the database.

2. Connection Events: This group is based around user logins. It monitors connections and disconnections, but also if somebody changes user while connected.

With most audit plugins, the principle behind the plugin is to track changes to the system over time and counters can be an important part of this process. The next step is to define and initialise the counters that are used to track the events in the service. There are 3 counters defined in total for our plugin - the # of general events, the # of connection events and the total number of events. 

static volatile int total_number_of_calls;
/* Count MYSQL_AUDIT_GENERAL_CLASS event instances */
static volatile int number_of_calls_general;
/* Count MYSQL_AUDIT_CONNECTION_CLASS event instances */
static volatile int number_of_calls_connection;

The init and deinit functions for the plugin are there to be called when the plugin is activated and when it is terminated. These offer the best option to initialise the counters for our plugin:

   Initialize the plugin at server start or plugin installation.
static int audit_syslog_init(void *arg __attribute__((unused)))
    total_number_of_calls= 0;
    number_of_calls_general= 0;
    number_of_calls_connection= 0;

The init function does a call to openlog to initialise the syslog functionality. The parameters are the service to log under ("mysql_audit" in this case), the syslog flags and the facility for the logging. Then each of the counters are initialised to zero and a success is returned. If the init function is not defined, it will return success by default.

   Terminate the plugin at server shutdown or plugin deinstallation.
static int audit_syslog_deinit(void *arg __attribute__((unused)))

The deinit function will simply close our syslog connection and return success. Note that the syslog functionality is part of the glibc libraries and does not require any external factors.  The function names are what we define in the general plugin structure, so these have to match otherwise there will be errors.

The next step is to implement the event notifier function that was defined in the type specific descriptor (audit_syslog_descriptor) which is audit_syslog_notify.

   Event notifier function
static void audit_syslog_notify(MYSQL_THD thd __attribute__((unused)),
                              unsigned int event_class,
                              const void *event)
  if (event_class == MYSQL_AUDIT_GENERAL_CLASS)         
    const struct mysql_event_general *event_general=    
      (const struct mysql_event_general *) event; 
    syslog(audit_loglevel,"%lu: User: %s  Command: %s  Query: %s\n",
            event_general->general_thread_id, event_general->general_user,
            event_general->general_command, event_general->general_query );
  else if (event_class == MYSQL_AUDIT_CONNECTION_CLASS)
    const struct mysql_event_connection *event_connection=
      (const struct mysql_event_connection *) event;
    syslog(audit_loglevel,"%lu: User: %s@%s[%s]  Event: %d  Status: %d\n",
            event_connection->thread_id, event_connection->user, event_connection->host,
            event_connection->ip, event_connection->event_subclass, event_connection->status );

In the case of an event, the notifier function is called. The first step is to increment the total number of events that have occurred in our database.
The event argument is then cast into the appropriate event structure depending on the class type, of general event or connection event. The event type counters are incremented and details are sent via the syslog() function out to the system log. There are going to be different line formats and information returned since the general events have different data compared to the connection events, even though some of the details overlap, for example, user, thread id, host etc.

On compiling the code now, there should be no errors and the resulting audit_syslog.so can be loaded into the server and ready to use. Log into the server and type:

mysql> INSTALL PLUGIN audit_syslog SONAME 'audit_syslog.so';

This will install the plugin and will start updating the syslog immediately. Note that the audit plugin attaches to the immediate thread and cannot be uninstalled while that thread is active. This means that you cannot run the UNISTALL command until you log into a different connection (thread) on the server.

Once the plugin is loaded, the system log will show output such as the following:

Oct  8 15:33:21 machine mysql_audit:[8337]: 87: User: root[root] @ localhost []  Command: (null)  Query: INSTALL PLUGIN audit_syslog SONAME 'audit_syslog.so'
Oct  8 15:33:21 machine mysql_audit:[8337]: 87: User: root[root] @ localhost []  Command: Query  Query: INSTALL PLUGIN audit_syslog SONAME 'audit_syslog.so'
Oct  8 15:33:40 machine mysql_audit:[8337]: 87: User: root[root] @ localhost []  Command: (null)  Query: show tables
Oct  8 15:33:40 machine mysql_audit:[8337]: 87: User: root[root] @ localhost []  Command: Query  Query: show tables
Oct  8 15:33:43 machine mysql_audit:[8337]: 87: User: root[root] @ localhost []  Command: (null)  Query: select * from t1
Oct  8 15:33:43 machine mysql_audit:[8337]: 87: User: root[root] @ localhost []  Command: Query  Query: select * from t1

It appears that two of each event is being shown, but in actuality, these are two separate event types - the result event and the status event. This could be refined further by changing the audit_syslog_notify function to handle the different event sub-types in a different manner. 

So far, it seems that the logging is working with events showing up in the syslog output. The issue now is that the counters created earlier to track the number of events by type are not accessible when the plugin is being run. Instead there needs to be a way to expose the plugin specific information to the service and vice versa. This could be done via the information_schema plugin api, but for something as simple as counters, the obvious choice is the system status variables. This is done using the standard structure and the declaration:

   Plugin status variables for SHOW STATUS
static struct st_mysql_show_var audit_syslog_status[]=
  { "Audit_syslog_total_calls",
    (char *) &total_number_of_calls,
    SHOW_INT },
  { "Audit_syslog_general_events",
    (char *) &number_of_calls_general,
    SHOW_INT },
  { "Audit_syslog_connection_events",
    (char *) &number_of_calls_connection,
    SHOW_INT },
  { 0, 0, SHOW_INT }

The structure is simply the name that will be displaying in the mysql service, the address of the associated variables, and the data type being used for the counter. It is finished with a blank structure to show that there are no more variables. Remember that status variables may have the same name for variables from other plugin, so it is considered appropriate to add the plugin name at the start of the status variable name to avoid confusion. Looking at the status variables in the mysql client shows something like the following:

mysql> show global status like "audit%";
| Variable_name                  | Value |
| Audit_syslog_connection_events | 1     |
| Audit_syslog_general_events    | 2     |
| Audit_syslog_total_calls       | 3     |
3 rows in set (0.00 sec)

The final connectivity piece for the plugin is to allow the interactive change of the logging level between the plugin and the system. This requires the ability to send changes via the mysql service through to the plugin. This is done using the system variables interface and defining a single variable to keep track of the active logging level for the facility.

   Plugin system variables for SHOW VARIABLES
static MYSQL_SYSVAR_STR(loglevel, audit_loglevel,
                        "User can specify the log level for auditing",
                        audit_loglevel_check, audit_loglevel_update, "LOG_NOTICE");

static struct st_mysql_sys_var* audit_syslog_sysvars[] = {

So now the system variable 'loglevel' is defined for the plugin and associated to the global variable 'audit_loglevel'. The check or validation function is defined to make sure that no garbage values are attempted in the update of the variable. The update function is used to save the new value to the variable. Note that the audit_syslog_sysvars structure is defined in the general plugin descriptor to associate the link between the plugin and the system and how much they interact. Next comes the implementation of the validation function and the update function for the system variable. It is worth noting that if you have a simple numeric such as integers for the variable types, the validate function is often not required as MySQL will handle the automatic check and validation of simple types.

/* longest valid value */

/* hold the valid values */
static const char *possible_modes[]= {
static int audit_loglevel_check(
    THD*                        thd,    /*!< in: thread handle */
    struct st_mysql_sys_var*    var,    /*!< in: pointer to system
                                        variable */
    void*                       save,   /*!< out: immediate result
                                        for update function */
    struct st_mysql_value*      value)  /*!< in: incoming string */
    char buff[MAX_LOGLEVEL_SIZE];
    const char *str;
    const char **found;
    int length;
    length= sizeof(buff);
    if (!(str= value->val_str(value, buff, &length)))
        return 1;
        We need to return a pointer to a locally allocated value in "save".
        Here we pick to search for the supplied value in an global array of
        constant strings and return a pointer to one of them.
        The other possiblity is to use the thd_alloc() function to allocate
        a thread local buffer instead of the global constants.
    for (found= possible_modes; *found; found++)
        if (!strcmp(*found, str))
            *(const char**)save= *found;
            return 0;
    return 1;

The validation function is simply to take the value being passed in via the SET GLOBAL VARIABLE command and check if it is one of the pre-defined values allowed  in our possible_values array. If it is found to be valid, then the value is assigned to the save variable ready for passing through to the update function.

static void audit_loglevel_update(
    THD*                        thd,        /*!< in: thread handle */
    struct st_mysql_sys_var*    var,        /*!< in: system variable
                                            being altered */
    void*                       var_ptr,    /*!< out: pointer to
                                            dynamic variable */
    const void*                 save)       /*!< in: pointer to
                                            temporary storage */
    /* assign the new value so that the server can read it */
    *(char **) var_ptr= *(char **) save;
    /* assign the new value to the internal variable */
    audit_loglevel= *(char **) save;

Since all the validation has been done already, the update function is quite simple for this plugin. The first part is to update the system variable pointer so that the server can read the value. The second part is to update our own global plugin variable for tracking the value. Notice that the save variable is passed in as a void type to allow handling of various data types, so it must be cast to the appropriate data type when assigning it to the variables.

Looking at how the latest changes affect the usage of the plugin and the interaction within the server shows:

mysql> show global variables like "audit%";
| Variable_name         | Value      |
| audit_syslog_loglevel | LOG_NOTICE |
1 row in set (0.00 sec)

mysql> set global audit_syslog_loglevel="LOG_ERROR";
Query OK, 0 rows affected (0.00 sec)

mysql> show global status like "audit%";
| Variable_name                  | Value |
| Audit_syslog_connection_events | 1     |
| Audit_syslog_general_events    | 11    |
| Audit_syslog_total_calls       | 12    |
3 rows in set (0.00 sec)

mysql> show global variables like "audit%";
| Variable_name         | Value     |
| audit_syslog_loglevel | LOG_ERROR |
1 row in set (0.00 sec)

So now we have a plugin that will audit the events on the system and log the details to the system log. It allows for interaction to see the number of different events within the server details and provides a mechanism to change the logging level interactively via the standard system methods of the SET command.

A more complex auditing plugin may have more detailed code, but each of the above areas is what will be involved and simply expanded on to add more functionality. With the above skeleton code, it is now possible to create your own audit plugins to implement your own auditing requirements. If, however, you are not of the coding persuasion, then you could always consider the option of the MySQL Enterprise Audit plugin that is available to purchase.

Sunday Aug 26, 2012

Speaking at MySQL Connect 2012

I'm Speaking at MySQL Connect 2012

At the end of September, the MySQL Connect 2012 conference will be held as part of Oracle OpenWorld in San Francisco. MySQL Connect is a two day event that allows attendees to focus on MySQL at a technical depth with presentations and interaction with many of the MySQL developers, engineers and other knowledgeable staff. There is also a range a international speakers to give broader knowledge to the presentations.

I am presenting a Hands-On Lab on Sunday 30th September 16:15 - 17:15 entitled HOL10474 - MySQL Security: Authentication and Auditing. The sessions goes through an introduction to the plugin API and how it can help expand the capabilities of MySQL. Since it is a hands-on lab, attendees will use practical examples of implementing simple plugins to get a start in developing their own plugins. These plugin examples are based around implementing PAM authentication and how it can be utilized to offer greater security for the MySQL Server. Once the authentication has been tested, a method to monitor it will be implemented using the auditing API and logging different events as they happen in the service.

There is a total of 78 sessions at MySQL Connect 2012 with a great range of speakers. Hope to see you there!

Monday Nov 02, 2009

Agents, Hosts and Instances, Oh My!

What defines instances for the MySQL Enterprise Monitoring (MEM) software and how do they relate to either other and the workings of MEM? [Read More]

Tuesday Jun 23, 2009

Partial Binary Log Recovery

I came across a situation recently where I was asked if it was possible to edit a binary log to remove a part of it to restore onto a slave server. Now the choice of doing something like a hexedit did not seem appealing, and the more experienced might suggest that it is simply a matter of using  mysqlbinlog with the --start-position and/or --stop-position options. However, the problem had arisen that required the binary log to played through the replication process onto the slave based on specific options in MySQL cluster, so using an SQL dump from the binary log was of no use.

Initially this may seem like a daunting task where you will have to find some specialist tool or delve into the deep recesses of the binary log format, but a much simpler solution was found. The replication process allows the slave to be started up to a specific point in the log files. The command is the START SLAVE UNTIL... statement as seen in the manual at:


This offers the ability to start up the slave or the io thread to some particular part of a binary log that is specified. Whether you use the io thread to get the specific details from the master binary log, or you use the SQL thread to limit the SQL statements that are executed on the slave.

All seems well with this approach until you find out that you need a section removed from the binary log not the end of the log. The START SLAVE UNTIL statement only allows for an ending point, there is no equivalent START SLAVE FROM... statement to specify a starting point. So how can we utilise the START SLAVE UNTIL... command in the replication process to allow us to restore a binary log, leaving out a window of the log statements?

The solution is to combine the START SLAVE UNTIL statement with the CHANGE MASTER TO statement to allow us to define a starting and stopping point in the replication process. Here is a simple outline of how it would work, assuming the master is still replicating and the slave is currently stopped, or using --skip-slave-start:

Start the slave using START SLAVE UNTIL MASTER_LOG_FILE='binary.00035',MASTER_LOG_POS=2500;

This will give us the initial replication of statements that we want up to the start of the window to be removed from the log.

The replication then stops at this position, we can then RESET SLAVE; CHANGE MASTER TO MASTER_LOG_FILE='binary.00035',MASTER_LOG_POS=3500;

This defines the starting point for our next section to replicate, or in another term it is the end of the window being removed from the log file.

We then start the replication process again, either with START SLAVE; if there is no other window, or we can continue the process all over again by using the START SLAVE UNTIL.. again.

This simple process will allow us to restore sections of the binary log without having to resort to any specialist tools or hexeditors of any sort. Again, this is not usually required when you can simply use the mysqlbinlog command with the --start-position and --stop-position options, but if you ever find yourself in the situation where you have to use replication and restore only part of the binary log, this can be an option.

Monday May 25, 2009

MEM and HTTP Proxy Not Compatible

A bug in libcurl affects how the heartbeat function of MySQL Enterprise Monitor works by sending it to an external website, often www.agent.com.[Read More]

Sunday Apr 05, 2009

MEM with missing Agents?

You could set this up simply by adding the no_proxy option to the .curlrc file of the MEM  user to make the change permanent. Remember that to turn off the proxy for all connections, you would use:

$ setenv  no_proxy  '\*'

[Read More]

Jonathon Coombes


« February 2017