MySQL to MongoDB: How to Move Your Data

Moving MySQL into MongoDB usually serves an application rather than an analysis. A service that needs a customer and everything attached to them in one fetch does badly against a relational schema spread over six tables, and well against a document holding the lot.

This guide covers the managed path with Airbyte. Two things shape the build: the initial snapshot can lock tables depending on your storage engine, and change capture through the binlog is what keeps the copy honest, including when rows are deleted.

MySQL to MongoDB at a glance:

Why move data from MySQL to MongoDB?

Two situations account for most of these pipelines.

The first is serving an application that wants whole objects. Assembling a customer, their orders and their preferences from six tables on every request is work a document store does once at write time instead, and the read becomes a single fetch by key.

The second is migration, where an application is moving from relational to document storage and needs both in step during the transition. What this is not is an analytics move, since MongoDB aggregates poorly compared with anything columnar, and MySQL to ClickHouse is the better answer if somebody wants to count things.

What do you need before you start?

Four things, and two of them concern the first sync rather than the steady state:

A user with the three required grants. SELECT, REPLICATION CLIENT and REPLICATION SLAVE together allow change capture from the binlog. The MySQL source documentation covers the grants and the server settings.

Binlog retention set sensibly. On RDS it defaults to zero, and Airbyte recommends 168 hours. Without that, any pause leaves nothing to resume from and the recovery is a full resnapshot.

A note of which tables use MyISAM. The initial snapshot can lock those, which on a live application is an outage rather than a slow query. InnoDB tables are unaffected, and most modern schemas are entirely InnoDB.

A document design and an index plan. Decide what one document contains before you sync anything, and remember the pipeline creates collections without creating any indexes.

If your database or cluster restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow lists before you begin.

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

Step 1: Check your storage engines and retention before the first sync

Two queries against the information schema tell you which tables are MyISAM and what your binlog retention is. The first matters because a snapshot that locks a MyISAM table on a live system is an incident, so schedule that sync accordingly or convert the table. The second matters because a retention of zero turns any future pause into a full resnapshot of the same tables you were careful about.

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 and credentials, and choose change capture. If your documents should combine several tables, select a view that performs the join rather than the underlying tables, since MongoDB will not join anything for you.

Step 3: Configure the MongoDB destination

Click Destinations, then New Destination, and select MongoDB, following adding a destination. Supply the connection string, database and credentials. Documents arrive with metadata fields the destination adds, so application code should request fields by name rather than assuming a document contains only what MySQL held.

Step 4: Create the connection, then build the indexes

Click Connections, then New connection, select your streams and a sync mode. Once the first sync completes, index the fields your application queries by before anything is pointed at the collection, because without them every lookup is a scan and the reason you chose this destination has gone.

Alert on sync failure, since a pipeline down longer than your binlog retention cannot resume and needs the whole snapshot again.

What happens during the initial snapshot?

The connector reads every selected table in full before switching to reading changes, and how disruptive that is depends on your storage engine. InnoDB handles it without blocking writes, which covers most modern schemas. MyISAM tables are the exception, and a snapshot against one can lock it while it runs.

On a live application that is not a performance concern but an availability one, since writes to that table wait. Older schemas are the usual place to find MyISAM, often a table somebody created years ago and nobody has revisited, which means the risk is concentrated exactly where nobody is looking.

Check before you sync rather than after, and if you find MyISAM tables in scope you have three options: convert them to InnoDB, run the first sync during a quiet window, or exclude them if the application does not need them in MongoDB. All three are reasonable and all three are considerably better than discovering the problem at eleven on a Tuesday morning.

Why is change capture worth the setup here?

Because it brings deletes, and a document store serving an application cannot afford to miss them. Cursor-based reading sees inserts and updates only, so a customer deleted in MySQL stays in MongoDB forever, and the application keeps serving a record the source system says does not exist. Binlog change capture removes it properly.

That matters more for this destination than for an analytical one. An extra row in a warehouse table is an annoyance somebody filters out; a document that should not exist is served to a user, appears in search results and may well be a record somebody formally asked to have deleted. The grants and the retention setting buy you correctness rather than convenience.

The price is the setup. Three grants, a retention parameter that defaults unhelpfully on RDS, and a first sync to plan around. Set retention to 168 hours so a weekend outage is recoverable, alert on failure so nobody discovers a week later that the pipeline stopped, and remember that resuming past the retention window means the snapshot again, including any MyISAM tables you were careful about the first time.

Frequently asked questions

Will deleted rows disappear from MongoDB?

With binlog change capture, yes. Cursor-based reading sees only inserts and updates, which leaves documents behind that the source no longer has.

Could the first sync affect my application?

If any selected table uses MyISAM, the snapshot can lock it. Check your storage engines first and either convert, schedule carefully or exclude those tables.

Why can the pipeline not resume after a pause?

Binlog retention is probably zero, which is the RDS default. Set it to 168 hours, and expect a full resnapshot to recover this time.

How do I combine several tables into one document?

Build a view in MySQL that performs the join and sync that, since MongoDB will not join collections for you at read time.

Can I do this without writing code?

The pipeline, yes, though the grants and retention setting are database administration. The view defining a document and the indexes serving it are yours to write.

Get your MySQL data into MongoDB

Check your storage engines and binlog retention before the first sync, because a MyISAM lock is an availability problem and a retention of zero turns any pause into a resnapshot. Use change capture rather than cursor-based reading, since deletes reaching a document store is correctness rather than tidiness. Shape documents with a view in MySQL, and build your indexes the moment the first sync finishes.

Airbyte's connector catalog includes 600+ pre-built connectors, so application data can move between storage models without a bespoke migration. For the same source into another relational database, see MySQL to PostgreSQL, and for another relational source into the same destination, PostgreSQL to MongoDB.

Start syncing now →