Getting MySQL Logs into Honeycomb

Our connector pulls your MySQL logs into Honeycomb for analysis, so you can finally get a quick handle on the database queries triggered by your application logic. It surfaces attributes like:

Honeycomb is unique in its ability to calculate metrics and statistics on the fly, while retaining the full-resolution log lines (and the original MySQL query that started it all!).

Once you’ve got data flowing, be sure to take a look at our starter queries! Our entry points will help you see how we recommend comparing lock retention by normalized query, scan efficiency by collection, or read vs. write distribution by host.

Note: This document is for folks running MySQL directly. If you’re running MySQL on RDS, check out our RDS connector page to set up your RDS instance instead.

The agent you’ll use to translate logs to events and send them to Honeycomb is called honeytail.

Setup Options:

Automated Setup

We provide an installer to quickly guide you through setting things up. It’ll download honeytail, locate your log, check config options, and walk you through sending both archived and current data to Honeycomb. It should be run on your MySQL host.

Get, verify, and start the current Linux version of the mysql_installer by running:

wget -q -O mysql_installer https://honeycomb.io/download/mysql_installer/1.52 && \
  echo "24f64e3fb1e603139d4c476ebc42dae8481c402109f4086cec358b9ad6c84b8c  mysql_installer" | sha256sum -c && \
  chmod 755 ./mysql_installer && \
  ./mysql_installer

Follow the prompts, and that’s it!

Run into problems? Try the Manual Setup, jump down to Troubleshooting, or ping us for help.

Manual Setup

If your configuration is non-standard and not served by the installer, or if you want to integrate into your configuration management system, below are the necessary (and recommended) steps. These should be run on your MySQL host.

Download and install the latest honeytail by running:

wget -q https://honeycomb.io/download/honeytail/linux/honeytail_1.378_amd64.deb && \
      echo '197401aa6877f09139d6927e2dbc038ec8ae91243e1b522d8131bc4bd66c4e78 honeytail_1.378_amd64.deb' | sha256sum -c && \
      sudo dpkg -i honeytail_1.378_amd64.deb

The packages install honeytail, its config file /etc/honeytail/honeytail.conf, and some start scripts. The binary is just honeytail, available if you need it in an unpackaged form or for ad-hoc use.

You should modify the config file and uncomment and set:

Make sure to run through the Important Configuration below before running honeytail, in order to get the most out of your logs.

For the current MySQL slow query log, often located at /usr/local/var/mysql/myhost-slow.log, first backfill the file to make sure that existing log lines are uploaded:

honeytail --writekey=YOUR_WRITE_KEY --dataset=MySQL --parser=mysql \
  --file=/usr/local/var/mysql/myhost-slow.log \
  --backfill

And then set honeytail up to tail new lines:

honeytail --writekey=YOUR_WRITE_KEY --dataset=MySQL --parser=mysql \
  --file=/usr/local/var/mysql/myhost-slow.log

Important Configuration

MySQL slow query logging is not turned on by default, and once it is, its default is to only log queries taking over 10 seconds. For the most insight into your system, you’ll want to turn slow query logging on for all queries if possible.

To turn on slow query logging (recommended) for your MySQL host, run the following in your MySQL shell:

mysql> SET GLOBAL slow_query_log = 'ON';

Set the threshold for a query to be considered a “slow” query to 0 (the default is 10):

mysql> SET GLOBAL long_query_time = 0;

And verify the slow query log’s location via:

mysql> SELECT @@GLOBAL.slow_query_log_file;

Note: If this technique is a problem for you—specifically, you don’t want to rely on slow query log output—let us know! We’ve got something in the works that might satisfy your needs.

Backfilling Archived Logs

Regardless of whether you pick Automated or Manual setup, you may have archived logs that you’d like to import into honeycomb. After either setup process, you’ll have a honeytail agent downloaded that you can use.

If you have a MySQL logfile located at /usr/local/var/mysql/myhost-slow.16.log, you can backfill using this command:

honeytail --writekey=YOUR_WRITE_KEY --dataset=MySQL --parser=mysql \
  --file=/usr/local/var/mysql/myhost-slow.16.log \
  --backfill

This command can be used at any point to backfill from archived log files. You can read more about our agent honeytail or its backfill behavior here.

Note: honeytail does not unzip log files, so you’ll need to do this before backfilling.

Once you’ve finished backfilling your old logs, we recommend transitioning to the default streaming behavior to stream live logs to Honeycomb.

Troubleshooting

First, check out honeytail Troubleshooting for general debugging tips.

No data is being sent, and --debug doesn’t seem to show anything useful

Take a look at the --file being handed to honeytail and make sure they look like MySQL slow query logs, with blocks of comments containing metadata alternating with the MySQL commands issued.

An example excerpt from a MySQL slow query log might look like:

# Time: 151008  0:31:03
# User@Host: rails[rails] @  [10.252.10.158]
# Query_time: 0.000547  Lock_time: 0.000019 Rows_sent: 1  Rows_examined: 938
use rails;
SET timestamp=1444264263;
SELECT `app_data`.* FROM `app_data` WHERE (`app_data`.user_id = 69213) LIMIT 1;

If your log file looks like a normal MySQL output log but honeytail is still failing to send events to Honeycomb, let us know! We’re available to help anytime via email or chat .

Only some queries seem to appear in Honeycomb

Did you remember to SET the GLOBAL long_query_time? Our parser relies on reading your server’s slow query logs, which contain much more valuable metadata than the general log—and the default slow query threshold is 10 seconds.

Try checking the output of:

mysql> SELECT @@GLOBAL.long_query_time;

If it’s not 0, take another look at the steps described in Important Configuration.

Still having trouble?

We’re happy to help—send us a message via chat anytime!

Example Extracted MySQL Fields

Ingesting a MySQL log line (resulting from a SELECT with a JOIN):

# Time: 161019 18:30:00
# User@Host: rdsadmin[rdsadmin] @ localhost [127.0.0.1]  Id:     1
# Query_time: 1.294391  Lock_time: 0.000119 Rows_sent: 4049  Rows_examined: 4049
SET timestamp=1476901800;
SELECT teams.* FROM teams INNER JOIN users_teams ON team_id=teams.id WHERE user_id=21782 AND slug='foobar' LIMIT 1

will produce MySQL events for Honeycomb that look like:

field name value type
client string localhost
client_ip string 127.0.0.1
lock_time float 0.000119
normalized_query string select teams.* from teams inner join users_teams on team_id = teams.id where user_id = ? and slug = ? limit ?
query string SELECT teams.* FROM teams INNER JOIN users_teams ON team_id=teams.id WHERE user_id=21782 AND slug=‘foobar’ LIMIT 1
query_time float 1.294391
rows_examined float 4049
rows_sent float 4049
statement string select
tables string teams users_teams
user string rdsadmin

Numbers are ingested as floats by default in Honeycomb, though you can coerce a field to integers in the Schema section of your dataset’s Overview.

You can find more on our MySQL query normalization in our mysqltools repository.

Scrubbing Personally Identifiable Information

While we believe strongly in the value of being able to track down the precise query causing a problem, we understand the concerns of exporting log data which may contain sensitive user information.

With that in mind, we recommend using honeytail’s MySQL parser, but adding a --scrub_field=query flag to hash the concrete query value. The normalized_query attribute will still be representative of the shape of the query, and identifying patterns including specific queries will still be possible—but the sensitive information will be completely obscured before leaving your servers.

More information about dropping or scrubbing sensitive fields can be found here.

Open Source

Honeytail and our installers are all open source, Apache 2.0 licensed. Their source can be found on GitHub: