--- title: MySQL repository description: 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, refer to Supported Repositories. component: pingidm version: 8.1 page_id: pingidm:install-guide:repository-mysql canonical_url: https://docs.pingidentity.com/pingidm/8.1/install-guide/repository-mysql.html keywords: ["Installation", "MySQL"] --- # MySQL repository This procedure assumes that you have installed MySQL on the local host. Follow the [MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/installing.html) that corresponds to your MySQL version. For supported MySQL versions, refer to [Supported Repositories](../release-notes/before-you-install.html#prerequisites-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: 1. Download [MySQL Connector/J](https://dev.mysql.com/downloads/connector/j/) version 8.0 or later. | | | | - | ------------------------------------------------------------------------------------------------------- | | | Do not use Connector/J versions 8.0.23 through 8.0.25. [Why?](https://bugs.mysql.com/bug.php?id=102372) | 2. Unpack the downloaded file, and copy the JAR file to `openidm/bundle/`: ``` cp mysql-connector-java-version.jar /path/to/openidm/bundle/ ``` 3. Make sure that IDM is stopped: ``` /path/to/openidm/shutdown.sh OpenIDM is not running, not stopping. ``` 4. Remove the default DS repository configuration file (`repo.ds.json` ) from your project's `conf/` directory: ``` rm my-project/conf/repo.ds.json ``` 5. 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/ ``` 6. 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) ``` 7. Import the IDM database and tables: ``` cd /path/to/mysql mysql -u root -p < /path/to/openidm/db/mysql/scripts/openidm.sql Enter password: ``` | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | If errors like `Access denied for user 'root'@'localhost'` display, and you 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. | 8. Create the IDM database user. > **Collapse: For MySQL 8.0** > > Run the following script: > > ``` > cd /path/to/mysql > mysql -u root -p < /path/to/openidm/db/mysql/scripts/createuser.sql > Enter password: > ``` > **Collapse: For MySQL 5.7** > > Run the following script: > > ``` > cd /path/to/mysql > mysql -u root -p < /path/to/openidm/db/mysql/scripts/createuser.mysql57.sql > Enter password: > ``` 9. 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: ``` 10. 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: ``` 11. Update the connection configuration to reflect your MySQL deployment. The default connection configuration (in the `conf/datasource.jdbc-default.json` file) is: ```json { "driverClass" : "com.mysql.cj.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 } } ``` | | | | - | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | * In a production environment, set up SSL as described in the [MySQL Connector/J Developer Guide](https://dev.mysql.com/doc/connector-j/en/connector-j-reference-using-ssl.html). The default configuration expects SSL, *which is strongly advised in a production environment*. If you are running this in a test environment, you can bypass the SSL requirement: * The default configuration expects SSL, unless you add `&useSSL=false` to the end of the `url`. * If you are running MySQL 8.0.11+, add `&allowPublicKeyRetrieval=true` to the end of the `url`. | 12. Specify the values for `openidm.repo.host` and `openidm.repo.port` in one of the following ways: > **Collapse: Set in an IDM Properties File** > > Set the values in `resolver/boot.properties` or your project's `conf/system.properties` file. For example: > > ```json > 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. > **Collapse: 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="-Xmx2048m -Xms2048m -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: -Xmx2048m -Xms2048m -Dopenidm.repo.host=localhost -Dopenidm.repo.port=3306 > ... > Using boot properties at /path/to/openidm/resolver/boot.properties > -> OpenIDM version "8.1.0" > OpenIDM ready > ``` 13. Make sure that the server starts without errors.