TL;DR: Many organizations store transactional data in MySQL while search logs and event data live in Elasticsearch. Analyzing these datasets together is often difficult because they exist in separate systems. With Bold Data Hub, you can integrate MySQL and Elasticsearch into a unified dataset and build interactive dashboards in Bold BI® without writing complex data pipelines.
Introduction
Managing data across multiple systems is a common challenge for developers. Key information often lives in MySQL, while logs, search activity, and system events sit separately in Elasticsearch. Without combining these datasets, teams often rely on manual exports, custom scripts, or complex data pipelines. This is where MySQL and Elasticsearch integration are needed.
By integrating MySQL and Elasticsearch sources, organizations can analyze operational data and search behavior together, creating a complete view of business performance.
In this blog, you’ll learn how to use Bold Data Hub to connect MySQL and Elasticsearch, combine them into a unified dataset, and publish the result in Bold BI for efficient data visualization. Let’s get started.
Benefits of integrating MySQL and Elasticsearch data for analytics
Combining MySQL and Elasticsearch data in one dashboard provides the following benefits:
- Better search and transaction analysis: By combining MySQL transactional data with Elasticsearch search logs, teams can analyze how users search for products and how those searches translate into purchases.
- Unified analytics across operational and search data: MySQL stores structured business data such as orders and users, while Elasticsearch stores indexed search or event data. Integrating both allows dashboards to correlate operational metrics with user behavior.
- Reduced manual data pipelines: Without integration tools, developers often export MySQL data and combine it with Elasticsearch using custom scripts. Bold Data Hub simplifies this by combining both sources in a single pipeline.
- Build a performance monitor dashboard: Once the datasets are combined, you can create dashboards with real-time insights without writing custom scripts.
Challenges of integrating MySQL and Elasticsearch
- Keeping both systems synchronized becomes difficult as data updates occur, causing inconsistencies between stored and indexed data.
- Mapping and schema mismatches arise as structures differ, creating indexing issues and unpredictable search results.
- Sync failures happen when pipelines break or delays occur, resulting in missing, outdated, or incomplete records.
- Query translation is required because SQL logic doesn’t align with Elasticsearch, increasing development time and complexity.
How Bold Data Hub simplifies data integration
- Unified data connectivity brings all your sources together, making it easy to connect databases, files, cloud apps, and APIs from one place.
- Automated ETL workflows handle data movement from end to end, ensuring accurate and consistent processing without manual effort.
- Powerful data transformation lets you clean, format, and reshape information using clear, customizable rules.
- Centralized monitoring and management give full visibility into every pipeline, making performance and data quality easy to track.
Now that you understand how Bold Data Hub simplifies data integration, let’s explore how to connect MySQL and Elasticsearch to the Bold Data Hub.
Integrate MySQL with Elasticsearch data using Bold Data Hub
Bold Data Hub lets you easily combine MySQL and Elasticsearch data in one workspace, enabling unified visualizations and reducing manual preparation.
Step 1: Prepare the data sources
Before integration, ensure that:
- MySQL database contains the required tables (for example: user data).
- Elasticsearch contains relevant indexes (for example: feedback data).
- Both datasets include a common field that can be used as a join key.
[caption id="" align="alignnone" width="1484"]
Sample data in MySQL[/caption]
[caption id="" align="alignnone" width="1484"]
Sample data in MySQL[/caption]
This field allows datasets from MySQL and Elasticsearch to be linked together.
Step 2: Create a data pipeline in Bold Data Hub
- Open Bold BI and launch Bold Data Hub.
[caption id="" align="alignnone" width="1484"]
Click the Bold Data Hub icon[/caption] - Next, select the plus (+) icon to create a pipeline. In this tutorial, the pipeline is named elasticsearch_mysql to represent the integration between Elasticsearch and MySQL.
[caption id="" align="alignnone" width="1484"]
Create the elasticsearch-mysql pipeline [/caption] - Add the MySQL data and configure it with your host, port, database, and credentials to extract the required tables. Example configuration:
version: 1.0.1 plugins: extractors: - name: mysql connectorname: MySQL config: host: localhost port: 3306 database: sales_db username: root password: ********
This configuration extracts transactional data from the MySQL database.
[caption id="" align="alignnone" width="1484"]
Extract MySQl data in Bold Data Hub[/caption]
Step 3: Add Elasticsearch as a data source
Next, configure the Elasticsearch connector inside the same pipeline with your host, port, database, and credentials to extract the required tables.
Example configuration:
version: 1.0.1 plugins: extractors: name: elastic_search connectorname: Elasticsearch config: host: http://localhost:9200 index: product_feedback username: elastic password: ********
This allows the pipeline to retrieve search or event data stored in Elasticsearch.
[caption id="" align="alignnone" width="1484"]
Add Elasticsearch data in Bold Data Hub[/caption]
Step 4: Save the datasets
- After combining the MySQL and Elasticsearch datasets in the pipeline, select Save to store the unified configuration.
[caption id="" align="alignnone" width="1484"]
Click the save button[/caption]
- Once saved, a window will appear displaying the newly combined data source, confirming that the integration has been successfully created and is ready for use in the Data Source page.
[caption id="" align="alignnone" width="1484"]
Data source listing page[/caption]
With the data source, you can join the tables from both MySQL and Elasticsearch to unify the information into a single, consistent structure.
Next, we will learn how to perform these joins using tables.
Joining tables
Joining tables allows you to combine information from different sources into a single analytical view. This makes it possible to correlate transaction data stored in MySQL with search or event data stored in Elasticsearch, enabling deeper analysis across systems.
The steps below show you how to create the join.
Step 1: Open the data source editor
Open the action menu for the created data source and select Edit Data Source.
[caption id="" align="alignnone" width="1484"]
Edit the data source[/caption]
Step 2: Identify the common fields
The design area opens automatically. Drag and drop the tables from the left panel into the design area. In this case, we dragged products and sales_feedback tables.
[caption id="" align="alignnone" width="1484"]
Identify common fields[/caption]
Step 3: Define the join condition
Once you drag and drop the second table into the designer, the query joiner window will open. In the Query Joiner window, define a join using a key.
Example:
mysql.products.product_id = elasticsearch.feedback_id
After configuring the join condition, select Update to apply the changes.
[caption id="" align="alignnone" width="1484"]
Query Joiner window[/caption]
Step 4: Preview the combined datasets
Once the join is applied, the design page automatically displays a preview of the merged dataset. This allows you to verify that the data from both sources has been combined correctly.
[caption id="" align="alignnone" width="1484"]
Preview of the combined datasets[/caption]
Next, let’s explore the key metrics you can build and visualize in the interactive dashboard.
How to create a dashboard using MySQL and Elasticsearch data
New users can begin by exploring our video on how to create a dashboard in Bold BI, which provides a quick overview of the dashboard-building process.
After connecting both MySQL and Elasticsearch as data sources, the next step is to blend the datasets to define meaningful metrics and KPIs. With the combined data, you can build a dashboard that highlights insights such as:
- Total purchases.
- Average render time.
- Sales payment by type and browser.
- Abandoned carts.
- Abandoned revenue.
- Products sold breakdown.
- Page visits by traffic and device type.
These insights help organizations understand both customer intent and business outcomes. With Bold BI, you can easily transform the integrated dataset into interactive dashboards for decision-making.
[caption id="" align="alignnone" width="1484"]
Preview of the dashboard[/caption]
Integrating MySQL with Elasticsearch data enables users to enjoy faster searches, improved data analysis, and more efficient access to insights from large datasets. To learn tips for designing effective dashboards, explore our article on 10 dashboard design best practices for insights.
[elementor-template id="150021"]
Start building unified analytics today
Integrating MySQL and Elasticsearch enables organizations to combine structured business data with advanced. With Bold Data Hub, developers can build a unified data pipeline without writing complex ETL workflows. Once the data is integrated, Bold BI dashboards make it easy to explore trends, monitor performance, and share insights across teams.
If you want to simplify MySQL and Elasticsearch integration and build powerful analytics dashboards, try Bold BI® today. Start your 30-day free trial or request a personalized demo to see how Bold BI and Bold Data Hub can help your team turn scattered data into actionable insights.
Frequently asked questions
-
1.
What data sources does Bold Data Hub support?
Bold Data Hub supports a wide range of data sources, including files, web APIs, SQL databases, and NoSQL systems. This includes popular options such as MySQL and Elasticsearch.
-
2.
Do I need any technical expertise to use Bold Data Hub?
No advanced technical expertise is required. A basic understanding of the data connectors you plan to use is sufficient to get started.
-
3.
Does Bold Data Hub offer additional features?
Yes. You can configure multiple datastores of the same server type and load data into them for different projects. It also includes an Open API connector and a Python connector for more advanced use cases.
-
4.
Do I need Bold Data Hub to use Bold BI?
No. Bold BI can connect directly to many supported data sources. Bold Data Hub is optional and is especially useful when you need to prepare, clean, or combine data, such as blending MySQL and Elasticsearch data, before building dashboards.
