MISP database

Introduction

In most MISP instances the database (MySQL or MariaDB) is on a local network, either directly on the machine or on a local DB-cluster. As a lot of organisations are moving towards a “full cloud” environment, this also means that they want to start making use of the database features offered by their cloud providers.

Microsoft offers Azure Database for MySQL and in this post I list the (limited) steps required to migrate the MISP database from a local instance to a cloud instance.

In this post I also assume you already have MISP installed with a local database. Even for new installs that already decided to use a cloud database, I first install it with a local database, and then move that local database to the cloud db.

Azure Database for MySQL

The provisioning of an Azure Database for MySQL is already described by Microsoft at https://learn.microsoft.com/en-us/azure/mysql/single-server/quickstart-create-mysql-server-database-using-azure-portal. Do not forget to set a firewall rule to allow access from your MISP instance to the cloud database.

Export and import the database

Exporting and importing a mysql database is fairly straightforward with mysqldump. The below command will export the database ‘misp’ to a file ‘/root/misp_dump.sql’.

mysqldump -u root -p misp > /root/misp_dump.sql

In order to access the cloud DB you need to force it to use SSL. Download the CA file and place it in your MISP directory (you also need it in the next step).

wget --trust-server-names --no-check-certificate  https://dl.cacerts.digicert.com/DigiCertGlobalRootCA.crt.pem
cp DigiCertGlobalRootCA.crt.pem /var/www/MISP/
chown www-data:www-data /var/www/MISP/DigiCertGlobalRootCA.crt.pem

Next we need to create the MISP database and user in the cloud DB. Add the database, the user and assign the user the correct permissions. Replace 1.2.3.4 with the IP address of your MISP server and replace misppassword with a strong MISP DB password.

mysql -u root -h misp.mysql.database.azure.com -p '--ssl-ca=/var/www/MISP/DigiCertGlobalRootCA.crt.pem'

create database misp;

create user 'misp'@'1.2.3.4' identified by 'misppassword';
grant usage on *.* to 'misp'@'1.2.3.4';
grant all privileges on misp.* to 'misp'@'1.2.3.4';
flush privileges;

Note that your actions in the mysql CLI are logged in .mysql_history. Because the history file will also contain the misp database user password it might be useful to overwrite the history file (echo > ~/.mysql_history).

You can now import the previously created database dump into the cloud DB.

mysqldump -u root -p misp < /root/misp_dump.sql

If you no longer require this file then do not forget to remove the database dump!

MISP database configuration

The main MISP database configuration is in /var/www/MISP/app/Config/database.php. Compared to a locally hosted version you will have to change the host and add the CA reference. If you have used a different combination of username/password then obviously you also have to update those settings.

<?php
  class DATABASE_CONFIG {
          public $default = array(
                  'datasource' => 'Database/Mysql',
                  'persistent' => false,
                  'host' => 'misp.mysql.database.azure.com',
                  'login' => 'misp',
                  'port' => 3306, // MySQL & MariaDB
                  'password' => 'misppassword',
                  'database' => 'misp',
                  'prefix' => '',
                  'encoding' => 'utf8',
		  'ssl_ca' => '/var/www/MISP/DigiCertGlobalRootCA.crt.pem',
          );
  }

If your MISP error logs return

'SQLSTATE[HY000] [3159] Connections using insecure transport are prohibited while --require_secure_transport=ON.',

then this means that the web server is unable to read the CA file. Make sure that you have updated the file permissions so that the web user can read the file.

A well-tuned database (innodb-) configuration can greatly improve the performance of MISP. Some of the settings that you normally configure in a “traditional” database server are now handled automatically by Azure. This concerns for example innodb_buffer_pool_size, innodb_io_capacity and innodb_read_io_threads. And although the Azure deployment provides already sane defaults, it can be useful to tweak (increase) these settings. Just be aware that a change sometimes requires a redeployment, and hence a downtime of the database, and MISP.

Personal notes

I’m not a big fan of using cloud provided MySQL services for MISP. In my experience it is fairly slow (‘network’, and at least for the low end options – this can also be related to my limited knowledge on how to setup cloud resources at Azure) and you introduce an extra external component / dependency. Obviously you can throw more money at it to make it go faster. But then why not spend that money on more resources for your MISP instance/cluster and limit the number of external dependencies?