MySQL Repository

This procedure assumes that you have installed MySQL on the local host. Follow the MySQL documentation that corresponds to your MySQL version. For supported MySQL versions, see Supported Repositories.

Configure IDM to use the new repository before you start IDM for the first time. This procedure assumes that a password has already been set for the MySQL root user:

Download MySQL Connector/J version 5.1 or later.
Unpack the downloaded file, and copy the .jar file to openidm/bundle/:
```
cp mysql-connector-java-version.jar /path/to/openidm/bundle/
```

Make sure that IDM is stopped:

/path/to/openidm/shutdown.sh
OpenIDM is not running, not stopping.

Remove the default DS repository configuration file (repo.ds.json) from your project's conf/ directory:
```
rm my-project/conf/repo.ds.json
```
Copy the MySQL database connection configuration file (datasource.jdbc-default.json) and the database table configuration file (repo.jdbc.json) to your project's conf directory:
```
cp /path/to/openidm/db/mysql/conf/datasource.jdbc-default.json my-project/conf/
cp /path/to/openidm/db/mysql/conf/repo.jdbc.json my-project/conf/
```

If you have previously set up a MySQL repository for IDM, you must drop the openidm database and users before you continue:

mysql> drop database openidm;
Query OK, 21 rows affected (0.63 sec)
mysql> drop user openidm;
Query OK, 0 rows affected (0.02 sec)
mysql> drop user openidm@localhost;
Query OK, 0 rows affected (0.00 sec)

Import the IDM database and tables:
```
cd /path/to/mysql
mysql -u root -p < /path/to/openidm/db/mysql/scripts/openidm.sql
Enter password:
```
Note
If you see errors like Access denied for user 'root'@'localhost', and are deploying on a new installation of Ubuntu 16.04 or later, the UNIX_SOCKET plugin may be installed, which applies Linux root credentials to MySQL. In that case, substitute sudo mysql -u root for mysql -u root -p in the commands in this section.

Create the IDM database user.

For MySQL 5.7 or higher

Run the following script:

cd /path/to/mysql
mysql -u root -p < /path/to/openidm/db/mysql/scripts/createuser.sql
Enter password:

For MySQL prior to 5.7

Run the following script:

cd /path/to/mysql
mysql -u root -p < /path/to/openidm/db/mysql/scripts/createuser.mysql56.sql
Enter password:

Run the script that creates the tables required by the workflow engine:

cd /path/to/mysql
mysql -D openidm -u root -p < /path/to/openidm/db/mysql/scripts/flowable.mysql.all.create.sql
Enter password:

Warning

You must use MySQL version 5.7 or later.

If you are planning to direct audit logs to this repository, run the script that sets up the audit tables:
```
mysql -D openidm -u root -p < /path/to/openidm/db/mysql/scripts/audit.sql
Enter password: 
```

Update the connection configuration to reflect your MySQL deployment. The default connection configuration (in the conf/datasource.jdbc-default.json file) is:

{
    "driverClass" : "com.mysql.jdbc.Driver",
    "jdbcUrl" : "jdbc:mysql://&{openidm.repo.host}:&{openidm.repo.port}/openidm?allowMultiQueries=true&characterEncoding=utf8&serverTimezone=UTC",
    "databaseName" : "openidm",
    "username" : "openidm",
    "password" : "openidm",
    "connectionTimeout" : 30000,
    "connectionPool" : {
        "type" : "hikari",
        "minimumIdle" : 20,
        "maximumPoolSize" : 50
    }
}

Note

In a production environment, set up SSL as described in the MySQL Connector Developer Guide.
The default configuration expects SSL, unless you add &useSSL=false to the end of the url.

Specify the values for openidm.repo.host and openidm.repo.port in one of the following ways:
Set in an IDM Properties File
Set the values in resolver/boot.properties or your project's conf/system.properties file, for example:
```
openidm.repo.host=localhost
openidm.repo.port=3306
```
These lines are commented out by default in resolver/boot.properties.
The default MySQL port is 3306. You can use the netstat -tlnp command to check which port your MySQL instance is running on.
Set as an Environment Variable
Set the properties in the OPENIDM_OPTS environment variable and export that variable before startup. You must include the JVM memory options when you set this variable. For example:
```
export OPENIDM_OPTS="-Xmx1024m -Xms1024m -Dopenidm.repo.host=localhost -Dopenidm.repo.port=3306"
/path/to/openidm/startup.sh -p my-project
Executing ./startup.sh...
Using OPENIDM_HOME:   /path/to/openidm
Using PROJECT_HOME:   /path/to/openidm
Using OPENIDM_OPTS:   -Xmx1024m -Xms1024m -Dopenidm.repo.host=localhost -Dopenidm.repo.port=3306
Using LOGGING_CONFIG: -Djava.util.logging.config.file=/path/to/openidm/conf/logging.properties
Using boot properties at /path/to/openidm/resolver/boot.properties
-> OpenIDM version "7.1.6"
OpenIDM ready
```
Make sure that the server starts without errors.