MySQL Database

To use a MySQL database, follow these procedures. For information on compatible versions of the MySQL database, see CDH and Cloudera Manager Supported Databases.

Installing the MySQL Server
Configuring and Starting the MySQL Server
Installing the MySQL JDBC Driver
Creating Databases for Activity Monitor, Reports Manager, Hive Metastore Server, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server
Configuring the Hue Server to Store Data in MySQL
Configuring MySQL for Oozie

Installing the MySQL Server

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 $ sudo zypper install libmysqlclient_r17 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

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
$ sudo zypper install libmysqlclient_r17

Ubuntu and Debian

$ sudo apt-get install mysql-server

After issuing the command to install MySQL, you may need to confirm that you want to complete the installation.

Configuring and Starting the MySQL Server

Determine the version of MySQL.
Stop the MySQL server if it is running.
OS Command
RHEL
$ sudo service mysqld stop
SLES, Ubuntu, and Debian
$ sudo service mysql stop
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.
Determine the location of the option file, my.cnf.
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. 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 use conservative buffer sizes and memory usage. Cloudera Management Service 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:
  - Small clusters (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 (the databases for Cloudera Manager Server, Activity Monitor, Reports Manager, Cloudera Navigator, and Hive metastore), set the maximum connections to 550.
  - Large clusters (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 need not be reserved exclusively for databases, but each database should be on a separate host.
- If the cluster has more than 1000 hosts, set the max_allowed_packet property to 16M. Without this setting, the cluster may fail to start due to the following exception: com.mysql.jdbc.PacketTooBigException.
- Binary logging is not a requirement for Cloudera Manager 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.
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_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

# For MySQL version 5.1.8 or later. For older versions, reference MySQL documentation for configuration help.
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
```
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.

OS	Command
RHEL	$ sudo service mysqld stop
SLES, Ubuntu, and Debian	$ sudo service mysql stop

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` might not be available on recent Ubuntu releases. You may need to use Upstart to configure MySQL to start automatically when the system boots. For more information, see the Ubuntu documentation or the Upstart Cookbook.

Start the MySQL server:
OS Command
RHEL
$ sudo service mysqld start
SLES, Ubuntu, and Debian
$ sudo service mysql start

OS	Command
RHEL	$ sudo service mysqld start
SLES, Ubuntu, and Debian	$ sudo service mysql start

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 JDBC driver on the Cloudera Manager Server host, as well as hosts to which you assign the Activity Monitor, Reports Manager, Hive Metastore Server, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server roles.

Cloudera recommends that you assign all roles that require databases on the same host and install the driver on that host. Locating all such roles on the same host is recommended but not required. If you install a role, such as Activity Monitor, on one host and other roles on a separate host, you would install the JDBC driver on each host running roles that access the database.

OS	Command
RHEL	Important: Do not use the `yum install` command to install the MySQL driver package, because it installs openJDK, and then uses the Linux `alternatives` command to set the system JDK to be openJDK. Download the MySQL JDBC driver from http://www.mysql.com/downloads/connector/j/5.1.html. Extract the JDBC driver JAR file from the downloaded file. For example: tar zxvf mysql-connector-java-5.1.31.tar.gz Copy the JDBC driver, renamed, to the relevant host. 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
SLES	$ sudo zypper install mysql-connector-java
Ubuntu or Debian	$ sudo apt-get install libmysql-java

Return to Establish Your Cloudera Manager Repository Strategy.

Creating Databases for Activity Monitor, Reports Manager, Hive Metastore Server, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata 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
Cloudera Navigator Metadata 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 install each database on the host on which the service runs, as determined by the roles you assign during installation or upgrade. In larger deployments or in cases where database administrators are managing the databases the services use, you can separate databases from services, but use caution.

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

Log into MySQL as the root user:
```
$ mysql -u root -p
Enter password:
```

Create databases for the Activity Monitor, Reports Manager, Hive Metastore Server, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server:

Configure all databases to use the utf8 character set.

Include the character set for each database when you run the CREATE DATABASE statements described below.

mysql> create database database DEFAULT CHARACTER SET <character set>;
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 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
Cloudera Navigator Metadata Server	navms	navms	navms_password

Configuring the Hue Server to Store Data in MySQL

To install and configure MySQL for Hue, see Connect Hue to MySQL or MariaDB.

Connect Hue Service to MySQL

Stop Hue Service
1. In Cloudera Manager, navigate to Cluster > Hue.
2. Select Actions > Stop.
  Note: Refresh the page if the Hue service does not look stopped: .

[migration only] Dump Current Database

Select Actions > Dump Database.
Click Dump Database. The file is written to /tmp/hue_database_dump.json on the host of the Hue server.
Log on to the host of the Hue server in a command-line terminal.

Edit /tmp/hue_database_dump.json by removing all objects with useradmin.userprofile in the model field. For example:

# Count number of objects
grep -c useradmin.userprofile /tmp/hue_database_dump.json

vi /tmp/hue_database_dump.json

{
  "pk": 1,
  "model": "useradmin.userprofile",
  "fields": {
    "last_activity": "2016-10-03T10:06:13",
    "creation_method": "HUE",
    "first_login": false,
    "user": 1,
    "home_directory": "/user/admin"
  }
},
{
  "pk": 2,
  "model": "useradmin.userprofile",
  "fields": {
    "last_activity": "2016-10-03T10:27:10",
    "creation_method": "HUE",
    "first_login": false,
    "user": 2,
    "home_directory": "/user/alice"
  }
},

Connect to New Database
1. Go to Hue > Configuration.
2. Filter by category, Database.
3. Set the following database parameters:
  - Hue Database Type: MySQL
  - Hue Database Hostname: FQDN of host running MySQL server
  - Hue Database Port: 3306,5432, or 1521
  - Hue Database Username: username
  - Hue Database Password: password
  - Hue Database Name: Hue database name or SID
4. Click Save Changes.
[migration only] Synchronize New Database
1. Select Actions > Synchronize Database
2. Click Synchronize Database.
[migration only] Load Data from Old Database
1. Log on to the host of the MySQL server in a command-line terminal.
```
mysql -u root -p
Enter password: <root password>
```
2. Drop the foreign key constraint (replace the ID value).
```
SHOW CREATE table hue.auth_permission;
ALTER TABLE hue.auth_permission DROP FOREIGN KEY content_type_id_refs_id_id value;
```
3. Clean the table, django_content_type.
```
DELETE FROM hue.django_content_type;
```
4. In Cloudera Manager, load the JSON file: select Actions > Load Database and click Load Database.
5. Add the foreign key back:
```
ALTER TABLE hue.auth_permission ADD FOREIGN KEY (content_type_id) REFERENCES django_content_type (id);
```
Start Hue service
1. Navigate to Cluster > Hue, if not already there.
2. Select Actions > Start.
3. Click Start.
4. Click Hue Web UI to log on to Hue with a custom MySQL database.

Configuring MySQL for Oozie

Install and Start MySQL 5.x

See MySQL Database.

Create the Oozie Database and Oozie MySQL User

For example, using the MySQL mysql command-line tool:

$ mysql -u root -p
Enter password:

mysql> create database oozie default character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql>  grant all privileges on oozie.* to 'oozie'@'localhost' identified by 'oozie';
Query OK, 0 rows affected (0.00 sec)

mysql>  grant all privileges on oozie.* to 'oozie'@'%' identified by 'oozie';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

Add the MySQL JDBC Driver JAR to Oozie

Copy or symbolically link the MySQL JDBC driver JAR into one of the following directories:

For installations that use packages: /var/lib/oozie/
For installations that use parcels: /opt/cloudera/parcels/CDH/lib/oozie/lib/

directory.

MariaDB Database

Oracle Database