How to Import Database in Magento 2

- Fire development, Magento 2

Magento 2 import export debug log

Recently, we’ve spotted a question on StackExchange regarding database import. David Duong faced problems while transferring a database on Magento 2.3.3. Below, we shed light on the issue, the provided solution, and our method of Magento 2 database import and export. For more tips and advice, read our Cookbook.


The Issue

David Duong created a backup database via CLI. He used the following command:

Although the process was successful, the problems started to occur when he tried to import it to his new PHPMyAdmin. The author of the discussion faced the following four errors:

  1. Unexpected character. (near “\” at position 0)
  2. Unexpected beginning of statement. (near “\” at position 0)
  3. Unexpected beginning of statement. (near “nCREATE” at position 1)
  4. Unrecognized statement type. (near “ALGORITHM” at position 9) 

What do StackExchange users recommend?

How to Import Database in Magento 2

Ashok Dubariya provides the following steps to import the database in the PHPMyAdmin

Use the following command (with SSH) to export the database:

Next, you have to move the exported file to a server where the import procedure should take place.

Import the database via the following command (with SSH):

For the first attempt, David Duong reports a bug. However, his next try seems successful.

How do we recommend to import and export databases in Magento 2?

How to Import Database in Magento 2 with no Errors

You can avoid all the headaches mentioned above with the help of Improved Import & Export. Our module offers an excellent opportunity to transfer any data between Magento 2 and any systems. It means that you can freely import a database into Magento 2 or export it to a third-party platform. In this article – How to Import Any Table From Remote Database to Magento 2 – we shed light upon the default mechanism of transferring databases and compare it with the one our plugin offers. The latter not only consists of less steps but can be entirely automated. You install the module, configure a new import profile, and launch the import process. The same is about export. Follow the link below for further information. Don’t hesitate to contact our support to discuss the possibilities Improved Import & Export provides.

Get Improved Import & Export Magento 2 Extension

Now, let’s briefly describe the core features of the module. First of all, it provides advanced options when it comes to the automation of data transfers. You can either create cron-based schedules or used triggers that launch import/export processes right after a specific event takes place. Thus, you can make database import to Magento 2 flawless: Improved Import & Export will do all the work for you. 

Magento 2 CMS Blocks

Another vital aspect of the extension is mapping. The module lets you apply mapping presets. It means that the plugin replaces all third-party attributes with ones used internally in a fully automated mode. 

Magento 2 CMS Blocks

If you cannot find a preset you need, there is a particular interface where you can do everything manually. Choose a third party value and specify a replacement in front of it. Thus, you can import any external database to Magento 2, avoiding errors caused by unsupported attributes. 

Other mapping features let you match and edit attribute values. You can also map third-party categories while importing products. Besides, the Improved Import & Export Magento 2 extension enables you to create attributes on the fly. You need to use a general form. It means that even a database that lacks some attributes can be transferred to your Magento 2 website with the missing data.

And, of course, our plugin dramatically extends the number of supported file formats (CSV, XML, JSON, ODS, and Excel) and sources (FTP/SFTP, numerous cloud platforms, and URL). And you can freely leverage API connections, like in the case of database import to Magento 2, and intermediary services, such as Google Sheets, Office 365 Excel, and Zoho Sheet.

Magento 2 CMS Blocks

For further information, follow the link below and contact our support:

Get Improved Import & Export Magento 2 Extension