Elastic Stack (ELK) and MySQL on Docker

Learn how to synchronize data from MySQL to Elasticsearch using Logstash and JDBC.

Run Services

Docker Compose is used to run Elastic Stack (ELK) and MySQL as multi-container Docker applications.

• To start the services, run
  • $ docker-compose up, or
  • $ docker-compose up -d [service1, service2, ...]
• To stop the services (without deleting states), run
  • $ docker-compose stop, or
  • $ docker-compose stop [service1, service2, ...]
• To remove containers together with saved states, run
  • $ docker-compose down -v

After everything is up,

• Open http://localhost:5601/app/dev_tools#/console.
• Log in with username: elastic and password: changeme.
• Type GET /temp_index/_search to query the temp_index on Elasticsearch.

Things to Note

• Access Services on Localhost
  • You can access ELK services by their ports.
  • MySQL can be accessed using MySQL Workbench with a new connection. You might need to stop MySQL services on your host machine to prevent port conflicts.
• Save Changes
  • Data of Elasticsearch and MySQL will be persisted on the host machine using volumes (managed by Docker).
  • In other words, all states will be saved and you can continue where you left off on next restart.
  • Kibana's dashboards will be saved in Elasticsearch.
  • Logstash only uses config files and does not have any states.
• Just In Case
  • If something goes wrong, you might need to bring down the services, rebuild the images and restart the containers. Just run

    $ docker-compose down -v
    $ docker-compose build --no-cache
    $ docker-compose up

Basic Configurations

• Specify versions of Elastic Stack (ELK) and MySQL in .env
• MySQL:
  • mysql/config/mysql.cnf
  • Save the config file as read-only or else configurations will fail.
• Elasticsearch: elasticsearch/config/elasticsearch.yml
• Logstash: logstash/config/logstash.yml
• Kibana: kibana/config/kibana.yml
• Refer to docker-compose.yml for configurations like username, password, ports, etc.

MySQL

• Database Credentials
  • Username: system
  • Password: admin123
• By default, temp_db and temp_table, which is populated with some dummy data, have been created.
• Write SQL scripts and store in mysql/sql to initialize database with new tables and populate data into the tables.
• Initialization of MySQL only takes place during the fresh installation. If you want to change the default database or modify existing SQL scripts, you must remove the saved states and rebuild the MySQL image. To achieve this, run

  $ docker volume rm docker-mysql-elk_mysql
  $ docker-compose build --no-cache mysql

Elasticsearch

• Login Credentials
  • Username: elastic
  • Password: changeme
• xpack.license.self_generated.type: basic (in elasticsearch/config/elasticsearch.yml)
  • By default, only basic X-Pack features are enabled.
  • Switch to xpack.license.self_generated.type: trial for paid features with a 30-day free trial.

Logstash

• Pipeline
  • init_temp_table.conf is used to load existing data from MySQL to Elasticsearch. It is only run once.
  • sync_temp_table.conf is used to sync newly inserted/updated data between MySQL and Elasticsearch. It is configured to run every 5 seconds. The sync frequency can be configured using cron syntax. See schedule settings in sync_temp_table.conf.
• When adding a new pipeline:
  • Write new config file and store it in logstash/pipeline/conf.
  • Write new SQL statement and store it in logstash/pipeline/sql. Link the .sql file path to statement_filepath in your config file. See sync_temp_table.conf for reference.
  • Remember to specify pipeline.id and path.config in logstash/pipeline/pipelines.yml.

References

• GitHub: Elastic stack (ELK) on Docker
• Elastic: How to keep Elasticsearch synchronized with a relational database using Logstash and JDBC
• Towards Data Science: How to synchronize Elasticsearch with MySQL
• Docker Hub: MySQL configurations
• Elastic Docs: Install Elasticsearch with Docker
• Elastic Docs: Configuring Logstash for Docker
• Elastic Docs: Install Kibana with Docker