Table of Contents
- 1. Kernel parameters for TokuDB
- 2. Installing Packages
- 3. Configuring MariaDB
- 4. Creating Doppler Database and User
- 5. Recommendations and Considerations
- 6. How to reset critical passwords for MariaDB and Doppler
- 7. Queries
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.
In /etc/default/grub add `transparent_hugepage=never` to the GRUB_CMDLINE_LINUX_DEFAULT variable.
Run `update-grub` and reboot
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.
$ 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
# 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
# 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
$ 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 firstname.lastname@example.org 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
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 How to reset critical passwords for MariaDB and Doppler
6.1 Reset the dashboard root user password
To reset the Doppler dashboard root user password, open the Doppler CLI in the terminal and enter:
This will generate a new password and reset the two-factor keys.
NOTE: The two-factor key (QR code) used to enroll the authenticator app is sensitive and should not be copied or stored insecurely.
6.2 Change the external MariaDB database password
On the MariaDB server:
1) Enter the MariaDB shell as the database root user by executing:
mysql -uroot -p
After executing the command, and providing your MariaDB root password, you should be presented by a MariaDB prompt.
2) List the database users by executing the following command in the MariaDB shell:
select User,Host from mysql.user;
Identify the user you would like to change the password for. A user is a combination of a username and a hostname/ip.
In this example, we are going to change the password for the email@example.com user, and delete the doppler@% user.
3) Recommended: Remove unused MariaDB users.
During setup, other users might have been created if you've run the GRANT statement with different hostnames. In this example, we'll remove the doppler@% user.
To remove doppler@% we'll run the following in the MariaDB shell:
DROP USER doppler@'%';
4) Changing the password in MariaDB
Execute the following command in the MariaDB shell, changing the IP address and password.
grant all privileges on doppler.* to firstname.lastname@example.org identified by 'changeme' require ssl;
NOTE: It's very important to use a strong password. Currently Doppler only supports passwords matching A-Za-z0-9.
5) Updating Doppler to use the changed MariaDB password.
Access the Doppler CLI via the terminal in ESXi/vSphere, and do the following:
- Run the `db` command. Supply the new password when prompted, and "Apply changes".
- Run `restart-containers` to restart the containers.
We're happy to help with the setup, please contact us on email@example.com 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