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,

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
```

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.

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
```

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.

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.