MIGRATION FROM MYSQL TO EDB POSTGRES USING MTK

by bensonyerima

MySQL is an open-source relational database management system. MySQL Database Service is a fully managed database service to deploy cloud-native applications using the world’s most popular open source database.

In this demo, we will not be going into understanding mysql and features. but this will be based on basic installation and mostly performing a migration using edbpostgres migration toolkit (MTK). If you want to learning more about MYSQL, you can get more information from here

[[email protected] ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> 
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> create database benson;
Query OK, 1 row affected (0.00 sec)

mysql> use benson
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| benson     |
+------------+
1 row in set (0.00 sec)

mysql> create user migrate_user identified by '[email protected]';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE USER 'foo'@'%' IDENTIFIED WITH mysql_native_password BY '[email protected]';
Query OK, 0 rows affected (0.00 sec)

mysql> select user from mysql.user;
+---------------+
| user          |
+---------------+
| foo           |
| migrate_user  |
| mysql.session |
| mysql.sys     |
| root          |
+---------------+
5 rows in set (0.00 sec)

mysql> 
mysql> grant all privileges on *.* to 'foo'@'%';
Query OK, 0 rows affected (0.00 sec)


[[email protected] Downloads]# mysql -u foo -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> 

Database changed
mysql> 
mysql> source /home/benson/Downloads/mysqlsampledatabase.sql;
Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 122 rows affected (0.02 sec)
Records: 122  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 23 rows affected (0.00 sec)
Records: 23  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 2996 rows affected (0.05 sec)
Records: 2996  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 326 rows affected (0.00 sec)
Records: 326  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 273 rows affected (0.01 sec)
Records: 273  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 110 rows affected (0.02 sec)
Records: 110  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 


mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| benson             |
| classicmodels      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> 
mysql> use classicmodels
Database changed
mysql> 
mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
+-------------------------+
8 rows in set (0.00 sec)

mysql> 

mysql> show full tables;
+-------------------------+------------+
| Tables_in_classicmodels | Table_type |
+-------------------------+------------+
| customers               | BASE TABLE |
| employees               | BASE TABLE |
| offices                 | BASE TABLE |
| orderdetails            | BASE TABLE |
| orders                  | BASE TABLE |
| payments                | BASE TABLE |
| productlines            | BASE TABLE |
| products                | BASE TABLE |
+-------------------------+------------+
8 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM customers;
+----------+
| COUNT(*) |
+----------+
|      122 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
|       23 |
+----------+
1 row in set (0.00 sec)


===ON MY EBD POSTGRES SERVER (192.168.91.157)===
TEST TO MAKE SURE I CAN CONNECT TO 192.168.91.132 WHERE MYSQL DATABASE IS RUNNING
[[email protected] ext]# 
[[email protected] ext]# mysql -h 192.168.91.132 -u foo -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| benson             |
| classicmodels      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

—-ABOVE SHOWS I CAN CONNECT..

==INSTALL MIGRATION TOOLKIT

[[email protected] ~]# yum install edb-migrationtoolkit

Download the mysql-connector-java-..*-bin.jar file and place it in the \jre\lib\ext directory (for example /usr/lib/jvm/jre-1.8.0/lib/ext/)under your Migration Toolkit server. When the .jar file is in place, you can establish a connection to a MySQL server with Migration Toolkit.

https://www.enterprisedb.com/downloads/third-party-jdbc-drivers 
[[email protected] ext]# ll
total 8412
-rw-r--r--. 1 root root 4003855 Apr 29  2020 cldrdata.jar
-rw-r--r--. 1 root root    9445 Apr 29  2020 dnsns.jar
-rw-r--r--. 1 root root   48733 Apr 29  2020 jaccess.jar
-rw-r--r--. 1 root root 1204895 Apr 29  2020 localedata.jar
-rw-r--r--. 1 root root     617 Apr 29  2020 meta-index
-rw-r--r--. 1 root root   26024 Apr 24  2019 mysql80-community-release-el7-3.noarch.rpm
-rw-r--r--. 1 root root  540852 Dec  1 13:54 mysql-connector-java-5.0.8-bin.jar
-rw-r--r--. 1 root root 2033791 Apr 29  2020 nashorn.jar
-rw-r--r--. 1 root root   51885 Apr 29  2020 sunec.jar
-rw-r--r--. 1 root root  306223 Apr 29  2020 sunjce_provider.jar
-rw-r--r--. 1 root root  280125 Apr 29  2020 sunpkcs11.jar
-rw-r--r--. 1 root root   78166 Apr 29  2020 zipfs.jar
[[email protected] ext]# 
[[email protected] ext]# 
[[email protected] ext]# pwd
/usr/lib/jvm/jre/lib/ext
[[email protected] ext]# 
[[email protected] ext]# 

/usr/edb/migrationtoolkit/bin
-bash-4.2$ cd ..
-bash-4.2$ ll
total 28
drwxr-xr-x. 2 root         root            84 Dec  1 13:29 bin
-r--r--r--. 1 enterprisedb enterprisedb 10242 Sep 14  2019 edb-migrationtoolkit_3rd_party_licenses.txt
-rwxr-xr-x. 1 root         root         15272 Nov  4 04:48 edb-migrationtoolkit_license.txt
drwxr-xr-x. 2 root         root            62 Dec  1 13:49 etc
drwxr-xr-x. 2 root         root           104 Dec  1 13:29 lib
-bash-4.2$ cd etc/
-bash-4.2$ ll
total 8
-rw-------. 1 enterprisedb enterprisedb 196 Dec  1 15:23 toolkit.properties
-rw-------. 1 root         root         177 Dec  1 13:47 toolkit.properties.old
-bash-4.2$ cat toolkit.properties
SRC_DB_URL=jdbc:mysql://192.168.91.132:3306/classicmodels
SRC_DB_USER=foo
[email protected]

TARGET_DB_URL=jdbc:edb://localhost:5444/edb
TARGET_DB_USER=enterprisedb
TARGET_DB_PASSWORD=postgres
-bash-4.2$

As seen above, your toolkit.properties needs look like seen.

-bash-4.2$ 
-bash-4.2$ ./runMTK.sh -sourcedbtype mysql -targetdbtype enterprisedb classicmodels
Running EnterpriseDB Migration Toolkit (Build 54.0.0) ...
Source database connectivity info...
conn =jdbc:mysql://192.168.91.132:3306/benson
user =foo
password=******
Target database connectivity info...
conn =jdbc:edb://localhost:5444/edb
user =enterprisedb
password=******
Connecting with source MySQL database server...
Connected to MySQL, version '5.7.32'
Connecting with target EDB Postgres database server...
Connected to EnterpriseDB, version '10.15.24'
Importing mysql schema classicmodels...
Creating Schema...classicmodels 
Creating Tables...
Creating Table: customers
Creating Table: employees
Creating Table: offices
Creating Table: orderdetails
Creating Table: orders
Creating Table: payments
Creating Table: productlines
Creating Table: products
Created 8 tables.
Loading Table Data in 8 MB batches...
Loading Table: classicmodels.customers ...
[customers] Migrated 122 rows.
[customers] Table Data Load Summary: Total Time(s): 0.132 Total Rows: 122 Total Size(MB): 0.0126953125
Loading Table: classicmodels.employees ...
[employees] Migrated 23 rows.
[employees] Table Data Load Summary: Total Time(s): 0.087 Total Rows: 23 Total Size(MB): 9.765625E-4
Loading Table: classicmodels.offices ...
[offices] Migrated 7 rows.
[offices] Table Data Load Summary: Total Time(s): 0.071 Total Rows: 7
Loading Table: classicmodels.orderdetails ...
[orderdetails] Migrated 2996 rows.
[orderdetails] Table Data Load Summary: Total Time(s): 0.14 Total Rows: 2996 Total Size(MB): 0.076171875
Loading Table: classicmodels.orders ...
[orders] Migrated 326 rows.
[orders] Table Data Load Summary: Total Time(s): 0.155 Total Rows: 326 Total Size(MB): 0.0224609375
Loading Table: classicmodels.payments ...
[payments] Migrated 273 rows.
[payments] Table Data Load Summary: Total Time(s): 0.076 Total Rows: 273 Total Size(MB): 0.0078125
Loading Table: classicmodels.products ...
[products] Migrated 110 rows.
[products] Table Data Load Summary: Total Time(s): 0.075 Total Rows: 110 Total Size(MB): 0.02734375
Loading Large Objects into table: classicmodels.productlines ...
[productlines] Migrated 1 rows.
[productlines] Migrated 2 rows.
[productlines] Migrated 3 rows.
[productlines] Migrated 4 rows.
[productlines] Migrated 5 rows.
[productlines] Migrated 6 rows.
[productlines] Migrated 7 rows.
[productlines] Table Data Load Summary: Total Time(s): 0.119 Total Rows: 7
Data Load Summary: Total Time (sec): 1.083 Total Rows: 3864 Total Size(MB): 0.151
Creating Constraint: PRIMARY
Creating Constraint: PRIMARY
Creating Constraint: PRIMARY
Creating Constraint: PRIMARY
Creating Constraint: PRIMARY
Creating Constraint: PRIMARY
Creating Constraint: PRIMARY
Creating Constraint: PRIMARY
Creating Constraint: customers_ibfk_1
Creating Constraint: employees_ibfk_1
Creating Constraint: employees_ibfk_2
Creating Constraint: orderdetails_ibfk_1
Creating Constraint: orderdetails_ibfk_2
Creating Constraint: orders_ibfk_1
Creating Constraint: payments_ibfk_1
Creating Constraint: products_ibfk_1
Creating Index: salesRepEmployeeNumber
Creating Index: reportsTo
Creating Index: officeCode
Creating Index: productCode
Creating Index: customerNumber
Creating Index: productLine

Schema classicmodels imported successfully.


Migration process completed successfully.

Migration logs have been saved to /var/lib/edb/.enterprisedb/migration-toolkit/logs

******************** Migration Summary ********************
Tables: 8 out of 8
Constraints: 16 out of 16
Indexes: 6 out of 6

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

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

Now lets check our edb postgres server to make sure everything is looking good.

-bash-4.2$ 

edb=# \dn
       List of schemas
     Name      |    Owner     
---------------+--------------
 benson        | enterprisedb
 classicmodels | enterprisedb
 public        | enterprisedb
(3 rows)

edb=# alter system set search_path to classicmodels;
ALTER SYSTEM
edb=# 
edb=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

edb=# \dt
                  List of relations
    Schema     |     Name     | Type  |    Owner     
---------------+--------------+-------+--------------
 classicmodels | customers    | table | enterprisedb
 classicmodels | employees    | table | enterprisedb
 classicmodels | offices      | table | enterprisedb
 classicmodels | orderdetails | table | enterprisedb
 classicmodels | orders       | table | enterprisedb
 classicmodels | payments     | table | enterprisedb
 classicmodels | productlines | table | enterprisedb
 classicmodels | products     | table | enterprisedb
(8 rows)

edb=# select count(*) from customers;
 count 
-------
   122
(1 row)

edb=# SELECT COUNT(*) FROM employees;
 count 
-------
    23
(1 row)

edb=#

About the author

Avatar photo

bensonyerima

Hi, I'm Benson Yerima, a database administrator with an obsession for all things tech. This blog is dedicated for helping people learn about database technology.

View all posts

8,856 Comments