[HoneyMonitor is a GUI for MySQL™. Server Administration, Database and Database Objects Administration, Code Development, Server Monitoring, Performance Monitoring, Performance Auditing. Read more on http://www.honeysoftware.com/]

This is a short tutorial on the features implemented in HoneyMonitor for the use and administration of Table Logs in MySQL™ 5.1 . It could be useful for Users who are new to the product and want to get start with it or to whom want to learn the basis of Table Logging in MySQL™ 5.1.

Contents

1. Introduction
2. Brief Introduction to the Server Logs Management Window
3. The General Log Table
4. General Log Administration
5. General Log Data: Selecting, Sorting, Filtering Log Data
6. Quick Tour of the other features of the Server Logs Management Window
7. Other Resources

1. Introduction

One of the main new features of MySQL™ 5.1 is Table Logging. Beginning with v.5.1.6, in fact, it is possible to choose a new destination for General and Slow Logs: not only log files but also log tables.

In general, if the log output is a table and all log statements are stored in a table rather than in a text file you can use a SELECT syntax to select over the log table thus improving and speed-up the way you can search information on your logs.

In MySQL™ 5.1 there are two log tables: general_log and slow_log. In this article we typically will refer to general_log but many of the considerations we make apply to slow_log as well.

A server’s variable, log_output, takes care of the destination of the output of the general_log and the slow_log.

log_output is a global, dynamic, string variable.

In general, a dynamic variable is a variable that you can change at run-time without the need to stop and restart the server. This is very useful if applied to log: you can switch from log-table to log-file at runtime.

Possibles values for log_output are “FILE”, “TABLE” and “NONE” but you can also use a combination between values. For example if you set “FILE,TABLE” all log statements will be written both in the file-log and in the table-log.

In early versions of MySQL™ 5.1, the default value for log_output was “TABLE” but then the default value has been “restored” to “FILE” (as for MySQL™ 5.0).

“NONE”, if specified, causes the server not to write log even if the log is enabled. Please pay attention at the following difference: setting a log output does not imply enabling log.

To enable general_log you have to set general_log = ON and, in a similar way, to enable slow_log you have to set log_slow_queries = ON. After log is enabled you can choose log_output but, while there are two separate variables for enabling general and slow_log, there is only one variable related to log output. So you can have the general_log enabled and the slow_log disabled but if you choose an output (and both logs are enabled) that output (”FILE“, “TABLE“, or “FILE,TABLE“) applies to both the logs.

If the logs are disabled, no logging occurs even if the value of log_output is not “NONE“.

Another thing you have to consider is that the slow_log is not an absolute, independent feature like general_log.

slow_log is in fact deeply related to the value of the variable long_query_time (if a query takes more than long_query_time to be executed, and slow_log is enabled, then it will be logged as a slow query).

If you start the server with the option –-log, the general_log is enabled by default (but not the slow_log).

Other variables of interest for log management are general_log_file (the location of the general_log if log_output is “FILE“), slow_query_log_file (the location of the slow_log if log_output is “FILE“) and log_error (the location of the error log).

This article can’t be exhaustive about log management in MySQL™ and we will try to cover only some aspects relating to the general and slow logs, leaving out errors log and binary logs from our treatment.

For more information about Server Logs, please visit http://dev.mysql.com/doc/refman/5.1/en/log-files.html.

2. Brief Introduction to the Server Logs Management Window

In HoneyMonitor, our GUI for MySQL™, the Server Logs Management Window allows you to view and manage the server’s logs (general log, slow log, error log, binary log, relay log).

For this tutorial we will use HoneyMonitor v.1.0.14-alpha, MySQL™ v.5.1.28-rc, the latest version of the 5.1 rc series of the server, and MySQL™ v.5.1.26-rc (we use the old version 5.1.26-rc for the sole reason that the v.5.1.28-rc has just been released few hours ago and, in my laptop, the log tables of that version contain only few rows).

The version 1.0.14-alpha of HoneyMonitor includes many improvements in the log management if compared with v.1.0.13-alpha but we also plan to add other two main features in this Window in next versions so stay tuned for a better management of log in future versions of our software.

When opening the Server Logs Management Window, the Status Tab is selected (Fig. 1).

Fig. 1: Server Logs Management

In this Tab some quick information about the status of the logs are shown (for example, the number of records contained in the General and Slow Log Tables).

Furthermore, server’s variables that are related to the logs management are listed. Dynamic variables, in the list, are identified with a different icon and can be edited just double-clicking on their names or using the popup menu of the list.

When clicking on a variable, a useful tooltip with the variable’s description will appear.

As you can see from Fig. 1, you can ”Flush” the logs by clicking on the ”Flush Logs” button.

If we click on the “General Log” Tab we can see that the general log is enabled but the log output isn’t “TABLE” (Fig. 2)

Fig. 2: Server Logs Management Window – General Log Tab

so the first thing we must do in order to continue our tutorial is changing the log_output (Fig. 3).

Fig. 3: Setting log_output

and then checking that the (orange) warning disappear (Fig. 4).

Fig. 4: General Log Entries

3. The General Log Table

The General Log Table, as well as the Slow Log Table is included in the mysql database:

mysql> use mysql;
Database changed
mysql> describe general_log\G
*************************** 1. row ***************************
Field: event_time
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
*************************** 2. row ***************************
Field: user_host
Type: mediumtext
Null: NO
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: thread_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 4. row ***************************
Field: server_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 5. row ***************************
Field: command_type
Type: varchar(64)
Null: NO
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Field: argument
Type: mediumtext
Null: NO
Key:
Default: NULL
Extra:
6 rows in set (0.11 sec)

mysql> describe slow_log\G
*************************** 1. row ***************************
Field: start_time
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
*************************** 2. row ***************************
Field: user_host
Type: mediumtext
Null: NO
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: query_time
Type: time
Null: NO
Key:
Default: NULL
Extra:
*************************** 4. row ***************************
Field: lock_time
Type: time
Null: NO
Key:
Default: NULL
Extra:
*************************** 5. row ***************************
Field: rows_sent
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Field: rows_examined
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 7. row ***************************
Field: db
Type: varchar(512)
Null: NO
Key:
Default: NULL
Extra:
*************************** 8. row ***************************
Field: last_insert_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 9. row ***************************
Field: insert_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 10. row ***************************
Field: server_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 11. row ***************************
Field: sql_text
Type: mediumtext
Null: NO
Key:
Default: NULL
Extra:
11 rows in set (0.00 sec)

mysql>

You can see the table’s DDL as well as the table data from the General Log Tab of the Server Logs Management Window included in HoneyMonitor, too (Fig. 5).

Fig.5: General Log DDL

4. General Log Administration

You can enable / disable the General Log directly through the General Log Tab. Also you can clear all log entries and change the Engine of the Table form CSV to MyIsam and vice versa. Please, be aware of bug #39133 when altering Log Tables on master servers.

5. General Log Data: Selecting, Sorting, Filtering Log Data

The grid included in the General Log Tab allows you to see, sort and filter (general) log data in an easy way. Of course, data can’t be edited.

Pagination helps you manage large log tables (Fig. 6).

Fig. 6: Data Pagination

Simple sorting (i.e. on just one column) or advanced sorting (i.e. on more than one columns simultaneously) can help you too for your administration tasks (Fig. 7 and 8)

Fig. 7: Simple Sorting

Fig. 8: Advanced Sorting

as well as defining specific filters (Fig. 9).

Fig. 9: Filtering Log Data

6. Quick Tour of the other features of the Server Logs Management Window

The following images can help you understanding the other features implemented by the Server Logs Management Window.

More details on the HoneyMonitor Reference Manual at http://www.honeysoftware.com/honeymonitor/doc.

Fig. 10: General (or Slow) Query File

Fig. 11: Slow Table Log

Fig. 12: Binary and Relay Logs

Fig. 13: Errors Log

Fig. 14: Binlog Events

7. Other Resources

If you want to learn more on HoneyMonitor please visit the Project Home Page, at http://www.honeysoftware.com/honeymonitor, or check out one of the following resources:

• Getting Started Guide
• Video Tutorials
• User’s Guide