Use an external database
This page describes how to configure Cyberwatch to use an external database instead of the database deployed as a container of the Cyberwatch instance.
Software prerequisites
Cyberwatch supports the following databases:
- MariaDB 10.11 or 11.4
- MySQL 8.X
Material prerequisites
Cyberwatch recommends the following hardware configuration to ensure proper operation of the application:
- 2 vCPU
- 12 Go of RAM
- 100 Go of disk space
External Database use case
Cyberwatch uses a containerized MariaDB database by default.
However, using an externalized database can be beneficial in some cases.
Cyberwatch requires the use of an external database for any instance that monitors 5000 or more assets. The use of an external database is also possible for smaller instances, considering the advantages and limitations below.
Advantages of using an external database
- ability to configure and customize database configurations;
- use of a dedicated server, allowing for more accurate resource allocation;
- better performance;
- replication and backup mechanisms that can be more finely tuned.
Limitations related to the use of an outsourced database
- the use of a dedicated server leads to additional infrastructure requirements;
- the maintenance of the database and the dedicated server is not the responsibility of Cyberwatch, although our teams are available to advise you if necessary.
Creating a user and a dedicated external database
When using a dedicated database server, it is necessary to create a database with full privileges for a dedicated user. By default, Cyberwatch uses a database and 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 assigning privileges, it is necessary to replace it with the Cyberwatch server’s IP address, or its domain name, in the format %.example.com
, as long as its reverse resolution is functional.
Cyberwatch base orchestrator configuration
Generating a backup of the containerized database
In case of a migration of an existing containerized database, a backup of the application database should be saved.
To achieve this, use the command below:
sudo cyberwatch backup save
The generated dump is saved in the /var/lib/cyberwatch/backups/
directory and will later be used to restore the Cyberwatch database.
Configure the master Cyberwatch node to use the external database
Using an external database requires to configure the master node with the --no-db
option. For a single node instance, with the command:
sudo cyberwatch configure --no-db
If your instance is made of several nodes, you need to specify again which configuration to use.
For instance, to use an external database on a master node with satellites, the command is:
sudo cyberwatch configure --no-db --master
The --no-db
option indicates to Cyberwatch that we will be using an external database and therefore disables the containerized database.
In case configuration files are already present, the cyberwatch
executable will ask the user if he wants to change the configuration.
Answering positively to this configuration change demand will allow Cyberwatch to correctly configure the TLS certificates and the database connection variables.
Certificate Generation
The cyberwatch
executable allows generating the certificates necessary for establishing TLS encryption for communication between Cyberwatch instances and the external database.
These certificates allow Cyberwatch instances to verify that they are connecting to the expected service, but they do not allow these services to verify the identity of the clients connecting to them.
Therefore, when requesting information for generating certificates, it is necessary to provide the IP address and the DNS name(s) of the services to which the instances will connect.
For the database, the useful certificates are as follows:
/etc/cyberwatch/certs/cbw-root-ca-cert.pem
: root certificate used to generate the certificates for the services. It allows Cyberwatch instances to verify the certificates deposited 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 are to be copied to the database server and pointed in the configuration file of the database, these elements are detailed below.
Adapting the Cyberwatch configuration to the external base
Then, you need to replace the MYSQL_PASSWORD
value by the external database user password in the file /etc/cyberwatch/secrets.env
.
In addition, all connection information to the external database can be edited from the /etc/cyberwatch/containers.env
file, where it may be necessary to modify:
- the value of the
MYSQL_HOSTNAME
field with the address of the database server to contact; - the value of the
MYSQL_DATABASE
field by the name of the dedicated database; - the value of the
MYSQL_USER
field by the name of the dedicated user created previously.
Restoring the database dump
In case of a migration only, it is then possible to restore the application database backup with the command below:
sudo cyberwatch backup restore
Configuring the external database
Modifying the default configuration
In order to actually profit from the performance increase expected through the use of an external database, it is required to configure it based on the size of the Cyberwatch instance. The default configuration is not suitable.
The configuration file to edit can depend on the database used and on the system on which it is installed. In this documentation, we choose to modify the file /etc/my.cnf
(or /etc/mysql/my.cnf
) considering the configuration detailed below.
Here is an configuration example for an external database:
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
innodb_buffer_pool_size = 2048M
innodb_log_file_size = 512M
innodb_fast_shutdown = 0
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 first two lines define the charset to be used, in order to avoid any encoding problem later down the line.
- The following two lines are minimal InnoDB configurations to be used at the date of the writing of this documentation.
- The next three lines indicate the location of the certificates on the database server. These certificates are used to enable TLS encryption between between the Cyberwatch instances and the database.
- The last line is to set the database time zone to UTC to avoid delays in Cyberwatch.
For more information, please refer to the MariaDB documentation regarding the different 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 should be adapted based on the size of the Cyberwatch database.
The size of the database typically depends on the number of assets monitored in Cyberwatch.
Two golden rules must be followed in any circumstances for optimal performances:
- the variable
innodb_buffer_pool_size
should always be superior to the size of the database; - the variable
innodb_log_file_size
should be about equal or slightly superior to the value ofinnodb_buffer_pool_size
/8.
Other parameters are often modified to improve the performance of the database. Ideally, you should follow recommendations given by the tool MySQLTuner
which is commonly used to review a MySQL installation and that makes recommendations adjustments to increase performance and stability.
Using MySQLTuner
MySQLTuner is an open source tool used to audit the configuration of a MySQL database and recommends configurations tweaks to improve the installation performance and stability: https://github.com/major/MySQLTuner-perl
The MySQLTuner tool is embedded in the sidekiq
container of the Cyberwatch application and can be used with the following command:
sudo cyberwatch exec sidekiq mysqltuner
Based on the script recommendations, configuration changes can be applied to the database. If you have any question, please feel free to contact our technical support.
Checking the connection with the database
Communication with the new base is considered operational when the sudo cyberwatch logs sidekiq
command return is similar to:
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 executed from the master node or from a satellite node in order to check the communication of the given node with the database.