How to Migrate a MongoDB Database to MySQL with Minimal Downtime

Background

The author, a Rails developer, spent a month migrating a production system that originally used MySQL to MongoDB. After experiencing instability and schema‑less data corruption, the team decided to move back to MySQL using ActiveRecord migrations to enforce strong data constraints.

From Relational to Document (MySQL → MongoDB)

Exporting MySQL tables as CSV and importing them with mongoimport is straightforward, but the author notes many pitfalls when doing it in practice.

From Document to Relational (MongoDB → MySQL)

Moving data back is considerably more complex because MongoDB stores embedded documents, arrays, and hashes that have no direct equivalents in MySQL.

Data Pre‑processing

All embedded relationships ( embeds_many and embeds_one) must be flattened to reference relationships ( has_many and has_one). The following Ruby script converts embedded documents to separate records and adds foreign keys:

def embeds_many_to_has_many(parent, child)
  child_key_name = child.to_s.underscore.pluralize
  parent.collection.find({}).each do |parent_document|
    next unless parent_document[child_key_name]
    parent_document[child_key_name].each do |child_document|
      new_child = child_document.merge "#{parent.to_s.underscore}_id": parent_document['_id']
      child.collection.insert_one new_child
    end
  end
  parent.all.unset(child_key_name.to_sym)
end

embeds_many_to_has_many(Person, Address)

After flattening, add indexes on the new foreign‑key columns to avoid full‑table scans.

Handling Arrays and Hashes

Arrays can be stored as delimited strings or separate one‑to‑many tables; hashes can become JSON columns (if the target MySQL version supports JSON) or simple key‑value tables. Example model adjustments illustrate how to keep the original API while changing the underlying storage.

class Post
  ...
  def tag_titles
    tags.map(&:title)
  end

  def split_categories
    categories.split(',')
  end
end

Mongoid Plugins

Plugins such as mongoid‑enum, mongoid‑slug, and mongoid‑history store data differently from their ActiveRecord counterparts. During migration, slug fields are ignored, and enum fields are remapped from strings to integers.

Primary Keys and UUIDs

MongoDB uses 32‑byte ObjectId values, which are unwieldy in MySQL. The migration adds a uuid string column (unique index) to every table and creates corresponding *_uuid foreign‑key columns (e.g., post_uuid). The original _id values are mapped to these UUIDs.

class AddUuidColumns < ActiveRecord::Migration[5.1]
  def change
    Rails.application.eager_load!
    ActiveRecord::Base.descendants.each do |klass|
      add_column klass.table_name, :uuid, :string, unique: true
      add_index  klass.table_name, :uuid, unique: true
      # add *_uuid columns for each *_id attribute
      klass.attribute_names.select { |a| a.include?('_id') }.each do |attr|
        add_column klass.table_name, attr.sub('_id', '_uuid'), :string
      end
    end
  end
end

Code Migration

All models change their superclass from Mongoid::Document to ActiveRecord::Base. Corresponding migration files are created for each table, including the new uuid column and any necessary indexes.

# app/models/post.rb
class Post < ActiveRecord::Base
  validates :title, :content, presence: true
end

# db/migrate/20170908075625_create_posts.rb
class CreatePosts < ActiveRecord::Migration[5.1]
  def change
    create_table :posts do |t|
      t.string :title, null: false
      t.text   :content, null: false
      t.string :uuid, null: false
      t.timestamps null: false
    end
    add_index :posts, :uuid, unique: true
  end
end

Data Migration Script

The DatabaseTransformer module iterates over each MongoDB collection, removes obsolete columns, renames columns, converts _id to uuid, and inserts records into MySQL using ActiveRecord models. Callbacks are disabled during the import to speed up the process.

module DatabaseTransformer
  def import(collection_name, *obsolete_columns, **rename_columns)
    collection = Mongoid::Clients.default.collections.find { |c| c.namespace == "#{database}.#{collection_name.to_s.pluralize}" }.first
    return unless collection
    constant = collection_name.to_s.singularize.camelcase.constantize
    reset_callbacks constant
    collection.find.each_with_index do |doc, index|
      doc = yield doc if block_given?
      delete_obsolete_columns doc, obsolete_columns
      update_rename_columns   doc, rename_columns
      update_id_columns       doc
      insert_record constant, doc
      puts "#{index}/#{collection.find.count}" if (index % 1000).zero?
    end
  end
end

Relation Rebuilding

After data is loaded, the RelationBuilder module scans each model for *_uuid attributes, looks up the corresponding records, and updates the foreign‑key columns ( *_id) to restore proper ActiveRecord associations.

module RelationBuilder
  def build_relations(class_name)
    uuids = class_name.attribute_names.select { |n| n.end_with?('_uuid') }
    return if uuids.empty?
    reset_callbacks class_name
    class_name.find_each do |model|
      updates = uuids.map do |uuid|
        assoc_name = uuid[0...-5]
        assoc_model = find_by_uuid(model[uuid])
        [assoc_name + '_id', assoc_model.id] if assoc_model
      end.compact.to_h
      model.update_columns updates
    end
  end
end

Many‑to‑Many Relationships

Mongoid’s has_and_belongs_to_many stores an array of ObjectIds (e.g., tag_ids). In MySQL, a join table (e.g., posts_tags) is required. After importing the join table data, the script maps post_uuid and tag_uuid to the newly created post_id and tag_id columns.

PostsTag.all.each do |jt|
  post = Post.find_by_uuid jt.post_uuid
  tag  = Tag.find_by_uuid jt.tag_uuid
  next unless post && tag
  jt.update_columns post_id: post.id, tag_id: tag.id
end

Testing Importance

Before a production cut‑over, the team should run partial migrations on a scaled‑down MongoDB dump (1/10 or 1/100 of production data) and then a full‑scale migration in a staging environment. Comprehensive unit and integration tests are essential to catch data‑loss bugs during the extensive code refactoring.

Conclusion

Migrating from MongoDB to MySQL is an engineering effort that requires careful data preprocessing, UUID handling, codebase conversion, automated data import, relationship rebuilding, and thorough testing to keep downtime under two hours for a million‑row dataset. The approach can be adapted for larger datasets by employing online migration techniques.