Configure an External Database

All persistent data for the API Portal is stored in a database. This topic contains information for setting up an external database for your production environment. Skip this step if you are using PostgreSQL for running a test environment.
All persistent data for the API Portal is stored in a database. This topic contains information for setting up an external database for your production environment. Skip this step if you are using PostgreSQL for running a test environment.
The PostgreSQL database provided with the API Portal is intended for use in test environments only. Production environments require an external database.
API Portal supports MySQL external databases. To use your corporate MySQL 5.5 or MySQL 5.7 database, perform the following tasks:
Specify External Database Details for Production Environments
An external database is required for production environments. Specify external database details in the
portal.conf
file.
To specify external database details:
  1. Open the
    /conf/portal.conf
    file.
  2. Ensure that following PORTAL_DATABASE_* parameters appear at the end of the file. If they are not there, add them.
    Replace the parameter values with the appropriate values for your external database:
    PORTAL_DATABASE_TYPE=mysql PORTAL_DATABASE_HOST=database.mycompany.com PORTAL_DATABASE_PORT=3306 PORTAL_DATABASE_USERNAME=portal PORTAL_DATABASE_PASSWORD=password
  3. Save and exit the file.
  4. Execute the
    portal.sh
    script to apply the configuration.
Create a Database User
Create a user with permission to create and delete databases.
To create a database user:
  1. Log in with the root user account using the following command in a command prompt:
    mysql -u root -p
  2. Specify the root password when prompted.
  3. When prompted with the MySQL console, use the following command and substitute both
    <USERNAME>
    and
    <PASSWORD>
    with your values:
    GRANT ALL PRIVILEGES ON *.* TO '<USERNAME>'@'%' IDENTIFIED BY '<PASSWORD>'; FLUSH PRIVILEGES;
  4. Exit the MySQL console using the following command:
    exit;
  5. Test connecting to MySQL using the newly created account using the following command. Specify the user account and password that you created when prompted.
    mysql -u <USERNAME> -p -e "SELECT 1 FROM DUAL;"
  6. If the test connection fails, recreate the user account.
Allow Remote Access to the MySQL Database
Open port 3306 to the private network to allow
API Portal
to access the MySQL database remotely.
Port 3306 and this system should never be publicly reachable.
To allow remote access:
  1. Enter the following command in the command prompt:
    sudo firewall-cmd --permanent --zone=trusted --add-port=3306/tcp sudo firewall-cmd --reload
  2. From a different system, test the connection to the database using the following command and substitute
    <USERNAME>
    and
    <MYSQL_HOST/IP>
    with the appropriate value.
    mysql -u <USERNAME> -h <MYSQL_HOST/IP> -p -e "SELECT 1 FROM DUAL;"
  3. If this command fails, verify that you can log in with this account locally on the MySQL instance and that you have opened the port 3306.
Provide MySQL Settings
Edit the my.cnf file to assign parameter values that are required for API Portal.
To provide required settings:
  1. Edit the
    /etc/my.cnf
    file. Create the file if it does not exist.
  2. Enter the following values in the file. If the file is empty, enter the following content exactly as it appears. The settings in the
    my.cnf
    file depend on whether you are using MySQL 5.5 or MySQL 5.7.
    [client] default-character-set=utf8 [mysqld] character-set-server=utf8 innodb_log_buffer_size=32M innodb_log_file_size=80M max_allowed_packet=8M tmp_table_size=1000000000 max_heap_table_size=1000000000 group_concat_max_len=512000
    [client] default-character-set=utf8 [mysqld] character-set-server=utf8 innodb_log_buffer_size=32M innodb_log_file_size=80M max_allowed_packet=8M sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    If the content of the /etc/my.cnf file has existing settings, merge any missing configuration into its appropriate section.
  3. Save and exit your text editor.
  4. Restart MySQL.
Tip:
Create a snapshot of this MySQL VM instance.
(Optional) Specify Multiple Database Hosts
You can specify multiple database hosts in the database entries, which allows you to have two or more databases in case of a failover.
Multiple database support for failover scenarios is contingent on the databases being configured as master-master. A master-worker setup provides no failover because you cannot log in using a read-only database. All servers should also have the same login credentials.
To specify multiple database hosts:
  1. Add the following definitions to
    /etc/my.cnf
    :
    binlog_format=MIXED log_bin_trust_function_creators=1
  2. Configure the
    /conf/portal.conf
    :
    PORTAL_DATABASE_TYPE=mysql PORTAL_DATABASE_HOST=${server1},${server2} PORTAL_DATABASE_PORT=${server1_port},${server2_port} PORTAL_DATABASE_USERNAME=${username} PORTAL_DATABASE_PASSWORD=${password}
    • In PORTAL_DATABASE_HOST, specify the list of servers with comma-separated entries.
    • In PORTAL_DATABASE_PORT, specify the associated server ports with comma-separated entries.
    • All servers should have the same login credentials.