Bash script to fetch the log for that hour from Amazon RDS and push it to ElasticSearch / Kibana

slowquery_logger.sh

#!/bin/bash
#
# Ashutosh Agrawal
# http://blog.theprogrammer.in
#
#
# This bash script simply fetches the log for that hour from Amazon RDS,
# parse and genarlise it using mysql_slow_log_parser (https://gist.github.com/theprogrammerin/e3206a4ec7a7a4086ac2)
# and then push the parsed log to elastic search using logstash (slowquery.conf)(https://gist.github.com/theprogrammerin/034a3efd849112d166ea)
# For analysis on analytic tool like kibana.
#
# To run this script you have to simply call
#
# ./slowquery_logger.sh <DB NAME>


AWS_ACCESS_KEY="<YOUR AWS ACCESSS KEY>"
AWS_SECRET_KEY="<YOUR AWS SECRET KEY>"
PATH=$PATH:/var/lib/rds-cli/bin/
HOUR=`date -u +"%k" | tr -d ' '`
export DATABASE=$1

# Temporary Log Storage location
TMP_LOG="/tmp"
TMP_FILE="$TMP_LOG/$DATABASE.slowquery.$HOUR.log"
RDS_LOG_FILE="slowquery/mysql-slowquery.log.$HOUR"

# Location of your script folder. This should contain mysql_slow_log_parser and slowquery.conf
SCRIPT_LOCATION="/mnt/slowquery"

# Get the log from RDS
echo "Starting Slow Query Log Fetching for $HOUR hour"
echo "Gettting the log from RDS for Databse $DATABASE"
source $SCRIPT_LOCATION/.bash_config && rds-download-db-logfile $DATABASE -I $AWS_ACCESS_KEY -S $AWS_SECRET_KEY --log-file-name $RDS_LOG_FILE > $TMP_FILE
echo "Log Fetcehed and stored in $TMP_FILE"
# Parse the log to get the pattern
echo "Parsing log to generate patterns"
PARSED_FILE="$TMP_LOG/$DATABASE.slowquery.$HOUR.log.parsed"

cd $SCRIPT_LOCATION && ./mysql_slow_log_parser $TMP_FILE > $PARSED_FILE
echo "Log Parsed, stored in $PARSED_FILE"

echo "Pushing log to kibana"
# Push to kibana

# used for logstash installed on OS X using brew.
#logstash -f $SCRIPT_LOCATION/slowquery.conf < $PARSED_FILE

# If you have logstash installed on ubuntu machine and  needs to be run via java
sudo /usr/bin/java -Xmx256m -Djava.io.tmpdir=/var/lib/logstash/ -jar /opt/logstash/logstash.jar agent -f $SCRIPT_LOCATION/slowquery.conf --log /var/log/logstash/logstash.log < $PARSED_FILE
echo "Done"