Install and Configure MySQL for Cloudera Software

Installing MySQL Server

  1. Install the MySQL database.
    OS Command
    RHEL

    MySQL is no longer included with RHEL. You must download the repository from the MySQL site and install it directly. You can use the following commands to install MySQL. For more information, visit the MySQL website.

    wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
    sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
    yum update
    sudo yum install mysql-server
    sudo systemctl start mysqld
    SLES
    sudo zypper install mysql libmysqlclient_r17
    Ubuntu
    sudo apt-get install mysql-server

Configuring and Starting the MySQL Server

  1. Stop the MySQL server if it is running.
    OS Command
    RHEL 7 Compatible
    sudo systemctl stop mysqld
    RHEL 6 Compatible
    sudo service mysqld stop
    SLES, Ubuntu
    sudo service mysql stop
  2. 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.
  3. Determine the location of the option file, my.cnf (/etc/my.cnf by default).
  4. Update my.cnf so that it conforms to the following requirements:
    • To prevent deadlocks, set the isolation level to READ-COMMITTED.
    • Configure the InnoDB 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 use conservative buffer sizes and memory usage. CDH roles need high write throughput because they might insert many records in the database. 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:
      • Fewer than 50 hosts - 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, set the maximum connections to 550.
      • More than 50 hosts - Do not store more than one database on the same host. Use a separate host for each database/host pair. The hosts do not need to be reserved exclusively for databases, but each database should be on a separate host.
    • Although binary logging is not a requirement for CDH installations, it provides benefits such as MySQL replication or point-in-time incremental recovery after a database restore. The provided example configuration enables the binary log. For more information, see The Binary Log.
    Here is an option file with Cloudera recommended settings:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    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
    #expire_logs_days = 10
    #max_binlog_size = 100M
    
    #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 and chown the specified folder to the mysql user.
    log_bin=/var/lib/mysql/mysql_binary_log
    
    #In later versions of MySQL, if you enable the binary log and do not set
    #a server_id, MySQL will not start. The server_id must be unique within
    #the replicating group.
    server_id=1
    
    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
    
    sql_mode=STRICT_ALL_TABLES
  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 the MySQL server starts at boot:
    OS Command
    RHEL 7 compatible
    sudo systemctl enable mysqld
    RHEL 6 compatible
    sudo chkconfig mysqld on
    SLES
    sudo chkconfig --add mysql
    Ubuntu
    sudo chkconfig mysql on
  7. Start the MySQL server:
    OS Command
    RHEL 7 Compatible
    sudo systemctl start mysqld
    RHEL 6 Compatible
    sudo service mysqld start
    SLES, Ubuntu
    sudo service mysql start
  8. Run /usr/bin/mysql_secure_installation to set the MySQL root password and other security-related settings. In a new installation, the root password is blank. Press the Enter key when you're prompted for the root password. For the rest of the prompts, enter the responses listed below in bold:
    $ 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 JDBC driver on any hosts running services that require database access. For more information on Cloudera software that uses databases, see Required Databases.

Cloudera recommends that you consolidate all roles that require databases on a limited number of hosts, and install the driver on those hosts. Locating all such roles on the same hosts is recommended but not required. Make sure to install the JDBC driver on each host running roles that access the database.
OS Command
RHEL
  1. Download the MySQL JDBC driver from http://www.mysql.com/downloads/connector/j/5.1.html (in .tar.gz format).
  2. Extract the JDBC driver JAR file from the downloaded file. For example:
    tar zxvf mysql-connector-java-5.1.46.tar.gz
  3. Copy the JDBC driver, renamed, to /usr/share/java/. For example:
    sudo cp mysql-connector-java-5.1.46/mysql-connector-java-5.1.46-bin.jar \
    /usr/share/java/mysql-connector-java.jar

    If the target directory does not yet exist, create it before copying the JAR file. For example:

    sudo mkdir -p /usr/share/java/
    sudo cp mysql-connector-java-5.1.46/mysql-connector-java-5.1.46-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 Databases for Cloudera Software

Create databases and service accounts for components that require databases:
  • Each Hive metastore
  • Sentry Server
  • Sqoop Server
  • Hue
  • Oozie

The databases must be configured to support the MySQL utf8 character set encoding.

Record the values you enter for database names, usernames, and passwords. You will need them to configure the individual services later.

  1. Log in as the root user, or another user with privileges to create database and grant privileges:
    mysql -u root -p
    Enter password:
  2. Create databases for each service you are using from the below table:
    mysql> CREATE DATABASE <database> DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
    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)
    You can use any value you want for <database>, <user>, and <password>. The following examples are the suggested names, but you are not required to use them:
    Databases for Cloudera Software
    Service Database User
    Hue hue hue
    Hive Metastore Server metastore hive
    Sentry Server sentry sentry
    Oozie oozie oozie
  3. Confirm that you have created all of the databases:
    mysql> SHOW DATABASES;
    You can also confirm the privilege grants for a given user by running:
    mysql> SHOW GRANTS FOR '<user>'@'%';

Install CDH Packages

After completing the above instructions to install and configure MySQL databases for Cloudera software, continue to Step 4: Install CDH Packages.