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
databasecreated
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 wantwe speciy the
module
attribute which points to themodule
package we created earlier for the driver JARfinally, 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 serverwe 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]).