Use an external database

This page describes how to configure Cyberwatch to use an external database instead of the containerized database of the Cyberwatch instance.

Software prerequisites

Cyberwatch supports the following databases:

  • MariaDB 11.8 or 12.3

  • MySQL 8.4 or 9.7

    We recommend using MariaDB 12.3, or, if you use MySQL, version 9.7 LTS.

Material prerequisites

Cyberwatch recommends the following hardware configuration on the server hosting the database to ensure the proper functioning of the application:

  • 2 vCPU
  • 12 GB of RAM
  • 100 GB disk space

External database use case

By default, Cyberwatch uses a containerized MariaDB database.

Using an externalized database rather than a containerized one is necessary in certain situations.

Cyberwatch requires the use of an externalized database for any instance intended to supervise 5000 or more assets. The use of an externalized database is also possible for smaller instances, considering the advantages and limitations below.

Advantages of using an external database

  • ability to customize database configurations
  • use of a dedicated server, allowing for more precise resource allocation
  • better performance
  • replication and backup mechanisms can be more finely tuned
  • using a dedicated server entails additional infrastructure requirements
  • the maintenance of the database and the dedicated server is not handled by Cyberwatch, although our teams are available to advise you if needed

Creating a user and a dedicated external database

When using a dedicated database server, it is necessary to create a database and grant all privileges to a dedicated user. By default, Cyberwatch uses a database and a user named olympe.

These operations can be performed by executing the following commands from the database server:

CREATE DATABASE olympe;
CREATE USER 'olympe'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON olympe.* TO 'olympe'@'%';
FLUSH PRIVILEGES;

When granting privileges, you must replace % with the IP address of the Cyberwatch server, or its domain name, in the format %.example.com, provided that its reverse resolution is functional. In the case of a multi-node architecture, this operation must be repeated for each satellite, if privileges are assigned by IP address.

Cyberwatch base orchestrator configuration

Generating a backup of the containerized database

In the case of migrating an existing containerized database, a backup of the application database must be performed.

To do this, use the command:

sudo cyberwatch backup save

The generated dump is available in the /var/lib/cyberwatch/backups/ directory and will allow you to restore the Cyberwatch database.

Configure the Cyberwatch nodes to use the external database

Using an externalized database requires configuring the Cyberwatch master node with the --no-db option. In the case of a single-node instance, use the command:

sudo cyberwatch configure --no-db

For a multi-node instance, all configuration options must be specified. Configure the master node using the following command:

sudo cyberwatch configure --no-db --master

Then configure the satellites using the following command:

sudo cyberwatch configure --no-db --satellite

The --no-db option tells Cyberwatch to use an external database and thus disables the use of the containerized database.

If the configuration files are already present, the cyberwatch executable will ask the user if they wish to make configuration changes.

Answering yes to the configuration change prompts will allow you to configure the TLS certificates as well as part of the database connection variables.

Certificate generation

The cyberwatch executable allows you to generate the certificates required to set up TLS encryption for communication between Cyberwatch instances and the dedicated database.

These certificates allow Cyberwatch instances to verify that they are connecting to the expected service, but do not allow these services to verify the identity of the clients connecting to them. A good way to harden the unidirectional TLS configuration in this case is to limit database access privileges, by defining an exhaustive list of client sources.

Thus, when providing information for certificate generation, you must enter the IP address and DNS name(s) of the services to which the instances will connect.

For the database, the relevant certificates are:

  • /etc/cyberwatch/certs/cbw-root-ca-cert.pem: root certificate used to generate service certificates. It allows Cyberwatch instances to verify the certificates placed on the database and the Redis database
  • /etc/cyberwatch/certs/cbw-db-cert.pem: certificate for the database
  • /etc/cyberwatch/certs/cbw-db-key.pem: private key for the database

These three files must be copied to the database server and specified in the database configuration file; these elements are detailed below.

Add root certificate to the Cyberwatch application

When setting up a key pair signed by an external certificate authority, it is necessary to declare it as an additional trusted authority to Cyberwatch. To do this, simply concatenate the root certificate of this authority and that of Cyberwatch.

Docker-Swarm

On Docker Swarm, this operation can be performed using the following command:

sudo cat path_to_the_external_root_ca.pem >> /etc/cyberwatch/certs/cbw-root-ca-cert.pem

Kubernetes

On Kubernetes, this is done by creating a dedicated secret using the following command, replacing YOUR_CA_FILE.pem with the name of the file containing the root certificate:

kubectl -n cyberwatch create secret generic db-root-ca --from-file=db-root-ca.pem=YOUR_CA_FILE.pem

The name of the generated secret must then be specified in the values.yml file of the Helm chart:

database:
  external: true
  
  tls: 
    mode: required
    secret: db-root-ca

The chart must then be deployed according to the procedure indicated in Update Cyberwatch application and the orchestrator base on Kubernetes.

Adapting the Cyberwatch configuration to the external base

Next, replace the MYSQL_PASSWORD with that of the dedicated user in the /etc/cyberwatch/secrets.env file.

In addition, the connection information for the external database can be edited from the /etc/cyberwatch/containers.env file; you may need to modify:

  • the value of the MYSQL_HOSTNAME field to the address of the database server to contact
  • the value of the MYSQL_DATABASE field to the name of the dedicated database
  • the value of the MYSQL_USER field to the name of the dedicated user created previously

Once this configuration is applied, it is necessary to restart the database server to make it persistent. In addition, it is important to check that TLS is enabled after setting these certificates; to do so, simply run the command:

SHOW VARIABLES LIKE 'have_ssl';

Restoring the database dump

In the case of migration only, it is finally possible to restore the application database by running the command:

sudo cyberwatch backup restore

Configuring the external database

Modifying the default configuration

To benefit from the performance improvements provided by using an externalized database, it is necessary to configure it according to the size of the Cyberwatch instance. The default configuration is not sufficient.

The configuration file to edit may depend on the database as well as the system on which it is installed. In this documentation, we choose to modify the /etc/my.cnf (or /etc/mysql/my.cnf) file, considering the configuration detailed below.

Here is a typical configuration example for an externalized database:

[mysqld]
character-set-server       = utf8mb4
collation-server           = utf8mb4_general_ci
innodb_buffer_pool_size    = 3072M
innodb_log_file_size       = 768M
innodb_fast_shutdown       = 0
innodb_snapshot_isolation  = 0                  # Only for MariaDB servers
ssl-ca=/path/to/cbw-root-ca-cert.pem
ssl-cert=/path/to/generated-db-cert.pem
ssl-key=/path/to/generated-db-key.pem
default-time-zone=+00:00

The innodb_snapshot_isolation option is only compatible with MariaDB servers. You must remove it for MySQL servers.

  • The first two lines define the charset to use to avoid any encoding issues
  • The next three lines are the minimal InnoDB configurations to use at the time of writing this documentation
  • The next three lines specify the location of the certificates on the server hosting the database These allow the implementation of TLS encryption between Cyberwatch instances and the database
  • The last line sets the database to the UTC time zone to avoid any time offset within Cyberwatch

For more information, refer to the MariaDB documentation regarding configuration files: https://mariadb.com/kb/en/configuring-mariadb-with-option-files/#default-option-file-locations-on-linux-unix-mac

Adapting the Cyberwatch configuration to the size of the database

This default configuration must be adapted according to the size of the Cyberwatch database.

The size of the database will mainly depend on the number of supervised assets.

Two golden rules must always be followed to ensure reasonable performance:

  • the innodb_buffer_pool_size variable must always be greater than the size of the database
  • the innodb_log_file_size variable should be about equal to or slightly greater than the value of innodb_buffer_pool_size/8

Other parameters are frequently set to improve database performance. Ideally, you should follow the recommendations of the MySQLTuner tool, which allows diagnosing the health and performance of the database.

Using MySQLTuner

MySQLTuner is an open source tool that audits the configuration of a MySQL database and provides configuration recommendations to improve the performance and stability of the installation: https://github.com/major/MySQLTuner-perl

The MySQLTuner tool is integrated into the application’s sidekiq container and can therefore be used with the following command:

sudo cyberwatch mysqltuner

Depending on the recommendations provided by the script, configuration changes may be made to the database. If you have any questions, feel free to contact our technical support.

Checking the connection with the database

Communication with the dedicated database is considered operational when the output of the following command is similar to the output below:

  • sudo cyberwatch logs sidekiq_master for Docker Swarm
  • kubectl -n cyberwatch logs --selector app=sidekiq-master --tail=-1 for Kubernetes
Start health monitoring server...
Monitoring Redis
Test redis uri redis:6379
Test connection to redis://redis:6379/0
Redis is up and running!
Watch the migration
All migrations completed
Healthcheck completed and reports successful startup
Redis is on the same node, TLS is not required.
Check if MariaDB supports TLS
TLS is available for MariaDB
The root certificate authority has been found and is valid, the MariaDB certificate will be verified.
2023-12-04T16:22:58.483Z pid=1 tid=53x INFO: Rails 7.0.8 application started in production environment

This command can be run from the master node or a satellite node to check the communication of the node in question with the database.

Back to top

English Français Español