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
Really easy to use
Which MySQL Objects are supported for the migration?
– Table Data
What about partitionned table?
You have to remove the partitions before the migration
mysql> ALTER TABLE Table_name REMOVE PARTITIONING;
MySQL: 5.7.14 on Oracle Linux Server 7.1
PostgreSQL: 184.108.40.206 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
– download the MySQL JDBC driver: mysql-connector-java-5.1.40-bin.jar
– Install the driver by moving it to the right directory:
– 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
Following is the content of the config file
TARGET_DB_URL=jdbc:edb://192.168.56.200:5433/employees # the database must be created in Postgres before
In case you get MySQL connection problems (SSL), modify the parameter SRC_DB_URL
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...
Target database connectivity info...
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 '220.127.116.11'
Importing mysql schema employees...
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.
Why should you migrate?