Logstash (MySQL -> AWS Elasticsearch Service) Integration in Ubuntu 16.04

logstash-mysql-elasticsearch-service.md

Make sure that you have installed and tested Connector/J on your system: https://gist.github.com/craigvantonder/98391eb72f0e23525377ddbd89d607af/

Then read through: https://www.elastic.co/guide/en/logstash/current/installing-logstash.html

And proceed to download and install the Public Signing Key:

wget -qO - https://artifacts.elastic.co/GPG-KEY-elasticsearch | sudo apt-key add -

You may need to install the apt-transport-https package on Debian before proceeding:

sudo apt-get install apt-transport-https

Save the repository definition to /etc/apt/sources.list.d/elastic-5.x.list:

echo "deb https://artifacts.elastic.co/packages/5.x/apt stable main" | sudo tee -a /etc/apt/sources.list.d/elastic-5.x.list

Install Logstash

sudo apt-get update && sudo apt-get install logstash

Install the latest Connector/J (which fixes LoadError: no such file to load -- /usr/share/java/mysql-connector-java-5.1.28-bin):

mkdir /tmp/connectorj/
cd /tmp/connectorj/
wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.41.tar.gz
tar xvf mysql-connector-java-5.1.41.tar.gz
mv mysql-connector-java-5.1.41/mysql-connector-java-5.1.41-bin.jar /usr/share/java/mysql-connector-java-5.1.41-bin.jar
cd /etc/logstash
rm -rf /tmp/connectorj/

Add your config file for Elasticsearch

echo "input {
  jdbc {
    // The ip and port that the server is listening on
    jdbc_connection_string => \"jdbc:mysql://10.0.0.1:3306/yourtestdatabase\"
    jdbc_user => \"yourusername\"
    jdbc_password => \"youruserpassword\"
    jdbc_driver_library => \"/usr/share/java/mysql-connector-java-5.1.41-bin.jar\"
    jdbc_driver_class => \"Java::com.mysql.jdbc.Driver\"
    # Shedule is once per hour or customise in cron type syntax
    schedule => \"* * * * *\"
    statement => \"SELECT * FROM yourtesttable\"
    # Uncomment and experiement for large tables
    #jdbc_paging_enabled => true
    #jdbc_page_size => 15000
    # http://stackoverflow.com/questions/1318354/what-does-statement-setfetchsizensize-method-really-do-in-sql-server-jdbc-driv
    #jdbc_fetch_size => 5000
  }
}
output {
  # Take this off for debugging
  #stdout { codec => \"json\"}
  elasticsearch {
    index => \"yourtestindex\"
    document_type => \"yourtesttype\"
    hosts => [\"https://yourawsdomainendpoint:443\"]
  }
}
" >> /etc/logstash/conf.d/mystash.conf

Restart the Logstash service:

service logstash restart

If you're wondering where Logstash is installed:

/usr/share/logstash/

And you can use it like if you do not want your stash to run on a schedule through the service:

/usr/share/logstash/bin/logstash -f /path/to/myunscheduledconfig.conf