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:
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:
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.
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.
Once in the market place locate the Mysql monitor
And then 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.
Pretty nice eh?
Next time I’ll show you how to make the splunk monitor daemon start on boot.
Print 
PDF
