How to import data from external MySQL database to Magento 2

Often it is necessary to import products, customers, orders, or other data types to Magento 2 from a third-party MySQL database. However, Magento 2 doesn’t let you do that. Although the default tools don’t support such file sources as external databases, there are several ways to synchronize Magento 2 with external databases. 

In the following post, we shed light on how to achieve this goal in two different ways. Firstly, we describe a common approach that involves the default Magento 2 import solution. It requires tons of manual work and cannot be automated. You need to run a CSV export from your MySQL database, match third-party attributes to ones used in Magento 2, and import your MySQL table into your e-commerce website.

On the contrary, you will find out how to avoid numerous unnecessary chores while importing data to Magento 2 from a MySQL database. This approach involves the Improved Import & Export extension. The module combines MySQL database connection and other data import activities within a single interface right in your Magento 2 admin.

After that, we shed light on several MySQL facts as well as describe the core features of the Improved Import & Export extension. To make the long story short, let’s see how to import data from an external MySQL database to Magento 2.

‘

The Common Way of Moving MySQL Bases to Magento 2

Let’s imagine a situation when you have a remote MySQL database with a product table in it. The most common (and at the same time hardest) way is based on CSV files. It requires lots of manual work to get data from a specific table of the external MySQL database. You have to pass three big stages:

• Export a CSV table from the MySQL database;
• Map third-party attributes of the table to Magento 2 attributes;
• Import the table into Magento 2.

All three stages are disconnected and act like separate procedures that require different tools to be utilized. Let’s describe each one briefly.

CSV Export from MySQL Database

Below, we describe core steps necessary to export a CSV file from MySQL using phpMyAdmin.

1. First of all, you need to open the database.
2. Next, select a specific table that includes the data you need.
3. Now, you should find the “Export” tab.
4. After that, you need to configure the parameters of the export process: select “Custom” in “Export Methods”.
5. In “Format”, select “CSV”.
6. Next, check the “Put columns in the first row” option.
7. At last, you can hit the “Go” button.

Watch the video below for further information:

[embedded content]

Map Third-Party Attributes to Magento 2 Attributes

Now, you have a standard CSV file with the desired data. Unfortunately, you cannot upload it right into Magento unless it uses the default attributes of the platform. Now, it is necessary to spend some time matching the attributes of the exported CSV file to the native Magento attributes. Depending on the size of your table, this procedure may consume lots of time, since you need to edit the name of every column.

Import MySQL Table into Magento 2

When the data file is modified, you can import it to Magento 2. It is possible to use the default Magento 2 import interface to achieve this goal. If attributes are mapped correctly, you will easily transfer data from an external MySQL database to Magento 2. In case of errors, try to find the issue in the CSV file. Fix it and relaunch the import process.

It was the common way to import data from an external MySQL database to Magento 2. It is proven by the community but takes much time to complete the procedure especially if you have to deal with multiple big tables. Alternatively, you can implement the following approach which is a way more efficient.

The Easy Way of Moving MySQL Bases to Magento 2

To reduce the number of steps and as a result time necessary to import data from an external MySQL database to Magento 2, you can use the Improved Import & Export Magento 2 extension. How does the module simplify the procedure?

MySQL Database Connection

First of all, it reduces the three stages above to two leading to the 30% reduction of work. You don’t need to use phpMyAdmin to export a CSV file. Furthermore, you don’t need any files to be exported at all. The seven steps of the “CSV Export from MySQL Database” stage are wholly reduced since the Improved Import & Export Magento 2 extension connects to an external MySQL database directly to import all the necessary data.

Magento 2 Data Import from MySQL

Instead of the common way, you just need to open the interface of the module, create a new import job, specify the required MySQL database, select all the necessary tables, apply mapping (match the external attributes to Magento 2 attributes), and update your Magento 2 database with the desired information. Furthermore, you can create a schedule of updates to automate data import from the external MySQL database to Magento 2.

The mapping procedure is dramatically simplified in comparison to the aforementioned one since you don’t need to edit data files directly. The Improved Import & Export Magento 2 extension provides an intuitive interface where you match Magento 2 attributes to external requirements and vice versa. Next, the created scheme is saved and applied to further updates. In case of the common data import from an external MySQL database to Magento 2, you should edit new CSV files manually before importing them into the system. In combination with scheduling, this feature saves tons of time and effort. That’s why we think that the Improved Import & Export extension provides the easiest and the fastest MySQL data import into Magento 2. Now, let’s say a few words about MySQL and then return to core features of the module.

Get Improved Import & Export Magento 2 Extension

MySQL Facts

MySQL is the most popular database system used with PHP. It is usually utilized on the web, runs on a server, and suits best for both big and small apps. The database is fast, reliable, and easy to use. The information in a MySQL database is stored in tables. Being an open-source relational database management system, MySQL is widely used as a central component of the LAMP stack as well as other “AMP” stacks.

As for other facts, the name of the system stands for the abbreviation for Structured Query Language combined with the name of co-founder Michael Widenius’s daughter. Initially, MySQL was owned and sponsored by the Swedish company MySQL AB. Now, it is owned by Oracle Corporation.

The project is available in several free and paid editions that offer additional functionality. Many popular apps rely on the MySQL database. Drupal, WordPress, Joomla, TYPO3, and many others are among its prominent users.  Furthermore, several Internet giants also incorporate the database. Facebook, Twitter, YouTube, and Flickr streamline it to provide a better experience.

As we’ve just mentioned, there is a paid edition of MySQL. It is the proprietary MySQL Enterprise Server. At the same time, you can always leverage the open source MySQL Community Server. A series of proprietary extensions differentiate the Enterprise edition. You can install them as server plugins to get additional features. At the same time, both versions share the same numbering system and are built from the common code base.

Below, you can see a list of features available in MySQL 5.6:

• ANSI SQL 99 subset;
• Multiple extensions;
• Cross-platforming capabilities;
• Triggers and Cursors;
• Updatable views;
• Online DDL for the InnoDB Storage Engine;
• Information and Performance Schemas;
• SQL Mode options to control runtime behavior;
• X/Open XA DTP support;
• SSL support;
• Transactions with savepoints;
• Query caching;
• ACID compliance;
• Sub-SELECTs
• Replication support;
• Full-text indexing and searching;
• Unicode support;
• Embedded database library;
• Multiple storage engines.

For the full list of features, follow this link: MySQL Features. Note that minor updates of the MySQL Server are released approximately once per two months. You can obtain the sources from MySQL’s website or the corresponding GitHub repository. Note that the GPL license is applied in both cases.

MySQL and Magento 2

As for the Magento 2 MySQL tandem, it is strongly recommended to observe the following standard:

• Since Magento improves database access during reindexing using MySQL database triggers, you should keep in mind that it does not support any custom triggers in the Magento database. The reason is issues caused by custom triggers regarding future Magento versions.
• It is also recommended to explore these potential MySQL trigger limitations before going any further with the development.
• In case of MySQL database replication, you should remember that Magento does not support MySQL statement-based replication. To avoid further mistakes and corresponding issues, it is necessary to row-based replication only.

You can find more information here: Magento 2 MySQL. Now, let’s take a look at our extension.

Improved Import & Export Magento 2 Extension

The Improved Import & Export Magento 2 extension is developed to connect Magento 2 to any third-party systems and sources. You can use our module to synchronize your e-commerce store with multiple external platforms, marketplaces, cloud services, etc. To achieve the broad connectivity options, the plugin supports numerous different approaches to data import/export.

Supported File Formats

First of all, you can leverage the default file transfer (which is eliminated in case of data import from an external MySQL database to Magento 2) to connect your e-commerce store to an external system. This approach is often used to provide price comparison engines and marketplaces with product data. At the same time, you can easily import order data back to your website. Besides, data file transfers help to migrate from external e-commerce platforms to Magento 2. You can even leverage this approach to migrate from your old Magento 1 store.

The Improved Import & Export extension supports multiple file formats. While Magento works with CSV files and most third-party solutions add only XML support, our plugin extends this connectivity options significantly. In addition to these two formats, it efficiently works with JSON as well as uses XLS, and XLSX formats to transform XML files. You can not only import files of supported formats to Magento 2 but also create the corresponding output. As a result, there is no need to convert data files before every update.

Supported File Sources

Besides, the Improved Import & Export Magento 2 extension supports multiple file sources. It means that you can import/export data files from/to various places. First of all, it is a local or remote server – the extension fully supports FTP/SFTP so that you have the first basic option. Next, it is possible to utilize Dropbox as a source. Use your account in the cloud to transfer information between two systems. When it comes to importing procedures, our extension goes even further. It allows importing files right via a direct URL. Also, note that all compressed data is unzipped/untarred automatically.

Alternative Ways of Import/Export

Another way to move data to/from Magento 2 incorporates REST API. This approach is applied to complex synchronizations, such as the Magento 2 ERP integration. It helps to eliminate data files from the transfer. At the same time, you can still automate this process by creating different schedules and applying mapping (both features are described below in more details).

If the information is provided from Google Sheets, it is not a problem for the Improved Import & Export Magento 2 extension. With the help of our module, you can easily import data from the popular service within a few clicks. Just take a look at the following illustration:

Flexible Cron Schedules

Now, let’s focus on schedules and mapping. As mentioned above, you can fully automate every import/export process by applying mapping and creating a scheme of updates. And it is true to data import from an external MySQL database to Magento 2.

The Improved & Export Magento 2 extension supports cron so that you can create any custom schedule of updates. If you don’t want to deal with the standard cron syntax, it is possible to select one of the predefined intervals. If necessary, you can customize it as well. At the same time, the ability to launch every import/export procedure manually is preserved. Below, you can see how to configure a schedule:

Advanced Mapping Functionality

As for mapping, you can apply it within a convenient interface of the Improved Import & Export Magento 2 extension. No data files should be edited manually. You create a mapping scheme, and it is used for all further updates. The process looks as follows:

Moreover, the Improved Import & Export Magento 2 extension offers mapping presets – pre-made mapping schemes that simplify data migration even more. You don’t even need to match attributes manually. Instead, our module allows selecting the corresponding preset that can be applied within a few clicks creating the required mapping scheme automatically. You can see how it work below:

To discover more features of the Improved Import & Export Magento 2 Extension, watch this video:

[embedded content]

If you want to import data from an external MySQL database to Magento 2 in the most efficient manner without spending hours of time on CSV import/export, get the Improved Import & Export Magento 2 extension. You can purchase the module here:

Get Improved Import & Export Magento 2 Extension

Magento 2 MySQL Integration FAQ

Let’s look at several other facts about the Magento 2 MySQL integration.

‘