Using MySQL for Cloudera Director Server

Cloudera Director stores various kinds of data, including information about deployments, database servers, users, CDH clusters, and Cloudera Manager instances. By default, this data is stored in an embedded H2 database stored on the filesystem where the server is running at the following location:
/var/lib/cloudera-director-server/state.h2.db
Alternatively, you can use a MySQL database instead of the embedded H2 database, as described below.

Installing the MySQL Server

  1. Install the MySQL database.
    OS Command
    RHEL
    $ sudo yum install mysql-server
    SLES
    $ sudo zypper install mysql
    $ sudo zypper install libmysqlclient_r15
    Ubuntu and Debian
    $ sudo apt-get install mysql-server
    After issuing the command, you may need to confirm that you want to complete the installation.

Configuring and Starting the MySQL Server

  1. Determine the version of MySQL.
  2. Stop the MySQL server if it is running.
    OS Command
    RHEL
    $ sudo service mysqld stop
    SLES, Ubuntu, and Debian
    $ sudo service mysql stop
  3. Move old InnoDB log files /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1 from /var/lib/mysql/ to a backup location.
  4. Determine the location of the option file, my.cnf, and update it as follows::
    • To prevent deadlocks, set the isolation level to read committed.
    • Configure MySQL to use the InnoDB engine, rather than MyISAM. (The default storage engine for MySQL is MyISAM.) To check which engine your tables are using, run the following command from the MySQL shell:
      mysql> show table status;
    • To configure MySQL to use the InnoDB storage engine, add the following line to the [mysqld] section of the my.cnf option file:
      [mysqld]
      default-storage-engine = innodb
    • Binary logging is not a requirement for Cloudera Director installations. Binary logging provides benefits such as MySQL replication or point-in-time incremental recovery after database restore. Examples of this configuration follow. For more information, see The Binary Log.
    Following is a typical option file:
    [mysqld]
    default-storage-engine = innodb
    transaction-isolation = READ-COMMITTED
    # Disabling symbolic-links is recommended to prevent assorted security risks;
    # to do so, uncomment this line:
    # symbolic-links = 0
    
    key_buffer_size = 32M
    max_allowed_packet = 32M
    thread_stack = 256K
    thread_cache_size = 64
    query_cache_limit = 8M
    query_cache_size = 64M
    query_cache_type = 1
    
    max_connections = 550
    
    #log_bin should be on a disk with enough free space. Replace '/var/lib/mysql/mysql_binary_log' with an appropriate path for your system.
    #log_bin=/var/lib/mysql/mysql_binary_log
    #expire_logs_days = 10
    #max_binlog_size = 100M
    
    # For MySQL version 5.1.8 or higher. Comment out binlog_format for lower versions.
    binlog_format = mixed
    
    read_buffer_size = 2M
    read_rnd_buffer_size = 16M
    sort_buffer_size = 8M
    join_buffer_size = 8M
    
    # InnoDB settings
    innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit  = 2
    innodb_log_buffer_size = 64M
    innodb_buffer_pool_size = 4G
    innodb_thread_concurrency = 8
    innodb_flush_method = O_DIRECT
    innodb_log_file_size = 512M
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
  5. If AppArmor is running on the host where MySQL is installed, you might need to configure AppArmor to allow MySQL to write to the binary.
  6. Ensure that the MySQL server starts at boot.
    OS Command
    RHEL
    $ sudo /sbin/chkconfig mysqld on
    $ sudo /sbin/chkconfig --list mysqld
    mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off
    SLES
    $ sudo chkconfig --add mysql
    Ubuntu and Debian
    $ sudo chkconfig mysql on
  7. Start the MySQL server:
    OS Command
    RHEL
    $ sudo service mysqld start
    SLES, Ubuntu, and Debian
    $ sudo service mysql start
  8. Set the MySQL root password. In the following example, the current root password is blank. Press the Enter key when you're prompted for the root password.
    $ sudo /usr/bin/mysql_secure_installation
    [...]
    Enter current password for root (enter for none):
    OK, successfully used password, moving on...
    [...]
    Set root password? [Y/n] y
    New password:
    Re-enter new password:
    Remove anonymous users? [Y/n] Y
    [...]
    Disallow root login remotely? [Y/n] N
    [...]
    Remove test database and access to it [Y/n] Y
    [...]
    Reload privilege tables now? [Y/n] Y
    All done!

Installing the MySQL JDBC Driver

Install the MySQL JDBC driver for the Linux distribution you are using.

OS Command
RHEL 5 or 6
  1. Download the MySQL JDBC driver from the Download Connector/J page of the MySQL web site.
  2. Extract the JDBC driver JAR file from the downloaded file. For example:
    tar zxvf mysql-connector-java-version.tar.gz
  3. Add the JDBC driver, renamed, to the relevant server. For example:
    $ sudo cp mysql-connector-java-version/mysql-connector-java-version-bin.jar /usr/share/java/mysql-connector-java.jar

    If the target directory does not yet exist on this host, you can create it before copying the JAR file. For example:

    $ sudo mkdir -p /usr/share/java/
    $ sudo cp mysql-connector-java-version/mysql-connector-java-version-bin.jar /usr/share/java/mysql-connector-java.jar
SLES
$ sudo zypper install mysql-connector-java
Ubuntu or Debian
$ sudo apt-get install libmysql-java

Creating a Database for Cloudera Director Server

You can create the database on the host where the Cloudera Director server will run, or on another host that is accessible by the Cloudera Director server. The database must be configured to support UTF-8 character set encoding.

Record the values you enter for database names, usernames, and passwords. Cloudera Director requires this information to connect to the database.

  1. Log into MySQL as the root user:
    $ mysql -u root -p
    Enter password:
  2. Create a database for Cloudera Director server:
    mysql> create database database DEFAULT CHARACTER SET utf8;
    Query OK, 1 row affected (0.00 sec)
    
    mysql > grant all on database.* TO 'user'@'%' IDENTIFIED BY 'password';
    Query OK, 0 rows affected (0.00 sec)
    database, user, and password can be any value. The examples match the names you provide in the Cloudera Director configuration settings described below in Configure Cloudera Director Server to use the MySQL Database.

Backing Up MySQL Databases

To back up the MySQL database, run the mysqldump command on the MySQL host, as follows:
$ mysqldump -hhostname -uusername -ppassword database > /tmp/database-backup.sql

Configuring Cloudera Director Server to use the MySQL Database

Before starting the Cloudera Director server, edit the "Configurations for database connectivity" section of /etc/cloudera-director-server/application.properties. .
#
# Configurations for database connectivity.
#

# Optional database type (H2 or MySQL) (defaults to H2)
#lp.database.type: mysql

# Optional database username (defaults to "director")
#lp.database.username:

# Optional database password (defaults to "password")
#lp.database.password:

# Optional database host (defaults to "localhost")
#lp.database.host:

# Optional database port (defaults to 3306)
#lp.database.port:

# Optional database (schema) name (defaults to "director")
#lp.database.name:

Migrating Cloudera Director Server from H2 to MySQL

If you are already running Cloudera Director with an embedded H2 database, you can migrate to a MySQL database by following the steps in this section. The SQL dialect that H2 uses is not compatible with MySQL, so this process requires the use of SQuirreL SQL, which translates the SQL dialect of H2 into that of MySQL.
  1. Stop Cloudera Director and back up the H2 database file, state.h2.db (located at /var/lib/cloudera-director-server/state.h2.db).
  2. Start the H2 database.
    1. Download the H2 driver.
    2. Start H2 with the following command:
      java -jar h2-version.jar &
  3. Start the MySQL database.
    1. Start mysqld with the following command:
      sudo service mysqld start
    2. Create a database for Cloudera Director (here named "director"):
      CREATE DATABASE director;
    3. Download the MySQL driver from the MySQL Downloads site.
  4. Install SQuirreL SQL
    1. Download SQuirreL SQL from the SQuirreL SQL web site.
    2. When installing SQuirreL SQL, make sure you have enabled the H2 and MySQL database plugins.
    3. Create H2 and MySQL drivers in SQuirreL SQL with drivers downloaded in steps 1 and 2.
    4. Create aliases named mysql and h2. Test the connection to make sure you can connect to both databases.




  5. Prepare the H2 database for migration.
    1. H2 generates names for indexes that are longer than what is permitted in MySQL, so you must rename the indexes in H2 to ensure that they do not violate the MySQL length limit. For example:
      ALTER INDEX DEPLOYMENTS_UNIQUE_PER_ENVIRONMENT_BY_DEPLOYMENT_NAME_INDEX_8 RENAME TO DEPLOYMENTS_UNIQUE_PER_ENVIRONMENT_BY_DEPLOYMENT_NAME
      ALTER INDEX UK_EXTERNAL_DATABASE_SERVERS_UNIQUE_PER_ENVIRONMENT_BY_NAME_INDEX_8 RENAME TO UK_EXTERNAL_DATABASE_SERVERS_UNIQUE_PER_ENVIRONMENT_BY_NAME
  6. Prepare MySQL for migration.
    1. Depending on the Cloudera Director version you are running, you may need the following step. If you see the error message invalid default value in step 7, you need to do this. Otherwise, this step is optional.
      select @@global.sql_mode
      // Keep the value somewhere if you would like to restore the value 
      // at the end of this procedure. (See step 9c below.)
      set @@global.sql_mode = "..."; 
      // remove NO_ZERO_IN_DATE,NO_ZERO_DATE from previous value.
  7. Migrate from H2 to MySQL.
    1. Go to the Alias pane in SQuirreL SQL, and connect to H2.
    2. Go to h2 > STATE > PUBLIC > TABLE, right click TABLE and select Copy Table.
    3. Go to the Alias pane in SQuirreL SQL and connect to MySQL.
    4. Go to mysql > director > TABLE, right click TABLE and select Paste Table.
    5. Close SQuirreL SQL.
  8. After migration, the primary key and auto increment are lost, and you need to restore those. (Specifically, primary key is replaced with unique key constraint, but auto increment is not picked up):
    SET FOREIGN_KEY_CHECKS=0;
    Modify the following tables: AUTHORITIES, CLUSTERS, CLUSTER_UPDATE_EVENTS, DEPLOYMENTS, ENVIRONMENTS, EXTERNAL_DATABASE_SERVERS, INSTANCE_TEMPLATES, PIPELINES, PIPELINE_JOBS, and PIPELINE_VALUES with the following SQL statement, replacing the table name with the actual name for each table:
    ALTER TABLE ${table} ADD PRIMARY KEY(ID), MODIFY COLUMN ID bigint(20) AUTO_INCREMENT;
  9. Start Cloudera Director with MySQL.
    1. Configure Cloudera Director to use MySQL, as described in Configuring Cloudera Director Server to use the MySQL Database.
    2. When Cloudera Director starts, if a database schema update is needed, it will auto-migrate the data to the new schema.
    3. After data is migrated to the new schema, restore sql_mode, if desired.