Skip to main content

MySQL Metadata Store

To use this Apache Druid extension, include mysql-metadata-storage in the extensions load list.

info

The MySQL extension requires the MySQL Connector/J library, which is not included in the Druid distribution. Refer to the following section for instructions on how to install this library.

Installing the MySQL connector library

This extension can use Oracle's MySQL JDBC driver which is not included in the Druid distribution. You must install it separately. There are a few ways to obtain this library:

This fetches the MySQL connector JAR file with a name like mysql-connector-java-5.1.49.jar.

Copy or symlink this file inside the folder extensions/mysql-metadata-storage under the distribution root directory.

Setting up MySQL

To avoid issues with upgrades that require schema changes to a large metadata table, consider a MySQL version that supports instant ADD COLUMN semantics. For example, MySQL 8.

  1. Install MySQL

    Use your favorite package manager to install mysql, e.g.:

  • on Ubuntu/Debian using apt apt-get install mysql-server

  • on OS X, using Homebrew brew install mysql

    Alternatively, download and follow installation instructions for MySQL Community Server here: http://dev.mysql.com/downloads/mysql/.

  1. Create a druid database and user

    Connect to MySQL from the machine where it is installed.

    mysql -u root

    Paste the following snippet into the mysql prompt:

    -- create a druid database, make sure to use utf8mb4 as encoding
    CREATE DATABASE druid DEFAULT CHARACTER SET utf8mb4;

    -- create a druid user
    CREATE USER 'druid'@'localhost' IDENTIFIED BY 'diurd';

    -- grant the user all the permissions on the database we just created
    GRANT ALL PRIVILEGES ON druid.* TO 'druid'@'localhost';
  2. Configure your Druid metadata storage extension:

    Add the following parameters to your Druid configuration, replacing <host> with the location (host name and port) of the database.

    druid.extensions.loadList=["mysql-metadata-storage"]
    druid.metadata.storage.type=mysql
    druid.metadata.storage.connector.connectURI=jdbc:mysql://<host>/druid
    druid.metadata.storage.connector.user=druid
    druid.metadata.storage.connector.password=diurd

Encrypting MySQL connections

This extension provides support for encrypting MySQL connections. To get more information about encrypting MySQL connections using TLS/SSL in general, please refer to this guide.

Configuration

PropertyDescriptionDefaultRequired
druid.metadata.mysql.ssl.useSSLEnable SSLfalseno
druid.metadata.mysql.ssl.clientCertificateKeyStoreUrlThe file path URL to the client certificate key store.noneno
druid.metadata.mysql.ssl.clientCertificateKeyStoreTypeThe type of the key store where the client certificate is stored.noneno
druid.metadata.mysql.ssl.clientCertificateKeyStorePasswordThe Password Provider or String password for the client key store.noneno
druid.metadata.mysql.ssl.verifyServerCertificateEnables server certificate verification.falseno
druid.metadata.mysql.ssl.trustCertificateKeyStoreUrlThe file path to the trusted root certificate key store.Default trust store provided by MySQLyes if verifyServerCertificate is set to true and a custom trust store is used
druid.metadata.mysql.ssl.trustCertificateKeyStoreTypeThe type of the key store where trusted root certificates are stored.JKSyes if verifyServerCertificate is set to true and keystore type is not JKS
druid.metadata.mysql.ssl.trustCertificateKeyStorePasswordThe Password Provider or String password for the trust store.noneyes if verifyServerCertificate is set to true and password is not null
druid.metadata.mysql.ssl.enabledSSLCipherSuitesOverrides the existing cipher suites with these cipher suites.noneno
druid.metadata.mysql.ssl.enabledTLSProtocolsOverrides the TLS protocols with these protocols.noneno

MySQL InputSource

{
"type": "index_parallel",
"spec": {
"dataSchema": {
"dataSource": "some_datasource",
"dimensionsSpec": {
"dimensionExclusions": [],
"dimensions": [
"dim1",
"dim2",
"dim3"
]
},
"timestampSpec": {
"format": "auto",
"column": "ts"
},
"metricsSpec": [],
"granularitySpec": {
"type": "uniform",
"segmentGranularity": "DAY",
"queryGranularity": {
"type": "none"
},
"rollup": false,
"intervals": null
},
"transformSpec": {
"filter": null,
"transforms": []
}
},
"ioConfig": {
"type": "index_parallel",
"inputSource": {
"type": "sql",
"database": {
"type": "mysql",
"connectorConfig": {
"connectURI": "jdbc:mysql://some-rds-host.us-west-1.rds.amazonaws.com:3306/druid",
"user": "admin",
"password": "secret"
}
},
"sqls": [
"SELECT * FROM some_table"
]
},
"inputFormat": {
"type": "json"
}
},
"tuningConfig": {
"type": "index_parallel"
}
}
}