Databases and Data Stores¶

As root user, run mariadb from command line, no separate authentication required. But note that this won’t work via sudo, but an interactive root user shell session is required.

• Username = root
• Password = The same as your root login password, default is dietpi

Directories

• Database files: /mnt/dietpi_userdata/mysql
• Configuration files: /etc/mysql

Configuration files

The MariaDB/MySQL tools read configuration files in the following order:

1. /etc/mysql/my.cnf symbolic links to this file, reason why all the rest is read.
2. /etc/mysql/mariadb.cnf (this file) to set global defaults.
3. /etc/mysql/conf.d/*.cnf to set global options.
4. /etc/mysql/mariadb.conf.d/*.cnf to set MariaDB-only options.
5. ~/.my.cnf to set user-specific options.

As a best practice, it is recommended to place MariaDB related user configuration files into /etc/mysql/mariadb.conf.d/*.cnf and to name the files in the manner of <2-digit-number>-<configurationname>.cnf (example: 99-my_config.cnf).

Configuration example

[mysqld]
# Disable file logging in favour of "journalctl -u mariadb"
skip_log_error=1
# Disable host name resolving
# NB: For remote access, IP addresses then need to be used when creating/altering database users.
skip_name_resolve=1
# Disable the TCP listener all together
# NB: Prefer "/run/mysqld/mysqld.sock" as database host, but "127.0.0.1" is usually resolved to the UNIX socket, too.
skip_networking=1
# The query cache is not recommended on multi-core machines.
query_cache_size=0
query_cache_type=0
# Since MyISAM tables are usually not used, disable key buffer
key_buffer_size=0
# Reduce Aria page cache to 512 KiB, usually used by some internal tables only
aria_pagecache_buffer_size=512k
# Reduce the number of concurrent connections to ~2x the number of PHP-FPM workers
max_connections=16

The above can be applied almost as is for MariaDB instances used only by software which runs on the same host. There are other settings like innodb_buffer_pool_size and innodb_log_file_size for the commonly used InnoDB engine, which have a much larger impact on performance and resource usage. However, to get a performance benefit instead of a penalty, those should only be set after estimating the actual database usage.

A common tool to collect info and get performance recommendations is MySQLTuner. Run the following commands after your database was in production use for some days:

curl -sSf https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl | sudo perl - --skippassword

It will show you a bunch of info and some recommendations, e.g. assuring that innodb_buffer_pool_size has at least the size of all InnoDB tables, and innodb_log_file_size being 25% of the former. We recommend to use the next power of two above the InnoDB data/table size.

Not all recommendations are reasonable in every case. E.g. it recommends to raise tmp_table_size and max_heap_table_size if more than 25% of temporary tables were created on disk. However, some queries simply cannot used the memory engine, and raising those settings then won’t reduce the temporary tables on disk ratio, but just consume unnecessarily much RAM.

• URL = http://<your.IP>/phpmyadmin
• Username = phpmyadmin
• Password = The same as your root login password, default is dietpi

To create a database and run commands, use the quick start documentation.

Since SQLite is a file based, zero configuration database without any server process, no dedicated database and configuration directories are defined.

The first thing to do in order to check Redis is working properly is sending a PING command:

redis-cli ping

For more commands and an introduction to Redis data types and commands, read the quick start documentation.

Directories

• Configuration directory:
  /etc/redis
• Main configuration file:
  /etc/redis/redis.conf

After the installation, the data transfers are made via the HTTP requests, and are handled directly by the InfluxDB service running on http://<your.IP>:8086.

Create a database using influxdb via command line tool. This tool also uses HTTP, so it can manage a database on a remote machine setting the -host option:

influx -execute 'create database myfirstdb'

Create a database using a HTTP request and curl tool:

curl -i -X POST http://<your.IP>:8086/query --data-urlencode 'q=CREATE DATABASE myfirstdb'

Post data:

curl -i -X POST 'http://<your.IP>:8086/write?db=myfirstdb' --data-binary 'temperature value=20.12'

Retrieve and display data from the database:

influx -database myfirstdb -execute 'SELECT * FROM temperature'

Retrieve data using a HTTP request and curl tool:

curl -i -XPOST http://<your.IP>:8086/query?db=mydb --data-urlencode "q=SELECT * FROM temperature"

Create users and authorizations using influx CLI

To start the InfluxDB database management interface enter:

influx -username admin -password admin01

Then create the database entries:

CREATE USER admin WITH PASSWORD 'admin01' WITH ALL PRIVILEGES
CREATE USER test_user WITH PASSWORD 'test_user01'
GRANT ALL ON mydb TO test_user
exit

By default the HTTP authentication is disabled. To enable it, follow next two steps:

1. Change next setting in the configuration file /etc/influxdb/influxdb.conf:

   auth-enabled = true
   

2. Restart the service:

   systemctl restart influxdb
   

Directories

• Data location (database files): /mnt/dietpi_userdata/influxdb
  (The data location for InfluxDB is stored respectively linked with symbolic links.)
• Configuration file: /etc/influxdb/influxdb.conf

While the Debian package by ships PostgreSQL with an active TCP/IP listener, though on localhost only, when installed via DietPi-Software this is disabled by default. We recommend using the UNIX domain socket in /run/postgresql to connect to the database, which has performance benefits. When TCP/IP connections are required, best practice is to create an override config like /etc/postgresql/*/main/conf.d/99local.conf and setting the listening address:

listen_addresses = 'localhost'

Replace localhost with an actual IP address to allow remote access or with * to all access via all LAN and public IP addresses and domain names.

When installed via DietPi-Software, the actual database files are stored in /mnt/dietpi_userdata/postgresql, so that it can easily moved to an external drive, together with other DietPi userdata. For backwards-compatibility, a symlink is created at /var/lib/postgresql.

Directories

• Config directory:
  /etc/postgresql/*/main
  with the asterisk * being the PostgreSQL version number, e.g. 11 or 13
• Main config file:
  /etc/postgresql/*/main/postgresql.conf
• DietPi config override:
  /etc/postgresql/*/main/conf.d/00dietpi.conf

Configuration override

To add or change settings, best practice is to create a new override configuration, e.g.:

/etc/postgresql/*/main/conf.d/99local.conf

For changes to take effect, the service needs to be reloaded:

systemctl reload postgresql

The systemd service postgresql.service is used to start and control the PostgreSQL server. The following commands can be used:

• Start: systemctl start postgresql
• Stop: systemctl stop postgresql
• Restart: systemctl restart postgresql
• Reload config: systemctl reload postgresql
• Print status: systemctl status postgresql

Service logs are done to the system journal an can be viewed via:

journalctl -u postgresql

The server itself by default logs to a file:

cat /var/log/postgresql/postgresql-*-main.log

Since PostgreSQL is installed via APT, it can be updated via:

apt install postgresql