Using MySQL as Database for Keycloak with Docker

Keycloak and MySQL

Using Keycloak to handle users has been nice for me so far, but I'm not a fan of the default way to backup (import/export) all our users info (and basically all the Keycloak configuration).

So, having MySQL as the Keycloak Database makes the backups easier and not only that, we can use Workbench to explore it.

Docker Compose

The next docker-compose file gives us the simple way to integrate both services. They are in the same network, MySQL has its own volume and Keycloak container starts after the MySQL one (though it does not necessarily waits for MySQL to be started, see: Control startup and shutdown order in Compose).

For the full Environment variables explanation visit the official Docker Hub of MySQL and Keycloak.

version: '3.9'

services:

  mysql-kc:
    image: mysql:8.0.27
    ports:
      - 3366:3306
    restart: unless-stopped
    environment:
      # The user, password and database that Keycloak
      # is going to create and use
      MYSQL_USER: keycloak_user
      MYSQL_PASSWORD: keycloak_password
      MYSQL_DATABASE: keycloak_db
      # Self-Explanatory
      MYSQL_ROOT_PASSWORD: root_password
    volumes:
      - keycloak-and-mysql-volume:/var/lib/mysql
    networks:
      - keycloak-and-mysql-network

  keycloak-w:
    image: jboss/keycloak:16.1.0
    ports:
      - 8181:8080
    restart: unless-stopped
    environment:
      # User and password for the Administration Console
      KEYCLOAK_USER: admin_user
      KEYCLOAK_PASSWORD: admin_password
      DB_VENDOR: mysql
      DB_ADDR: mysql-kc
      DB_PORT: 3306
      # Same values as the mysql-kc service
      DB_USER: keycloak_user
      DB_PASSWORD: keycloak_password
      DB_DATABASE: keycloak_db
    depends_on:
      - mysql-kc
    networks:
      - keycloak-and-mysql-network

networks:
  keycloak-and-mysql-network:

volumes:
  keycloak-and-mysql-volume:

And we run it with:

docker-compose -f compose.yml up -d

Import/Export Keycloak Database

Now that Keycloak is running, we can use MySQL Workbench to Export keycloak_db. Just go to Administration > Data Export and basically select everything to export.

To import it back, using Workbench again, go to Administration > Data Import/Restore. Or, if you want to use a new instance of a MySQL container on Docker, you can add a volume to the MySQL service with the initial Keycloak database (exported previously) this way:

# ...
volumes:
  - keycloak-and-mysql-volume:/var/lib/mysql
  - /path/to/file/keycloak_exported.sql:/docker-entrypoint-initdb.d/db.sql
# ...

More info of this at the official MySQL Docker Hub.

Caveats

If you try to add an Attribute to a user that contains a special character or unicode characters it will fail. This is something addressed on the Keycloak docs, but for the MySQL 5.5 version.

For this example we are using MySQL 8.0.27, so, when you export the database with Workbench, you'll see that there are a lot columns created like:

-- ...
`VALUE` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
-- ...

The default MySQL server character set and collation are utf8mb4 and utf8mb4_0900_ai_ci_ as stated on the docs, so we can safely remove the CHARACTER SET instruction on the keycloak_exported.sql to fix this and then import it in a new instance of MySQL so it gets created again with the correct charset and collation. So now, the column creation will look like:

-- ...
`VALUE` varchar(255) DEFAULT NULL
-- ...

After this changes, we can add unicode characters to the user attributes.