MariaDB Database

To use a MariaDB database, follow these procedures.

Installing the MariaDB Server
Configuring and Starting the MariaDB Server
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 MariaDB
Configuring MariaDB for Oozie

Installing the MariaDB Server

Install the MariaDB database.

OS	Command
RHEL	$ sudo yum install mariadb-server
SLES	$ sudo zypper install mariadb-server Note: Some SLES systems encounter errors when using the `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 mariadb-server

After issuing the command to install MariaDB, you might need to confirm that you want to complete the installation.

Configuring and Starting the MariaDB Server

Stop the MariaDB server if it is running.
```
$ sudo service mariadb 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.
- The default settings in the MariaDB 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.
- Binary logging is not a requirement for Cloudera Manager installations. Binary logging provides benefits such as MariaDB 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 = 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
#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

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/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
```
If AppArmor is running on the host where MariaDB is installed, you might need to configure AppArmor to allow MariaDB to write to the binary.

Ensure the MariaDB server starts at boot:

OS	Command
RHEL	$ sudo systemctl enable mariadb $ sudo systemctl list-unit-files \| grep mariadb mariadb.service enabled
SLES	$ sudo chkconfig --add mariadb
Ubuntu and Debian	$ sudo chkconfig mariadb on Note: `chkconfig` may not be available on recent Ubuntu releases. You may need to use Upstart to configure MariaDB to start automatically when the system boots. For more information, see the Ubuntu documentation or the Upstart Cookbook.

Start the MariaDB server:
```
$ sudo service mariadb start
```

Set the MariaDB 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
 ... Success!

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.

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

Record 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.

Log into MariaDB 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:

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 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 MariaDB

For information about installing and configuring a MariaDB database , see MariaDB Database.

In the Cloudera Manager Admin Console, go to the Hue service status page.
Select Actions > Stop. Confirm you want to stop the service by clicking Stop.
Select Actions > Dump Database. Confirm you want to dump the database by clicking Dump Database.
Note the host to which the dump was written under Step in the Dump Database Command window. You can also find it by selecting Commands > Recent Commands > Dump Database.
Open a terminal window for the host and go to the dump file in /tmp/hue_database_dump.json.

Remove all JSON objects with useradmin.userprofile in the model field, for example:

{
"pk": 14,
"model": "useradmin.userprofile",
"fields":
{ "creation_method": "EXTERNAL", "user": 14, "home_directory": "/user/tuser2" }
},

Set strict mode in /etc/my.cnf and restart MySQL:
```
[mysqld]
sql_mode=STRICT_ALL_TABLES
```

Create a new database and grant privileges to a Hue user to manage this database. For example:

mysql> create database hue;
Query OK, 1 row affected (0.01 sec)
mysql> grant all on hue.* to 'hue'@'localhost' identified by 'secretpassword';
Query OK, 0 rows affected (0.00 sec)

In the Cloudera Manager Admin Console, click the Hue service.
Click the Configuration tab.
Select Scope > All.
Select Category > Database.
Specify the settings for Hue Database Type, Hue Database Hostname, Hue Database Port, Hue Database Username, Hue Database Password, and Hue Database Name. For example, for a MySQL database on the local host, you might use the following values:
- Hue Database Type = mysql
- Hue Database Hostname = host
- Hue Database Port = 3306
- Hue Database Username = hue
- Hue Database Password = secretpassword
- Hue Database Name = hue
Optionally restore the Hue data to the new database:
1. Select Actions > Synchronize Database.
2. Determine the foreign key ID.
```
$ mysql -uhue -psecretpassword
mysql > SHOW CREATE TABLE auth_permission;
```
3. (InnoDB only) Drop the foreign key that you retrieved in the previous step.
```
mysql > ALTER TABLE auth_permission DROP FOREIGN KEY content_type_id_refs_id_XXXXXX;
```
4. Delete the rows in the django_content_type table.
```
mysql > DELETE FROM hue.django_content_type;
```
5. In Hue service instance page, click Actions > Load Database. Confirm you want to load the database by clicking Load Database.
6. (InnoDB only) Add back the foreign key.
```
mysql > ALTER TABLE auth_permission ADD FOREIGN KEY (content_type_id) REFERENCES django_content_type (id);
```
Start the Hue service.

Configuring MariaDB for Oozie

Install and Start MariaDB 5.5

See MariaDB Database.

Create the Oozie Database and Oozie MariaDB User

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

$ mysql -u root -p
Enter password:

MariaDB [(none)]> create database oozie default character set utf8;
Query OK, 1 row affected (0.00 sec)

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

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

MariaDB [(none)]> exit
Bye

Add the MariaDB JDBC Driver JAR to Oozie

Cloudera recommends that you use the MySQL JDBC driver for MariaDB. Copy or symbolically link the MySQL JDBC driver JAR to the /var/lib/oozie/ directory.

External PostgreSQL Database

MySQL Database