Shortly before MySQL Users Conference I announced that I would be cover new ground in table logs management.
I am keeping that promise, and in addition I am also showing some related hacks.

The announced facts from last year usability report were that you can't change log tables at will, as you can do with log files, and you can't change the log table engine to FEDERATED. Both claims, as it turned out, were incorrect. You can do such things, albeit not in a straightforward manner. As a bonus side effect, you can also:

  • add triggers to log tables;
  • filter log tables depending on user defined criteria, such as query type, user database, or time;
  • centralize logs from several servers.

DISCLAIMER. The information in this article is essentially a hack. It is not recommended nor endorsed by MySQL AB.

Overview: switching logs basics

Switching log files is easily done in just one step, assuming that you are using files as log output.
To enable file usage, you must enable the general_log variable and set the log_output variable to 'FILE'.
In this situation, changing to a dedicated sometask.log file requires just one command:


set global general_log_file='/tmp/sometask.log';

Going back to the default log file, or to any other, is just a command away:


set global general_log_file='/usr/local/mysql/data/general.log';

Switching tables is a little trickier. The default log table location (_mysql.general_log_) is not negotiable. Thus, we need to adopt a more devious approach.

  1. First, we create a table with the same structure as the default one. 
    
DROP TABLE IF EXISTS gl0, gl1,
CREATE TABLE mysql.gl1 like mysql.general_log;
  2. If needed, we change the engine to a something more efficient than CSV. This means basically MyISAM, since any other engine will be refused. But the following hacks will circumvent this limitation as well. One of the reasons for changing engine is if we want to add a key that we plan to user later for searches. 
    
ALTER TABLE gl1 ENGINE=MyISAM, KEY (user_host);
  3. Then, we rename both tables at once: 
    
RENAME TABLE general_log to gl0, gl1 to general_log;

That's all. The default table has always the same name, but we can switch to one table to another, if we need to log action during a given task.
The above method is also important because it is the basis for the following hacks, which make dynamic logging much more interesting.

How to: smarter logs

We are now ready to take the hack to a new dimension. The original usability report mentions that you can only use the CSV and MyISAM engines for logging. Any attempt to assign a different engine will fail. While this is technically accurate, we can use the above trick to cheat the server into using a FEDERATED table for logging.

If you have dealt with general logs before, you would know that logs introduce two administrative problems:

  • they grow large very quickly, eating up precious storage space;
  • if the server crashes for a hardware failure, your logs are lost.

A simple way of dealing with the first problem is using the Archive storage engine for logging. You can't assign it directly, but you can rename an existing table.


# Squeezing table logs
DROP TABLE IF EXISTS gl0, gl1,
CREATE TABLE gl1 like general_log;
ALTER TABLE gl1 ENGINE=ARCHIVE;
RENAME TABLE general_log to gl0, gl1 to general_log;

The ARCHIVE table will work exactly as the CSV one, with the important difference that it will only take 15~20% of its storage. Since log tables must only support INSERT statements, using an Archive engine makes sense.

How to: centralized remote logs

Another way is to send the logs to a different server. The second issue can be solved with the same trick. Current table logging implementation does not allow triggers, but if you have federated your log table to a remote one, nothing prevents the usage of triggers as well.

To create remote logs, act as follows:

  1. Install a MySQL server on a box with sufficient storage for your purposes. The version is not important, but if you want to use triggers, version 5.0 or higher is necessary.
  2. On the remote server, create a database for logging.
  3. Inside that database, create a table with the same data structure of the general_log table, using your favorite storage engine.
  4. In the local server, create a table with the same structure as the general log, federated to the remote server.
  5. Use the technique illustrated in the previous paragraph to rename the new table to the general_log table.
  6. Enable table logging and start seeing the effects.

# remote server
CREATE SCHEMA logs;
USE logs;
CREATE TABLE mylog LIKE mysql.general_log;
ALTER TABLE mylog ENGINE=MyISAM, KEY (user_host);

# local server
create server logserver foreign data wrapper mysql options (
host 'remote_server.net',
database 'logs',
port 3306,
user 'remote_user_name',
password 'remote_secret');

use mysql;
DROP TABLE IF EXISTS gl1, gl0;
CREATE TABLE gl1 (
event_time timestamp NOT NULL,
user_host mediumtext,
thread_id int DEFAULT NULL,
server_id int DEFAULT NULL,
command_type varchar(64) DEFAULT NULL,
argument mediumtext
) ENGINE = FEDERATED
CONNECTION = 'logserver/mylog';
# Note: MySQL 5.1.18 is required for the above syntax
# For older versions, you can use the old syntax instead:
# CONNECTION='mysql://user:password@hostname:port/schema/table'

RENAME TABLE general_log to gl0, gl1 to general_log;

When the preparation is over, your general log will end up in the remote server.

Once this organization is in place, you may start thinking about expanding it. If you can relocate log activity from one server, you can do the same for multiple ones.

What happens if you repeat the same steps for another server, pointing to the same remote server,schema and table? Exactly what you would expect. All the queries from both servers will end up in the same table. You will be centralizing your logs, but you need a way of telling the queries from different servers apart. It can be easily accomplished. The log table includes a server_id column, which records the unique ID of the server. If you assign a different server ID to each server, then you can use a centralized remote log for several servers.
On each server, issue the command


SET GLOBAL server_id = <a unique number>;

and your centralized logs will be also easily searchable.

More hacks: filtering with triggers

We mentioned before that logs don't allow triggers. That's a limitation builtin in the mysql database, where no table can have triggers. However, we are federating a table to an remote server, in a normal database, where triggers are not restricted.
Thus, although we can't add a trigger to the log table itself, nothing prevents us from adding one to the shadow table in the remote server.

Let's consider the common case where the DBA wants to log in two separate tables events related to sales and customers, and discard all the rest.
That is simply done. Filtering the wanted records and inserting them into the appropriate tables can be done inside a trigger. Discarding the rest is tricky, and it could not be done with standard SQL. You need to assign the remote log table to the BlackHole storage engine, which is an engine that simply discards everything. The data is passed to the table, and thus a BEFORE INSERT index will work just fine.
The steps to achieve our goal are:

  1. Create the remote log tables that will store the wanted records;
  2. Alter the remote log table, so that it uses the BlackHole engine;
  3. create a trigger that filters the records and inserts the right ones in the auxiliary tables.

In the following example, the trigger performs a brutish check to see if the query contains 'customer' or 'sales'. In a real world situation you may want to perform a more exhaustive check, but for now this would do.


delimiter //

create trigger logs_bi
    BEFORE INSERT
    ON logs
    FOR EACH ROW
begin
    if (new.argument regexp 'customer')
    then
        insert into log_customers
            (event_time, user_host, thread_id,
            server_id, command_type, argument)
        values
            (new.event_time, new.user_host, new.thread_id,
            new.server_id, new.command_type, new.argument);
    end if;
    if (new.argument regexp 'sales')
    then
        insert into log_sales
            (event_time, user_host, thread_id,
            server_id, command_type, argument)
        values
            (new.event_time, new.user_host, new.thread_id,
            new.server_id, new.command_type, new.argument);
    end if;
end //

delimiter ;

Wrapping up

Table logs are an excellent addition in MySQL 5.1. They allow logging on demand, which is one of the most appreciated features for DBAs.
If you add some flexibility in your choice of storage engines for logging, this feature can be truly a bless for demanding administrative tasks.

It would be desirable to change log tables storage engine without much fuss. MySQL developers are working on it. In the meantime, you can use the workarounds in this article to twist your logs the way you want them.

I would like to thank Tobias Asplund and Petr Chardin for useful hints to complete these hacks.