​Many organizations today use MySQL Database in their environment for various in-house or external applications. In fact, it is considered the second most popular database in the world after Oracle Database, according to various third-party rankings eg Statista and DB-Engines. As a MySQL user, you have likely already considered moving your database to the cloud for the cloud benefits. Read on!

Additionally, in the process, if you also want to migrate the database to Oracle Database, to take full advantage of Oracle Autonomous Database’s features and capabilities, as well as that of cloud, this post is for you.

You will be introduced to Oracle SQL Developer and how easy it is to use for such migration. Depending on your specific environment and scenario, there are other migration tools in the market, but here, I will talk about using Oracle SQL Developer. I will also explain briefly, other Oracle tools for migration. If you want to go straight to step-by-step guide to do this migration, here is a link to the supporting LiveLabs workshop: Migrate MySQL to Oracle ADB using Oracle SQL Developer. If however, you only want to move your on-premises MySQL database to MySQL Database in the cloud as Oracle’s Platform-as-a-Service (PaaS), refer to this instead: Migrate from on-premises MySQL to MySQL Database service.

Let me first introduce you to Oracle SQL Developer and Autonomous Database.

Oracle SQL Developer logo

 

​“The world’s #1 tool for managing your Oracle database”

Oracle SQL Developer GUI - Migration configured
Oracle SQL Developer GUI – Migration configuredCaption

​ 

Oracle SQL Developer

Oracle SQL Developer is a free, integrated development environment (IDE) that simplifies the development and management of Oracle Database in both traditional and cloud deployments. SQL Developer offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a database administration console for managing the database, a reports interface, a complete data modeling solution, and a migration platform for moving your third-party databases to Oracle. Oracle SQL Developer provides you with three interfaces: Desktop (discussed here), browser, and command line. Oracle SQL Developer is a tried-and-tested tool for years for Oracle Database users.

 

Oracle Autonomous Database

Oracle Autonomous Database (ADB) is an all-in-one cloud database solution for data marts, data lakes, operational reporting, and batch data processing. Oracle Autonomous Database is built on the proved hardware infrastructure of Oracle Exadata, combined with Oracle Cloud Infrastructure (OCI) capabilities and machine learning. Provision, scale, tune, protect, patch, and repair the database—all automatically without user intervention. This database option is only available in cloud and not in on-premise environment.

Using ADB, you not only get the advantages of cloud, like lowered cost of IT operations and  maintenance, easy of use, improved efficiency, security, scalability, and availability, but also benefit of automation of ADB, which reduces day-to-day database administration tasks, like backup, performance, tuning, recovery of the database, and applying patches and upgrades. ADB provides out-of-the-box high availability, including SLA (Service level agreements) and improved features including one-click setup of disaster recovery with Data Guard, one-click enable Data Safe for auditing and masking feature, one-click creation of database clones, and not to mention, one-click creation of a database in minutes. Similarly, you also get the benefit of cloud payment model: You pay for the time and quantity you use (pay-per-use) with our Oracle universal annual cloud subscription model.

 

Benefits

With Oracle SQL Developer, you can move your MySQL database to the cloud and migrate it to Oracle Database at the same time. In the process, you will create migration projects, define data type conversions, choose and skip objects for migration, run and re-run jobs, debug issues, or define offline or online migration options, SQL Developer provides you all of these features.

SQL Developer migration wizard

The Oracle SQL Developer’s Migration Wizard provides convenient, comprehensive guidance through the actions involved in database migration: Capturing the source database, converting it to Oracle format, generating data definition language to perform the conversion, and so on. You can use SQL Developer to migrate data, from MySQL, MS SQL Server, Sybase, or IBM DB2 databases.

You have two options: Either migrate the full database or right-click any table and choose the option to migrate that table. With SQL Developer, you can also import csv or delimited files directly into Autonomous Database. Similarly, you can import data from text/csv files, stored in OCI Object Storage, directly into Autonomous Database. You can even use Oracle SQL Developer Migration Wizard to migrate from Amazon Web Services (AWS) Redshift to Autonomous Database.

Installation of SQL Developer is also quite simple, where you simply download the zip file, extract and run the exe. Then, you need a migration metadata repository under the specific database user with appropriate access rights. The creation of users, rights, and repositories can be easily run through SQL Developer worksheet itself.

 

Other options for migrations

Other than Oracle SQL Developer, you have following options from Oracle, to move data from MySQL to Autonomous Database:

  • OCI Data Integration: Our cloud native service, provides sophisticated extract, transform, load (ETL) and extract, load, transform (ELT) that supports different source and target database integration scenarios. You can use it to migrate tables, perform data modification, filter, cleanse, merge, or schedule data movement. To perform this migration, you will need to setup an OCI DI instance and learn to use this tool.
  • If you need to migrate a few tables only, you can right-click those tables in Oracle SQL Developer and choose the Copy to Oracle option. This option directly migrates data without the need to set up migration repository or capture and convert objects.
  • Another option is to export MySQL tables into csv or text files and import it into Autonomous Database. To export the table data into delimited files, refer to: MySQL Migration Workbench. Then you can follow any of the following options to load those text files into Autonomous Database.

Conclusion

Oracle SQL Developer is a powerful, free tool for test and development on Oracle Database for many years. One feature of SQL Developer is third-party database migration. This post talked about the benefits of using Oracle SQL Developer to perform database migration from MySQL Database on-premises to Oracle Autonomous Database in the cloud in one go.

Try Oracle Cloud Free Tier! A 30-day trial with US$300 in free credits gives you access to OCI services. Ready to learn more?

​