MySQL to MongoDB: 5 Easy Steps to Replicate Data

The database is a crucial element to keep in mind for companies that are constantly seeking to pave the way in digital transformation. With every organization generating data like never before, companies have started to migrate to non-relational databases. MongoDB is a highly popular open-source non-relational database that is capable of storing large data sets efficiently. This article will provide you with a step-by-step guide to easily migrate data from MySQL to MongoDB.

MySQL is one of the most widely used open-source Relational Database Management Systems (RDMS) in the world. It is built to store and manage relational data in a tabular format in a normalized manner. However, relational databases have a hard time scaling as they require a lot of memory and compute power.

This blog piece could be resourceful if you are looking for information on the types of database models.

Non-relational databases, on the other hand, are developed to store hundreds of petabytes of data and run millions of queries per second. MongoDB supports a flexible schema approach and is capable of storing large quantities of unstructured and semi-structured data. Let’s discuss MySQL and MongoDB in brief before getting started with the process of data migration.

What is MySQL?

MySQL is one of the most popular Relational Database Management Systems used for managing Relational Databases. It uses the Structured Query Language (SQL) to define, update, and query the Database. SQL is the most widely used language for accessing and managing records in any relational database. Supported by Oracle, MySQL is an open-source and free database software under the GNU license. It is popularly used with PHP programming and other web applications.

MySQL is faster, highly scalable, and an easy-to-use Database Management System when compared to Microsoft SQL Server and Oracle Database. It is based on the Client-Server model, which means that the database typically runs on a server and the data is accessed over the network part clients and workstations. The server returns the desired output to the Graphical User Interface (GUI) requests sent by the clients. MySQL supports different types of Operating Systems with many languages like PHP, PERL, JAVA, C++, C, etc.

Key Features of MySQL

Below are the reasons mentioned for the immense popularity of MySQL.

• MySQL is an open-source relational database, which means it is completely free to use.
• It is based on a well-known and most widely used SQL language. It lets you execute queries on Tables, Rows, Columns, and Indexes.
• It stores data in a collection of Rows and Columns called Tables, also known as Relations.
• It works well even with large sets of data and can support up to 50 million rows or more in a table.
• MySQL is highly customizable and the open-source GPL license allows the SQL software to be modified easily as per the needs of developers or programmers.

What is MongoDB?

MongoDB is an open-source, cross-platform Non-Relational Database Management System. Developed in 2009 by MongoDB Inc, MongoDB uses the document-oriented database model to store information in a schema-less manner, it groups data into documents and collections rather than tables. This allows it to store different types of data. It supports multiple languages, comprises excellent features, and provides high performance.

MongoDB is a popular NoSQL database that supports a flexible schema approach and is capable of storing large data sets efficiently. It is written in different programming languages like JavaScript, Python, Java, PHP, C++, C, Ruby, and Perl. On top of that, it is released under the Server Side Public License (SSPL) which supports a unique mechanism to store and retrieve high-volume data. MongoDB is a simple and easy to configure program but offers high performance, automatic scalability, and high availability.

Key Features of MongoDB

Below are some of the key features of MongoDB that can be attributed to its growing popularity.

• Schema-less: MongoDB is a Non-Relational Database that stores and uses documents and collections to retrieve data. It doesn’t require you to create a table or pre-defined schema.
• Scalability: Unlike other SQL Databases, MongoDB uses horizontal scalability which allows users to generate clusters with real-time replication. MongoDB supports the Sharding process, which makes it easy to horizontally scale the data across multiple servers.
• Code-native Data Access: MongoDB stores all the collected data in a JSON-like document which allows changes/modifications over time. This makes it easy for developers to retrieve information in the data structure using any programming language.
• Cost-Effective: MongoDB is a free, open-source Non-Relational Database Management program that allows you to store large data sets in a cost-effective manner.
• High-Performance: MongoDB is fast and easy to use because of its NoSQL nature. Data can be stored, manipulated, and retrieved quickly without compromising Data Integrity.

Here’s a blog that talks about MongoDB replication, in case you’re willing to get your hands on more information.

Now that you’re familiar with MySQL and MongoDB, let’s dive straight into the MySQL to MongoDB integration.

Why Migrate from MySQL to MongoDB?

MySQL came into existence in 1995 and it has been powering the most demanding Web, E-commerce, and Enterprise Data Management applications since then. But in today’s world, processing data, building applications, and analyzing results have all gotten considerably much more complex.

With data incoming from a variety of sources, organizations are finding it difficult to manage their increasingly complicated user loads with traditional Relational Databases such as MySQL. And, this is primarily the reason why it is important for companies to migrate from MySQL to MongoDB.

New world engineering applications demand the persistence of complex and dynamic forms of data to match the highly flexible and powerful languages used for software development in today’s world. MongoDB, the top NoSQL database, tackles the above-mentioned issues and provides users with a flexible and scalable solution to match the needs of modern applications.

The schema-less and unstructured design of MongoDB allows you to store documents with different datasets in contrast to a relational database. Setting an example, industry leaders such as Cisco, Verizon, and others have successfully transitioned from relational databases like MySQL to MongoDB. Take a look at how to migrate data from relational databases to MongoDB in this blog.

Coming back to the topic at hand, here’s what you’ll be getting after migrating from MySQL to MongoDB.

• The Document Data Model allows you to work with data in the most efficient way possible.
• A Distributed Systems Design allows you to intelligently put data wherever you want to.
• A unified MongoDB experience frees you from vendor lock-in and gives you the freedom to future-proof your work.

Replicate MySQL to MongoDB with Python

The representation of data in MongoDB is completely different from MySQL. Hence, migration of MySQL to MongoDB can be a challenging and tedious task. Fortunately, Python’s strong connectivity and data handling capabilities come to the rescue. This article will use a simple Python script to migrate your MySQL Table data to MongoDB Collections. The scripts will work with any Python 3+ version on any platform.

Follow the below-mentioned steps to achieve a MySQL to MongoDB migration successfully.

• Step 1: Install the Required Modules
• Step 2: Read Data from MySQL Table
• Step 3: Write to MongoDB Collections
• Step 4: Complete Script
• Step 5: Enhance the Script to Load All Tables in a MySQL Schema

Step 1: Install the Required Modules

The first step requires you to install the required modules to connect to the MySQL and MongoDB database instances. To do so, you can use the mysql.connector official module to connect to MySQL database. Similarly, you can use pymongo module to connect to MongoDB from Python.

Run the PIP commands as shown below to install the required modules for migrating data from MySQL to MongoDB.

pip install mysql-connector 
pip install pymongo

PIP is a package manager for Python modules/packages.

Step 2: Read Data from MySQL Table

The next step of the migration requires you to read data from the source MySQL Table. This data is then prepared in a format supported by MongoDB. As you know, MongoDB is a NoSQL database and it stores data as JSON documents. Hence, it is recommended to convert the MySQL data to JSON format before loading it into the target MongoDB database. 

Python’s strong data handling capabilities make it easy to generate the MySQL data in JSON format. Execute the following script to generate data in JSON format.

import mysql.connector

mysqldb = mysql.connector.connect(
    host="localhost"t,
    database="employees",
    user="root",
    password=""
)
 
mycursor = mysqldb.cursor(dictionary=True)
mycursor.execute("SELECT * from categories;")
myresult = mycursor.fetchall()
 
print(myresult)

Compiling the script without any error will return the following output.

[
  {
     "id":4,
     "name":"Medicine",
     "description":"<p>Medicine<br></p>",
     "created_at":"",
     "updated_at":""
  },
  {
     "id":6,
     "name":"Food",
     "description":"<p>Food</p>",
     "created_at":"",
     "updated_at":""
  },
  {
     "id":8,
     "name":"Groceries",
     "description":"<p>Groceries<br></p>",
     "created_at":"",
     "updated_at":""
  },
  {
     "id":9,
     "name":"Cakes & Bakes",
     "description":"<p>Cakes & Bakes<br></p>",
     "created_at":d"",
     "updated_at":""
  }
]

The result is a JSON array as dictionary=True argument was passed to the cursor. Now that you have the MySQL source data in JSON format, let’s go ahead and write it to MongoDB Collections.

Step 3: Write to MongoDB Collections

The next step requires you to load the JSON format source data into a MongoDB Collection. A MongoDB Collection is basically the NoSQL equivalent of a MySQL Table and it comprises a set of documents.

You can use the insert_many() method of the collection class to return the list of Object IDs of the inserted documents. However, this method requires a length check before the call as it will throw an exception when an empty list is passed as the argument. Execute the following script to load data into a MongoDB Collection.

import pymongo
 
mongodb_host = "mongodb://localhost:27017/"
 
mongodb_dbname = "mymongodb"
 
myclient = pymongo.MongoClient(mongodb_host)
 
mydb = myclient[mongodb_dbname]
 
mycol = mydb["categories"]
 
if len(myresult) > 0:
 
       x = mycol.insert_many(myresult) #myresult comes from mysql cursor
 
       print(len(x.inserted_ids))

After successful execution of this script, you can check your MongoDB Database Instance to verify if the documents have been inserted into the collection.

Since MongoDB is schema-less, you don’t need to pre-define a schema to insert documents. The database, collection, and schema are created automatically by MongoDB.

Step 4: Complete Script

Just combine the above scripts to read a MySQL Table and insert it into a MongoDB Collection.

import mysql.connector
 
import pymongo
 
delete_existing_documents = True
 
mysql_host="localhost"
 
mysql_database="mydatabase"
 
mysql_schema = "myschema"
 
mysql_user="myuser"
 
mysql_password="********"
 
mongodb_host = "mongodb://localhost:27017/"
 
mongodb_dbname = "mymongodb"
 
mysqldb = mysql.connector.connect(
 
   host=mysql_host,
 
   database=mysql_database,
 
   user=mysql_user,
 
   password=mysql_password
 
)

mycursor = mysqldb.cursor(dictionary=True)
 
mycursor.execute("SELECT * from categories;")
 
myresult = mycursor.fetchall()
 
myclient = pymongo.MongoClient(mongodb_host)
 
mydb = myclient[mongodb_dbname]
 
mycol = mydb["categories"]
 
if len(myresult) > 0:
 
       x = mycol.insert_many(myresult) #myresult comes from mysql cursor
 
       print(len(x.inserted_ids))

Step 5: Enhance the Script to Load All Tables in a MySQL Schema

The next step of MySQL MongoDB data replication requires you to iterate through the list of all tables in the MySQL Database and insert the results in a new collection. You can perform this MySQL to MongoDB query using the information_schema.tables metadata table. This returns the list of all tables in a given schema. After that, you may iterate through the tables and call the above script to move the data from each table.

#Iterate through the list of tables in the schema
 
table_list_cursor = mysqldb.cursor()
 
table_list_cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = %s ORDER BY table_name;", (mysql_schema,))
 
tables = table_list_cursor.fetchall()
 
for table in tables:
 
    #Execute the migration script for 'table'

This can be accomplished by abstracting the migration logic into a function.

#Function migrate_table
 
def migrate_table(db, col_name):
 
   mycursor = db.cursor(dictionary=True)
 
   mycursor.execute("SELECT * FROM " + col_name + ";")
 
   myresult = mycursor.fetchall()
 
   mycol = mydb[col_name]
 
   if delete_existing_documents:
 
       #delete all documents in the collection
 
       mycol.delete_many({})
 
   #insert the documents
 
   if len(myresult) > 0:
 
       x = mycol.insert_many(myresult)
 
       return len(x.inserted_ids)
 
   else:
 
        return 0

You can use the print statements to track the progress of the script. You can refer to the complete script for a better understanding.

That’s it, with some basic knowledge of Python programming, you can easily migrate data from MySQL to MongoDB. However, this method is best suited for professions with technical experience. You can opt for a third-party solution if you don’t want to spend a lot of time writing custom scripts and resolving data issues.

Conclusion

The way applications are built and run in today’s world is pushing relational databases like MySQL to their limits. This is why NoSQL databases like MongoDB have exploded in popularity in recent years. MongoDB stores data into collections of self-contained JSON documents. MongoDB has gained widespread acceptance as a leading database owing to its dynamic schema, high scalability, and faster access.

This article introduced you to MySQL and MongoDB and later provided you with a step-by-step guide to migrating from MySQL to MongoDB. However, it is important to consolidate data from MySQL, MongoDB, and other data sources into a Single Storage System to get a complete overview of your business performance. Connecting MySQL and MongoDB to a data warehouse like Snowflake, Redshift, and BigQuery using a data integration tool like Hevo can save you a lot of time and effort.

visit our website to explore hevo

Hevo Data with its strong integration with 150+ Sources & BI tools such as MySQL and MongoDB, allows you to not only export data from sources & load data to the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools.

Give Hevo Data a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs, check them out!

Share your experience of migrating from MySQL to MongoDB in the comments section below.