Installing and configuring a database for EFM

Learn how to install a database and configure it with your Edge Flow Manager (EFM) to manage, control, and monitor dataflows.

By default, Edge Flow Management is preconfigured with an embedded H2 database. But when setting up Edge Flow Management, you have the option to install and configure an external MySQL or PostgreSQL database to use with EFM.

The H2 database that is preconfigured in the default efm.properties file. The contents of the embedded H2 database are stored in a file on your local file system. The H2 database location is specified by he JDBC URL property: Edge Flow Management JDBC URL (efm.db.url) – jdbc:h2:./database/efm

If you plan to use the embedded H2 database, you can skip the steps for installing and configuring an external database. Alternatively, you can install an external MySQL or PostgreSQL database. To proceed with external database installation, review the following steps.

For a list of supported MySQL or PostgreSQL databases, see System requirements for EFM.

Installing MySQL

Learn how to install a MySQL external database to support Edge Flow Management (EFM). If you have already installed a PostgreSQL or an Oracle database, you can skip these steps, as only one of the databases is required.

Ensure that you have installed the JDK. For installation instructions, see Installing the JDK.

1. Log in to the node where you intend to install EFM.
Install MySQL and the MySQL community server, and start the MySQL service.
```
yum localinstall https://dev.mysql.com/get/mysql[***MYSQL_VERSION***]-com munity-release-el[***RHEL_VERSION***]-[***PACKAGE_VERSION***].noarch.rpm 
yum install mysql-community-server 
systemctl start mysqld.service
```
Replace the placeholders with the appropriate values for your specific use case:
- [***MYSQL_VERSION***]: Selected MySQL version
- [***RHEL_VERSION***]: Red Hat Enterprise Linux (RHEL) version you are using
- [***PACKAGE_VERSION***]: Specific package version
For a list of supported MySQL databases, see Supported Edge Flow Management databases.
For example, if you want to install the MySQL 8.0 Community Edition repository configuration package on a RHEL 8 system, you would use:
```
yum localinstall https://dev.mysql.com/get/mysql80-community-release-el8 -8.noarch.rpm
```

Obtain the randomly generated MySQL root password.

grep 'A temporary password is generated for root@localhost' \ 
/var/log/mysqld.log |tail -1

Reset the MySQL root password.
```
/usr/bin/mysql_secure_installation
```
You are prompted for the password you obtained in the previous step. MySQL will request you to change the password.

Once you have completed the MySQL installation, configure it for use with Edge Flow Manager.

Configuring MySQL

MySQL offers the option to use an externally located database that supports high availability. Learn how to set up a MySQL database for use with the Edge Flow Manager.

Ensure that you have installed a MySQL database. For instructions, see Installing MySQL.

Download the MySQL JDBC driver and save it in an accessible location for later use.
```
/path/to/drivers/mysql-connector-java-8.0.16.jar
```
Create a database in MySQL.
1. Access the MySQL shell using the following command: mysql -u root -p
2. Run the following command to create a database:
  CREATE DATABASE efm CHARACTER SET latin1;
Create a database user and grant privileges.
For remote hosts, specify the appropriate IP address using nifireg'@'<IP-ADDRESS> or nifireg'@'%
```
CREATE USER 'efm'@'%' IDENTIFIED BY 'efmPassword';
GRANT ALL PRIVILEGES ON efm.* TO 'efm'@'%';
FLUSH PRIVILEGES;
```
After the Edge Flow Management service is installed, copy the MySQL driver JAR file saved in Step 1 to /opt/cloudera/parcels/CEM/EFM/lib
Configure the following database properties in Cloudera Manager.
- Edge Flow Management JDBC URL (efm.db.url) – jdbc:mysql://<MYSQL-HOSTNAME>:<MYSQL-PORT>/efm
- Edge Flow Management JDBC Driver (efm.db.driverClass) – com.mysql.cj.jdbc.Driver
- Edge Flow Management Database Username (efm.db.username) – efm
- Edge Flow Management Password (efm.db.password) – efmPassword

When you have completed the Edge Flow Management database configuration, proceed to installing Cloudera Manager and your CDP Private Cloud Base cluster.

Installing PostgreSQL

Learn how to install a PostgreSQL external database to support Edge Flow Management (EFM). If you have already installed a MySQL or an Oracle database, you can skip these steps. Only one of the databases is required.

Ensure that you have installed the JDK. For installation instructions, see Installing the JDK.

Install Red Hat Package Manager (RPM) according to your operating system's requirements.
```
yum install https://yum.postgresql.org/[***POSTGRESQL_VERSION***]/redhat/ rhel-[***RHEL_VERSION***]-x86_64/[***REPOSITORY_PACKAGE_NAME***]
```
Replace the placeholders with the appropriate values for your specific use case:
- [***POSTGRESQL_VERSION***]: Desired PostgreSQL version
- [***RHEL_VERSION***]: Red Hat Enterprise Linux (RHEL) version you are using
- [***REPOSITORY_PACKAGE_NAME***]: Name of the repository configuration package for the specified PostgreSQL version and RHEL version.
For a list of supported PostgreSQL databases, see Supported Edge Flow Management databases.

For example, to add the PostgreSQL 12 repository to a RHEL 8 system, you would use:
```
yum install https://yum.postgresql.org/12/redhat/rhel-8-x86_64/pgdg-redh at12-12-4.noarch.rpm
```

Install the PostgreSQL version of your choice.

yum install postgresql[***POSTGRESQL_VERSION***]-server postgresql[***POSTGRESQL_VERSION***]-contrib postgresql[***POSTGRESQL_VERSION***]

For example, if you wanted to add the PostgreSQL 12, you would use:

yum install postgresql12-server postgresql12-contrib postgresql12

Initialize the database.

For CentOS 7, use the following syntax:

[***POSTGRESQL_BIN_PATH***]/postgresql[***POSTGRESQL_VERSION***]-setup initdb

For example, to initialize PostgreSQL 12, you would use:

/usr/pgsql-12/bin/postgresql12-setup initdb

Enable and start PostgreSQL.

systemctl enable postgresql-[***POSTGRESQL_VERSION***].service systemctl start postgresql-[***POSTGRESQL_VERSION***].service

For example, if you are using CentOS 7, use the following syntax:

systemctl enable postgresql-12.service systemctl start postgresql-12.service

Verify that you can log in.
```
sudo su postgres 
psql
```

When PostgreSQL is installed, configure the database for use with Edge Flow Management.

Configuring PostgreSQL

PostgreSQL offers the option to use an externally located database that supports high availability. Learn how to set up a MySQL database for use with the Edge Flow Manager.

Ensure that you have installed a PostgreSQL database. For instructions, see Installing PostgreSQL.

Download the Postgres JDBC driver and save it in an accessible location for later use.
```
/path/to/drivers/postgresql-42.2.2.jar
```
Create a database in PostgreSQL using createdb efm.

Create a database user and grant privileges.

CREATE USER efm WITH PASSWORD 'efmPassword';
GRANT ALL PRIVILEGES ON DATABASE "efm" to efm;

After Edge Flow Management service is installed, copy the PostgreSQL driver JAR file saved in Step 1 to /opt/cloudera/parcels/CEM/EFM/lib
Configure the following database properties in Cloudera Manager.
- Edge Flow Management JDBC URL (efm.db.url) – jdbc:mysql://<POSTGRES-HOSTNAME>:<POSTGRES-PORT>/efm
- Edge Flow Management JDBC Driver (efm.db.driverClass) – org.postgresql.Driver
- Edge Flow Management Database Username (efm.db.username) – efm
- Edge Flow Management Password (efm.db.password) – efmPassword

When you have completed the Edge Flow Management database configuration, proceed to installing Cloudera Manager and your CDP Private Cloud Base cluster.

Installing Oracle

Learn how to install an external Oracle database to support Edge Flow Management (EFM).

Follow the Oracle documentation to install the database. This step is required only if you do not have the Oracle database installed in your data center or environment. If you are using an existing Oracle database, you can skip this step. If you have already installed a PostgreSQL or a MySQL database, you can also skip these steps. Only one of the databases is required.

For a list of supported Oracle databases, see Supported Edge Flow Management databases.

Once you have installed Oracle, configure the database for use with Edge Flow Management.

Configuring Oracle

Learn how to configure a Oracle database to integrate with Edge Flow Manager.

Oracle provides the option to use an externally located database that supports high availability.

You have installed an Oracle database. For details, see Installing Oracle.

Download the Oracle JDBC connector and save it somewhere accessible for later use.
```
/path/to/drivers/ojdbc11.jar
```
Open Oracle SQL*Plus or SQLcl using OS-level authentication.
```
sqlplus / as sysdba
```

Create a database user and grant privileges.

CREATE USER efm IDENTIFIED BY efmPassword;
GRANT CONNECT, RESOURCE, CREATE VIEW TO efm;
COMMIT;

After the Edge Flow Management service is installed, copy the Oracle driver jar file saved in Step 1 to /opt/cloudera/parcels/CEM/EFM/lib.
Configure the database properties in Cloudera Manager.
- Edge Flow Management JDBC Url (efm.db.url) – jdbc:oracle:thin://<ORACLE-HOSTNAME>:<ORACLE-PORT>/efm
- Edge Flow Management JDBC Driver (efm.db.driverClass) – oracle.jdbc.OracleDriver
- Edge Flow Management Database Username (efm.db.username) – efm
- Edge Flow Management Password (efm.db.password) – efmPassword

When you have completed the Edge Flow Management database configuration, proceed to installing Cloudera Manager and your CDP Private Cloud Base cluster.