MySQL to Elasticsearch: How to Move Your Data

Indexing MySQL into Elasticsearch is what you do when your application has outgrown LIKE queries. Full-text search, typo tolerance, relevance ranking and faceted filtering are all things a relational database does poorly and a search engine does well, and the usual first attempt is application code that writes to both on every save.

That dual-write approach fails quietly the first time one write succeeds and the other does not. This guide covers the managed path: replicating MySQL into Elasticsearch with Airbyte using change data capture. Before anything else, check one thing, because it decides whether this pipeline is available to you at all.

MySQL to Elasticsearch at a glance:

Why move data from MySQL to Elasticsearch?

Two situations account for most of these pipelines.

The first is product search. Your catalogue, your listings or your knowledge base live in MySQL, and users need to find things by typing approximately what they mean. Relevance scoring, stemming and fuzzy matching are not things you want to implement in SQL, and a search index keeps that workload off the database serving your transactions.

The second is operational search over records: support agents finding a customer by fragments of an address, or an internal tool searching across several fields at once. Both cases want current data rather than analytical history, which is why change data capture matters more here than sync frequency does.

What do you need before you start?

The first item can stop the project outright, so confirm it before designing anything:

A self-managed Airbyte deployment. The Elasticsearch destination is available on Airbyte Core and PyAirbyte, and not on the Standard, Plus, Pro or Enterprise Flex plans. If you are on a paid Cloud tier the connector will not appear in your catalogue, and everything below assumes self-managed.

Binary logging with generous retention. Change data capture reads the MySQL binary log. Around ten days, expressed as 864000 seconds, is the figure commonly used. On Amazon RDS the setting is different, called binlog retention hours, and it defaults to zero, which purges logs immediately and makes CDC impossible until you change it.

A dedicated read-only user. With replication permissions rather than an application account. Encryption is required by default and the connection fails if the source will not encrypt. The MySQL source documentation covers the available SSL modes.

A view that produces the document you want to search. This is the part people skip. A table row is usually a poor document, because the thing users search for is spread across a parent table and several children, and Elasticsearch does not join.

Finally, an index mapping you have designed rather than inherited. Decide which fields are analysed for full-text search and which are keyword fields for filtering and aggregation, before you index a few million documents under a mapping you then have to rebuild.

How do you build a MySQL to Elasticsearch pipeline in Airbyte?

Step 1: Build the document in MySQL

Create a view that joins whatever a search result needs into a single row: the product with its category name, brand and attributes, rather than four tables of identifiers. Denormalising here is cleaner than any alternative, because MySQL is good at joins and you end up with one document per business entity rather than one per row. Add an updated_at column maintained across the underlying tables if the view does not already have one.

Step 2: Configure the MySQL source

Click Sources in the left navigation, then New Source, and select MySQL, following adding a source. Supply the host, port, database, credentials and SSL mode, and choose Read Changes using Binary Log as the replication method so deletes are captured. Enable binary logging and set retention before this step, because a connection against a server with no binlog passes its credential test and then fails in a way that looks like a connector problem.

Step 3: Configure the Elasticsearch destination

Click Destinations, then New Destination, and select Elasticsearch, following adding a destination. Supply your endpoint and authentication. Create the index with your intended mapping first, so the connector writes into a schema you designed rather than one dynamic mapping invented on the first document.

Step 4: Create the connection and schedule it

Click Connections, then New connection, select your view and an incremental sync mode so the binlog is used rather than a full re-read. Set a frequency that reflects how quickly users expect new records to become findable, which for a product catalogue is usually minutes rather than hours, and for a knowledge base is usually the opposite.

The first sync performs a full snapshot before switching to reading changes. On a large table, confirm that snapshot completes inside your binlog retention window, because if it does not the connection loops through full snapshots indefinitely while reporting success each time.

Why does the index mapping matter so much?

Because Elasticsearch decides at index time how a field can be searched, and changing that decision later means reindexing everything. Dynamic mapping will guess from the first document it sees, and its guesses are reasonable rather than right.

The distinction that matters is analysed text versus keyword. A product name should be analysed, so that searching for running shoe matches Running Shoes. A status, a SKU or a category identifier should be a keyword field, so it can be filtered and aggregated exactly. Get these the wrong way round and your filters return nothing while your searches match everything.

Numeric and date fields from MySQL usually map cleanly, but check anything stored as a string in the source that should behave as a number or a date in the index, since dynamic mapping will take it at face value. Spending an hour on the mapping before the first sync is the single best-value hour in this build.

How do deletes behave in the index?

This is the main argument for using change data capture rather than a cursor here. A search index that returns deleted products is worse than one that is slightly stale, because a user clicks the result and lands on nothing. CDC reads the binlog, so deletions are captured and can propagate.

Deleted rows arrive carrying a deletion timestamp in a metadata column rather than vanishing. In a warehouse you would choose between mirroring current state and keeping history. In a search index there is no such tension: you want the document gone, so filter on that metadata column when querying, or handle removal in whatever layer sits between your application and the index.

One caveat if you took the view-based approach in step 1. Deleting a child row may change what the view produces without deleting the parent, so a document can need updating rather than removing. Work out what a deletion in each underlying table should mean for the document before you rely on this, because the binlog tells you a row went away and not what that implies for a joined result.

Frequently asked questions

Why can I not find the Elasticsearch destination in Airbyte Cloud?

It is available on Airbyte Core and PyAirbyte, and not on the Standard, Plus, Pro or Enterprise Flex plans. This pipeline needs a self-managed deployment.

Should I index one table or a joined view?

A joined view, almost always. Elasticsearch does not join, so build the document you want to search inside MySQL and index that as a single stream.

Why does my connection keep running full snapshots?

The binlog Airbyte needed was purged before it could resume. Extend retention, sync more often, or check whether the initial snapshot itself is taking longer than the retention window.

Do I need to define the index mapping myself?

Strongly recommended. Dynamic mapping guesses from the first document, and correcting an analysed versus keyword decision later means reindexing everything you have already written.

Can I do this without writing code?

The Airbyte setup is entirely UI-driven. You will want to write the MySQL view and the index mapping, both of which are short and both of which determine whether the result is any good.

Get your MySQL data into Elasticsearch

Check your Airbyte plan first, because that decides everything else. Then build the document in MySQL rather than hoping a table row will do, design the index mapping before the first sync, set binlog retention generously, and decide what a deletion in each underlying table should mean for the document it feeds.

Airbyte's connector catalog includes 600+ pre-built connectors, so the same MySQL source can feed a warehouse alongside the index. For that pairing, see MySQL to Snowflake, and for the same destination fed by another relational source, PostgreSQL to Elasticsearch.

Start syncing now →