How to change default databases of WSO2 API Manager to databases in MySQL Docker Container on Ubuntu

1. Install Docker Engine using this link.
2. Create and run MySQL as a Docker container using following command.

sudo docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:latest

3. [Optional] Start the mysql container.

sudo docker start mysql

4. View running Docker containers to get container-id of mysql container.

sudo docker ps -a

5. Copy <API-M_HOME>/dbscripts/apimgt/mysql.sql file to mysql container.

sudo docker cp <API-M_HOME>/dbscripts/apimgt/mysql.sql <container-id>:/var/lib/mysql/

6. Login to mysql container.

sudo docker exec -i -t mysql /bin/bash

7. Login to MySQL console as root user.

mysql -u root -proot

8. Create a database named test.

create database test character set latin1;

9. Change current database to test.

use test

10. Run mysql.sql database script.

source var/lib/mysql/mysql.sql

11. Exit the mysql console.

exit

12. Download and copy mysql-connector-java-<version>.jar from this link to <API-M_HOME>/repository/components/lib directory.

13. Change the configurations.

Below configurations show changes to WSO2AM_DB.
Follow the same pattern for other databases.

• If your WSO2 API-M version < 3.0.0 then change configurations in <API-M_HOME>/repository/conf/datasources/master-datasources.xml file as follows.

<datasource>
    <name>WSO2AM_DB</name>
    <description>The datasource used for API Manager database</description>
    <jndiConfig>
        <name>jdbc/WSO2AM_DB</name>
    </jndiConfig>
    <definition type="RDBMS">
        <configuration>
            <url>jdbc:mysql://localhost:3306/test?useSSL=false&#38;allowPublicKeyRetrieval=true</url>
            <username>root</username>
            <password>root</password>
            <driverClassName>com.mysql.cj.jdbc.Driver</driverClassName>
            <maxActive>80</maxActive>
            <maxWait>360000</maxWait>
            <minIdle>5</minIdle>
            <testOnBorrow>true</testOnBorrow>
            <validationQuery>SELECT 1</validationQuery>
            <validationInterval>30000</validationInterval>
            <defaultAutoCommit>false</defaultAutoCommit>
        </configuration>
    </definition>
</datasource>

• If your WSO2 API-M version ≥ 3.0.0 then change configurations in <API-M_HOME>/repository/conf/deployment.toml file as follows.

[database.apim_db]
type = "mysql"
url = "jdbc:mysql://localhost:3306/test?useSSL=false"
username = "root"
password = "root"
driver = "com.mysql.jdbc.Driver"
validationQuery = "SELECT 1"

14. Now start/restart WSO2 API Manager.

Note: If you get java.sql.SQLException: Unable to load authentication plugin ‘caching_sha2_password’ Try running following command in mysql console.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';