Goal

• Learn about Logstash and install in Linux.
• Move data from MariaDB to Elasticsearch.

Practice

What is Logstash

• Logstash is an open source data collection engine with real-time pipelining capabilities.

Install Logstash

• Download install file
  • https://www.elastic.co/kr/downloads/past-releases
  • Download Logstash which is matched to version of installed Elasticsearch.
• Upload install file and unzip
  • Upload file by FTP/SFTP

$ tar xvf logstah-[x.x.x].tar.gz

• Edit Configure file
  • Edit logstash configrue file

$ cd $LOGSTASH_HOME/config
$ cp logstash-sample.conf logstash-mariadb.conf
$ vi logstash-mariadb.conf

[edit]

• Directory Layout for Docker Images

Type	Description	Default Location	Setting
home	Home directory of the Logstash installation.	/usr/share/logstash
bin	Binary scripts, including logstash to start Logstash and logstash-plugin to install plugins	/usr/share/logstash/bin
settings	Configuration files, including logstash.yml and jvm.options	/usr/share/logstash/config	path.settings
conf	Logstash pipeline configuration files	/usr/share/logstash/pipeline	path.config
plugins	Local, non Ruby-Gem plugin files. Each plugin is contained in a subdirectory. Recommended for development only.	/usr/share/logstash/plugins	path.plugins
data	Data files used by logstash and its plugins for any persistence needs.	/usr/share/logstash/data	path.data

Other layout

• JDBC Input Configuration Options

Setting	Input type	Required
clean_run	boolean	No
columns_charset	hash	No
connection_retry_attempts	number	No
connection_retry_attempts_wait_time	number	No
jdbc_connection_string	string	Yes
jdbc_default_timezone	string	No
jdbc_driver_class	string	Yes
jdbc_driver_library	string	No
jdbc_fetch_size	number	No
jdbc_page_size	number	No
jdbc_paging_enabled	boolean	No
jdbc_password	password	No
jdbc_password_filepath	a valid filesystem path	No
jdbc_pool_timeout	number	No
jdbc_user	string	Yes
jdbc_validate_connection	boolean	No
jdbc_validation_timeout	number	No
last_run_metadata_path	string	No
lowercase_column_names	boolean	No
parameters	hash	No
record_last_run	boolean	No
schedule	string	No
sequel_opts	hash	No
sql_log_level	string, one of [“fatal”, “error”, “warn”, “info”, debug”]	No
statement	string	No
statement_filepath	a valid filesystem path	No
tracking_column	string	No
tracking_column_type	string, one of [“numeric”, “timestamp”]	No
use_column_value	boolean	No

• Add MariaDB JDBC driver in library directory
  • Download Site
  • Copy driver file in Logstash library directory

$ cd $LOGSTASH_HOME/lib
$ cp [location where driver is] . 

Excecute Logstash

• Execute Logstash

$ cd $LOGSTASH_HOME
$ bin/logstash -f config/[CONFIG_FILE_NAME]

• If. SQLException
  • If you face the situation like this, you should change configuraiton of MariaDB. Because the main cause of this is that MariaDB recognize double quotes which are being used in Logstash process.

  

  • In MariaDB, change SQL_MODE option

ANSI_QUTOES : Changes “ to be treated as `, the identifier quote character. This may break old MariaDB applications which assume that “ is used as a string quote character.

# mysql -uroot -p

MariaDB [(none)]> use mysql
MariaDB [(mysq;)]> SET GLOBAL sql_mode = 'ANSI_QUOTES';

Verify the data

---

Refences

• Getting Started with Logstash
• Jdbc Input Plugin
• ElasticSearch 실습1(logstash 활용)
• SQLSyntaxErrorException
• MariaDB SQL_MODE