This repository was archived by the owner on May 30, 2020. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 7
Move keycloak database from H2 to MySql
Adrian Matei edited this page May 10, 2017
·
5 revisions
By default Keycloak uses an embedded H2 database. This should be sufficient for development on the local machine, but for production we will replace it with a MySQL standalone database. I will use MySQL Community Edition, 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 page presents what need to be done on Keycloak to use MySQL.
Connect to the MySQL shell as root and create keycloak database 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;
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
See keycloak documentation - Export and Import
$ KEYCLOAK_HOME/standalone.sh -Dkeycloak.migration.action=export -Dkeycloak.migration.provider=dir -Dkeycloak.migration.dir=exported_realms -Dkeycloak.migration.strategy=OVERWRITE_EXISTING
KEYCLOAK_HOMEis on my local machine/opt/keycloak
Verification
$ cd /opt/keycloak/bin/exported_realms/
$ ls -lrt
total 224
-rw-r--r-- 1 ama staff 893 May 7 10:52 master-users-0.json
-rw-r--r-- 1 ama staff 52293 May 7 10:52 master-realm.json
-rw-r--r-- 1 ama staff 993 May 7 10:52 codingpedia-users-0.json
-rw-r--r-- 1 ama staff 51778 May 7 10:52 codingpedia-realm.json
*less on the files to see what content they have
On local MySql Server create a new schema called keycloak:
CREATE SCHEMA `keycloak` DEFAULT CHARACTER SET utf8 ;
Add a new user keycloak with the password keycloak and grant all privileges for the keycloak schema created before.
$ mkdir -p /opt/keycloak/modules/system/layers/base/com/mysql/main
$ cd /opt/keycloak/modules/system/layers/base/com/mysql/main
$ 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
$ cp ~/tmp/mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar .
$ vim module.xml
Add the following content to the module.xml file:
<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>
Replace the KeycloakDS and add the mysql driver
Before:
<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true">
<connection-url>jdbc:h2:${jboss.server.data.dir}/keycloak;AUTO_SERVER=TRUE</connection-url>
<driver>h2</driver>
<security>
<user-name>sa</user-name>
<password>sa</password>
</security>
</datasource>
After
<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>
$ KEYCLOAK_HOME/standalone.sh -Dkeycloak.migration.action=import -Dkeycloak.migration.provider=dir -Dkeycloak.migration.dir=exported_realms -Dkeycloak.migration.strategy=OVERWRITE_EXISTING
Keycloak started successfully and the database has been migrated.
Quick checkups:
- login in the admin console with the configured
admin/admin1user - codingpedia is present and configured as before (clients etc.)
- login to codingmarks application locally http://localhost:4200
adrians-mbp:bin ama$ ./standalone.sh -Dkeycloak.migration.action=import -Dkeycloak.migration.provider=dir -Dkeycloak.migration.dir=exported_realms -Dkeycloak.migration.strategy=OVERWRITE_EXISTING
=========================================================================
JBoss Bootstrap Environment
JBOSS_HOME: /opt/keycloak
JAVA: /Library/Java/JavaVirtualMachines/jdk1.8.0_65.jdk/Contents/Home/bin/java
JAVA_OPTS: -server -Xms64m -Xmx512m -XX:MetaspaceSize=96M -XX:MaxMetaspaceSize=256m -Djava.net.preferIPv4Stack=true -Djboss.modules.system.pkgs=org.jboss.byteman -Djava.awt.headless=true
=========================================================================
19:19:17,145 INFO [org.jboss.modules] (main) JBoss Modules version 1.5.1.Final
19:19:17,328 INFO [org.jboss.msc] (main) JBoss MSC version 1.2.6.Final
19:19:17,397 INFO [org.jboss.as] (MSC service thread 1-6) WFLYSRV0049: Keycloak 2.5.5.Final (WildFly Core 2.0.10.Final) starting
19:19:18,220 INFO [org.jboss.as.server] (Controller Boot Thread) WFLYSRV0039: Creating http management service using socket-binding (management-http)
19:19:18,236 INFO [org.xnio] (MSC service thread 1-6) XNIO version 3.3.4.Final
19:19:18,241 INFO [org.xnio.nio] (MSC service thread 1-6) XNIO NIO Implementation Version 3.3.4.Final
19:19:18,264 WARN [org.jboss.as.txn] (ServerService Thread Pool -- 45) WFLYTX0013: Node identifier property is set to the default value. Please make sure it is unique.
19:19:18,274 INFO [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 32) WFLYCLINF0001: Activating Infinispan subsystem.
19:19:18,278 INFO [org.wildfly.extension.io] (ServerService Thread Pool -- 31) WFLYIO001: Worker 'default' has auto-configured to 16 core threads with 128 task threads based on your 8 available processors
19:19:18,285 INFO [org.jboss.as.jsf] (ServerService Thread Pool -- 38) WFLYJSF0007: Activated the following JSF Implementations: [main]
19:19:18,313 INFO [org.jboss.as.naming] (ServerService Thread Pool -- 40) WFLYNAM0001: Activating Naming Subsystem
19:19:18,313 INFO [org.jboss.as.security] (ServerService Thread Pool -- 44) WFLYSEC0002: Activating Security Subsystem
19:19:18,317 INFO [org.wildfly.extension.undertow] (MSC service thread 1-8) WFLYUT0003: Undertow 1.3.15.Final starting
19:19:18,317 INFO [org.wildfly.extension.undertow] (ServerService Thread Pool -- 46) WFLYUT0003: Undertow 1.3.15.Final starting
19:19:18,319 INFO [org.jboss.as.security] (MSC service thread 1-7) WFLYSEC0001: Current PicketBox version=4.9.4.Final
19:19:18,331 INFO [org.jboss.as.connector] (MSC service thread 1-3) WFLYJCA0009: Starting JCA Subsystem (WildFly/IronJacamar 1.3.2.Final)
19:19:18,357 INFO [org.jboss.as.connector.subsystems.datasources] (ServerService Thread Pool -- 27) WFLYJCA0004: Deploying JDBC-compliant driver class org.h2.Driver (version 1.3)
19:19:18,368 INFO [org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-3) WFLYJCA0018: Started Driver service with driver-name = h2
19:19:18,389 INFO [org.jboss.as.connector.subsystems.datasources] (ServerService Thread Pool -- 27) WFLYJCA0005: Deploying non-JDBC-compliant driver class com.mysql.jdbc.Driver (version 5.1)
19:19:18,389 INFO [org.jboss.remoting] (MSC service thread 1-6) JBoss Remoting version 4.0.18.Final
19:19:18,390 INFO [org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-7) WFLYJCA0018: Started Driver service with driver-name = mysql
19:19:18,407 INFO [org.jboss.as.mail.extension] (MSC service thread 1-8) WFLYMAIL0001: Bound mail session [java:jboss/mail/Default]
19:19:18,407 INFO [org.jboss.as.naming] (MSC service thread 1-4) WFLYNAM0003: Starting Naming Service
19:19:18,491 INFO [org.wildfly.extension.undertow] (ServerService Thread Pool -- 46) WFLYUT0014: Creating file handler for path '/opt/keycloak/welcome-content' with options [directory-listing: 'false', follow-symlink: 'false', case-sensitive: 'true', safe-symlink-paths: '[]']
19:19:18,526 INFO [org.wildfly.extension.undertow] (MSC service thread 1-1) WFLYUT0012: Started server default-server.
19:19:18,528 INFO [org.wildfly.extension.undertow] (MSC service thread 1-2) WFLYUT0018: Host default-host starting
19:19:18,634 INFO [org.wildfly.extension.undertow] (MSC service thread 1-1) WFLYUT0006: Undertow HTTP listener default listening on 127.0.0.1:8380
19:19:18,650 INFO [org.jboss.as.ejb3] (MSC service thread 1-3) WFLYEJB0481: Strict pool slsb-strict-max-pool is using a max instance size of 128 (per class), which is derived from thread worker pool sizing.
19:19:18,650 INFO [org.jboss.as.ejb3] (MSC service thread 1-8) WFLYEJB0482: Strict pool mdb-strict-max-pool is using a max instance size of 32 (per class), which is derived from the number of CPUs on this host.
19:19:18,794 INFO [org.jboss.as.connector.subsystems.datasources] (MSC service thread 1-7) WFLYJCA0001: Bound data source [java:/jboss/datasources/KeycloakDS]
19:19:18,794 INFO [org.jboss.as.connector.subsystems.datasources] (MSC service thread 1-1) WFLYJCA0001: Bound data source [java:jboss/datasources/ExampleDS]
19:19:18,910 INFO [org.jboss.as.server.deployment.scanner] (MSC service thread 1-2) WFLYDS0013: Started FileSystemDeploymentService for directory /opt/keycloak/standalone/deployments
19:19:18,921 INFO [org.jboss.as.server.deployment] (MSC service thread 1-1) WFLYSRV0027: Starting deployment of "keycloak-server.war" (runtime-name: "keycloak-server.war")
19:19:19,064 INFO [org.infinispan.factories.GlobalComponentRegistry] (MSC service thread 1-7) ISPN000128: Infinispan version: Infinispan 'Mahou' 8.1.0.Final
19:19:19,064 INFO [org.infinispan.factories.GlobalComponentRegistry] (MSC service thread 1-4) ISPN000128: Infinispan version: Infinispan 'Mahou' 8.1.0.Final
19:19:19,064 INFO [org.infinispan.factories.GlobalComponentRegistry] (MSC service thread 1-2) ISPN000128: Infinispan version: Infinispan 'Mahou' 8.1.0.Final
19:19:19,064 INFO [org.infinispan.factories.GlobalComponentRegistry] (MSC service thread 1-5) ISPN000128: Infinispan version: Infinispan 'Mahou' 8.1.0.Final
19:19:19,444 INFO [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 49) WFLYCLINF0002: Started offlineSessions cache from keycloak container
19:19:19,444 INFO [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 53) WFLYCLINF0002: Started sessions cache from keycloak container
19:19:19,444 INFO [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 55) WFLYCLINF0002: Started work cache from keycloak container
19:19:19,444 INFO [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 52) WFLYCLINF0002: Started keys cache from keycloak container
19:19:19,444 INFO [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 54) WFLYCLINF0002: Started authorization cache from keycloak container
19:19:19,444 INFO [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 51) WFLYCLINF0002: Started users cache from keycloak container
19:19:19,444 INFO [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 56) WFLYCLINF0002: Started loginFailures cache from keycloak container
19:19:19,444 INFO [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 50) WFLYCLINF0002: Started realms cache from keycloak container
19:19:20,040 INFO [org.keycloak.services] (ServerService Thread Pool -- 51) KC-SERVICES0001: Loading config from standalone.xml or domain.xml
19:19:20,940 INFO [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 51) WFLYCLINF0002: Started realmRevisions cache from keycloak container
19:19:20,948 INFO [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 51) WFLYCLINF0002: Started userRevisions cache from keycloak container
19:19:22,181 INFO [org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider] (ServerService Thread Pool -- 51) Initializing database schema. Using changelog META-INF/jpa-changelog-master.xml
19:19:30,541 INFO [org.hibernate.jpa.internal.util.LogHelper] (ServerService Thread Pool -- 51) HHH000204: Processing PersistenceUnitInfo [
name: keycloak-default
...]
19:19:30,581 INFO [org.hibernate.Version] (ServerService Thread Pool -- 51) HHH000412: Hibernate Core {5.0.7.Final}
19:19:30,582 INFO [org.hibernate.cfg.Environment] (ServerService Thread Pool -- 51) HHH000206: hibernate.properties not found
19:19:30,583 INFO [org.hibernate.cfg.Environment] (ServerService Thread Pool -- 51) HHH000021: Bytecode provider name : javassist
19:19:30,609 INFO [org.hibernate.annotations.common.Version] (ServerService Thread Pool -- 51) HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
19:19:30,716 INFO [org.hibernate.dialect.Dialect] (ServerService Thread Pool -- 51) HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
19:19:30,744 INFO [org.hibernate.envers.boot.internal.EnversServiceImpl] (ServerService Thread Pool -- 51) Envers integration enabled? : true
19:19:31,156 INFO [org.hibernate.validator.internal.util.Version] (ServerService Thread Pool -- 51) HV000001: Hibernate Validator 5.2.3.Final
19:19:31,832 INFO [org.hibernate.hql.internal.QueryTranslatorFactoryInitiator] (ServerService Thread Pool -- 51) HHH000397: Using ASTQueryTranslatorFactory
19:19:32,452 INFO [org.keycloak.exportimport.dir.DirImportProvider] (ServerService Thread Pool -- 51) Importing from directory /opt/keycloak-2.5.5.Final/bin/exported_realms
19:19:32,455 INFO [org.keycloak.services] (ServerService Thread Pool -- 51) KC-SERVICES0030: Full model import requested. Strategy: OVERWRITE_EXISTING
19:19:33,440 INFO [org.keycloak.exportimport.util.ImportUtils] (ServerService Thread Pool -- 51) Realm 'master' imported
19:19:33,666 INFO [org.keycloak.exportimport.dir.DirImportProvider] (ServerService Thread Pool -- 51) Imported users from /opt/keycloak-2.5.5.Final/bin/exported_realms/master-users-0.json
19:19:34,180 INFO [org.keycloak.exportimport.util.ImportUtils] (ServerService Thread Pool -- 51) Realm 'codingpedia' imported
19:19:34,278 INFO [org.keycloak.exportimport.dir.DirImportProvider] (ServerService Thread Pool -- 51) Imported users from /opt/keycloak-2.5.5.Final/bin/exported_realms/codingpedia-users-0.json
19:19:34,339 INFO [org.keycloak.services] (ServerService Thread Pool -- 51) KC-SERVICES0032: Import finished successfully
19:19:34,365 INFO [org.jboss.resteasy.resteasy_jaxrs.i18n] (ServerService Thread Pool -- 51) RESTEASY002225: Deploying javax.ws.rs.core.Application: class org.keycloak.services.resources.KeycloakApplication
19:19:34,367 INFO [org.jboss.resteasy.resteasy_jaxrs.i18n] (ServerService Thread Pool -- 51) RESTEASY002200: Adding class resource org.keycloak.services.resources.ThemeResource from Application class org.keycloak.services.resources.KeycloakApplication
19:19:34,367 INFO [org.jboss.resteasy.resteasy_jaxrs.i18n] (ServerService Thread Pool -- 51) RESTEASY002205: Adding provider class org.keycloak.services.filters.KeycloakTransactionCommitter from Application class org.keycloak.services.resources.KeycloakApplication
19:19:34,367 INFO [org.jboss.resteasy.resteasy_jaxrs.i18n] (ServerService Thread Pool -- 51) RESTEASY002200: Adding class resource org.keycloak.services.resources.JsResource from Application class org.keycloak.services.resources.KeycloakApplication
19:19:34,367 INFO [org.jboss.resteasy.resteasy_jaxrs.i18n] (ServerService Thread Pool -- 51) RESTEASY002220: Adding singleton resource org.keycloak.services.resources.ServerVersionResource from Application class org.keycloak.services.resources.KeycloakApplication
19:19:34,367 INFO [org.jboss.resteasy.resteasy_jaxrs.i18n] (ServerService Thread Pool -- 51) RESTEASY002220: Adding singleton resource org.keycloak.services.resources.WelcomeResource from Application class org.keycloak.services.resources.KeycloakApplication
19:19:34,367 INFO [org.jboss.resteasy.resteasy_jaxrs.i18n] (ServerService Thread Pool -- 51) RESTEASY002220: Adding singleton resource org.keycloak.services.resources.admin.AdminRoot from Application class org.keycloak.services.resources.KeycloakApplication
19:19:34,368 INFO [org.jboss.resteasy.resteasy_jaxrs.i18n] (ServerService Thread Pool -- 51) RESTEASY002210: Adding provider singleton org.keycloak.services.util.ObjectMapperResolver from Application class org.keycloak.services.resources.KeycloakApplication
19:19:34,368 INFO [org.jboss.resteasy.resteasy_jaxrs.i18n] (ServerService Thread Pool -- 51) RESTEASY002220: Adding singleton resource org.keycloak.services.resources.RobotsResource from Application class org.keycloak.services.resources.KeycloakApplication
19:19:34,368 INFO [org.jboss.resteasy.resteasy_jaxrs.i18n] (ServerService Thread Pool -- 51) RESTEASY002220: Adding singleton resource org.keycloak.services.resources.RealmsResource from Application class org.keycloak.services.resources.KeycloakApplication
19:19:34,443 INFO [org.wildfly.extension.undertow] (ServerService Thread Pool -- 51) WFLYUT0021: Registered web context: /auth
19:19:34,466 INFO [org.jboss.as.server] (ServerService Thread Pool -- 47) WFLYSRV0010: Deployed "keycloak-server.war" (runtime-name : "keycloak-server.war")
19:19:34,565 INFO [org.jboss.as] (Controller Boot Thread) WFLYSRV0060: Http management interface listening on http://127.0.0.1:10290/management
19:19:34,566 INFO [org.jboss.as] (Controller Boot Thread) WFLYSRV0051: Admin console listening on http://127.0.0.1:10290
19:19:34,566 INFO [org.jboss.as] (Controller Boot Thread) WFLYSRV0025: Keycloak 2.5.5.Final (WildFly Core 2.0.10.Final) started in 17687ms - Started 426 of 784 services (526 services are lazy, passive or on-demand)