Installing MySQL docker on Mac Os and configuring MySQL DBs with WSO2 APIM 4.2.0

5 min readJul 31, 2023

Hello everyone,

I hope everyone lives happily and safely.

Reference: https://www.pinterest.com/pin/641270434416638331/

Recently I had a requirement to keep two versions of MySQL on my machine. I have already installed MySQL 5.7. Now I need to install and use MySQL 8 too. To configure MySQL 8 on my machine, I need to remove the existing MySQL 5.7 and install the new version. Therefore, I decided to go with Docker because it allows me to keep & consume both MySQL versions.

Prerequisites

Install Docker Desktop or Rancher Desktop on Mac OS
Download WSO2 APIM 4.2.0
Install JDK 11 or JDK 17 (You need to install JDK 11 or higher version to run the APIM 4.2.0 on your machine) and set up JAVA HOME.
Download the MySQL CJ JDBC driver for MYSQL 8.

Please complete the above prerequisites before proceeding to the next step.

Install MySQL 8.0 through Docker

First, ensure Docker Desktop or Rancher Desktop is installed successfully and verify the Docker works fine on your machine (e.g., execute `docker — version` and see whether you can get the version). All fine, let’s start,

Pull the latest MySQL server docker image: Present, MySQL 8 is the latest version. Therefore, pulling the MySQL server's latest tag will download the MYSQL 8.

$ docker pull mysql/mysql-server

2. Run the “mysql-server” docker container: Here, I have used the “mysql-server” as my container name, and this name can use for future steps. And I have mapped the docker MySQL server default port(3306) to a different port(3308) on my machine(Host machine) becuase the existing MySQL server consumes 3306 on my machine.

docker run --name=<your_name_mysql-server> -d -p <host_machine_port_to_map>:<mysql_default_port> mysql/mysql-server:latest 

Example:
docker run --name=mysql-server -d -p 3308:3306 mysql/mysql-server:latest

3. Get the current one-time use password via docker logs and change the root user password:

You need to execute the below command to get the MySQL docker logs.

docker logs <your_name_mysql-server>

Exammple:

docker logs mysql-server

Then you will see MySQL server logs and 1-time password generated after creating the MySQL server instance. Please refer to the below screenshot for your reference.

One-time password for root use of MySQL server

Now you can access the “mysql-server” container and change the root user's password with the below steps.

docker exec -it <your_name_mysql-server> mysql -u root -p
Enter Password: <enter GENERATED ROOT PASSWORD shown from docker logs>

Example: 

docker exec -it mysql-server mysql -u root -p
Enter password:

You can access the MySQL server once you give the one-time password we found early.

Log in to the MySQL server with the root user.

Now you change the root user’s password by executing the following commands.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'your-password'

As the next step, you can create a separate user to connect with the WSO2 APIM product or other DB tools such as dbeaver. Here, I put the username as “root” for easiness.

Afterwards, You need to grant relevant permission for that user, such as create connection and crud operations. Here, I have granted all privileges.

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'your_password'
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
mysql> FLUSH PRIVILEGES;

Now you can use this DB user to configure on APIM toml file to establish the connection between the product server and the DB server.

4. Let’s create a DB to configure the APIM product.

As a sample, I am going to create an apimgt DB and configure it with the APIM product. You can follow the following options.

Option 01

mysql> CREATE DATABASE <Your_DB_Name> CHARACTER SET latin1 COLLATE latin1_general_ci;
Example:
mysql>  CREATE DATABASE WSO2AM_DB CHARACTER SET latin1 COLLATE latin1_general_ci;
mysql> use <Your_DB_Name>;
Example:
mysql> use WSO2AM_DB;
mysql> source mysql.sql ;

Here “mysql.sql”, you can find this db script under the “<APIM-420-HOME>/dbscripts/apimgt” directory. Then you need to copy this MySQL script to the docker mysql container. For that, you can use the below copy commands.

Get the up-and-running docker container list and find the container ID of the MySQL server.

 % docker ps 
CONTAINER ID   IMAGE                        COMMAND                  CREATED              STATUS                   PORTS                                                        NAMES                                                     k8s_wso2am_wso2am-single-node-am-deployment-6455c7c6f6-zj4mz_wso2am420_7a9b7e89-0bfe-4388-9693-bbc3ee111b7a_19
de0*********   mysql/mysql-server:latest    "/entrypoint.sh mysq…"   6 minutes ago        Up 5 minutes (healthy)   33060-33061/tcp, 0.0.0.0:3308->3306/tcp, :::3308->3306/tcp   mysql-server

Then copy the relevant MySQL script into the container.

docker cp <your_file_path>/file_name.sql <mysql_container_id>:<path_of_container>/<file_name_to_copy>/sql
Example:
docker cp <APIM_420_HOME>/dbscripts/apingt/mysql.sql de******:/mysql.sql
Successfully copied 30.2kB to de****:/mysql.sql

You will see the copied file once you access the container.

 docker exec -it <container_id> bin/bash
Example: 
 docker exec -it de***** bin/bash

You can use the copied file to create the required tables on the created DB with the source command .
Option 02
The second option is you can access the created DB using a DB tool like DB viewer, DBeaver .. etc. Then you can execute the relevant DB script using the tool’s UI.

Now you have finished the MySQL server installation and DB creation. Let’s configure it on the APIM 4.2.0 product.

Configure APIM 4.2.0

Unzip the downloaded APIM 4.2.0 product and open the “deployment.toml” file, which resides in the “<APIM-HOME-420>/repository/conf ‘ folder.
Configure/Replace the APIM DB config on the toml file as follows.

[database.apim_db]
type = "mysql"
url = "jdbc:mysql://localhost:3308/WSO2AM_DB?useSSL=false"
username = "root"
password = "**********"
driver= "com.mysql.cj.jdbc.Driver"

3. Copy the “com.mysql.cj.jdbc.Driver” to the “<APIM-HOME-420>/repository/components/lib” directory.

4. Start the APIM 4.2.0 pack with the "sh api-manager.sh" command.

That’s all……… :-))