Configuring a Ranger or Ranger KMS Database: MySQL/MariaDB

Prior to upgrading your cluster to CDP Private Cloud Base you must configure the MySQL or MariaDB database instance for Ranger by creating a Ranger database and user. Before you begin the transition, review the support policies of database and admin policy support for transactions.

A supported version of MySQL or MariaDB must be running and available to be used by Ranger. See Database Requirements.

When using MySQL or MariaDB, the storage engine used for the Ranger admin policy store tables must support transactions. InnoDB supports transactions. A storage engine that does not support transactions is not suitable as a policy store.

Log in to the host where you want to set up the MySQL database for Ranger.
Make sure you have the MYSQL connector version 5.1.x. in the /usr/share/java/ directory with name mysql-connector-java.jar.

important
If you are using TLS v1.2, you must use version 5.1.48.
Edit the following file: /etc/my.cnf and add the following line:
```
log_bin_trust_function_creators = 1 
```

Restart the database:

systemctl restart mysqld

or:

systemctl restart mariadb

Log in to mysql:
```
mysql -u root
```
Run the following commands to create the Ranger database and user.
Substitute the following in the command:
- (optional) Replace rangeradmin with a username of your choice. Note this username, you will need to enter it later when running the Upgrade Cluster command.
  note
  For Ranger KMS, use (for example) rangerkms rather than rangeradmin.
- (optional) Replace cloudera with a password of your choice. Note this password, you will need to enter it later when running the Upgrade Cluster command.
- <Ranger Admin Role hostname> – the name of the host where the Ranger Admin role will run. Note this host, you will need to enter it later when running the Upgrade Cluster command.
```
CREATE DATABASE ranger;
CREATE USER 'rangeradmin'@'%' IDENTIFIED BY 'cloudera';
CREATE USER 'rangeradmin'@'localhost' IDENTIFIED BY 'cloudera';
CREATE USER 'rangeradmin'@'<Ranger Admin Role hostname>' IDENTIFIED BY 'cloudera';
GRANT ALL PRIVILEGES ON ranger.* TO 'rangeradmin'@'%';
GRANT ALL PRIVILEGES ON ranger.* TO 'rangeradmin'@'localhost';
GRANT ALL PRIVILEGES ON ranger.* TO 'rangeradmin'@'<Ranger Admin Role hostname>';
FLUSH PRIVILEGES;
```
Use the exit; command to exit MySQL.
Test connecting to the database using the following command:
```
mysql -u rangeradmin -pcloudera
```
After testing the connection, use the exit; command to exit MySQL.
Continue with the cluster installation or upgrade to complete the transition.