This is the documentation for Cloudera Manager 5.1.x. Documentation for other versions is available at Cloudera Documentation.

MySQL Database

Installing the MySQL Server

  Note:
  • If you already have a MySQL database set up, you can skip to the section Configuring and Starting the MySQL Server to verify that your MySQL configurations meet the requirements for Cloudera Manager.
  • It is important that the datadir directory, which, by default, is /var/lib/mysql, is on a partition that has sufficient free space.
  1. Install the MySQL database.
    OS Command
    RHEL
    $ sudo yum install mysql-server
    SLES
    $ sudo zypper install mysql
    $ sudo zypper install libmysqlclient_r15
      Note: Some SLES systems encounter errors when using the preceding zypper install command. For more information on resolving this issue, see the Novell Knowledgebase topic, error running chkconfig.
    Ubuntu and Debian
    $ sudo apt-get install mysql-server
    After issuing the command to install MySQL, you may need to respond to prompts to confirm that you do 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 out of /var/lib/mysql/ to a backup location.
  4. Determine the location of the option file, my.cnf.
  5. Update my.cnf so that it conforms to the following requirements:
      Important:
    • To prevent deadlocks, Cloudera Manager requires the isolation level to be set to read committed.
    • Configure the InnoDB engine. Cloudera Manager will not start if its tables are configured with the MyISAM engine. (Typically, tables revert to MyISAM if the InnoDB engine is misconfigured.) To check which engine your tables are using, run the following command from the MySQL shell:
      mysql> show table status;
    • The default settings in the MySQL installations in most distributions are very conservative with regards to buffer sizes and memory usage. Cloudera Management Service roles need high write throughput as, based on cluster size, they may insert a lot of records in the database. Therefore Cloudera recommends that you set the innodb_flush_method property to O_DIRECT.
    • Set the max_connections property according to the size of your cluster. Clusters with fewer than 50 hosts can be considered small clusters and clusters with more than 50 hosts can be considered large clusters:
      • Small clusters - you can store more than one database (for example, both the Activity Monitor and Service Monitor) on the same host. If you do this, you should:
        • Put each database on its own storage volume.
        • Allow 100 maximum connections for each database and then add 50 extra connections. For example, for two databases set the maximum connections to 250. If you store five databases on one host (the databases for Cloudera Manager Server, Activity Monitor, Reports Manager, Cloudera Navigator, and Hive Metastore), set the maximum connections to 550.
      • Large clusters - do not store more than one database on the same host. In such a case, use a separate host for each database/host pair. The hosts need not be reserved exclusively for databases, but each database should be on a separate host.
    Here is an option file with Cloudera recommended settings:
    [mysqld]
    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              = 16M
    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
    # NOTE: replace '/x/home/mysql/logs/binary' below with
    #       an appropriate path for your system.
    log_bin=/x/home/mysql/logs/binary/mysql_binary_log
    
    # For MySQL version 5.1.8 or later. Comment out binlog_format for older 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
  6. 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.
  7. Ensure 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
      Note: chkconfig may not be available on recent Ubuntu releases. In such cases, you may need to use Upstart to configure MySQL to start automatically when the system boots. See the Ubuntu documentation or the Upstart Cookbook for more information.
  8. Start the MySQL server:
    OS Command
    RHEL
    $ sudo service mysqld start
    SLES, Ubuntu, and Debian
    $ sudo service mysql start
  9. Set the MySQL root password. In the following procedure, your 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 Connector

Install the JDBC connector on the Cloudera Manager Server host, as well as hosts to which you assign the Activity Monitor, Reports Manager, Hive Metastore, Sentry Server, and Cloudera Navigator Audit Server roles.

  Note: If you already have the JDBC connector installed on the hosts that need it, you can skip this section. However, MySQL 5.6 requires a connector version 5.1.26 or higher.
Cloudera recommends that you assign all roles that require databases on the same host and install the connector on that host. While putting all such roles on the same host is recommended, it is not required. You could install a role, such as Activity Monitor on one host and other roles on a separate host. In such a case you would install the JDBC connector on each host running roles that access the database.
OS Command
RHEL 5 and 6
  1. Download the MySQL JDBC connector from http://www.mysql.com/downloads/connector/j/5.1.html.
  2. Extract the JDBC driver JAR file from the downloaded file; for example:
    tar zxvf mysql-connector-java-5.1.31.tar.gz
  3. Add the JDBC driver, renamed, to the relevant server; for example:
    $ sudo cp mysql-connector-java-5.1.31/mysql-connector-java-5.1.31-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-5.1.31/mysql-connector-java-5.1.31-bin.jar /usr/share/java/mysql-connector-java.jar
  Note: Do not use the yum install command to install the MySQL connector package, because it installs the openJDK, and then uses Linux alternatives command to set the system JDK to be the openJDK.
SLES
$ sudo zypper install mysql-connector-java
Ubuntu or Debian
$ sudo apt-get install libmysql-java

Creating Databases for Activity Monitor, Reports Manager, Hive Metastore, Sentry Server, and Cloudera Navigator Audit Server

Create databases and user accounts for components that require databases:
  • If you are not using the Cloudera Manager installer, the Cloudera Manager Server.
  • Cloudera Management Service roles:
    • Activity Monitor (if using the MapReduce service)
    • Reports Manager
  • Each Hive Metastore
  • Sentry Server
  • Cloudera Navigator Audit Server

You can create these databases on the host where the Cloudera Manager Server will run, or on any other hosts in the cluster. For performance reasons, you should typically install each database on the host on which the service runs, as determined by the roles you will assign during installation or upgrade. In larger deployments or in cases where database administrators are managing the databases the services will use, databases may be separated from services, but do not undertake such an implementation lightly.

The database must be configured to support UTF-8 character set encoding.

Note the values you enter for database names, user names, and passwords. The Cloudera Manager installation wizard requires this information to correctly connect to these databases.

  1. Log into MySQL as the root user:
    $ mysql -u root -p
    Enter password:
  2. Create databases for the Activity Monitor, Reports Manager, Hive Metastore, Sentry Server, and Cloudera Navigator Audit 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)
    where database, user, and password can be anything you want. The examples shown match the default names provided in the Cloudera Manager configuration settings:
    Role Database User Password
    Activity Monitor amon amon amon_password
    Reports Manager rman rman rman_password
    Hive Metastore Server metastore hive hive_password
    Sentry Server sentry sentry sentry_password
    Cloudera Navigator Audit Server nav nav nav_password

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
For example, to back up the Activity Monitor database amon on the local host as the root user, with the password amon_password:
$ mysqldump -pamon_password amon > /tmp/amon-backup.sql
To back up the sample Activity Monitor database amon on remote host myhost.example.com as the root user, with the password amon_password:
$ mysqldump -hmyhost.example.com -uroot -pcloudera amon > /tmp/amon-backup.sql
Page generated September 3, 2015.