Database

Manage databases including users, grants, and their configuration.

Tip

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

MariaDB

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

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"
      ]
    }
  }
}

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. Use the following settings to connect:

  • Server: database hostname

  • Username: see DB_USERNAME in ~/.profile

  • Password: see DB_PASSWORD in ~/.profile

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

Or directly with every ssh connection to the server with the following ssh .config entry:

LocalForward 3306 127.0.0.1:3306

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.

PostgreSQL

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

Get in touch with us for further details.

MongoDB

Due to MongoDB licensing restriction, we are not allowed to provide MongoDB as a service. We can provide MongoDB as Managed Service though. 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.