Installation of External MariaDB server

 

 

Document Version: 2019.3

We'll be running MariaDB 10.3 with the TokuDB table backend, and self signed server TLS certificate to host the database for Doppler onPrem. This document describes how install and configure MariaDB, primarily on Debian or RHEL7.

In principle, any modern Linux distribution supported by MariaDB should work fine. Please see https://mariadb.com/downloads/ for specific instructions and installation notes for your platform.

1 Kernel parameters for TokuDB

TokuDB needs THP (Transparent Huge Pages) to be disabled in order to function properly.

1.1 Debian

In /etc/default/grub add `transparent_hugepage=never` to the GRUB_CMDLINE_LINUX_DEFAULT variable.

GRUB_CMDLINE_LINUX_DEFAULT="transparent_hugepage=never"

Run `update-grub` and reboot

1.2 RHEL/CentOS

In /etc/default/grub add `transparent_hugepage=never` to the end of the GRUB_CMDLINE_LINUX variable.

Run `grub2-mkconfig -o /boot/grub2/grub.cfg` and reboot.

1.3 Verify that THP is disabled

After rebooting the system, verify that transparent_hugepage is disabled.

$ cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]

2 Installing Packages

We'll use the official MariaDB and MariaDB-Tokudb 10.3 packages from MariaDB.com. Please visit https://mariadb.com/downloads/ if you need to download packages for offline installation.

2.1 Debian

$ apt-get -y install software-properties-common dirmngr
$ apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xF1656F24C74CD1D8
$ add-apt-repository 'deb [arch=amd64] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.3/debian stretch main'
$ apt-get update
$ apt-get install mariadb-server mariadb-plugin-tokudb

2.2 RHEL

# Add the following to /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/rhel7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

$ yum install MariaDB-server MariaDB-client MariaDB-tokudb-engine
$ systemctl enable --now mariadb

2.3 CentOS

# Add the following to /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

$ yum install MariaDB-server MariaDB-client MariaDB-tokudb-engine
$ systemctl enable --now mariadb

MariaDB should be started automatically on installation, and TokuDB should be enabled. Check this by running `show engines;` in the MySQL cli and verify that TokuDB has YES next to it.

2.4 Check that TokuDB is enabled

$ mysql -uroot -p -e 'show engines'
Enter password: 
+--------------------+---------+-------------------------------------------...
| Engine             | Support | Comment                                   ...
+--------------------+---------+-------------------------------------------...
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables     ...
| CSV                | YES     | Stores tables as CSV files                ...
| MEMORY             | YES     | Hash based, stored in memory, useful for t...
| MyISAM             | YES     | Non-transactional engine with good perform...
| TokuDB             | YES     | Percona TokuDB Storage Engine with Fractal...
| Aria               | YES     | Crash-safe tables with MyISAM heritage    ...
| InnoDB             | DEFAULT | Supports transactions, row-level locking, ...
| PERFORMANCE_SCHEMA | YES     | Performance Schema                        ...
| SEQUENCE           | YES     | Generated tables filled with sequential va...
+--------------------+---------+-------------------------------------------...

3 Configuring MariaDB

3.1 Importing TZ tables

To import time zone tables, run the following command.

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

3.2 Enabling TLS

We're going to set up a self signed certificate for this server for simplicity, this is easier to manage and doesn't rely on any CA infrastructure. The certificate (but not the private key) is needed during the Doppler onPrem database configuration, so keep a copy of it.

In this example, the database server has a static IP address of "192.168.4.100" so we use this at the Common Name. You can alternatively provide a hostname here, but this will require Doppler onPrem to be configured with DNS.

Generate a self signed certificate:

$ mkdir -p /etc/mysql/ssl
$ openssl req -subj '/CN=192.168.4.100' -new -newkey rsa:2048 -sha256 -days 7300 -nodes -x509 -keyout /etc/mysql/ssl/server.key -out /etc/mysql/ssl/server.crt
$ chmod 640 /etc/mysql/ssl/server.key
$ chown root.mysql /etc/mysql/ssl/server.key

Configure MariaDB to enable TLS and use the certificate by adding the the following to /etc/mysql/mariadb.conf.d/tls.cnf (Debian) or /etc/my.cnf.d/tls.cnf (RHEL/CentOS)

[mysqld]
ssl
ssl-cert=/etc/mysql/ssl/server.crt
ssl-key=/etc/mysql/ssl/server.key

Restart MariaDB and verify that TLS has been enabled

$ systemctl restart mariadb.service

Verify that TLS has been enabled by checking that 'have_ssl" is set to YES

# mysql -p -e "show variables like '%ssl%'"
Enter password:
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| have_openssl        | YES                         |
| have_ssl            | YES                         |
| ssl_ca              |                             |
| ssl_capath          |                             |
| ssl_cert            | /etc/mysql/ssl/server.crt   |
| ssl_cipher          |                             |
| ssl_crl             |                             |
| ssl_crlpath         |                             |
| ssl_key             | /etc/mysql/ssl/server.key   |
| version_ssl_library | OpenSSL 1.1.0j  20 Nov 2018 |
+---------------------+-----------------------------+

NOTE: After you have created a MySQL user with 'require ssl' parameters, you can verify that external clients can connect using TLS by create the following ~/.my.cnf file.

[client]
user=doppler
password=changeme
host=192.168.4.100 
ssl-ca=~/server.crt
ssl-verify-server-cert

And executing:

$ mysql -e "\s" | grep SSL
SSL:      Cipher in use is DHE-RSA-AES256-SHA

NOTE: Keep a copy of the `server.crt` file as you need it during the Doppler onPrem installation.

3.3 Access control, Securing the installation

Run the `mysql_secure_installation` script:

  • Change the root password, unless you did so previously.
  • Enable network traffic (listen on all interfaces)
  • Disable anonymous users
  • Remove test tables

4 Creating Doppler Database and User

 

4.1 User for Doppler onPrem

In the example below, 192.168.4.50 is the IPv4 address of the Dopper onPrem system, and 'changeme' will be used the password for the database user. Please modify these variables.

MariaDB [(none)]> create database doppler;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> grant all privileges on doppler.* to doppler@192.168.4.50 identified by 'changeme' require ssl;
Query OK, 0 rows affected (0.000 sec)

Use a random source to create secure passwords, preferably 16 characters or longer. Please make a secure note of this password, as it is needed during the the Doppler onPrem installation.

# The following shell command can be used to generate a secure password:
$ tr -dc A-Za-z0-9 < /dev/urandom | head -c32; echo

4.2 Schema

The schema for the database is installed automatically during the Doppler onPrem installation process.

4.3 Network Access

MariaDB uses port 3306, the Doppler onPrem server needs to be able to connect to this port on the database server. We recommend that the firewall on the machine is configured to only allow access incoming connections from the Doppler onPrem server.

5 Recommendations and Considerations

  • Remember to set up NTP time synchronication.
  • The application can be sensitive to round trip times between the database server and application server. We recommend a network round trip time of 1ms or less.
  • Consider installing the `haveged` package to ensure that the server has enough enthropy when performing crypto operations.
  • Consider installing a monitoring solution which charts disk I/O, memory usage and cpu usage. We recommend `munin`.
  • Upgrading MariaDB packages will cause the database daemon to restart, we recommend performing these upgrades under supervision.

6 Queries

We're happy to help with the setup, please contact us on doppler@nettec.no should you require any assistance.

PGP fingerprint is E0F4 A329 FEEA 90C7 7F07 C694 87ED 1FBF D489 F615

Created: 2019-02-25 Mon 10:46

 

Var denne artikkelen nyttig?
0 av 0 syntes dette var nyttig
Har du flere spørsmål? Send oss en henvendelse

Kommentarer

0 kommentarer

Logg på hvis du vil legge inn en kommentar.