MySQL - Global Database

Owned by Daniel Colon
Last updated: Dec 11, 2023Version comment
4 min read

You will learn

By default, ThreadFix installs with a database deployed inside a container with the appropriate values already set. The following instructions cover installation of ThreadFix with an external database or other advanced database configurations.

Prerequisites

Audience: IT Professional
Difficulty: Intermediate
Time needed: Approximately 25 minutes
Tools required: MySQL cli or Kubernetes cluster with MySQL container

Things to have on hand

  • MySQL server running MySQL 8

  • FQDN or IP for the MySQL server

  • Account with Administrative access to MySQL

  • Account with permissions to modify MySQL configuration

Helm installations are configured with yaml files that override default properties. The following examples would need to be invoked with the command -f .yaml appended to the helm installation command. For example, if the yaml file is namedappsec-db.yaml, invoke with ‘helm install tf denimgroup/threadfix -f appsec-db.yaml.

Infrastructure Database only supports MySQL, with the MySQL requirements being the same as AppSec’s.

Configuring External AppSec Database

The following properties must be set for the database to function correctly depending on the database size:

Small Databases

innodb_buffer_pool_size=12G max_allowed_packet=1G lower_case_table_names=1 ## Pre 3.1.0 configs ## max_allowed_packet=256MB #tmp_table_size=6GB #max_heap_table_size=6GB

Changes to prior recommendations

  • Lowers max_allowed_packet

  • Disables/removes the following configs by default. Please note that this needs to be left configured or adjusted (most likely to lower values) for very large CSV Vulnerability Exports

#tmp_table_size=6G #max_heap_table_size=6G

Medium & Large Databases

The following sample configurations have been tested against a MySQL server hosted on a t3a.2xlarge EC2 instance with 8 vCPU and 32.0 GB RAM.

Please consult the Support team or a DBA to help configure the database to ensure stability and performance.

## new configs recommended as of release 3.1.0 ## # The buffer pools size should be 50-70% of overall ram innodb_buffer_pool_size=16GB innodb_buffer_pool_instances=16 innodb_log_file_size=1GB innodb_log_buffer_size = 256M innodb_io_capacity=1000 join_buffer_size=256K innodb_read_io_threads=10 innodb_write_io_threads=14 ## Pre 3.1.0 configs ## max_allowed_packet=256MB #tmp_table_size=6GB #max_heap_table_size=6GB

Consult vendors guides for how to properly set these values:

  1. Log into MySQL using the MySQL CLI

    1. If a local MySQL CLI is installed, it may be run directly (replace values in angle brackets, <> with their appropriate value).

    2. If a local MySQL CLI is not installed, the ThreadFix kubernetes cluster may be leveraged to run one:

      kubectl run -it --restart=Never --rm --image=mysql:8.0 setup-db -- /bin/bash
      When a bash prompt appear, run the MySQL CLI (replace values in angle brackets, <> with their appropriate value) mysql -u<user> -h<hostname> -p.

  2. Validate that the lower_case_table_names parameter is correctly set.

    The following output should appear:


    Warning: If the above parameter is not set, the ThreadFix installation will fail. Attempting to install ThreadFix without this variable will cause issues with the installation that will be difficult to reverse.

  3. Create the ThreadFix database.

     

  4. Create the ThreadFix user (replace values in angle brackets, <> with appropriate values).

     

  5. Grant access to the ThreadFix user.

     

  6. Exit the CLI with CTRL-C.

  7. Validate the user login functions (replace values in angle brackets, <> with appropriate values).

     

  8. Validate access to the database.

    The output should show the following

     

  9. Exit the CLI with CTRL-C.

Create External Database Helm Values

To review the pre-requisites for hosting an SQL Server ThreadFix database externally, please refer to the MySQL database setup guides (recommended) or the Windows SQL Server Configuration guide (supported).

  1. Create two files, username.txt and password.txt, containing the username and password credentials respectively for the external database.

    • Warning: The external database must already contain the database threadfix, and the username and password configured above must be granted all permissions on that database. For external MySQL instances, the instance must be run with the configuration lower_case_table_names=1.

    • Warning: Many text editing tools insert a trailing newline character, \n, to text files by default. If the text editor being used has this property, consult the documentation on how to disable this functionality.

  2. Validate no newline characters have been added to the username and password files. The following should output 0 for both files.

     

  3. Create a kubernetes secret for the external database:

     

  4. Remove the previously created files.

     

  5. Set the FQDN or IP of the database (replace <hostname> with the FQDN or IP of the database):

     

  6. Create myValues folder (if it does not exist).

     

  7. Create db.yaml.

     

  8. Complete any other tasks from the Installation Checklist , then Install with Helm.

www.threadfix.it | www.coalfire.com
Copyright © 2024 Coalfire. All rights reserved.

This Information Security Policy is CoalFire - Public: Distribution of this material is not limited.