--- title: MySQL repository description: Configure MySQL or MariaDB as a PingIDM repository, including Connector/J setup, schema import, user creation, and connection configuration component: pingidm version: 7.5 page_id: pingidm:install-guide:repository-mysql canonical_url: https://docs.pingidentity.com/pingidm/8.1/install-guide/repository-mysql.html llms_txt: https://docs.pingidentity.com/pingidm/llms.txt docs_for_agents: https://developer.pingidentity.com/build-with-ai/docs-for-agents.md keywords: ["Installation", "MySQL"] superseded_by: https://docs.pingidentity.com/pingidm/8.1/install-guide/repository-mysql.html --- # 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 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.5.4" > OpenIDM ready > ``` 13. Make sure that the server starts without errors.