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

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 password keycloak (please use a strong password for production)
  • granted all privileges to the keycloak on the keycloak 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/
$ unzip ~/tmp/ -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">
        <resource-root path="mysql-connector-java-5.1.42-bin.jar" />
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>

Declare and Load JDBC Driver

vim /opt/keycloak/standalone/configuration/standalone.xml

<subsystem xmlns="urn:jboss:domain:datasources:4.0">
          <driver name="mysql" module="com.mysql">
          <driver name="h2" module="com.h2database.h2">
  • name is set to mysql, but can be everything we want
  • we speciy the module attribute which points to the module 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">
        <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
        <datasource jndi-name="java:/jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true">
                <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
            <driver name="h2" module="com.h2database.h2">
            <driver name="mysql" module="com.mysql">

What we modified:

  • we’ve searched for datasource definition for KeycloakDS.
  • 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 and password, 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">
                <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"/>
  • dataSource- JNDI name of the dataSource
  • initializeEmpty - 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 to manual which will create a file with SQL commands to initialize the database. Defaults to true.
  • migrationStrategy - strategy to use to migrate database. Valid values are update, manual and validate.
    • 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]).