[ElasticStack] MySQL과 Logstash

MySQL의 데이터베이스를 logstash를 사용하여 테이블에서 데이터 추출하기 & elasticsearch 인덱스에 넣기

1. MySQL 사용을 위해 mysql-server 다운로드

sudo apt-get update
sudo apt-get install mysql-server

[output]

2. 데이터를 불러와 MySQL에서 실제로 테이블 만들어보기

1) movielens의 데이터를 불러오기

cd ~
wget http://files.grouplens.org/datasets/movielens/ml-100k.zip
unzip ml-100k.zip

2) MySQL로 진입하기

sudo mysql -r root -p

[output]

3) movielens 데이터베이스 생성

CREATE DATABASE movieslens;

[output]

4) movielens.movies라는 테이블을 생성하고 데이터베이스와 해당 테이블에 대한 스키마 정의

CREATE TABLE movielens.movies (
    movieID INT PRIMARY KEY NOT NULL,
    title TEXT,
    releaseDate DATE
    );

[output]

5) 생성한 데이터 로드하기

LOAD DATA LOCAL INFILE 'ml-100k/u.item' INTO TABLE movielens.movies FIELDS TERMINATED BY '|'
    (movieID, title, @var3)
    set releaseDate = STR_TO_DATE(@var3, '%d-%M-%Y');

[output]

샘플 데이터셋(movielens의 ml-100k)에서 1,682개의 레코드를 가져왔다.

6) 확인하기

USE movielens;

Star로 시작하는 영화 제목 SELECT 해보기

SELECT * FROM movies WHERE title LIKE 'Star%';

[output]

3. MySQL의 데이터를 elasticsearch에 넣기

1) MySQL 상태 빠져나가기

exit

[output]

2) Connector/J 다운받기

Connector/J 다운로드 바로가기
Select Operating System을 Platform independent 버전으로 변경하고 zip archive 다운로드를 누르면 다음 페이지로 넘어간다.

No thanks, just start my download를 오른쪽 클릭하여 링크 주소를 복사한다.

다음 명령어를 사용하고 있던 server shell창에 입력하여 다운받는다.
주소는 달라질 수 있으므로 꼭 '링크 주소 복사'를 이용하여 복사해두자.

wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-8.0.26.zip

[output]

3) 다운로드 된 zip 파일 압축 풀기

버전에 의해 파일 명이 다를 수 있으므로 다운로드 된 파일을 복붙하자.

unzip mysql-connector-java-8.0.26.zip

[output]

4) logstash 구성파일 생성하기

logstash를 구성하여 mysql 데이터베이스에서 입력을 얻는 방법 알아내기
json으로 뽑아낼 거임

sudo nano /etc/logstash/conf.d/mysql.conf

//mysql.conf
input {
        jdbc {
                jdbc_connection_string => "jdbc:mysql://localhost:3306/movielens"
                jdbc_user => "silverpaper" 
                jdbc_password => "password"
                #jdbc_driver_library는 경로와 jar파일 명 확인할 것
                jdbc_driver_library => "/home/silverpaper/mysql-connector-java-8.0.26/mysql-connector-java-8.0.26.jar"
                jdbc_driver_class => "com.mysql.jdbc.Driver"
                statement => "SELECT * FROM movies"
        }
}
output {
        stdout { codec => json_lines }
        elasticsearch {
                hosts => ["localhost:9200"]
                index => "movielens-sql"
        }
}

[화면예시]

5) MySQL에 진입하여 사용자 생성

sudo mysql -uroot -p

CREATE USER 'silverpaper'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'silverpaper'@'localhost';
(*.*에 대한 모든 권한을 로컬호스트의 학생에게 부여)
FLUSH PRIVILEGES;

6) quit로 MySQL에서 나와 mysql.conf 실행

quit

cd /usr/share/logstash
sudo bin/logstash -f /etc/logstash/conf.d/mysql.conf

[output]

7) elasticsearch로 읽어올 수 있는지 확인

curl -XGET 'localhost:9200/movielens-sql/_search?q=title:Star&pretty'

[output]