MySQL Monitoring with Splunk

January 30th, 2013 by Erin Scott

MySQL Logging with Splunk

Getting Splunk running and monitoring common log formats, such as apache logs and system logs, is a pretty straightforward process. Some would even call it intuitive but setting up some of the optional plugins can be tricky the first time you set it up. The following is a quick and dirty guide to getting the MySQL monitor from remora up and running in your splunk instance.

This article assumes you have a splunk server as well as a separate database server running a splunk forwarder that is pushing logs to the main splunk server.

The first step is to prepare your splunk server for the incoming mysql stats. We’ll need to make a custom index (called mysql in our case) on both the server and the database host.  See below:

create mysql index on splunk server

Once that’s done we’ll also need to create a custom tcp listener on the splunk server.  This is different from the standard listener that runs on port 9997.  Go to the manager and then data inputs to create:

add listener1

 

add listener2

 

set raw tcp lictener on splunk server

 

As you see we used port 9936 to be a listener that automatically imports into the mysql index. You’ll want to ensure that this port is reachable from your database server to ensure there are no firewalls blocking your connection. You can test this with a simple telnet command.  If you see a prompt that says “Escape character is” then you’re good to go.

telnet to port 9936 to test

 

Once we have verified the listener is up and running the next step is to get the mysql monitor installed on all the machines. It’s easily available via the splunk marketplace. All you need is to create a username and password.

go to marketplace to install apps

Once in the market place locate the Mysql monitor

install mysql monitor on splunk server and db servers

And then restart splunk

restart splunk

Now that that’s installed we need to make sure all the dependencies for the mysql monitor are setup on the database servers that will be pushing data to the main splunk server.

To install on a debian based os use this command:

    apt-get install python-mysqldb

For a redhat based os use this

    yum install MySQL-python

Accept all the dependencies and assuming there were no issues you’re just about ready.

Next on the list is to make sure your splunk monitoring daemon can talk to the local mysql server. On the machine in our test, we only have mysql running on the internal ip and have to ensure that the mysql user splunk@172.16.154.141 can connect and has permission. You may need to run the following command to grant yourself permission.

     grant all privileges on *.* to 'splunk'@'mysql_ip' identified by 'your-password';

To verify that splunk can access your tables use the following command

     mysql -u splunk -h mysql_ip -p

Once you’ve got that down the last step is to configure the mysql monitor’s config.ini. Here’s the config.ini we used:

[mysql]
 host=172.16.154.141
 port=3306
 username=splunk
 password=your-password
[splunk]
 host=172.16.154.250
 port=9936
[statusvars]
 interval=10
[slavestatus]
 interval=10
[tablestats]
 interval=3600
[processlist]
 interval=10

As of this writing, the place to put that config file is: /opt/splunk/etc/apps/mysqlmonitor/bin/daemon

To start the mysql monitor type this on the db server: /opt/splunk/etc/apps/mysqlmonitor/bin/daemon/splunkmysqlmonitor.py

That’s it!  If you check the Splunk server then you should start seeing the mysql logs popping in immediately.

view mysql logs

 

mysql host overview

 

Pretty nice eh?

 

Next time I’ll show you how to make the splunk monitor daemon start on boot.

Comments are closed.