Keycloak MySQL Setup¶
Prepare Keycloak schema and user¶
$ mysql -uroot -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE USER 'keycloak'@'%' IDENTIFIED BY 'keycloak';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE DATABASE keycloak CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON keycloak.* TO 'keycloak'@'%';
Query OK, 0 rows affected (0.00 sec)
- created
keycloak
database - created
keycloak
user with the passwordkeycloak
(please use a strong password for production) - granted all privileges to the
keycloak
on thekeycloak
database
RDBMS Setup Checklist¶
- Locate and download a JDBC driver for your database
- Package the driver JAR into a module and install this module into the server
- Declare the JDBC driver in the configuration profile of the server
- Modify the datasource configuration to use your database’s JDBC driver
- Modify the datasource configuration to define the connection parameters to your database
JDBC Setup¶
$ wget -P ~/tmp/ https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.42.zip
$ unzip ~/tmp/mysql-connector-java-5.1.42.zip -d ~/tmp
$ sudo mkdir -p /opt/keycloak/modules/system/layers/keycloak/com/mysql/main
$ sudo cd /opt/keycloak/modules/system/layers/keycloak/com/mysql/main
$ sudo cp ~/tmp/mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar .
$ sudo touch module.xml
<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.3" name="com.mysql">
<resources>
<resource-root path="mysql-connector-java-5.1.42-bin.jar" />
</resources>
<dependencies>
<module name="javax.api"/>
<module name="javax.transaction.api"/>
</dependencies>
</module>
Declare and Load JDBC Driver¶
vim /opt/keycloak/standalone/configuration/standalone.xml
<subsystem xmlns="urn:jboss:domain:datasources:4.0">
<datasources>
<drivers>
<driver name="mysql" module="com.mysql">
<driver-class>com.mysql.jdbc.Driver</driver-class>
</driver>
<driver name="h2" module="com.h2database.h2">
<xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
</driver>
</drivers>
</datasources>
</subsystem>
name
is set tomysql
, but can be everything we want- we speciy the
module
attribute which points to themodule
package we created earlier for the driver JAR - finally, we specify the driver’s Java class, which in case of MySQL is com.mysql.jdbc.Driver
Datasource setup¶
<subsystem xmlns="urn:jboss:domain:datasources:4.0">
<datasources>
<datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
<connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
<driver>h2</driver>
<security>
<user-name>sa</user-name>
<password>sa</password>
</security>
</datasource>
<datasource jndi-name="java:/jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true">
<connection-url>jdbc:mysql://localhost:3306/keycloak?useSSL=false&characterEncoding=UTF-8</connection-url>
<driver>mysql</driver>
<pool>
<min-pool-size>5</min-pool-size>
<max-pool-size>15</max-pool-size>
</pool>
<security>
<user-name>keycloak</user-name>
<password>keycloak</password>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
<validate-on-match>true</validate-on-match>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
</validation>
</datasource>
<drivers>
<driver name="h2" module="com.h2database.h2">
<xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
</driver>
<driver name="mysql" module="com.mysql">
<driver-class>com.mysql.jdbc.Driver</driver-class>
</driver>
</drivers>
</datasources>
</subsystem>
What we modified:
- we’ve searched for
datasource
definition forKeycloakDS
. - we modified the
connection-url
to point to the MySQL server - we defined the driver we use for the connection; this is the logical name of the JDBC driver we declared in the previous section (
mysql
) - it is expensive to open a new connection to a database every time you want to perform a transaction. To compensate, the datasource implementation maintains a pool of open connections. The
max-pool-size
specifies the maximum number of connections it will pool. We may want to change the value of this depending on the load of the system. - finally we need to define the database
user-name
andpassword
, that is needed to connect to the database.
Database configuration¶
<subsystem xmlns="urn:jboss:domain:keycloak-server:1.1">
<spi name="connectionsJpa">
<provider name="default" enabled="true">
<properties>
<property name="dataSource" value="java:jboss/datasources/KeycloakDS"/>
<property name="initializeEmpty" value="true"/>
<property name="migrationStrategy" value="update"/>
<property name="migrationExport" value="${jboss.home.dir}/keycloak-database-update.sql"/>
</properties>
</provider>
</spi>
</subsystem>
dataSource
- JNDI name of the dataSourceinitializeEmpty
- initialize database if empty. If set to false the database has to be manually initialized. If you want to manually initialize the database set migrationStrategy tomanual
which will create a file with SQL commands to initialize the database. Defaults to true.migrationStrategy
- strategy to use to migrate database. Valid values areupdate
,manual
andvalidate
.- Update will automatically migrate the database schema.
- Manual will export the required changes to a file with SQL commands that you can manually execute on the database.
- Validate will simply check if the database is up-to-date.
migrationExport
- path for where to write manual database initialization/migration file.
Unicode Considerations for MySQL¶
Please note that utf8mb4
character set does not work due to different storage requirements to utf8
character set footnote:[Tracked as https://issues.jboss.org/browse/KEYCLOAK-3873]).