Skip to content

Usage

Version: 1.0.0

Creating a new MySQL database instance

To create a new MySQL database client instance, use the following procedure:

  1. Open the ESF Administrative UI and press the + button in the side menu, under the Services section. A pop-up dialog should appear.
  2. Select com.eurotech.framework.mysql.client.provider.MySQLCLient from the Factory drop-down list, enter an arbitrary name for the new instance and click Apply.

MysqlFactory

  1. An entry for the newly created client instance should appear in the side menu under Services, click on it to review its configuration:

MysqlService

The component can be configured with the following parameters:

  • Connector URL (mandatory) it specifies the JDBC connector URL of the database instance. Usually the URL is in the form jdbc:<protocol>//<hostDescription>?<parameter1>&<parameter2> where:
    • protocol can be mysql or mariadb
    • hostDescription is <host>:<portnumber>
    • parameter is in key value pair name=value

An example of valid URL is jdbc:mysql://192.168.2.1:3306/testSchema?maxPoolSize=10&minPoolSize=1

For a detailed list of available parameters, please refer to the official MariaDB Connector/J documentation. Be aware that the User, Password, SSL Mode, Server CA, Client keystore path and password properties passed in the connector URL will be overridden by the fields below.

  • User (mandatory) it specifies the user for the database connection
  • Password it specifies the password for the connection. The default password is the empty string.
  • SSL Mode (mandatory) it enables SSL/TLS connection in a specific mode. The allowed modes are the following:
    • Disabled the SSL/TLS connection is disabled.
    • Trust the SSL/TLS is used only for encryption.
    • Verify CA the SSL/TLS is used for encryption and certificates verification.
    • Verify All the SSL/TLS is used for encryption, certificates and hostname verification.
  • Server CA it specifies the path of the server CA or the verbatim server CA in DER format. It is mandatory when the SSL Mode is not in disable or trust mode.
  • Client keystore path it specifies the path of the client keystore containing the private and public keys for mutual authentication. It is optional and used when the SSL Mode is not in disable or trust mode.
  • Client keystore password it specifies the password of the client keystore.

Selecting a Database Instance for Existing ESF Components

A database instance is identified by its Kura service PID. The PID for instances created using the Web UI is the string entered in the Name field at step 2 of the previous section.

The ESF components that use database functionalities allow to specify which instance to use in their configuration. For example, the MySQLClient DB Filter and MySQLClient DB Store wire components allow the user to specify the database instance to connect to with the Target Filter field.

Examples

The following sections provide some examples on how to configure the service for connecting to a MySQL instance with different users. The provided SQL commands are for test purposes only: do not use them in production environment.

Connect with a user without password

On the mySQL instance use the following commands for creating a user: 

create user 'noneUser'@'%' require none;
GRANT ALL PRIVILEGES ON *.* TO 'noneUser'@'%';
FLUSH PRIVILEGES;
Configure the connector as follows:

  • User noneUser
  • Password Empty
  • SSL Mode Disabled
  • Server CA Empty
  • Client keystore path Empty
  • Client keystore password Empty

Connect with a user with password

On the mySQL instance use the following commands for creating a user: 

CREATE USER 'noneUser'@'%' IDENTIFIED BY 'password' REQUIRE none;
GRANT ALL PRIVILEGES ON *.* TO 'noneUser'@'%';
FLUSH PRIVILEGES;
Configure the connector as follows:

  • Connector URL add "?allowPublicKeyRetrieval=true" to the url
  • User noneUser
  • Password password
  • SSL Mode Disabled
  • Server CA Empty
  • Client keystore path Empty
  • Client keystore password Empty

or

  • User noneUser
  • Password password
  • SSL Mode Trust
  • Server CA Empty
  • Client keystore path Empty
  • Client keystore password Empty

Connect with a user with SSL

On the mySQL instance use the following commands for creating a user: 

CREATE USER 'sslUser'@'%' IDENTIFIED BY 'password' REQUIRE ssl;
GRANT ALL PRIVILEGES ON *.* TO 'sslUser'@'%';
FLUSH PRIVILEGES;
Configure the connector as follows:

  • User sslUser
  • Password password
  • SSL Mode Trust
  • Server CA Empty
  • Client keystore path Empty
  • Client keystore password Empty

or

  • User sslUser
  • Password password
  • SSL Mode Verify CA or Verify All
  • Server CA filled with the server CA or the path of CA
  • Client keystore path Empty
  • Client keystore password Empty

Connect with a user with mutual authentication

On the mySQL instance use the following commands for creating a user: 

CREATE USER 'x509User'@'%' IDENTIFIED BY 'password' REQUIRE x509;
GRANT ALL PRIVILEGES ON *.* TO 'x509User'@'%';
FLUSH PRIVILEGES;
Configure the connector as follows:

  • User x509User
  • Password password
  • SSL Mode Verify CA or Verify All
  • Server CA filled with the server CA or the path of CA
  • Client keystore path filled with the path of the keystore
  • Client keystore password filled with the password of the keystore