SQL Developer for migrations from MySQL to Oracle
Sometimes, you may need to migrate an application from MySQL to something more advanced — like Oracle database.
For a reference on the differences between MySQL and Oracle database, refer to the documentation.
Oracle SQL Developer will assist you in the migration, and the official documentation is below.
It can also be helpful to setup Vagrant virtual machines to do your test migration. Fortunately, Oracle provides Vagrant projects that includes the LAMP stack (featuring MySQL).
It’s easy to clone the Oracle Vagrant repo like below, to setup the Vagrant Oracle Linux 7 project with LAMP extension.
$ git clone https://github.com/oracle/vagrant-projectsAlso for the test migration, let’s setup Oracle SQL Developer to connect to your MySQL instance and an Oracle instance at the same time.
The following instructions are for Windows 10.
Install Oracle Linux 7 with the LAMP extension
Setting up a LAMP stack, will provide you will a pre-built MySQL instance out of the box.
However the Oracle vagrant-projects\LAMP directory is now deprecated in favour of Oracle Linux 7 with the LAMP extension. ie vagrant-projects\OracleLinux\7.
As we want to login to the Oracle Linux 7 virtual machine via SQL Developer on the host, we also need to add a local port forward of 3306.
To create a LAMP stack in a Virtual box with Vagrant do the following (Windows 10):
$ cd vagrant-projects\OracleLinux\7
$ set EXTEND=lamp && set EXPOSE=8080:80 && set EXPOSE=3306:3306 && vagrant upIf all is going well then you see that the first LAMP component installed will be Apache.
====================================================================
Package Arch Version Repository Size ====================================================================
Installing:
httpd24 x86_64 1.1-19.el7 ol7_software_collections 4.5 kThen the MySQL server and client is next.
====================================================================
Package Arch Version Repository Size
====================================================================
Installing:
mysql-community-client x86_64 8.0.23-1.el7 ol7_MySQL80 48 M
mysql-community-server x86_64 8.0.23-1.el7 ol7_MySQL80 518 MFinally the PHP components will follow.
====================================================================
Package Arch Version Repository Size
====================================================================
Installing:
rh-php73 x86_64 1-1.el7 ol7_software_collections 4.3 k
rh-php73-php x86_64 7.3.20-1.el7 ol7_software_collections 1.4 M
rh-php73-php-fpm x86_64 7.3.20-1.el7 ol7_software_collections 1.5 M
rh-php73-php-mysqlnd x86_64 7.3.20-1.el7 ol7_software_collections 165 kNow login to confirm that all is well with the virtual machine.
$ vagrant sshYou should be presented with the following:
==> vagrant: Extension lamp using scripts/lamp.sh enabled
==> vagrant: Guest port 3306 exposed to port 3306 on hostWelcome to Oracle Linux Server release 7
LAMP architecture based on Oracle Linux Software Collections:
- Apache 2.4, MySQL Community 8 and PHP 7.3The Oracle Linux End-User License Agreement can be viewed here:
* /usr/share/eula/eula.en_USFor additional packages, updates, documentation and community help, see:
* https://yum.oracle.com/To test your environment is correctly working, just open following URL from your Host OS:
http://localhost:8080/info.phpPlease use following commands to enable Software Collection environments:
- Apache 2.4: # scl enable httpd24 /bin/bash
- PHP 7.3: # scl enable rh-php73 /bin/bash[vagrant@ol7-vagrant ~]$
Now do a quick test, by opening a browser on your host and open the following URL:
http://localhost:8080/info.phpYou should get the top of the page that looks like this:
Scroll down and you’ll see some information for MySQL:
So the basic setup is installed.
Set up to login to MySQL from the host
To create the ability to login remotely for MySQL (including SQL Developer), we need to initially login locally with the temporary root password.
Display the temporary root password (ie system password) of the MySQL instance as follows:
$ sudo grep 'temporary password' /var/log/mysqld.logNow login with the temporary root password shown:
$ mysql -uroot -p
Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
It’s a good idea to change the root password of the local account:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Wlrb1234,';While we are logged in locally, we need to setup the account for login from the host.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.23Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> CREATE USER root IDENTIFIED BY 'Root-passw0rd';
Query OK, 0 rows affected (0.02 sec)mysql>
Now we are set to login from MySQL Workbench — and the same login credential will also work with SQL Developer later.
From the MySQL Workbench, use a hostname of 127.0.0.1 with port 3306, and the username of root and the password.
Now we can checkout the database from MySQL Workbench.
This confirms that we can login from the Windows 10 host.
However more helpful for migration purposes is to login to MySQL from the SQL Developer tool.
Download and install the J Connector
For migration purposes we can also login using SQL Developer, and then we can access MySQL and Oracle database from the same tool.
To login to MySQL from SQL Developer, we need to install the J Connector from the MySQL installer.
If you don’t already have the MySQL installer, then get it from here:
https://dev.mysql.com/downloads/windows/installer/8.0.html
Just download the basic installer: mysql-installer-web-community-8.0.23.0.msi (2.38 MB)
From the basic installer, choose to install selected products.
Choose the most recent J Connector, then Next, and then Execute.
You’ll see the install log as follows:
1: Action 13:28:12: INSTALL.
1: 1: MySQL Connector J 2: {42EFD60B-24CE-466E-864F-8ED744643ED3}
1: Action 13:28:12: FindRelatedProducts. Searching for related applications
1: Action 13:28:12: ValidateProductID.
1: Action 13:28:12: CostInitialize. Computing space requirements
1: Action 13:28:12: FileCost. Computing space requirements
1: Action 13:28:12: CostFinalize. Computing space requirements
1: Action 13:28:12: InstallValidate. Validating install
1: Action 13:28:12: InstallInitialize.
1: Action 13:28:12: RemoveExistingProducts. Removing applications
1: Action 13:28:12: ProcessComponents. Updating component registration
1: Action 13:28:12: GenerateScript. Generating script operations for action:
1: Updating component registration
1: Action 13:28:12: UnpublishFeatures. Unpublishing Product Features
1: Action 13:28:12: RemoveFiles. Removing files
1: Action 13:28:12: InstallFiles. Copying new files
1: File: Copying new files, Directory: , Size:
1: Action 13:28:12: RegisterUser. Registering user
1: Action 13:28:12: RegisterProduct. Registering product
1: Registering product
1: Action 13:28:12: PublishFeatures. Publishing Product Features
1: Feature: Publishing Product Features
1: Action 13:28:12: PublishProduct. Publishing product information
1:
1: Action 13:28:12: InstallFinalize.
1: Action 13:28:12: ProcessComponents. Updating component registration
1: Action 13:28:12: InstallFiles. Copying new files
1: File: CHANGES, Directory: C:\Program Files (x86)\MySQL\Connector J 8.0\, Size: 267994
1: File: INFO_BIN, Directory: C:\Program Files (x86)\MySQL\Connector J 8.0\, Size: 186
1: File: INFO_SRC, Directory: C:\Program Files (x86)\MySQL\Connector J 8.0\, Size: 136
1: File: LICENSE, Directory: C:\Program Files (x86)\MySQL\Connector J 8.0\, Size: 100771
1: File: mysql-connector-java-8.0.23.jar, Directory: C:\Program Files (x86)\MySQL\Connector J 8.0\, Size: 2415211
1: File: README, Directory: C:\Program Files (x86)\MySQL\Connector J 8.0\, Size: 1245
1: Action 13:28:13: RegisterProduct. Registering product
1: {42EFD60B-24CE-466E-864F-8ED744643ED3}
1: Action 13:28:13: PublishFeatures. Publishing Product Features
1: Feature: ProductFeature
1: Action 13:28:13: PublishProduct. Publishing product information
1: Action 13:28:13: RollbackCleanup. Removing backup files
1: 1: MySQL Connector J 2: {42EFD60B-24CE-466E-864F-8ED744643ED3} 3: 1
1: The action 'Install' for product 'Connector/J 8.0.23' completed successfully.So next time you start the MySQL Installer you will see all installed components like this:
Setup SQL Developer to use J connector
Now we need to setup SQL Developer to manage database connections using the J connector.
Within SQL Developer, choose Tools from the top menu, then Preferences, then under Database, you’ll see Third Party JDBC Drivers.
Now navigate to the jar file “C:\Program Files (x86)\MySQL\Connector J 8.0\mysql-connector-java-8.0.23.jar”.
Now create a database connection in SQL Developer, and the MySQL database type will become available to select.
So use the same root account that was used earlier, and the 127.0.0.1 hostname and forwarded local port 3306.
Now you should have the ability to access the MySQL database.
If you already have a login to an Oracle database, then you can connect to both at the same time.
For a Vagrant virtual machine running an Oracle database, you will use 127.0.0.1 as before, and the local forwarded port of 1521.
Now you’ll have access to the Oracle database.
Now you are setup with access to MySQL and Oracle instances with a common tool.
Paul Guerin is an international consultant that specialises in Oracle database. Paul is based from a global delivery center in South East Asia, but has clients from Australia, Europe, Asia, and North America. Moreover, he has presented at some of the world’s leading Oracle conferences, including Oracle Open World 2013. Since 2015, his work has been featured in the IOUG Best Practices Tip Booklet, and in publications from AUSOUG, Oracle Technology Network, Quest, and Oracle Developers (Medium). In 2019, he was awarded as a most valued contributor for the My Oracle Support Community. He is a DBA OCP, and continues to be a participant of the Oracle ACE program.
