Inspect In-Flight MySQL Queries: Tungsten Connector Audit Logging to the Rescue

Introduction

Sometimes you really, really want to know what a MySQL client is asking of the MySQL server.

As of Tungsten Clustering v7.0.0, you may now enable Connector Audit Logging, which captures data transferred between client applications and MySQL servers to a file, database or socket.

This blog post will walk you through the feature and how to enable, configure and disable Tungsten Connector Audit Logging.

The Brief

The audit logging configuration is done directly in the tungsten-connector/conf/log4j.properties file by adding or editing the parameters explained below.

By default, audit logging will be output to the tungsten-connector/log/connector-audit.log file.

Changes to this configuration file do NOT require a full restart of the Tungsten Connector! Simply executing the `connector reconfigure` command will reload the log4j configuration and apply your changes immediately while the Connector remains running.

What Information Can Be Viewed?

Five categories of information can be displayed: ClientRequests, ServerResponses, AppConnections, Commands and Perf.

Each category gets its own line in the configuration file, allowing granular control of what is displayed.

The Commands and Perf loggers are only available when the Connector is running in Proxy Mode.

The 5 categories are defined below:

• ClientRequests - client application sends a MySQL request (select, insert, update, …) to the server. Compatible with both bridge and proxy mode, full packet text will have to be printed in order to see the actual request.
• ServerResponses - MySQL server returns a result in response to a client request. Compatible with both bridge and proxy mode, full packet text will have to be printed in order to see the actual responses.
• AppConnections - client application connects or disconnects to/from the Connector. Compatible with both bridge and proxy mode, will print one log entry per connection and one per disconnection.
• Commands (proxy mode only) - Will print each "mysql command" that is sent by the application to the connector. Commands include not only regular COM_QUERY and COM_STMT_xxx used for executing requests and preparing statements, but also more specific commands such as COM_PING, COM_SLEEP, or COM_INIT_DB and COM_CHANGE_USER to switch user or schema. Full list of commands is available here.
• Perf (proxy mode only) - When set to info, this logger will print the round trip time to execute individual MySQL commands (see above), as well as the time taken to connect to underlying data source. A typical use of this logger is to track individual requests execution time.

Getting Started With Connector Audit Logging

The regular tungsten-connector/conf/log4j.properties file contains entries for audit logging, with everything disabled by default:

shell> vi tungsten-connector/conf/log4j.properties
logger.ClientRequests.level=OFF
logger.ServerResponses.level=OFF
logger.AppConnections.level=OFF
logger.Commands.level=OFF
logger.Perf.level=OFF

To ENABLE the audit logging of a category, set the desired logger line(s) to INFO, then run `connector reconfigure`, for example:

shell> vi tungsten-connector/conf/log4j.properties
logger.ClientRequests.level=INFO
logger.ServerResponses.level=OFF
logger.AppConnections.level=OFF
logger.Commands.level=OFF
logger.Perf.level=OFF

shell> connector reconfigure
Connector session established, encryption=true, authentication=true
Starting Connector reconfiguration...
Connector successfully reconfigured. Updated server threads count=2

To DISABLE the audit logging of a category, set the desired logger line(s) to OFF, then run `connector reconfigure`.

Customizing the Audit Logging Output

The format of individual log entries (i.e. each line printed) can be adjusted by adding a new line to customize the output, for example the line shown below will display the client IP, client port, datasource, user, schema and packet text:

appender.audit.layout.pattern=%d - <->%X{datasource} %X{user} %X{schema} %m: %X{packet-text}%n

The available %X entries are:

• client-ip - client application IP address as seen by the connector.
• client-port - client application outgoing TCP port. Convenient to identify a given client application since a connected IP/port pair is unique at any given time.
• user - (not available in bridge mode) user name authenticated to the connector. Since bridge mode doesn’t inspect packet internals, it will not be able to display this information.
• schema - (not available in bridge mode) schema on which the client application is connected. Will be blank if no schema has been selected. Since bridge mode doesn’t inspect packet internals, it will not be able to display this information.
• datasource - cluster data source used to execute the request, composed of <hostname>@<dataservice>(role:STATE).
• packet-header - MySQL packet header as Packet #<packet number> size=<full packet length (inc. header)> pos=<internal pointer position>.
• packet-text - internal packet bytes printable as characters. This will display a readable form of the requests. Note that the header bytes are not printed here.
• packet-binary - internal packet bytes as hexadecimal values. Will display the full packet, yet non-human-readable form. Note that the header bytes are also printed.

Note that since most of the other information is not available at (dis-)connection time, only client-ip and client-port are applicable to AppConnections. A similar limitation applies to Perf when printing the time to connect to the data source.

Wrap-Up

In this post we explored the details of the new Tungsten Connector Audit Logging feature included with Tungsten Clustering version 7.0+. We covered how to enable and disable logging for 5 different categories of data, and how to customize the output.

For more information, please visit the online documentation at https://docs.continuent.com/tungsten-clustering-7.0/connector-advanced-audit-logging.html

Smooth sailing!