Infrastructure at your Service

Saïd Mendi

From MySQL (Oracle) to Postgres using the EDB Migration Toolkit

Why should you migrate?
If your current MySQL database does not offer some needed functionnalities according to your business as:
– more security
– more high availibilty options (hot standby)
– Strong Data Warehouse capabilities
If you want to consolidate the number of different instances (Postgres, MySQL, MS-SQL,…)
If you want to reduce administrative costs by using fewer database platforms
Which tool should you use?
the migration Toolkit command-line from EnterpriseDB that can be found below
http://www.enterprisedb.com/products-services-training/products-overview/postgres-plus-solution-pack/migration-toolkit
Why ?
Really easy to use
Which MySQL Objects are supported for the migration?
– Schemas
– Tables
– Constraints
– Indexes
– Table Data
What about partitionned table?
You have to remove the partitions before the migration
mysql> ALTER TABLE Table_name REMOVE PARTITIONING;
My environment:
MySQL: 5.7.14 on Oracle Linux Server 7.1
PostgreSQL: 9.6.1.4 on Oracle Linux Server 7.1
What are the prerequisites?
– download the migration toolkit from EnterpriseDB
Note that it can be only installed by registered users but the registration is free and can be done directly on the EnterpriseDB website.
– Install it and follow the instructions
./edb-migrationtoolkit-50.0.1-2-linux-x64.run
– download the MySQL JDBC driver: mysql-connector-java-5.1.40-bin.jar
http://www.enterprisedb.com/downloads/third-party-jdbc-drivers
– Install the driver by moving it to the right directory:
/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.111-2.b15.el7_3.x86_64/jre/lib/ext
– To facilitate  the migration, you have to prepare the configuration file: toolkit.properties located in your installation directory
the most important is to associate the right JDBC URL to the SRC_DB_URL parameter
SRC_DB_URL=jdbc:mysql://hostname[:port]/database
Following is the content of the config file
SRC_DB_URL=jdbc:mysql://192.168.56.200:33001/employees
SRC_DB_USER=root
SRC_DB_PASSWORD=manager
TARGET_DB_URL=jdbc:edb://192.168.56.200:5433/employees # the database must be created in Postgres before
TARGET_DB_USER=postgres
TARGET_DB_PASSWORD=manager

In case you get MySQL connection problems (SSL), modify the parameter SRC_DB_URL
SRC_DB_URL=jdbc:mysql://192.168.56.200:33001/employees?autoReconnect=true&useSSL=false
This will disable SSL and also suppress SSL errors.
Before starting the Migration, it is mandatory to create a blank target database in the Postgres instance
What options for the migration ?
-sourcedbtype is mysql
-targetdbtype is enterprisedb
-fetchsize is 1  to avoid  an ‘out of heap space’ error and force the toolkit to load data one row at a time
How to start the migration?
[[email protected]_essentials_p1 mtk]# bin/runMTK.sh -sourcedbtype mysql -targetdbtype enterprisedb -fetchSize 1 employees
Running EnterpriseDB Migration Toolkit (Build 50.0.1) ...
Source database connectivity info...
conn =jdbc:mysql://192.168.56.200:33001/employees?autoReconnect=true&useSSL=false
user =root
password=******
Target database connectivity info...
conn =jdbc:edb://192.168.56.200:5433/employees
user =postgres
password=******
Connecting with source MySQL database server...
Connected to MySQL, version '5.7.14-enterprise-commercial-advanced-log'
Connecting with target EDB Postgres database server...
Connected to EnterpriseDB, version '9.6.1.4'
Importing mysql schema employees...
Creating Schema...employees
Creating Tables...
Creating Table: departments
..........................
Created 6 tables.
Loading Table Data in 8 MB batches...
Loading Table: departments ...
[departments] Migrated 9 rows.
..............................
Loading Table: salaries ...
[salaries] Migrated 246480 rows.
................................
[salaries] Migrated 2844047 rows.
[salaries] Table Data Load Summary: Total Time(s): 20.143 Total Rows: 2844047 Total Size(MB): 94.1943359375
Loading Table: titles ...
[titles] Migrated 211577 rows.
[titles] Migrated 419928 rows.
[titles] Migrated 443308 rows.
[titles] Table Data Load Summary: Total Time(s): 3.898 Total Rows: 443308 Total Size(MB): 16.8955078125
Data Load Summary: Total Time (sec): 33.393 Total Rows: 3919015 Total Size(MB): 138.165
Creating Constraint: PRIMARY
Creating Constraint: dept_name
................................
Creating Index: dept_no1
Schema employees imported successfully.
Migration process completed successfully.
Migration logs have been saved to /root/.enterprisedb/migration-toolkit/logs
******************** Migration Summary ********************
Tables: 6 out of 6
Constraints: 11 out of 11
Indexes: 2 out of 2
Total objects: 19
Successful count: 19
Failed count: 0
Invalid count: 0
************************************************************

So as you can see, this migration process is really easy and you can take immediately benefits of all the standard features.

One Comment

  • Simon says:

    This is my toolkit.properties
    SRC_DB_URL=jdbc:mysql://localhost:3306/misoo_retail_dynamics
    SRC_DB_USER=root
    SRC_DB_PASSWORD=root
    TARGET_DB_URL=jdbc:edb://localhost:5432/misoo_jugo
    TARGET_DB_USER=postgres
    TARGET_DB_PASSWORD=postgres

    but compalins as follows
    C:\Program Files (x86)\PostgresPlus\edbmtk\bin>runMTK.bat -sourcedbtype mysql -targetdbtype postgres misoo_jugo
    Running EnterpriseDB Migration Toolkit (Build 52.0.3) …
    Source database connectivity info…
    conn =jdbc:mysql://localhost:3306/misoo_retail_dynamics
    user =root
    password=******
    Target database connectivity info…
    conn =jdbc:edb://localhost:5432/misoo_jugo
    user =postgres
    password=******
    Connecting with source MySQL database server…
    MTK-11009: Error Connecting Database “MySQL Server”
    DB-null: java.sql.SQLException: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

    One or more schema objects could not be imported during the migration process. Please review the migration output for more details.

    Migration logs have been saved to C:\Users\Simon\.enterprisedb\migration-toolkit\logs

    ******************** Migration Summary ********************

    Total objects: 0
    Successful count: 0
    Failed count: 0
    Invalid count: 0

    *************************************************************

    I have put mysql and postgres jdbc drivers in C:\Program Files (x86)\PostgresPlus\edbmtk\lib

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Saïd Mendi
Saïd Mendi

Senior Consultant