Database

Manage databases including users, grants, and their configuration.

Website Database

Many website types already offer the possibility to add a database to a website without any configuration. For most users this will fulfill all requirements. Yet these database can still be configured using the Custom JSON Website Level Configuration, which is found under the “Advanced” tab.

The databases that come directly with the websites, allow for configuring the charset and collation. The example below provides the default values for the specified configuration options.

{
  "db_charset": "utf8mb4",
  "db_collate": "utf8mb4_unicode_ci"
}

MariaDB

Instead of MySQL, we use MariaDB, which is a drop-in replacement with API/ABI compatibility to MySQL.

Tip

This configurations are used in custom setups only. Usually, it is sufficient to select your desired database within the Website configuration.

Database

You can configure MariaDB databases through the database::databases hash within the Custom JSON Server Level Configuration.

Options

hash name: database Name
type: database type, use mysql
user_password: adds a user with the same name as the database with this password and all privileges to the created database

Example

Configure databases through the database::databases hash within the Custom JSON Server Level Configuration:

{
  "database::databases": {
    "<database-name-without-user>": {
      "type": "mysql"
    },
    "<database-name-with-user>": {
      "type": "mysql",
      "user_password": "<cleartext-password>"
    }
  }
}

Tip

If you add a database without user_password option, you have to configure the desired users and grants by yourself. For special configurations like external access, you have to configure the desired users and grants by yourself.

Users

You can configure MariaDB users through the database::users hash within the Custom JSON Server Level Configuration:

{
  "database::users": {
    "<username>@localhost": {
      "password": "<cleartext-password>"
    },
    "<username>@<remote-hostname>": {
      "password": "<cleartext-password>"
    }
  }
}

Tip

If you add users for remote hosts, also add corresponding Firewall Rules.

Grants

You can configure MariaDB grants through the database::grants hash within the Custom JSON Server Level Configuration:

{
  "database::grants": {
    "<username>@localhost": {
      "user": "<username>@localhost",
      "database": "<database-name>",
      "table": "*"
    },
    "<username>@<remote-hostname>": {
      "user": "<username>@<remote-hostname>",
      "database": "<database-name>",
      "table": "*"
    },
    "<username-for-specific-table>@<remote-hostname>": {
      "user": "<username-for-specific-table>@<remote-hostname>",
      "database": "<database-name>",
      "table": "<specific-table-name>"
    },
    "<username-for-specific-table-with-privileges>@<remote-hostname>": {
      "user": "<username-for-specific-table>@<remote-hostname>",
      "database": "<database-name>",
      "table": "<specific-table-name>",
      "privileges": [
        "SELECT",
        "INSERT"
      ]
    }
  }
}

Charsets and Collations

Custom Databases also allow for configuration of the used charset and collation on a per-database level:

{
  "database::databases": {
    "<database-name>": {
      "type": "mysql",
      "charset": "utf8mb4",
      "collate": "utf8mb4_unicode_ci",
    }
  }
}

Custom configuration

You can set custom MariaDB configuration options through the database::wrapper::mysql::options hash within the Custom JSON Server Level Configuration:

{
  "database::wrapper::mysql::options": {
    "ft_min_word_len": 1
  }
}

Warning

This will directly affect the MariaDB server configuration. We have no means to check your configuration and cannot guarantee anythign if you change such values. Please make sure that you know what you’re doing and contact us beforehand if you have any questions.

Restore

You can restore mysql databases from snapshots with the mysqlrestore command.

mysqlrestore starts a second and temporary MariaDB instance from which then can be restored
the temporary instance runs on a separate port, further details are displayed directly on the console
mysqlrestore must be running to work with it. So you need to use a second SSH connection until you are done.

Slow Query Log

The MySQL slow query log is disabled by default. You can activate the slow query log in the Custom JSON Server Level Configuration and then access the log with the devop user (see Generic Admin User). In this example, all queries longer than 5 seconds are logged to /var/log/mysql/slow.log.

{
  "database::wrapper::mysql::options": {
    "slow_query_log": "1",
    "long_query_time": "5",
    "slow_query_log_file": "/var/log/mysql/slow.log"
  }
}

Keep in mind that if you name the log differently, you may not be able to read the log.

Binary Logging

The MySQL binary log is disabled by default. You can activate the binary log as follows. But keep in mind that binary logging can take up a lot of diskspace.

{
  "database::wrapper::mysql::skip_log_bin": false
}

Rollback with binary logging:

start-datetime: time of the last nightly dump
stop-datetime: required restore point

mysqlbinlog --start-datetime="2020-02-09 22:07:00" --stop-datetime="2020-02-10 17:15:00" /var/log/mysql/mysql-bin.* | mysql database

Access

phpMyAdmin

We provide a central phpMyAdmin installation to access your database.

SSH tunnel

To access the database with common database tools like MySQL Workbench, create a SSH tunnel to the server and forward the MySQL port. After that, configure your favorite MySQL tool to connect to the forwarded localhost.

ssh -L 3306:localhost:3306 user@remotehost

local

simply access your database over the shell:

mysql

TLS

You can connect to all MariaDB databases with TLS enabled. Each server does generete its own, self-signed certificate. To verify the servers identity, you can fetch the corresponding certificate from /etc/mysql/tls.crt by using the devop user (see Generic Admin User).

Example

Configure databases through the database::databases hash within the Custom JSON Server Level Configuration:

{
  "database::databases": {
    "withuser": {
      "type": "mysql",
      "user_password": "cleartext-password"
    }
  }
}

Backup

The database backups are included in our server Backup and can be restored as described under Restore.

Solr

We provide Solr as Managed Service. Setup is individual according to your needs.

Get in touch with us for further details.

PostgreSQL

We provide PostgreSQL as Managed Service. Setup is individual according to your needs.

Get in touch with us for further details.

Elasticsearch

We provide Elasticsearch as Managed Service. Setup is individual according to your needs.

Get in touch with us for further details.