6 Easy Step To Monitor Debezium MySQL Connector Metrics using Grafana

Debezium is the most popular data monitoring tool that traces or captures every row-level modification made on the external database servers. It continuously captures and streams real-time changes from database systems into Kafka servers. Since data-change capture and streaming is a continuous and automatic operation, sometimes, if Kafka servers are highly loaded or held up with any connection issues, the data streaming will be interrupted, thereby causing bottlenecks and data congestion.

To eliminate such complications, you can use a monitoring tool that continuously traces and records what is happening within the application environment. One such continuous monitoring tool is Grafana, which helps you visualize real-time changes or metrics when connected to supportive data sources.

In this article, you will learn about Debezium, Grafana, and how to monitor Debezium MySQL connector metrics using Grafana.

Prerequisites

• Fundamentals of real-time data streaming.

What is Debezium?

Debezium is an open-source and distributed data monitoring tool that is mainly developed for implementing CDC (Change Data Capture) operations. In other words, Debezium allows you to capture and stream real-time modifications made on external database systems like Oracle, MySQL, and PostgreSQL.

Debezium has a vast set of connectors in which each connector establishes a connection with the respective external database systems to capture and stream real-time changes. In addition, Debezium streams all the real-time changes made on the databases to Kafka servers since it is built on top of the Kafka environment.

What is Grafana?

Developed by Torkel Ödegaard in 2014, Grafana is an open-source and web-based tool that allows you to visualize data or application metrics. It is a multiplatform and interactive visualization tool that enables you to generate metric insights in the form of graphs, charts, and dashboards.

With Grafana, you can flexibly transform your numeric metric data into efficient graphical formats like heatmaps, histograms, and pie charts. Furthermore, Grafana also notifies you with alert messages or notifications when certain thresholds are reached in your application since it is a continuous monitoring tool. 

Setting Up Grafana to Monitor MySQL Connector Metrics

For setting up the Grafana environment to monitor MySQL connector metrics, you have to install and configure the JMX (Java Management Extensions) metrics exporter. JMX exporter is a Java application that reads or collects JMX metrics from JVM-based applications. In other words, with JMX exporter, developers can read Java application metrics like run time statistics, memory usage, and much more. 

The MySQL connector metrics fetched from the third-party database applications can be further exported into metrics monitoring tools like Grafana and Prometheus. Such metric monitoring tools help you analyze, visualize, and report metric data fetched from respective external database applications.

Furthermore, using the metric monitoring application, you can create interactive dashboards that include rich charts, graphs, and analytic models based on metrics captured using external database connectors.

In this case, for monitoring Debezium MySQL connector metrics, you can use the JMX metrics exporter that is offered by Kafka, Zookeeper, and Kafka connect instances. In addition, each Debezium connectors like MySQL, PostgreSQL, and Oracle have their monitoring metrics.

You can enable and activate the metric characteristics while starting the Zookeeper, Kafka, and Kafka Connect services. 

In the further steps, you will start the Kafka environment to enable the JMX exporter agent and monitor the MySQL connector metrics. Kafka environment can be started locally on your local machine with default Apache Kafka installation or Docker containers. 

MySQL Connector Metrics Setup: Enabling JMX Exporter Locally

The JMX exporter can be locally enabled while starting each of the Kafka services that belong to the Kafka environment. You can easily enable JMX by customizing the appropriate environment variables of each service like Zookeeper, Kafka, and Kafka Connect instances. Since JMX exporter is started and enabled while starting the Kafka and Zookeeper instances, you should initially set up a Kafka environment with one Kafka server and Zookeeper instances. 

• Step 1: Before enabling the JMX exporter, ensure that the Java 8+ version is installed and running on your local machine. You have to also configure the file path and Java_Home environment variables for enabling your operating system to locate the Java utilities, thereby allowing the Kafka environment to run correctly.
• Step 2: Initially, open the zookeeper-server-start.sh script file with your preferred editor and edit the arguments as given below.

$ export EXTRA_ARGS="-javaagent:/opt/jmx-exporter/jmx-exporter.jar=7070:/etc/jmx-exporter/zookeeper.yml"

• Step 3: Now, open a new command prompt and execute the following command to enable and start the JMX exporter and Zookeeper, respectively.

$ /opt/kafka_2.11-0.10.1.0/bin/zookeeper-server-start.sh /opt/kafka_2.11-0.10.1.0/config/zookeeper.properties

• Step 4: The above-given command is applicable when you use a distributed Zookeeper instance. 
• Step 5: If you use a standalone Zookeeper, open the zookeeper-env.sh script file with the editor tool and edit the arguments as given below, and then run the receptive standalone Zookeeper instance.

SERVER_JVMFLAGS="-javaagent:/opt/jmx-exporter/jmx-exporter.jar=7070:/etc/jmx-exporter/zookeeper.yml"

• Step 6: After enabling the JMX exporter with the Zookeeper instance, you are now ready to start the Kafka server.
• Step 7: Now, open the Kafka-server-start.sh script file and set the environment variable KAFKA_OPTS as given below.

$ export KAFKA_OPTS='-javaagent:/opt/jmx-exporter/jmx-exporter.jar=7071:/etc/jmx-exporter/kafka.yml'

• Step 8: Then, open a new command prompt and execute the following command to start the Kafka server.

$ /opt/kafka_2.11-0.10.1.0/bin/kafka-server-start.sh /opt/kafka_2.11-0.10.1.0/conf/server.properties

• Step 9: In the next step for MySQL connector metrics, you have to configure the binary file of Kafka Connect for setting up the Kafka Connect instance. Open the Kafka connect script file and add the JMX export arguments, as shown below.

exec $(dirname $0)/kafka-run-class $EXTRA_ARGS -javaagent:/opt/jmx/jmx-exporter.jar=7071:/opt/jmx/config.yml org.apache.kafka.connect.cli.ConnectDistributed "$@"

• Step 10: To confirm whether the JMX exported is adequately enabled, restart the connection service by running the following command.

systemctl restart confluent-connect-distributed

• Step 11: For viewing the status of the JMX exporter agent, execute the command as given below.

netstat -tulpn | grep 7071

• Step 12: On executing the above command, you get the output that resembles the following image.

MySQL Connector Metrics Setup: Enabling JMX Exporter Using Docker

• Step 1: To enable the JMX exporter in Docker, you should already have Docker setup on your local machine. 
• Step 2: Execute the following command to activate the JMX exporter in a Zookeeper Docker Container.

$ docker run -it --rm --name zookeeper -p 2181:2181 -p 2888:2888 -p 3888:3888 -p 9010:9010 -e JMXPORT=9010 -e JMXHOST=10.0.1.10 debezium/zookeeper:latest

• Step 3: In the above command, the debezium/zookeeper image is used to start a Docker container running a Zookeeper instance at port 2181.
• Step 4: Now, run the command below to enable JMX exporter in a Kafka Docker container.

$ docker run -it --rm --name kafka -p 9092:9092 -p 9011:9011 -e JMXPORT=9011 -e JMXHOST=10.0.1.10 --link zookeeper:zookeeper debezium/kafka:latest

• Step 5: In the above command, debezium/Kafka image is used to start a Docker container running a Kafka server at port 9092.
• Step 6: With the Docker container, you can also start the Kafka Connect service, which can be used to establish connections with external applications. Execute the following command to run the Kafka Connect in Docker container.

• Step 7: On executing the above commands, you successfully started the Kafka environment using the Docker containers.

MySQL Connector Metrics Setup: Getting the Debezium Metrics

• As you are ready with the JMX exporter, you can fetch Debezium metrics of the respective Debezium-database connector. In this case, execute the following command to fetch or get the metrics of the MySQL connector.

curl localhost:7071 | grep debezium :-debezium_metrics_NumberOfDisconnects{context="binlog",name="mysql-db01",plugin="mysql",} 0.

MySQL Connector Metrics Setup: Installing Prometheus

Prometheus is an open-source monitoring solution that stores and manages real-time metrics data that can be further used by any external data processing application. Since Grafana visualizes the real-time metric data stored in Prometheus, you have to install and configure the respective application.

• Step 1: Initially, you have to create a new user for Prometheus by executing the following command.

sudo useradd --no-create-home --shell /bin/false prometheus

• Step 2: Then, run the command given below to create directories in Prometheus.

• Step 3: Now, download the Prometheus binary files by executing the following command.

• Step 4: After downloading the binary files, copy them to the respective directories, as shown in the below image.

• Step 5: Then, create a Prometheus configuration file (.yml) to monitor metrics, as shown in the following image.

• Step 6: Now, set permission or access to the Prometheus configuration file by executing the following command.

sudo chown prometheus:prometheus /etc/prometheus/prometheus.yml

• Step 7: In the next step, create a Prometheus systemctl file by following the codes given below.

• Step 8: Now, you are ready to start the Prometheus application. Execute the following commands to start the Prometheus service.

sudo systemctl daemon-reload

sudo systemctl start prometheus

sudo systemctl enable prometheus

• Step 9: Since you are about to monitor the Debezium MySQL connector metrics using Grafana, you must add the respective metrics to the Prometheus application.

• Step 10: Now, restart the Prometheus service to save all the necessary configurations that are made before.

sudo systemctl restart prometheus

• Step 11: After restarting, open the following URL given below to access the Web interface of Prometheus.

http://18.217.62.18:9090/targets

MySQL Connector Metrics Setup: Installing Grafana

• Step 1: As you successfully installed Prometheus, you now have to install and configure Grafana to make Prometheus a datasource or metric source. 

• Step 2: After executing the above command, Grafana is running at port 3000.
• Step 3: Follow the URL given below to access the web interface of Grafana.

http://grafana-server-ip:3000

• Step 4: On following the above URL, you are directed to the web console of Grafana that resembles the image given below.

MySQL Connector Metrics Setup: Monitoring Metrics

• Step 1: For the next step of MySQL connector metrics, you have to add the Prometheus data source. Click on the Settings icon and select Datasources. Then, select the “Add data source” button.

• Step 2: You will be prompted to enter the name of the data source and URL. Provide the name as Prometheus-1 and URL as localhost:9090. After filling in the details, click on the “Save and test button.” 

• Step 3: Now, go to the Grafana workspace and click on the Plus icon. Then, click on the Import button.
• Step 4: You will be prompted to import the JSON file that contains the necessary metrics. You can use the Debezium MySQL connector metrics’ JSON file available in the official repository of Debezium. 
• Step 5: Download the JSON file from the Github repository and upload it to the Grafana workspace. You can also copy the JSON content from the repository and paste it into the “Import via panel JSON” space.
• Step 6: After importing the JSON file, you will see a Grafana dashboard with graphs and charts that exhibit insights into MySQL connector metrics, as shown below.

Following the above-given steps, you successfully created a Grafana dashboard to monitor Debezium MySQL connector metrics.

Conclusion

In this article, you learned about Debezium, Grafana, enabling JMX exporter, and steps to monitor Debezium MySQL connector metrics. This article mainly covered the steps to capture and monitor the Debezium MySQL connector metrics using Grafana. However, you can also monitor and manage the metrics of any other Debezium database connectors like PostgreSQL, Oracle, and SQL Server connectors.

However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, Marketing Platforms to your MySQL Database can seem to be quite challenging. If you are from non-technical background or are new in the game of data warehouse and analytics, Hevo Data can help!

Visit our Website to Explore Hevo

Hevo Data will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 150+ multiple sources to Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!