Import MySQL data to Amazon RDS

July 15th, 2020

Importing and exporting data are common tasks involved with administering databases. Migrating data between on-premises and cloud databases can be frustrating especially if the jobs need to be repeated again and again. In this article, we provide a comprehensive step-by-step guide to getting data in Amazon RDS with the help of dbForge Studio for MySQL.

Data Export and Import tools of dbForge Studio for MySQL

dbForge Studio for MySQL can boast advanced Data Export and Import tools. They are highly customizable and enable data transfer quickly and easily. Studio supports data import and export via the user interface and its Export\Import wizards help you through data migration one step at a time by asking for the necessary configurations. The wizards are easy-to-follow allowing non-experts to use it effectively.

Import MySQL data to AWS RDS

In this worked example, we will import MySQL data previously saved to an MS Excel file.

Step 1: Establish a connection

To start working with the Import utility of dbForge Studio for MySQL, we need to establish a connection to the target database. To add a new connection navigate Database -> New connection. In the Database Connection Properties window, make all the necessary connection settings.

Amazon RDS import database data - setting connection

Step 2: Open Data Import wizard

To import data from a file, you need to call the Import wizard. This can be done from the Data Pump tab on the Start Page. Alternatively, in the Database Explorer, right-click a table you want to import data to and select the Import Data command from the context menu that appears.

AWS RDS import MyQL database data - open Import wizard

Step 3: Select file format

In the Data Import wizard, select a file to import and specify its format. In this tutorial, we will import MySQL data to Amazon RDS from one of the most common formats – MS Excel 2007.

Having selected a format and provided a path to the file, click Next.

Import MySQL data to Amazon RDS - select source file

Step 4: Choose a destination table

On the Destination tab of the Data Import wizard, you need to select a table on AWS RDS to import your MySQL data. You can choose to move data either to a new table or to an existing one. In our example, we select to import data to a new table: Customers_new. Click Next after you’ve made all the necessary settings.

AWS RDS import MySQL data - select destination table

Step 5: Set import options for the selected file format

The Options tab of the Data Import wizard provides the possibility to set import options for the selected file format and preview the data.

AWS RDS import MySQL data - select import options

Step 6: Adjust data formats

On the Data formats tab, you can adjust data formats and column settings.

Step 7: Mapping the file contents to the table columns

The Mapping tab enables mapping the file contents to the table columns. Here you can:
– change the sequence of imported columns
– change the names of the columns and their types of data
– remove a column from import
– preview the data to be imported
… and more.

AWS RDS import MySQL database data - mapping

Step 8: Select the import mode

On the Modes tab of the Data Import wizard, you can select how MySQL database data will be imported to Amazon RDS. The following options are available:
– Append (the records will be added to the destination table)
– Update (a record in the destination table will be updated with a matching record from the source table)
– Delete (the records in the destination table that match records in the source table will be deleted)
– Repopulate (all the records in the destination table will be deleted and then the table will be populated with the records from the source table).

Here you can also choose a mechanism of data insert: either using a single transaction or via a bulk insert.

Amazon RDS import database data - import mode

Step 9: Select the script output options

In this step, you need to select output options to manage the data import script. You can choose to:
– Open the data import script in the internal editor
– Save the data import script to a file and specify a path to it
– Import data directly to a database.

Step 10: Specify errors processing behavior and logging options

Finally, on the Errors handling tab, you can configure the application behavior in case it encounters an error. Here you can also tune the logging options.

Import to Amazon RDS - errors handling

Step 11: Finish import and save the template

Having completed all the steps, click the Import button. You will see a notification about the result of your import job. An essential advantage of dbForge Studio for MySQL is the ability to save the import configurations for recurring import scenarios. Click the Save button to save the settings either as an import template or as a batch file for command-line operations.

Import MySQL data to Amazon RDS - finish export

Step 12: Check the result

As a result of our import efforts, the Customers_new table appeared on Amazon RDS.

Import MySQL data to AWS RDS - check the result

Note:

It is also possible to export MySQL data to a file to migrate it to another local or remote database. To learn how to export data to a file using dbForge Studio for MySQL, please see our next tutorial.

Conclusion

With dbForge Studio for MySQL, MySQL data import to Amazon RDS becomes much simpler, faster, and more robust. The ability to save import options as a template allows you to skip configuring import jobs again and again and thus saves your time and efforts. The Save as Command line option enables scheduling and automating migration tasks making database management easier. Try dbForge Studio for MySQL and see for yourself!

See also
Leave a Comment