Using MySQL for Altus Director Server

You can set up an external MySQL database to store Altus Director server data. In a production environment, Cloudera recommends that you set up an external database for the Altus Director server instead of using the default H2 database.

To use a MySQL database for Altus Director data, complete the following steps:
  1. Install the MySQL server.
  2. Configure and start the MySQL server.
  3. Install the MySQL JDBC driver.
  4. Create a database for the Altus Director server.
  5. Configure the Altus Director server to use the MySQL database.

Installing the MySQL Server

  1. Install the MySQL database.
    OS Command
    RHEL and Centos 
    $ sudo yum install mysql-server
       
    Ubuntu 
    $ sudo apt-get install mysql-server
    After issuing the command, you might 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 and Centos 
    $ sudo service mysqld stop
    Ubuntu 
    $ 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 Altus 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 and Centos 
    $ 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
    Ubuntu 
    $ sudo chkconfig mysql on
  7. Start the MySQL server:
    OS Command
    RHEL and Centos 
    $ sudo service mysqld start
    Ubuntu 
    $ 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 and Centos
  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-convnector-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
Ubuntu 
$ sudo apt-get install libmysql-java

Creating a Database for Altus Director Server

You can create the database on the host where the Altus Director server will run, or on another host that is accessible by the Altus 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. Altus 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 Altus 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 Altus Director configuration settings described below in Configuring Altus 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
You can restore the MySQL database from your backed-up copy.

Configuring Altus Director Server to use the MySQL Database

Before starting the Altus 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: