Skip to content

Instantly share code, notes, and snippets.

@BlackMaria
Last active December 17, 2015 01:59
Show Gist options
  • Save BlackMaria/5532734 to your computer and use it in GitHub Desktop.
Save BlackMaria/5532734 to your computer and use it in GitHub Desktop.
The following is an example of how one could send logs to mysql. If you have specific logs that you want to save in an SQL for some odd reason ( eg: you work for an old company that goes about things in archaic ways ).
-- NB '%{@type}' must be the same as table_name
-- create database logstash;
CREATE TABLE table_name (
table_name_id int(8) unsigned NOT NULL AUTO_INCREMENT,
logtime datetime DEFAULT NULL,
tags varchar(255) DEFAULT NULL,
message varchar(255) DEFAULT NULL,
PRIMARY KEY (table_name_id)
);
GRANT INSERT,SELECT ON logstash.* TO logstash@localhost IDENTIFIED BY 'mysqlpasswd';
#!/bin/sh
# a cheap and dirty example of how this can be done
# if you do this at home, be sure to be more elegant!
DATE=`echo $2| cut -dT -f2 | cut -d. -f1`
TIME=`echo $2| cut -dT -f1 `
TABLE="$1"
printf " INSERT INTO $TABLE SET logtime='$TIME $DATE', tags='$3', message='$4';" > /tmp/mysql-logstash.pipe
output {
exec {
type => "table_name"
tags => "send2sql"
command => "/opt/logstash/bin/write2mysqlpipe.sh '%{@type}' '%{@timestamp}' '%{@tags}', '%{@message}'"
}
}
#!/bin/sh
# Run this first to create a pipe to your MySQL server
#
if [ ! -e /tmp/mysql-logstash.pipe ] ;then
mkfifo /tmp/mysql-logstash.pipe
fi
while [ -e /tmp/mysql-logstash.pipe ]; do
mysql -u logstash --password=mysqlpasswd logstash < /tmp/mysql-logstash.pipe >/dev/null
done
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment