search cancel

API Portal: MySQL database migration

book

Article ID: 227225

calendar_today

Updated On:

Products

CA API Developer Portal

Issue/Introduction

Migrate database from PostgreSQL to MySQL following this Document: 

https://techdocs.broadcom.com/us/en/ca-enterprise-software/layer7-api-management/api-developer-portal/5-0/install-configure-and-upgrade/install-portal-on-docker-swarm/migrating-portal-data-from-postgresql-to-mysql.html

Start portal after run migration tool:

sudo ./portal.sh

This command never end.

[[email protected] apim-portal-5.0-final]# ./portal.sh
Creating network portal_db
Creating service portal_db-upgrade
Creating service portal_db-upgrade-rbac
Checking to see if DB schema update is needed........................................

Environment

Release : 5.0

Component : API PORTAL

Cause

Ran into more that one root cause 

1. SSL issue between the portal and external mysql 8.0 failed

2. Data migration of data from PostgreSQL failing 

The migration data/logs are needed to determine this during the run need to use the -v then tar the migrate folder

-v /home/centos/migrate:/opt/store 

 

Resolution

1. SSL issue between the portal and external mysql 8.0 failed

Solved by updating poral to 5.0 CR1 (5.0 did not support mysql 8.0)

2. Data migration of data from PostgreSQL failing 

Steps to properly migrate

NOTE:  You would need to install docker and then pull our image using: docker pull caapim/gateway Please take a look at the following documentation: https://docops.ca.com/ca-api-gateway/9-3/en/other-gateway-form-factors/using-the-container-gateway

 

Need to PULL the proper image:

# docker pull caapim/apim-portal-migration:4.5

OR

# docker pull caapim/apim-portal-migration:5.0.cr1

 

Digest: sha256:00e69387707bd2d2c71d37b73f04bd758c6121fc2c1ccb37ca155e535c3521a4

Status: Downloaded newer image for caapim/apim-portal-migration:5.0.cr1

 

Check the digest

# docker images --digests  caapim/apim-portal-migration

REPOSITORY                     TAG                 DIGEST                                                                    IMAGE ID            CREATED             SIZE

caapim/apim-portal-migration   4.5                 sha256:848ff3f20147c5c493995c5109cf1f9cca88f5f9821b55769b891f72a01fbf2a   c20ff7ce4c2d        8 months ago        889MB

caapim/apim-portal-migration   5.0.cr1             sha256:00e69387707bd2d2c71d37b73f04bd758c6121fc2c1ccb37ca155e535c3521a4   f905a4d65262        8 months ago        912MB

 

Expose the postgres db to be accessed outside docker swarm

# docker service update --publish-add "5432:5432" portal_portaldb;

 

CHECK exposed 

# docker service ls | grep postgre

szz5sbyke06e        portal_portaldb             replicated          1/1                 apim-portal.packages.ca.com/apim-portal/postgres:4.5                      *:5432->5432/tcp

 

Setup docker network

# docker network create migration-net

76170c19cf56b3ce67e84e856d675f88ee7a0256400a87204ef4dee4f28efb24

 

Get PostgreSQL containerID

# docker ps | grep postgres

e19627125bd2        caapim/postgres:5.0.cr1                      "/usr/local/bin/entr…"   2 minutes ago       Up About a minute (healthy)   5432/tcp                                                                                portal_portaldb.1.qaolz39aud5enz4nka9sz4pmo

 

Connect postgres master container to new network and get ID:

# docker network ls

NETWORK ID          NAME                DRIVER              SCOPE

1c9c4dd1c95f        bridge              bridge              local

c9d8f9b46e40        docker_gwbridge     bridge              local

599ec0802dc2        host                host                local

ibnfboqc2mxi        ingress             overlay             swarm

76170c19cf56        migration-net       bridge              local

 

Command to connect network:

# docker network connect 76170c19cf56 e19627125bd2

OR

# docker network connect migration-net e19627125bd2

 

Check with docker inspect 

# docker network inspect migration-net

[

    {

        "Name": "migration-net",

        "Id": "76170c19cf56b3ce67e84e856d675f88ee7a0256400a87204ef4dee4f28efb24",

        "Created": "2021-10-26T18:58:02.757908079-04:00",

        "Scope": "local",

        "Driver": "bridge",

        "EnableIPv6": false,

        "IPAM": {

            "Driver": "default",

            "Options": {},

            "Config": [

                {

                    "Subnet": "<docker sub/16>",

                    "Gateway": "<docker ip>"

                }

            ]

        },

        "Internal": false,

        "Attachable": false,

        "Ingress": false,

        "ConfigFrom": {

            "Network": ""

        },

        "ConfigOnly": false,

        "Containers": {

            "e19627125bd2a183a2fe6e21e90b47593f8053f70b03ac3eebbd4357bbd0a2a9": {

                "Name": "portal_portaldb.1.qaolz39aud5enz4nka9sz4pmo",

                "EndpointID": "5c2ef3071b2f472760b56556a8bd9e422f474e310c42c46b893e33cabe9345d8",

                "MacAddress": "02:42:ac:13:00:02",

                "IPv4Address": "<docker sub/16>",

                "IPv6Address": ""

            }

        },

        "Options": {},

        "Labels": {}

    }

]

 

Run the migration scripts:

Portal 4.5

# docker run --rm -it -v /home/centos/migrate:/opt/store --network=migration-net caapim/apim-portal-migration:4.5 

OR 

Portal 5.0 cr1

# docker run --rm -it -v /home/centos/migrate:/opt/store --network=migration-net caapim/apim-portal-migration:5.0.cr1

 

IMPORTANT use the IP address used to access the portal

Source:

Please enter database connection settings PostgreSQL source database

PostgreSQL hostname [default: portaldb]: <extneral IP portal>

PostgreSQL port [default: 5432]:5432

PostgreSQL username [default: admin]:admin

PostgreSQL password [default: 7layer]: <password>

 

Target:

Please enter database connection settings MySQL targe database

MySQL hostname [default: 127.0.0.1]: <external IP mysql>

MySQL port [default: 3306]:3306

MySQL username [default: root]: portal

MySQL password [default: password]: <password>