- Back to Main menu
  
  Back to Products
  Product finder
- Back to Main menu
- Back to Main menu
- Back to Main menu

Contact Sales

Product finder

How to Import and Export Databases in MySQL or MariaDB

There are probably two key use cases for data export and import. The first case is migration. There's hardly anything simpler than exporting your data to a file or multiple files. Then, you only need to import it into the required database located in a different environment. The second case comprises backup and recovery. You schedule regular automated export of your data to files with timestamps—and you no longer need to worry about having a relevant version of your data at hand if something goes wrong with your database or server.

Whatever your case is, there is one thing that's certain. You want your export and import operations to be fast, flexible, reliable, and automated. And it won't be that hard with the right tools at hand.

Generally, when it comes to data import and export, you have two options. The first one is to use command-line utilities. The second one is to employ a GUI-powered database tool and carry out wizard-aided export and import with maximum convenience. In this guide, we will cover both and suggest a solution that combines their power - dbForge Studio for MySQL.

How to export and import MySQL and MariaDB databases with dbForge Studio for MySQL and the command line
Pros and cons of using dbForge Studio for backup and recovery
Data export and import formats supported by dbForge Studio
How to import a large MySQL/MariaDB file with dbForge Studio
How to import and export Microsoft Excel data
How to import and export MySQL/MariaDB data to and from CSV
Automation of recurring import and export operations
Common issues and possible solutions

How to export a MySQL database

Whenever you need to perform the migration of databases, for example, from one server to another, the following question comes up to their mind: "How can I copy a MySQL database and restore it quickly and smoothly?"

All possible methods to move MySQL data include the creation of a dump file with data. Later on, you can use this file to restore a database in the required location.

You can export selected tables and entire MySQL and MariaDB databases using dbForge Studio for MySQL, its intuitive wizards, and flexible settings.

Export a MySQL database

Command to export a database in MySQL

You can create a dump file from the command line. For this, you can use the mysqldump command.

mysqldump -u‹username› –p‹password› database_name table_name > dumpfile_name.sql

In this command:

username and password are your credentials to connect to a MySQL server.
database_name is the name of the database you want to dump.
table_name is an optional parameter. You can specify it when you need to back up a certain table.
">" states that you back up the database. This sign "<" is used for showing that you restore the database.
dumpfile_name.sql is the name of the dump file.

Command to export a database in MySQL

Export a MySQL dump: The easiest way

If you're searching for a trouble-free way to migrate a MySQL database, you can do it with dbForge Studio.

1. Navigate to Database > Backup and Restore > Backup Database.

2. Configure the options in the Database Backup Wizard window and click Next.

3. Select database objects to back up and click Next.

4. Set the backup options. To immediately export a MySQL database to a SQL file, click Backup. To configure the errors handling and log settings, click Next.

5. If you want the SQL file to open after the Wizard gets closed, select Open script.

6. Finally, click Finish.

In the same way, you can export a MariaDB database, which is also supported by dbForge Studio.

Export a MySQL dump: The easiest way

IDE for MySQL database export: Pros and cons

An IDE is an excellent solution for exporting databases in MySQL for those who do not want to bother. For example, dbForge Studio has an intuitive user interface that allows you to save time and effort while migrating data. This process really takes several minutes even if you are a new user of the tool. You can export MySQL data to 14 widely used formats.

With dbForge Studio, you do not need to know all the parameters to dump a MySQL database. It's unnecessary to enter and set them manually because you can do it with a convenient wizard.

The mysqldump utility will come in handy in case you need to back up several MySQL databases simultaneously.

IDE for MySQL database export: Pros and cons

Supported data export/import formats

With dbForge Studio for MySQL, you can easily export or import data. The Studio offers wizards to guide you through the entire process. Also, it's possible to save your selected export and import options for recurring use.

The Data Export Wizard helps you tune up export operations easily and export your MySQL data to 14 most popular formats: HTML, TXT, XLS, XLSX, MDB (Microsoft Access), RTF, PDF, JSON, XML, CSV, ODBC, DBF, SQL, and Google Sheets.

As for import operations, you have 10 formats at your disposal.

TXT
XLS
XLSX
MDB (Microsoft Access)
Google Sheets

XML
JSON
CSV
ODBC
DBF

Supported data export/import formats

Data export

dbForge Studio for MySQL has advanced functionality that simplifies the data export process. For example, it's not necessary to export an entire table. You can select to export all rows, selected rows only, or a certain range of rows. This makes export easier and saves your precious time.

You can specify error processing behavior and logging options: the Studio can be configured to prompt you for an action, ignore all errors, or abort the operation at the first error. You can also select to create a log file and allocate a path to it.

dbForge Studio allows you to create and save templates for recurring export scenarios. This helps you avoid wasting time configuring the same required options repeatedly.

You can set up table grid options for exported data. You can configure header text color and background, row text color and background, and the width and color of borders. For your convenience, you can make even and odd rows look different.

And if you're wondering how to export a MariaDB database—well, simply connect to it with the Studio, and the workflow will be the same.

Data export

Create a new MySQL database and assign a user

To create a new database in MySQL, do the following.

1. Navigate to Database > New Database.

2. Configure the Name, Charset, and Collation fields and click Apply Changes.

3. To check the created database, click Refresh in Database Explorer.

After you create the MySQL database, you can grant user privileges to it.

1. Navigate to Database > Security Manager.

2. Click the required user and click the Object Privileges tab in the left part of Security Manager.

3. Adjust the options to your needs and click Save.

Create a new MySQL database and assign a user

How to import a database in MySQL

You can import a database to the same or a different database server. We can suggest two ways of doing it.

The mysql utility
dbForge Studio for MySQL

Also, you can import a MySQL or MariaDB database from an SQL file.

How to import a MySQL database

Import a MySQL database using the command line

You can import MySQL data from the command line as follows.

mysql -u‹username› -p‹password› database_name < import_file_name.sql

In this command:

username and password are your credentials to connect to a MySQL server.
database_name is the name of the database you want to import.
"<" states that you import the database.
import_file_name.sql is the name of the import file.

Import MySQL using the command line

Load the import file using dbForge Studio for MySQL

You can easily import a MySQL database with dbForge Studio.

1. Navigate to Database > Import Data.

2. Choose the required import format, specify the location of the database file, and click Next.

3. Specify where you want the file to be imported. Configure the Connection and Database options and select whether you need to import as a new table or to one of the existing tables.

4. Click Next and configure the options for the selected format.

5. To begin the import operation, click Import.

6. Additionally, you can save the import settings as a template file. For this, just click Save at any moment.

In the same way, you can import a MariaDB database, which is also supported by dbForge Studio.

Load the import file using the IDE

IDE for MySQL database import: pros and cons

As you can see, the import process in dbForge Studio for MySQL is simple and does not require much effort. You can import MySQL data from 10 commonly used formats. Thanks to Data Import Wizard, which contains all necessary options and settings, you will be sure that MySQL data import will be completed successfully.

We would like to re-emphasize that it is not required to configure the import settings again and again. You can adjust them once and save them as a template file. It will save your time and simplify the import process.

But when it comes to recovering data to more than one database, the mysql command can solve this challenge. So importing MySQL data depends on how you need to restore it.

IDE for MySQL database import: pros and cons

Data import

dbForge Studio makes it possible to import data to a different server connection, database, or schema. You can choose whether you want to import data to a new or existing table.

Also, you can select a data import mode: Append, Update, Append/Update, Delete, and Repopulate. It's possible to import data in a single transaction.

In dbForge Studio, the import process is totally customizable. You can even configure a range of rows or columns you want to import.

Column mapping allows you to adjust the way your records should be restored to the target destination. If you import data to a new table, the Studio will automatically create and map all the columns. If you import data to an existing table, the columns with the same names will be already mapped. You can map the rest manually.

Data import

Data export and import
have never been easier

dbForge Studio is an easy-to-use GUI tool that helps you fully customize and even schedule import and export operations.

Check this video and see how you can import and export your MySQL data effortlessly with dbForge Studio at hand.

dbForge Studio for MySQL

How to import a large MySQL file

You can import a large MySQL database with Execute Script Wizard.

1. Navigate to Database > Execute Large Script.

2. Select the connection and database for import.

3. Select the file to be imported in the SQL file name field.

4. Specify the preferred encoding from the SQL file encoding drop-down list.

5. Finally, click Execute.

How to import a large MySQL file

Import and export Excel data

In this section, you will learn how to export and import MySQL data to and from Microsoft Excel files.

1 - Choose a database you want to export

Step 1: Navigate to Database > Export Data

Export Data

Step 2: Select MS Excel

In Data Export Wizard, select MS Excel and click Next.

Step 3: Configure the options on the Source tab

Select the required connection from the Connection drop-down menu. To edit the selected connection, click Edit. To add a new connection, click New.

Select the database you want to export from the Database drop-down menu. Select the required tables and views. Then, click Next.

2 - Configure the settings in Data Export Wizard

This step is optional because, at this stage, you can already export the database. But, if required, you can adjust the settings on the remaining pages of the wizard.

3 – Export the database

To export the database, click Export. After the export process is completed, click Finish. You can check the exported database in the location you have specified on the Output settings page.

4 - Choose an XLS file to be imported

Step 1: Navigate to Database > Import Data

Import Data

Step 2: Select MS Excel

In Data Import Wizard, select MS Excel. Select the XLS file you want to import. Then, click Next.

Step 3: Configure the options on the Destination page

Select the required connection from the Connection drop-down menu. To edit the selected connection, click Edit. To add a new connection, click New.

Select the database where you want to import the file from the Database drop-down menu. Select whether you want to import the file as a new table or to a new table.

Finally, click Next.

5 - Configure the settings in Data Import Wizard

Optionally, you can adjust the settings on other pages of the wizard.

6 – Import the XLS file

To import the file, click Import. After the export process is completed, click Finish. You can check the imported table in the location you have specified on the Destination page.

Import and export MySQL
data to/from CSV

The process of importing and exporting MySQL data to/from the CSV format is essentially the same.

1 - Choose a database you want to export

Step 1: Navigate to Database > Export Data

Step 2: Select CSV

Select CSV

In the Data Export Wizard, select CSV and click Next.

Step 3: Configure the options on the Source page

Select the required connection from the Connection drop-down menu. To edit the selected connection, click Edit. To add a new connection, click New.

Select the database you want to export from the Database drop-down menu. Select the required tables and views. Then, click Next.

2 - Configure the settings in Data Export Wizard

This step is optional because, at this stage, you can already export the database. But, if required, you can adjust the settings on the remaining pages.

3 – Export the database

To export the database, click Export. After the export process is completed, click Finish. You can check the exported database in the location you have specified on the Output settings page.

4 - Choose a CSV file to be imported

Step 1: Navigate to Database > Import Data

Step 2: Select CSV

In Data Import Wizard, select CSV. Select the CSV file you want to import. Then, click Next.

Select the CSV file

Step 3: Configure the options on the Destination page

Select the required connection from the Connection drop-down menu. To edit the selected connection, click Edit. To add a new connection, click New.

Select the database where you want to import the file from the Database drop-down menu. Select whether you want to import the file as a new table or to a new table.

Finally, click Next.

5 - Configure the settings in Data Import Wizard

Optionally, you can adjust the settings on other pages of the wizard.

6 – Start the CSV file import

To import the file, click Import. After the export process is completed, click Finish. You can check the imported table in the location you have specified on the Destination page.

Automation of recurring import and export operations

If you are using macOS or Linux, you can schedule regular export operations using command-line tools like cron. To do that, you will first need a script that performs export. For instance, you can use the same mysqldump command.

mysqldump -u‹username› –p‹password› database_name table_name > dumpfile_name.sql

Next, you will need to schedule this script to run regularly using cron. To see how it's done on a Mac, refer to How to schedule recurring tasks on macOS via cron.

You can automate recurring export operations even more conveniently using dbForge Studio. To do that, open Data Export Wizard and configure all the required settings from there. Once you are ready, click Save > Save Command Line in the lower left corner of the wizard. In a moment, the Studio will autogenerate the complete script for you.

Next, you can tweak the script, if necessary, save it to a .bat file, and schedule it for regular execution via Windows Task Scheduler or, if you are using macOS or Linux, via cron.

To learn more, refer to Export data from the command line.

Common issues and possible solutions

Character encoding mismatches can cause issues when exporting or importing data, particularly with non-ASCII characters. That said, make sure that both the source and destination databases use the same character encoding (e.g., UTF-8). You can also prevent these issues by specifying the required encoding (for instance, --default-character-set=utf8 for MySQL).

We should also mention ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option, which may occur when you are trying to perform an import or export operation against a MySQL server that has been configured with the --secure-file-priv option, which restricts file operations to a specific directory. You can check the allowed directory by running SHOW VARIABLES LIKE 'secure_file_priv';. The output will show the directory that MySQL is configured to allow file operations from (e.g., /var/lib/mysql-files/). If it is set to an empty string (''), it means that file operations are not allowed at all.

When importing and exporting data in the CSV format, various separator-related issues can arise, especially when handling data with special characters, varying delimiters, or simple inconsistencies. dbForge Studio helps avoid these situations by allowing you to specify delimiters explicitly. Moreover, you can always quote fields containing separators, newlines, or special characters.

When importing JSON data into a MySQL database, you can encounter certain conflicts caused by data format mismatches and system limitations. For instance, your JSON data might have a structure that does not align with the database schema, so you will need to double-check it and make sure it is well-formatted and includes consistent, valid data types. It is also recommended to use UTF-8 encoding to handle special characters. Finally, to avoid performance issues with large JSON files, you might want to tweak MySQL configurations (e.g., increase innodb_buffer_pool_size and max_allowed_packet).

Take note that dbForge Studio offers rich settings to help you configure import and export operations in the most flexible way. If something goes wrong, the Studio will notify you about it immediately. Additionally, you can configure error handling during import and export operations beforehand on the corresponding pages of the wizard.

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise

Professional

Standard

Express

Data export to 14+ file formats

Data import from 10+ file formats

What our customers say

"Filtering huge datasets is easy with dbForge Studio, and I am finally able to export data to any desired format. What's more, daily exports became 90% faster. In some cases, it's just a few clicks, and everything's done! The best thing about it is that now I can spend more time on helping people instead of sitting there and creating reports."

Sebastian Warmuz
IT Technician at Fundacja RSC

"[With dbForge Studio for MySQL,] working with raw data finally became super easy. Database design, import, export, and comparison of data is something that helps a lot in the daily life of a database administrator, and I got all that in a single tool. dbForge Studio is — for me — the best MySQL client on this planet!"

Michael Bladowski
CEO & Developer at MACROPAGE

"We became 25% more efficient as we saved 2 FTE hours per day. In most cases, about 30% of the time spent on a project is database work, and we have seen most of that work cut in half, so we now only allocate 15%, and it's more than enough. This increase in efficiency has a big impact across many areas that depend on IT to deliver results."

Bob Guzek
IT Manager at Creative Memories

More reviews from our customers

"We have a complex system using official financial ERP software, Excel for the recipe calculation and a PHP front-end based on a MySQL database. Our need was to make the communication among these as much automatic and error-free as possible. Add to this that we are based in Italy, and the localisation makes the usage of. csv files a nightmare. After trying unsuccessfully other software, finally, dbForge Studio for MySQL brilliantly resolved all our problems."

Claudio Maestrini,
Board Member

"dbForge Studio has tons of functionality - lots of import, export, and copy options, great syntax checker and highlighting; advanced and quick filtering possibilities to quickly find something in a table or modify. The support is great, whenever I have a question or find a bug, they are quick to respond. But what makes it overall the best is that it simply works; I struggled for years to find an excellent MySQL management tool. dbForge just does everything right."

Verified User at G2,
Information Technology and Services

"Fantastic! It is the best GUI for MySQL/MariaDB that I have used. It never breaks. The import and export wizards save a lot of time. The query autocompletion and error detection functionality saves a lot of time. Every advanced storage engines for MariaDB works with dbForge Studio for MySQL without any errors. I can't think of anything that I don't like about it. I tried the demos for all the major GUIs, and dbForge Studio for MySQL was the best."

Nicholas N.,
Hospital & Health Care

FAQ

What are the best tools for exporting and importing MySQL and MariaDB data?

If you prefer performing data export and import operations from an intuitive user interface, you should opt for GUI-based database tools like dbForge Studio for MySQL. It offers smart and versatile wizards that will help you configure and automate routine import and export tasks with maximum precision.

If you are well-versed in command-line tools, you can use the mysqldump utility. Note that it allows backing up several MySQL databases simultaneously.

What is the best way to automate MySQL and MariaDB data export and import?

The easiest one is the best one.

Open dbForge Studio for MySQL and connect to the required server.
In Database Explorer, find the database you want to export, right-click it, and select Export Data.
In Data Export Wizard that opens, tailor the export settings to your preferences and click Save > Save Command Line in the lower left corner of the wizard.
The Studio will immediately provide you with an autogenerated script that can be saved to a batch file.
The saved batch file can be scheduled for regular execution via tools like Windows Task Scheduler.

What are the most common issues with importing large MySQL databases?

The issues that may arise when importing large MySQL databases are typically related to resource limitations, data integrity, and configuration settings. Here are some of them along with possible solutions.

The import of large databases can take a long time, which in turn may lead to timeouts. To prevent this, you can increase the timeout values in the MySQL configuration file (my.cnf or my.ini).
The system might run out of memory while processing large imports, causing the server to crash or slow down significantly. To prevent this, you can similarly increase the values of memory-related settings in the same configuration file.
Import operations can be slow if there are complex indexes or foreign key constraints, so you may want to temporarily disable them during the import.
The import of large files can quickly consume disk space, particularly with temporary files that are generated during the process. You may want to monitor disk space during the import and make sure there is adequate free space for both the temporary directory (where MySQL stores temp tables) and the main data directory.
Large-scale import operations may also fail if the data contains invalid or incompatible entries, such as incorrect data types or missing values for required fields. Here, you need to simply validate your data before importing it.
The import of data with mismatched character sets (e.g., UTF-8 vs. Latin1) can lead to corrupted or misinterpreted data. This issue is not directly related to the size of the imported database, but still, it is better to verify that the character set has been specified correctly.
If the data contains large rows or binary data (e.g., BLOBs), the import can fail due to the max_allowed_packet value being too low. You can increase it in the MySQL configuration file.
The MyISAM storage engine uses table-level locking (unlike InnoDB, which uses row-level locking), so during data export/import, the table may be locked for writing and/or reading. This can affect database availability for other users or processes. In such cases, it is recommended to either temporarily switch to InnoDB or perform the export/import during off-peak hours.

What is the difference between logical and physical data export in MySQL?

Logical and physical data export are two different methods of backing up data.

Logical export copies the data of database tables, views, stored procedures, triggers, and other objects into SQL script files, which can be used later to reconstruct the database. In other words, a logical backup takes a snapshot of the database through SQL commands.

Physical export copies the actual database files that MySQL uses to store data (e.g., data files, index files, and log files). This method typically results in an exact, binary copy of the database's underlying storage structure.

How do I transfer MySQL and MariaDB data between servers efficiently?

dbForge Studio offers several ways of migrating data between servers. Choose the one that best suits your particular case.

Schema Compare and Data Compare are twin tools that help you synchronize source schemas and data to target ones with a few clicks.
Copy Database is a feature that creates a copy of an entire database. You can opt to include data by selecting the corresponding checkbox.
Duplicate Object is a feature that can create a copy of a table in a target environment. Similarly, you can select whether to include source data or not.
Create Scripts Folder is a feature that creates a collection of scripts that can be used to reconstruct a database along with all of its data.
Finally, you have data export and import that cover 14 most popular data formats.

What formats does dbForge Studio support for MySQL and MariaDB data export?

dbForge Studio supports 14 data formats for export: HTML, TXT, XLS, XLSX, MDB (Microsoft Access), RTF, PDF, JSON, XML, CSV, ODBC, DBF, SQL, and Google Sheets.

How do I export MySQL and MariaDB data in multiple formats at once using dbForge Studio?

The wizard for each format comes with a tailored set of options, so you need to select the required format from the very beginning. If you want to export the same data to another format, invoke Data Export Wizard once again, select that format, and tweak the settings, which may be slightly different this time around.

Does dbForge Studio support the export of MySQL views and stored procedures?

With dbForge Studio, you can export MySQL views just like regular tables by right-clicking the required view in Database Explorer, selecting Export Data, and following the wizard's instructions.

You can just as well export the output of a stored prodedure. After executing it via SQL Editor, select the required data in the grid. The rest is the same as in the previous case - right-click your selection, proceed to Export Data, and use the wizard to tweak export settings.

Empower yourself with dbForge Studio for MySQL and speed up all your database tasks!

Download for free now