keycloak 9.0/13.0.0 迁移数据库到MySQL 8.0.19

1.官方文档

• 下载地址

https://www.keycloak.org/downloads.html

• 官方数据库配置文档

https://www.keycloak.org/docs/latest/server_installation/index.html#database-configuration

2.驱动准备

• Oracle官网下载下载mysql-connector-java-8.0.19.zip，或者 https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.19/mysql-connector-java-8.0.19.jar下载。
• 解压~/Downloads/mysql-connector-java-8.0.19.zip.
• 解压mysql-connector-java-8.0.19.jar，进入目录查找

find . -name "MysqlXADataSource.class"

记下路径，增加MySQL驱动时会用到。

./com/mysql/cj/jdbc/MysqlXADataSource.class

mysql-connector-java-8.0.24.jar测试也通过。

3.下载并配置keycloak 9.0

• 下载地址

https://downloads.jboss.org/keycloak/9.0.0/keycloak-9.0.0.zip

keycloak 13.0.0测试也通过。

• 解压~/Downloads/keycloak-9.0.0.zip

cd ~/Downloads    
unzip keycloak-9.0.0.zip
cd keycloak-9.0.0

• 拷贝MySQL驱动

mkdir -p ./modules/system/layers/base/com/mysql/main/
cp ../mysql-connector-java-8.0.19/mysql-connector-java-8.0.19.jar ./modules/system/layers/base/com/mysql/main/

官方文档建议路径是./modules/system/layers/keycloak/com/mysql/main/，base目录应该是框架的插件，keycloak目录下应该是给keycloak用的

• 建立module.xml文件，与mysql-connector-java-8.0.19.jar同一目录

<?xml version="1.0" encoding="UTF-8"?>
<module name="com.mysql" xmlns="urn:jboss:module:1.3">
    <resources>
        <resource-root path="mysql-connector-java-8.0.19.jar"/>
    </resources>

    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
        <module name="javax.servlet.api" optional="true"/>
    </dependencies>
</module>

• 修改驱动及数据源配置

cd ~/Downloads/keycloak-9.0.0
vim standalone/configuration/standalone.xml

修改三处：
a.增加MySQL驱动

                <drivers>
                    <driver name="h2" module="com.h2database.h2">
                        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                    </driver>

                    <!-- mysql driver -->
                    <driver name="mysql" module="com.mysql">
                        <xa-datasource-class>com.mysql.cj.jdbc.MysqlXADataSource</xa-datasource-class>
                    </driver>
                </drivers>

b.注释原数据源，增加MySQL数据源

                <!-- <datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
                    <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> -->
                <!-- mysql datasource -->
                <datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
                    <connection-url>jdbc:mysql://127.0.0.1:3306/keycloak?useSSL=false&amp;connectionCollation=utf8_general_ci&amp;characterSetResults=utf8&amp;characterEncoding=utf8</connection-url>
                    <driver>mysql</driver>
                    <security>
                        <user-name>root</user-name>
                        <password>password</password>
                    </security>
                </datasource>
                

注意这个地方别把同为h2驱动的 java:jboss/datasources/ExampleDS 注释掉！系统默认的一个数据源，否则报错jboss.datasources.ExampleDS missing。

c.修改jpa配置

            <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="initializeEmpty" value="false"/>
                        <property name="migrationStrategy" value="manual"/>
                        <property name="migrationExport" value="${jboss.home.dir}/keycloak-database-update.sql"/>
                    </properties>
                </provider>
            </spi>

4.准备数据库

• 建立数据库

CREATE DATABASE IF NOT EXISTS keycloak  DEFAULT CHARSET utf8mb4;

*导入数据脚本，若keycloak-database-update.sql不存在，先执行第5步，运行./bin/standalone.sh，报错后就有了。

use keycloak;
source ~/Downloads/keycloak-9.0.0/keycloak-database-update.sql;

• 确认结果

mysql> show tables;
+-------------------------------+
| Tables_in_keycloak            |
+-------------------------------+
...
...
| WEB_ORIGINS                   |
+-------------------------------+
93 rows in set (0.00 sec)

5.启动

cd ~/Downloads/keycloak-9.0.0
./bin/standalone.sh

打开浏览器访问

http://localhost:8080/auth