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.
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.
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.
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.
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.
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.
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.
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.
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.
Step 12: Check the result
As a result of our import efforts, the Customers_new table appeared on Amazon RDS.
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! Also, you can watch this video tutorial:
