Send MySQL Logs - Honeycomb Docs

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

The normalized query shape
Time spent waiting to acquire lock
Number of rows examined to execute the query
Number of rows returned by MySQL
… and more!

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 have 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.

This document is for running MySQL directly. If running MySQL on RDS, Honeycomb offers support for ingesting RDS MySQL logs via CloudWatch Logs with the option to convert these unstructured logs into structured logs.

The agent used to translate logs to events and send them to Honeycomb is called honeytail.

Configure MySQL Query Logging

Before running honeytail, you will want to turn slow query logging on for all queries if possible. To turn on slow query logging 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;

If this technique is a problem for you—specifically, you do not want to rely on slow query log output—let us know! We have got something in the works that might satisfy your needs.

Install and Run Honeytail

On your MySQL host, download and install the latest honeytail by running:

Download the honeytail_1.10.0_amd64.deb package.

wget -q https://honeycomb.io/download/honeytail/v1.10.0/honeytail_1.10.0_amd64.deb

Verify the package.

echo '3db441215f97eaed068aa0531c986cf5405957e3e8e26b22c16b571091caf917  honeytail_1.10.0_amd64.deb' | sha256sum -c

Install the package.

sudo dpkg -i honeytail_1.10.0_amd64.deb

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

Download the honeytail_1.10.0_arm64.deb package.

wget -q https://honeycomb.io/download/honeytail/v1.10.0/honeytail_1.10.0_arm64.deb

Verify the package.

echo '4220756e5a941cde6a484cb4cfde184eb189aaf29170df301a874eb143e960ed  honeytail_1.10.0_arm64.deb' | sha256sum -c

Install the package.

sudo dpkg -i honeytail_1.10.0_arm64.deb

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

Download the honeytail_1.10.0-1.x86_64.rpm package.

wget -q https://honeycomb.io/download/honeytail/v1.10.0/honeytail_1.10.0-1.x86_64.rpm

Verify the package.

echo 'b23215a9301b20b2e2262a0823c9e761e8b57e1a62fd5cec35f697fce41fa863  honeytail_1.10.0-1.x86_64.rpm' | sha256sum -c

Install the package.

sudo rpm -i honeytail_1.10.0-1.x86_64.rpm

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

Download the 1.10.0 binary.

wget -q -O honeytail https://honeycomb.io/download/honeytail/v1.10.0/honeytail-linux-amd64

Verify the binary.

echo 'c9cc7dd1aa2b12afeb30b089061870f3407d2df0119e7c2807fec648b603e2d5  honeytail' | shasum -a 256 -c

Set the permissions to allow execution.

chmod 755 ./honeytail

Download the 1.10.0 binary.

wget -q -O honeytail https://honeycomb.io/download/honeytail/v1.10.0/honeytail-linux-arm64

Verify the binary.

echo '1dd37227788548c4ed44592554e3c90e374c4d796c444dde9f372db8618bc7fa  honeytail' | shasum -a 256 -c

Set the permissions to allow execution.

chmod 755 ./honeytail

Download the 1.10.0 binary.

wget -q -O honeytail https://honeycomb.io/download/honeytail/v1.10.0/honeytail-darwin-amd64

Verify the binary.

echo '9a3da0f48fe21b1e610ac6b63130dfb8118a9a0ec16abae13350edba02d85e4d  honeytail' | shasum -a 256 -c

Set the permissions to allow execution.

chmod 755 ./honeytail

Clone the Honeytail repository.

git clone https://github.com/honeycombio/honeytail

Install from source.

cd honeytail; go install

Make sure you have enabled MySQL query logging before running honeytail. To consume the current MySQL slow query log from the beginning, run:

honeytail --writekey=YOUR_API_KEY --dataset=MySQL --parser=mysql \
  --file=/usr/local/var/mysql/myhost-slow.log \
  --tail.read_from=beginning

Troubleshooting

Check out honeytail Troubleshooting for debugging tips.

Run Honeytail Continuously

To run honeytail continuously as a daemon process, first modify the config file /etc/honeytail/honeytail.conf and uncomment and set:

ParserName to mysql
WriteKey to your API key, available from the account page
LogFiles to the path for your MySQL slow query log file, often located at /usr/local/var/mysql/myhost-slow.log
Dataset to the name of the dataset you wish to create with this log file.

Then start honeytail using upstart or systemd:

upstart
systemd

sudo initctl start honeytail

sudo systemctl start honeytail

Backfill Archived Logs

You may have archived logs that you would like to import into Honeycomb. 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_API_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 honeytail’s backfill behavior here.

honeytail does not unzip log files, so you will need to do this before backfilling.

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

Scrub 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.

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 an event for Honeycomb that looks like: 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.

Open Source

Honeytail is open source and Apache 2.0 licensed.

​Configure MySQL Query Logging

​Install and Run Honeytail

​Troubleshooting

​Run Honeytail Continuously

​Backfill Archived Logs

​Scrub Personally Identifiable Information

​Example Extracted MySQL Fields

​Open Source

Configure MySQL Query Logging

Install and Run Honeytail

Troubleshooting

Run Honeytail Continuously

Backfill Archived Logs

Scrub Personally Identifiable Information

Example Extracted MySQL Fields

Open Source