Changing to MySQL¶
By default, WSO2 API Manager uses the embedded H2 database as the database for storing user management and registry data. Given below are the instructions you need to follow in order to use MySQL for this purpose.
The instructions involved in installing and configuring the databases are the same irrespective of whether you are using a single node (standalone) deployment, an active-active deployment, or a distributed deployment.
Setting up MySQL¶
The following sections describe how to set up a MySQL database to replace the default H2 database in your WSO2 product:
- Setting up the database and users
- Setting up the drivers
- Executing db scripts to create tables on MySQL database
Setting up the database and users¶
Follow the instructions below to set up a MySQL database:
WSO2 recommends that you use Failover configuration over Load Balanced configuration with the MySQL clusters.
Define the hostname for configuring permissions for the new database by opening the
/etc/hostsfile and adding the following:
Do this step only if your database is not on your local machine and on a separate server.
Start the MySQL service.
Install a mysql-client in each of the API-M servers in which WSO2 API-M is deployed.
Enter the following command in a command prompt, where
USER_NAMEis the username that you will use to access the databases and
MYSQL_HOST_IPis the IP of the host.
User should have the database creation privileges.
$ mysql -h <MYSQL_HOST_IP> -u <USER_NAME> -p
When prompted, specify the password that will be used to access the databases with the username you specified.
In the MySQL command prompt, create the database using the following command:
mysql> create database <DATABASE_NAME>;
Character Sets and Collations in MySQL
For users of Microsoft Windows, when creating the database in MySQL, it is important to specify the character set as latin1. Failure to do this may result in an error (error code: 1709) when starting your cluster. This error occurs in certain versions of MySQL (5.6.x) and is related to the UTF-8 encoding. MySQL originally used the latin1 character set by default, which stored characters in a 2-byte sequence. However, in recent versions, MySQL defaults to UTF-8 to be friendlier to international users. Hence, you must use latin1 as the character set as indicated below in the database creation commands to avoid this problem. Note that this may result in issues with non-latin characters (like Hebrew, Japanese, etc.). The following is how your database creation command should look.
mysql> create database <DATABASE_NAME> character set latin1;
If you are using MySQL to configure your datasource, we recommend that you use a case sensitive database collation. For more information, see the MySQL Official Manual. The default database collation, which is
latin1_swedish_ci, is case insensitive. However, you need to maintain case sensitivity for database collation, because when the database or table has a case-insensitive collation in MySQL 5.6 or 5.7, if a user creates an API with letters using mixed case, deletes the API, and then creates another API with the same name, but in lower case letters, then the later created API loses its permission information, because when deleting the API, it keeps the Registry collection left behind.
This issue could be avoided if you use a case sensitive collation for database and tables. In that case, when creating the second API (which has the same name, but is entirely in lowercase letters), it will create a new record with the lowercase name in the
Give authorization to the user you use to access the databases as follows.
For example, let's consider
apimadminas the user.
mysql> GRANT ALL ON regdb.* TO apimadmin@localhost IDENTIFIED BY "apimadmin";
If you are using MySQL version - 8.0.x, use following commands to create the user and grant authorization:
mysql> CREATE USER 'apimadmin'@'localhost' IDENTIFIED BY 'apimadmin';
mysql> GRANT ALL ON APIM.* TO 'apimadmin'@'localhost';
After you have finalized the permissions, reload all the privileges by executing the following command:
mysql> FLUSH PRIVILEGES;
Log out from the MySQL command prompt by executing the following command:
Setting up the drivers¶
Unzip the WSO2 API Manager pack. Let's refer to it as
Download the MySQL Java connector JAR file, and extract it.
Copy it to the
<API-M_HOME>/repository/components/lib/directory in all the nodes of the cluster.
Be sure to use the connector version that is supported by the MySQL version you use. If you come across any issues due to version incompatibility, follow the instructions below:
- Shut down the server and remove all existing connectors from the
- Download the connector JAR that is compatible with your current MySQL version.
Copy the JAR file only to the
Files will be copied automatically to the dropins folder during the server startup.
Executing db scripts to create tables on MySQL database¶
To create tables in the registry and user manager database (
WSO2_SHARED_DB), execute the relevant script as shown below.
$ mysql -u regadmin -p -Dshared_db < '<API-M_HOME>/dbscripts/mysql.sql';
To create tables in the apim database (
WSO2AM_DB), execute the relevant script as shown below.
$ mysql -u apimadmin -p -Dapim_db < '<API-M_HOME>/dbscripts/apimgt/mysql.sql';
<API-M_HOME>/dbscripts/mb-store/mysql-mb.sql is the script that should be used when creating the tables in
WSO2_MB_STORE_DB database. You can use H2 as the MB database even when working in production. However, if you need to change the MB database to MySQL, then you need to have seperate databases for each API-M Traffic Manager node.
- Ensure that MySQL is configured so that all nodes can connect to it.
- To access the databases from remote instances, its required to grant permission to the relevant username defined in the
[database.apim_db]elements, by using the grant command. See the following sample commands.
mysql> grant all on <DATABASE_NAME>.* TO '<username>'@'%' identified by '<password>';
mysql> grant all on shared_db.* TO 'wso2user'@'%' identified by 'wso2123'; mysql> grant all on apim_db.* TO 'wso2user'@'%' identified by 'wso2123';
In the sample commands above, its assumed that the username and password defined in the datasource configurations in
<API-M_HOME>/repository/conf/deployment.toml file is wso2user and wso2123 respectively.
Changing the database to MySQL¶
Creating the datasource connection to MySQL¶
A datasource is used to establish the connection to a database. By default,
WSO2AM_DB datasources are configured in the
deployment.toml file for the purpose of connecting to the default H2 databases.
After setting up the MySQL database to replace the default H2 database, either change the default configurations of the
WSO2AM_DB datasources, or configure a new datasource to point it to the new database as explained below.
If you are configuring API-M in a distributed setup, do the changes in all the WSO2 API-M components.
Follow the instructions below to change the type of the default datasources.
<API-M_HOME>/repository/conf/deployment.tomlconfiguration file and locate the
You simply have to update the URL pointing to your MySQL database, the username, and password required to access the database and the MySQL driver details as shown below.
Element Description type The database type used url The URL of the database. The default port for MySQL is 3306 username and password The name and password of the database user driverClassName The class name of the database driver
Sample configuration is shown below:
type = "mysql" url = "jdbc:mysql://localhost:3306/<DATABASE_NAME>" username = "<USER_NAME>" password = "<PASSWORD>"
[database.shared_db] type = "mysql" url = "jdbc:mysql://localhost:3306/shared_db" username = "regadmin" password = "regadmin" [database.apim_db] type = "mysql" url = "jdbc:mysql://localhost:3306/apim_db" username = "apimadmin" password = "apimadmin"
If you are using MySQL version - 8.0.x, you should add the driver name in the configuration as:
You can update the configuration elements given below for your database connection.
Element Description maxActive The maximum number of active connections that can be allocated at the same time from this pool. Enter any negative value to denote an unlimited number of active connections. maxWait The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception. You can enter zero or a negative value to wait indefinitely. minIdle The minimum number of active connections that can remain idle in the pool without extra ones being created, or enter zero to create none. testOnBorrow The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and another attempt will be made to borrow another. validationQuery The SQL query that will be used to validate connections from this pool before returning them to the caller. validationInterval The indication to avoid excess validation, and only run validation at the most, at this frequency (time in milliseconds). If a connection is due for validation but has been validated previously within this interval, it will not be validated again. defaultAutoCommit This property is not applicable to the Carbon database in WSO2 products because auto committing is usually handled at the code level, i.e., the default auto commit configuration specified for the RDBMS driver will be effective instead of this property element. Typically, auto committing is enabled for RDBMS drivers by default. When auto committing is enabled, each SQL statement will be committed to the database as an individual transaction, as opposed to committing multiple statements as a single transaction. commitOnReturn If
defaultAutoCommit =false, then you can set
commitOnReturn =true, so that the pool can complete the transaction by calling the commit on the connection as it is returned to the pool. However, If
rollbackOnReturn =truethen this attribute is ignored. The default value is false.
defaultAutoCommit =false, then you can set
rollbackOnReturn =trueso that the pool can terminate the transaction by calling rollback on the connection as it is returned to the pool. The default value is false.
Sample configuration is shown below:
type = "mysql" url = "jdbc:mysql://localhost:3306/<DATABASE_NAME>" username = "<USER_NAME>" password = "<PASSWORD>" pool_options.<OPTION-1> = <VALUE-1> pool_options.<OPTION-2> = <VALUE-2> ...
[database.shared_db] type = "mysql" url = "jdbc:mysql://localhost:3306/shared_db" username = "regadmin" password = "regadmin" pool_options.maxActive = 100 pool_options.maxWait = 10000 pool_options.validationInterval = 10000 [database.apim_db] type = "mysql" url = "jdbc:mysql://localhost:3306/apim_db" username = "apimadmin" password = "apimadmin" pool_options.maxActive = 50 pool_options.maxWait = 30000
For more information on other parameters that can be defined in the
<API-M_HOME>/repository/conf/deployment.tomlfile, see Tomcat JDBC Connection Pool.
Restart the server.
To give the Key Manager, Publisher, and Developer Portal components access to the user management data with shared permissions, JDBCUserStoreManager has been configured by default. For more information, refer Configuring Userstores.
Changing WSO2CARBON_DB to MySQL
WSO2CARBON_DBwill be an embedded H2 database and it is not necessary to change it to another database. But if you have a requirement to change it, you can follow the below steps. (When changing the carbon database, make sure that each server node have its own WSO2CARBON_DB. If you don't want to change the carbon database, then you can ignore this section.)
- Create tables in the carbon database (
WSO2CARBON_DB) using the script
- Open the
<API-M_HOME>/repository/conf/deployment.tomlconfiguration file. Locate the
[database.local]configuration element and update the URL pointing to your MySQL database, the username, and password required to access the database and the MySQL driver details similarly as explained before.
[database.local] type = "mysql" url = "jdbc:mysql://localhost:3306/carbon_db" username = "carbonadmin" password = "carbonadmin" driver = "com.mysql.cj.jdbc.Driver"
- Create tables in the carbon database (