mysql-gelf

GELF Library No release yet

A (very) basic Bash script to send MySQL status information to GrayLog through GELF

arikogan

free!

View on Github 3 15

Published

01 Sep 17:21

Last Push

23 Feb 12:58

Marketplace Rating

No rating yet

Discussion

3 Comments

Readme from Github

mysql-gelf

A (very) basic Bash script to send MySQL status information to GrayLog through GELF

Introduction

GrayLog is becoming a great tool for logs aggregation. It offers a good subset of the functionality you would use Splunk for but for free (money-wise).

This bash script aims to provide a data source for MySQL, a mechanism for feeding MySQL status information into GrayLog. The script aims to be as simple as possible and requires only bash and some basic commands that should hopefully be installed in any server. Information is transmitted using the GELF format. The script is triggered by cron. The information to be fed is retrieved through MySQL's SHOW GLOBAL STATUS query.

An example of how the information is displayed in GrayLog is shown on the screenshot below.

Installation

The installation is compromised of the following steps,

Set up and configuring the script.
Launch a new input in GrayLog.
Set up a cron job to run the script periodically.

Script set-up

The script needs only to reside on the file system of a machine that has both 1) Access to the MySQL instance to be monitored and 2) The possibility of setting up a cron job on it. You can just copy the script to the server or simply clone the Git repository.

Please edit the mysql-status.sh and replace the variables with your MySQL credentials and the location of the GrayLog instance you will going to be reporting to.

There are 2 other files that allow configuring the parameters that are going to be fed into GrayLog: variables-abs and variables-diff. A complete reference of the parameters is available in the MySQL manual.

variables-abs: List of variables to report whose value is absolute, doesn't depend on time span. variables-diff: List of variables to report whose value is relative, depends on the difference between the current value and the last recorded one.

GrayLog's Input

An input has to be set up in GrayLog in order to consume the messages the scrip is going to send. Go to GrayLog > System > Inputs and launch a new GELF UDP input on port 12305

Cron Job

The last step is to create a cron job that will trigger the script every so often. Depends on the level of granularity you are trying to achieve, you can decide how often to trigger the script. Most users will probably do it every minute.

crontab -e

* * * * * /path/to/mysql-gelf/mysql-status.sh

Limitations

Doesn't support messages chunking. This imposes a limit of 8192 bytes on each message. In case you are looking into monitoring many parameters, this can be problematic. We use messages compression to allow for more information in a message but you can easily reach the ceiling when adding more parameters to monitor.
The smallest granularity that can be achieved with the script is 1 minute. The script is triggered by the cron mechanism, which offers a granularity of no less than 1 minute.

Backlog

Make a cleaner separation of script configuration and script behaviour.
Add validation and error handling.
Allow messages chunking.

How to Contribute

This is a very basic script. It can become a much better tool if we all add our enhancements into it. Please feel free to fork the repo and send pull requests to any enhancements you have had the chance to write. Thank you.

Your Rating

Please sign in to rate this add-on.

Comments

henrylwilliams over 2 years ago

Submitted a pull request with some changes. As this wasn't working for me in Ubuntu 14+. I was receiving a 'Bad Substitution' error and a few other issues with with the script.

The main change was getting rid of the use of eval and correcting some syntax.

So, if you're having issues with this, try using what's here: https://github.com/henrylwilliams/mysql-gelf

I've got a pull request in to the main repository for this script, hopefully it will get pulled in soon. Arikogan may just want to make another branch for debian-based environments, however the updates should work across the board.

mocanal over 2 years ago

After configuring the input in Graylog Server and launch the script. I can see that my server receive data in Throughput / Metrics but when I go to Show received messages I can't see anything

pauldsmyth about 3 years ago

Excellent little utility, one little amendment to mysql-status.sh I've made for monitoring remote databases like RDS where you have no ability to run locally:

Line 11
-HOST=`hostname --long`
+HOST=[actual hostname]

Line 23
-STATUS_CMD="echo 'SHOW GLOBAL STATUS;' | mysql -u $MYSQL_USER -p$MYSQL_PASS"
+STATUS_CMD="echo 'SHOW GLOBAL STATUS;' | mysql -u $MYSQL_USER -p$MYSQL_PASS -h $HOST"

Please sign in to comment.

Back to listing