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.

Backup

Every database is backed up daily into the users backup directory:

/home/userdir/backup/

Restore

Choose between 2 options.

  1. “rollback” with the MySQL binlog (point in time recovery)

  2. restore the nightly backup

Rollback

Import the binlog.

  • start-datetime: time of the last nightly dump

  • stop-datetime: required restore point

and rollback:

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

Nightly restore

for a complete restore of the nightly database backup, decompress the backup, import it and remove the latest .sql.lzo file:

lzop -dc ~/backup/<database>.sql.lzo | mysql <database>

the database.sql.lzo.1 is the backup from yesterday.

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).

PostgreSQL

Database

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

Options

  • hash name: database Name

  • type: database type, use postgresql

  • user_password: adds a user with the same name as the database with this password and grant all privileges

Example

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

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

Backup

Every database is dumped daily into the ~/backup/ directory.

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.