Skip to content

Keycloak MySQL Setup

Jump to bottom
Adrian Matei edited this page May 14, 2017 · 12 revisions

Keycloak comes with its own embedded Java-based relational database called H2. This is the default database that Keycloak will use to persist data and really only exists so that you can run the authentication server out of the box. It's fine for development purposes, but for production it is recommended to replace it with a more production ready external database. Given my history and affinity with MySQL, we use the MySQL Community Edition v5.7 for the #codingmarks project, which is the freely downloadable version of the world's most popular open source database. It is available under the GPL license and is supported by a huge and active community of open source developers.

This guide presents what we need to do to run Keycloak on MySQL. Check out the Relational Database Setup official documentation, for configuring other Relational database management system(RDBMS)

The installation of the MySQL Community Edition is not in scope, of this guide, but I can tell you is a straight forward process. You can find instructions about installing MySQL on the different platforms in the official documentation

Prepare Keycloak schema and user

Connect to the MySQL shell as root and create a schema and user needed for Keycloak:

$ 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;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON keycloak.* TO 'keycloak'@'%';
Query OK, 0 rows affected (0.00 sec)

After giving in your password you have done the following

  • 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

If you prefer it visual, then MySQL Workbench is the right tool for this job.

RDBMS Setup Checklist

Now that we have the database prepared there are a couple of steps steps we will need to perform to get an RDBMS configured for Keycloak:

  • 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

JDBC is a Java API that is used to connect to a RDBMS. There are different JDBC drivers per database type that are provided by your database vendor.

First thing we need to do is find and download the JDBC driver JAR for MySQL. MySQL Connector/J is the official JDBC driver for MySQL. We can download it from the official page. We download the .zip version and place it in our tmp folder:

$ 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

Before we can use this driver, we must package it up into a module and install it into the server. Modules define JARs that are loaded into the Keycloak classpath and the dependencies those JARs have on other modules. They are pretty simple to set up.

Within the …​/modules/ directory of our Keycloak distribution, we need to create a directory structure to hold our module definition. The convention is use the Java package name of the JDBC driver for the name of the directory structure. For MySQL, create the directory com/mysql/main. Copy your database driver JAR into this directory and create an empty module.xml file within it too:

$ 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

Kecloak modules mysql driver folder structure

The -p option of the mkdir command, it makes parent directories as needed, and throws no error if they are existing

After we've done this, we open up the module.xml file and create the following 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>

The module name should match the directory structure of your module. So, com/mysql maps to com.mysql. The resource-root path attribute should specify the JAR filename of the driver. The rest are just the normal dependencies that any JDBC driver JAR would have.

Declare and Load JDBC Driver

The next thing we have to do is declare your newly packaged JDBC driver into your deployment profile so that it loads and becomes available when the server boots up. Because our current operation mode is standalone, we need to edit KEYCLOAK_HOME/standalone/configuration/standalone.xml.

You should know I am a big fan of aliases, so to open and edit this file I have created an alias:

$ alias keycloak-vim-standalone.xml='vim /opt/keycloak/standalone/configuration/standalone.xml'
$ keycloak-vim-standalone.xml

Within the profile, search for the drivers XML block within the datasources subsystem. You should see a pre-defined driver declared for the H2 JDBC driver. This is where you’ll declare the JDBC driver for your external database. This is how it looks like after we have declared the additional MySQL JDBC driver:

    <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>

Note:

  • 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

After declaring the JDBC driver, we have to modify the existing datasource configuration that Keycloak uses to connect it to your new external database. We’ll do this within the same configuration file and XML block that we registered the JDBC driver in. Here’s how the complete datasources subsystem looks like, once we do this:

        <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</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 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.
Clone this wiki locally