Data migration tool - Part II: from MySQL to IRIS

This article is a follow-up to the previous one on how to migrate from popular databases (like PostgreSQL and MySQL) to IRIS.
We will use the same procedures utilized to migrate from PostgreSQL. However, you will see that it is even easier since the data types in MySQL are very similar to IRIS. That is why we will not need to create transformation rules in the columns.

Get the sample data to the migration process

In GitHub it is possible to download a docker-compose project to build and run 2 databases:

• Source Database: MySQL database Docker instance with a sample database.
• Target Database: InterSystems IRIS data platform Docker instance with a ready schema to receive the source database.

To get the sample and run it, follow these steps:

1. Go to the git repository https://github.com/yurimarx/migration-mysql-iris.
2. Clone the project: git clone https://github.com/yurimarx/migration-mysql-iris.git.  
3. Go to the project folder migration-mysql-iris.
4. Do a build: docker-compose build.
5. Execute the containers: docker-compose up -d.
6. Check on your docker desktop with the instances if everything is ok:

About the data to be migrated

The data to be migrated is the same data model used in Part I, and it is represented here:

So, the migration process from MySQL to IRIS will migrate:

• 8 tables.
• 5000 rows of sale.
• 2500 rows of users.
• 200 rows of product.
• 50 rows of store.
• 100 rows of country.
• 30 rows of city.
• 5 rows of status_name.

The migration destination will be the dc_test schema inside the USER namespace in the InterSystems IRIS database.

Open-source tool to migrate from MySQL to IRIS: DBeaver

In Part I, we used DBeaver Community Edition to migrate. We will use it again.
DBeaver is a database tool to connect, create, drop, select, update and delete data objects in the major database products on the market. Download it from: https://openexchange.intersystems.com/package/DBeaver. Now follow the installation instructions to get this fantastic product into your laptop or desktop.
DBeaver can be used to migrate data between database connections, even if they are from different manufacturers and versions.

Connecting the Source and Target Databases using the DBeaver

Now we will set the database connections to be migrated.
To set MySQL connection to the DBeaver:
1.    In DBeaver, Go to File > New.
2.    Select Database Connection and click Next:

3.    Choose SQL tab > MySQL and click next:

4.    Fill in the MySQL connection fields in the Main tab as shown in this picture:

5.    Go to the Driver properties tab and set allowPublicKeyRetrieval to the value TRUE:

6.    Continue in the Driver properties tab and set useSSL to the value FALSE:

7.    If DBeaver requests to download the MySQL driver, press Yes or Ok.
8.    Click Finish.

To set InterSystems IRIS connection to DBeaver:
In part I, you configured the IRIS connection, but if you did not have it saved, do this:
1.    In DBeaver, Go to File > New.
2.    Select Database Connection and click Next:

3.    Choose SQL tab > InterSystems IRIS and click next:

4.    If DBeaver requests to download the InterSystems IRIS driver, press Yes or Ok.
5.    Set the InterSystems IRIS connection fields as shown in this picture:

The connections (db and user) are available in the Database Navigator:

Do the migration

The migration process is very similar to the one described in Part I. Just follow these steps:
1.    Expand the db connection > Databases > db > Tables and select all tables. Click with the mouse right button on the selected tables and choose Export Data, as demonstrated in this picture:

2.    Select Database, as illustrated in this picture and click Next:

3.    Click the Choose button:

4.    Select dc_test and click Ok (P.S.: if you need to create advanced transformations between target and sources columns, click the Columns button).  

5.    Now, click Next.

6.    Set Fetch size to 1000000 and click Next.

7.    Accept the default values in the Data load settings and click Next.

8.    In the Confirm click Proceed.

9.    Now, if you open the Database Navigator, you will be able to see all MySQL Tables inside InterSystems IRIS dc_test schema.

Just as PostgreSQL migration, the MySQL migration process was very simple for tables. However, for views, functions, triggers and stored procedures, you need to rewrite the SQL source code using ObjectScript or SQL.