OurSQL Episode 164: Who's Doing What?

This week we talk about how to install and use the MariaDB Audit plugin, and what the audit log looks like. Ear Candy presents a gotcha with MySQL and temporary directories, and At the Movies is about using Dynamo for more than just a data store.

Events
DB Hangops - every other Wednesday at noon Pacific time

Upcoming MySQL events

Training
SkySQL Trainings
Tungsten University trainings
Oracle's MySQL Trainings
Percona Trainings

MariaDB audit plugin
Episode 26 - we said we were not too excited about the audit and external authentication plugins available in MySQL 5.5 because we wanted to see what plugins would be written first.
Episode 61 ear candy - which talks about being able to add audit plugins.

MariaDB Audit Plugin 1.1.3 release notes

Activating Auditing MariaDB and MySQL in 5 Minutes

Find out where to install the plugin to:
SHOW GLOBAL VARIABLES LIKE 'plugin_dir';

You can add a line in the configuration file under the [mysqld] section:
plugin-load=server_audit.so

To install the plugin:
INSTALL PLUGIN server_audit SONAME 'server_audit.so';

show global variables like 'server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 1                     |
| server_audit_output_type      | file                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
14 rows in set (0.01 sec)

logrotate man page

Example audit log entries:

20131118 05:02:34,stage1.db.mozilla.com,test,localhost,4258811,18193,QUERY,percona,'CREATE VIEW testme AS select db,tbl,chunk_index,lower_boundary,upper_boundary from percona.checksums where !(this_crc<=>master_crc)',0
20131118 05:02:39,stage1.db.mozilla.com,test,localhost,4258811,18197,QUERY,percona,'select * from testme',0
20131118 05:06:39,stage1.db.mozilla.com,test,localhost,4258811,21539,QUERY,percona,'select * from testme where foo=bar',1054

    Let's disassemble the last row into parts with brief descriptions:
  • 20131118 05:06:39 - date and time the query was executed

  • stage1.db.mozilla.com - mysql server the query was executed on

  • test - username

  • localhost - host (part of username@host authentication to mysql)

  • 4258811 - connection ID. Note that this is the same for each query, as all three queries were run from the same connection.

  • 21539 - query ID. Note that this is different for each query

  • QUERY - a description of what is happening in this row

  • percona - the database name

  • 'select * from testme where foo=bar' - more information

  • 1054 - MySQL error code. Error 1054 is "Unknown column"
  • This example is from a MariaDB server, and shows how it logs that the "testme" view reads the percona.checksums table (see the 2nd line). Note that the 2nd line has no error code, as it was not actually a command that was run. Also note that it has the same query ID as the 3rd line (145) because it was part of the same query:

    20131118 05:28:54,mariadb1.db.mozilla.com,test,localhost,101,144,QUERY,percona,'CREATE VIEW testme AS select db,tbl,chunk_index,lower_boundary,upper_boundary from percona.checksums where !(this_crc<=>master_crc)',0
    20131118 05:29:00,mariadb1.db.mozilla.com,test,localhost,101,145,READ,percona,checksums,
    20131118 05:29:00,mariadb1.db.mozilla.com,test,localhost,101,145,QUERY,percona,'select * from testme',0

    OurSQL Episode 121, where we talked about CSV tables

    Ear Candy
    In this weeks ear candy we're going to cover a gotcha with MySQL and temporary directories. MySQL Bug 69151 describes one of such situations in MySQL 5.6. It arises when using ALTER TABLE with lots of data.

    At the Movies
    This week in At the Movies we present Susan Potter presenting Dynamo is Not Just for Datastores at the 2011 Strangeloop conference. She discusses Dynamo, Riak, distribution, consistency and fault tolerance, along with techniques and an example for building an application with riak_core.

    Feedback
    Facebook group
    Google+ page
    e-mail: podcast at technocation.org
    voicemail using phone/Skype: +1-617-674-2369
    twitter: @oursqlcast
    or Tweet about @oursqlcast

    Trackback URL for this post:

    http://technocation.org/trackback/1270