Configuring MySQL for Streaming Components

If you intend to use MySQL to store the metadata for Streams Messaging Manager or Schema Registry, you must first place the JDBC Driver for MySQL in the required locations. Then, configure the database to store metadata.

Download the JDBC Driver for MySQL (Connector/J) to the Streams Messaging Manager and Schema Registry hosts. Extract and copy the files to the required locations and then provide symlinks. Then configure the database to store:
  • For Schema Registry, the schemas and their metadata, all the versions and branches.
  • For SMM, the Kafka metadata, stores metrics, and alert definitions.
.
  1. Log in to the host.
    1. Run the following command for Schema Registry:
      ssh [MY_SCHEMA_REGISTRY_HOST]
    2. Run the following command for Streams Messaging Manager:
      ssh [MY_STREAMS_MESSAGING_MANAGER_HOST]
  2. Download the JDBC Driver for MySQL (Connector/J) from the MySQL Product Archives.
    Cloudera recommends that you use version 5.1.46. Examples in the following steps assume that you downloaded version 5.1.46. Make sure that you download or copy the JDBC Driver for MySQL (Connector/J) archive to the host that Streams Messaging Manager or Schema Registry is deployed on.
    • If your cluster has internet access, download the archive directly to the host.
      wget https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-java-5.1.46.tar.gz
    • If internet access is not available, download it on a machine that has access and then copy it over to your host.
  3. Extract the archive.
    Use the tar command or any other archive manager to extract the archive.
    tar -xvzf [ARCHIVE_PATH]
    Replace [ARCHIVE_PATH] with the path to the archive you have downloaded. For example, /root/mysql-connector-java-5.1.46.tar.gz.
  4. Copy the mysql-connector-java-5.1.46-bin.jar JAR file from the extracted archive to the parcel directory.
    cp [MYSQL_CONNECTOR_JAR] /opt/cloudera/parcels/CDH-[VERSION_NUMBER]/jars

    Replace [MYSQL_CONNECTOR_JAR] with the path to the connector JAR file. You can find the JAR file within the directory you extracted in the previous step. For example /root/mysql-connector-java-5.1.46/mysql-connector-java-5.1.46-bin.jar. Replace [VERSION_NUMBER] with the version number of the parcel you are upgrading to.

  5. Create symlinks to make the connector available in the required locations by running the following commands.
    1. Run the following commands for Schema Registry:
      cd /opt/cloudera/parcels/CDH-[VERSION_NUMBER]/lib/schemaregistry/bootstrap/lib
      chmod 751 mysql-connector-java-5.1.46-bin.jar
      ln -s ../../../../jars/mysql-connector-java-5.1.46-bin.jar
      cd /opt/cloudera/parcels/CDH-[VERSION_NUMBER]/lib/schemaregistry/libs
      ln -s ../../../jars/mysql-connector-java-5.1.46-bin.jar
    2. Run the following commands for Streams Messaging Manager:
      cd /opt/cloudera/parcels/CDH-[VERSION_NUMBER]/lib/streams_messaging_manager/bootstrap/lib
      chmod 751 mysql-connector-java-5.1.46-bin.jar
      ln -s ../../../../jars/mysql-connector-java-5.1.46-bin.jar
      cd /opt/cloudera/parcels/CDH-[VERSION_NUMBER]/lib/streams_messaging_manager/libs
      ln -s ../../../jars/mysql-connector-java-5.1.46-bin.jar
  6. Launch the MySQL monitor:
    mysql -u root -p
  7. Create the database for the Schema Registry and the SMM metastore:
    
    create database registry;
    create database streamsmsgmgr;
    
  8. Create Schema Registry and SMM user accounts, replacing the final IDENTIFIED BY string with your password:
    
    CREATE USER 'registry'@'%' IDENTIFIED BY 'R12$%34qw';
    CREATE USER 'streamsmsgmgr'@'%' IDENTIFIED BY 'R12$%34qw';
    
  9. Assign privileges to the user account:
    
    GRANT ALL PRIVILEGES ON registry.* TO 'registry'@'%' WITH GRANT OPTION ;
    GRANT ALL PRIVILEGES ON streamsmsgmgr.* TO 'streamsmsgmgr'@'%' WITH GRANT OPTION ;
    
    If you cannot grant all privileges, grant the following privileges that SMM and Schema Registry require at a minimum:
    • CREATE/ALTER/DROP TABLE
    • CREATE/ALTER/DROP INDEX
    • CREATE/ALTER/DROP SEQUENCE
    • CREATE/ALTER/DROP PROCEDURE
    For example:
    grant create session to streamsmsgmgr;
    grant create table to streamsmsgmgr;
    grant create sequence to streamsmsgmgr;
    
  10. Commit the operation:
    commit;