Login
      Contents x
      Integration with Cloud Database(MySQL) using Logstash
        • Print
        • PDF
        Contents

        Integration with Cloud Database(MySQL) using Logstash

          • Print
          • PDF
          Article Summary

          Available in VPC

          It describes how to build a Data Pipeline between the Search Engine Service and Cloud DB for MySQL using Logstash.

          Preparations

          Before following this guide, you need to complete the subscription for the following jobs:

          • Create VPC and server
          • Create Cloud DB for MySQL server
          • Create the Search Engine Service cluster

          The example shows how to run Logstash on the Server and then send Cloud DB for MySQL data to the Search Engine Service at regular intervals.

          Setting up network

          This is an example of a network setup.

          STEP 1. Set Cloud DB for MySQL User

          In Manage DB > Manage DB User, you should configure to enable DB User's access from the server band.
          ses-mysql-user-screen

          STEP 2. Set ACG

          The following describes how to set ACG so that access to port 9092 of the Cloud Data Streaming Service broker node is allowed.

          1. From the NAVER Cloud Platform console, click the Services > Compute > Server > ACG menus, in that order.
          2. Select 'cloud-mysql-xxxx’ from the ACG list and click the [Set ACG].
          3. Enter the ACG rule, and then click the [Add] button.
            ses-5-4-1-1_ko
            • Protocol: TCP
            • Access source: IP of the server on which Logstash will run
            • Allowed port: port set in DB (default: 3306)
          4. Click the [Apply] button.

          The following describes how to set ACG so that access to port 9200 of the Search Engine Service manager node is allowed.

          1. From the NAVER Cloud Platform console, click the Services > Compute > Server > ACG menus, in that order.
          2. Select 'searchengine-m-xxxxx' from the ACG list, and then click the [Set ACG].
          3. Enter the ACG rule, and then click the [Add] button.
            cdss-5-6_ko
            • Protocol: TCP
            • Access source: IP of the server on which Logstash will run
            • Allowed port: 9200

          Install Logstash

          This is an example of installing Logstash on a Server. The installation process includes processes for both ElasticSearch and OpenSearch. You must install it according to the version you are using to conduct a normal testing.

          STEP 1. Install Java

          The following describes how to install Java.

          1. Enter the following command to install java.
          yum install java-devel -y

          STEP 2. Install Logstash

          The following describes how to install Logstash.

          Note

          It must be installed with an OSS license to operate normally.

          1. Enter the following command to download Logstash to the /root path.
          # When using Elasticsearch version (Install OSS version)
wget https://artifacts.elastic.co/downloads/logstash/logstash-oss-7.7.0.rpm

# When using OpenSearch version
wget https://artifacts.opensearch.org/logstash/logstash-oss-with-opensearch-output-plugin-7.16.3-linux-x64.tar.gz
          1. Enter the following command to install the downloaded file.
          # When using Elasticsearch version
rpm -ivh logstash-oss-7.7.0.rpm

# When using OpenSearch version
tar -zxvf logstash-oss-with-opensearch-output-plugin-7.16.3-linux-x64.tar.gz
          1. Install the JDBC driver.
          wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-8.0.30.zip
unzip mysql-connector-java-8.0.30.zip
mkdir /etc/logstash/plugin
mv /root/mysql-connector-java-8.0.30/mysql-connector-java-8.0.30.jar /etc/logstash/plugin/

          1. Enter the following command to modify the logstash.conf file before starting Logstash.

            • When using the Elasticsearch version
              mv /etc/logstash/logstash-sample.conf /etc/logstash/conf.d/logstash.conf
vi /etc/logstash/conf.d/logstash.conf
            • ElasticSearch version logstash.conf
            input {
   jdbc {
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_driver_library => "/etc/logstash/plugin/mysql-connector-java-8.0.30.jar"
    jdbc_connection_string => "jdbc:mysql://${cdb mysql endpoint}:3306/${cdb mysql database name}?useSSL=false"
    jdbc_user => "${cdb user name}"
    jdbc_password => "${cdb mysql password}"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_paging_enabled => true
    jdbc_page_size => 50
    statement => "SELECT *, UNIX_TIMESTAMP(update_time) AS unix_ts_in_secs FROM logstash_test WHERE (UNIX_TIMESTAMP(update_time) > :sql_last_value AND update_time < NOW()) ORDER BY update_time ASC"
    record_last_run => true
    clean_run => true
    tracking_column_type => "numeric"
    tracking_column => "unix_ts_in_secs"
    use_column_value => true
    last_run_metadata_path => "/etc/logstash/data/student"
    schedule => "*/5 * * * * *"
  }
}

output {
  elasticsearch {
    hosts => ["http://${ses manager node1 ip}:9200", "http://${ses manager node2 ip}:9200"]
    index => "cdss-%{+YYYY.MM.dd}"
  }
}
            • When using the OpenSearch version
              # When installing in the /root/ path, the {installation path} is /root/logstash-7.16.3. 
mv {installation path}/config/logstash-sample.conf {installation path}/config/logstash.conf
vi {installation path}/config/logstash.conf
            • OpenSearch version logstash.conf
              input {
   jdbc {
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_driver_library => "/etc/logstash/plugin/mysql-connector-java-8.0.30.jar"
    jdbc_connection_string => "jdbc:mysql://${cdb mysql endpoint}:3306/${cdb mysql database name}?useSSL=false"
    jdbc_user => "${cdb user name}"
    jdbc_password => "${cdb mysql password}"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_paging_enabled => true
    jdbc_page_size => 50
    statement => "SELECT *, UNIX_TIMESTAMP(update_time) AS unix_ts_in_secs FROM logstash_test WHERE (UNIX_TIMESTAMP(update_time) > :sql_last_value AND update_time < NOW()) ORDER BY update_time ASC"
    record_last_run => true
    clean_run => true
    tracking_column_type => "numeric"
    tracking_column => "unix_ts_in_secs"
    use_column_value => true
    last_run_metadata_path => "/etc/logstash/data/student"
    schedule => "*/5 * * * * *"
  }
}

output {
  opensearch {
    hosts => ["https://${ses manager node1 ip}:9200", "https://${ses manager node2 ip}:9200"]
    index => "cdss-%{+YYYY.MM.dd}"
    user => ${userID}
    password => ${password}
    ssl_certificate_verification => false
  }
}
            • Logstash Conf Comment
              In the Sample Logstash Conf file above, the "statement" is executed every 5 seconds.
The "statement" is a query that selects rows updated more recently than the update_time of the most recently searched row.
${cdb mysql endpoint} - Change to the private domain of CDB MySQL.
${cdb mysql database name} - Enter the account name you created in CDB MySQL.
${cdb user name} - Enter the database name you created in CDB MySQL.
${cdb mysql password} - Enter the password for the account you created in CDB MySQL.
${ses manager node1 ip} - Enter the Search Engine Service manager node’s IP address.
${ses manager node2 ip} - Enter the Search Engine Service manager node’s IP address (Do not enter if the manager node is not redundant).
${userID} - For OpenSearch, the ID you entered when creating the cluster.
${password} - For OpenSearch, the password you entered when creating the cluster.
            • Create logstash metadata path
              mkdir /etc/logstash/data
chown -R logstash:logstash /etc/logstash/data

          Set Cloud DB for MySQL

          An example of setting Cloud DB for MySQL is as follows:

          • Create table
            create table logstash_test(
id BIGINT(20) UNSIGNED NOT NULL,
PRIMARY KEY (id),
contents nvarchar(255),
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
          • Enter data
            insert into logstash_test(id, contents) values(1, "this is first contents");
insert into logstash_test(id, contents) values(2, "this is second contents");

          Run Logstash

          An example of running Logstash is as follows:

          # When using Elasticsearch version
systemctl start logstash

# When using OpenSearch version
# Use nohup to run in the background.
# Specify the path for logstash.conf using the -f option
nohup {installation path}/bin/logstash -f ~{installation path}/config/logstash.conf &

          View Cloud DB for MySQL data

          An example of viewing data from Cloud DB for MySQL from the Search Engine Service is as follows:

          GET mysql-2022.08.08/_search

          {
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "mysql-2022.08.08",
        "_type" : "_doc",
        "_id" : "VNkwe4IBicE7MyrTCKIL",
        "_score" : 1.0,
        "_source" : {
          "contents" : "this is first contents",
          "update_time" : "2022-08-08T01:55:47.000Z",
          "unix_ts_in_secs" : 1659923747,
          "id" : 1,
          "create_time" : "2022-08-08T01:55:47.000Z",
          "@version" : "1",
          "@timestamp" : "2022-08-08T02:02:01.082Z"
        }
      },
      {
        "_index" : "mysql-2022.08.08",
        "_type" : "_doc",
        "_id" : "3yEwe4IBBeKbW6yXB2l_",
        "_score" : 1.0,
        "_source" : {
          "contents" : "this is second contents",
          "update_time" : "2022-08-08T01:59:05.000Z",
          "unix_ts_in_secs" : 1659923945,
          "id" : 2,
          "create_time" : "2022-08-08T01:59:05.000Z",
          "@version" : "1",
          "@timestamp" : "2022-08-08T02:02:01.093Z"
        }
      }
    ]
  }
}
          Was this article helpful?
          What's Next
          Change password!
          Changing your password will log you out immediately. Use the new password to log back in.
          Change profile
          Success!
          First name must have atleast 2 characters. Numbers and special characters are not allowed.
          Last name must have atleast 1 characters. Numbers and special characters are not allowed.
          Enter a valid email
          Enter a valid password
          Your profile has been successfully updated.
          Logout