Database

Install and manage your favorite databases. Including users, grants, and the configuration.

MySQL / MariaDB

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

Databases

  • add a Database
  • title: Database Name
  • type: Database Type: “mysql”
  • user_password: adds a User with the same Name as the Database with this Password and grant all privileges
    • without this, you have to add user/grants by yourself (see below), otherwise only root can access this database
    • it is only possible to add a local User here. For special Configurations (e.g. external access or grants to particular Tables use users/grants below)
{
  "database::databases": {
    "<database-name-without-user>": {
      "type": "mysql"
    },
    "<database-name-with-user>": {
      "type": "mysql",
      "user_password": "<cleartext-password>"
    }
  }
}

Users

  • add a User
  • you have to add desired grants additionally
  • if you add Users for remote Hosts, also add corresponding Firewall Rule
{
  "database::users": {
    "<username>@localhost": {
      "password": "<cleartext-password>"
    },
    "<username>@<remote-hostname>": {
      "password": "<cleartext-password>"
    }
  }
}

Grants

  • grant Access for a User to a Database and Tables
{
  "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"
      ]
    }
  }
}

Additional configuration options

  • mysql::server::password: Password for the root User
  • mysql::server::ft_min_word_len: Value for the ft_min_word_len option

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 .sql file:

cd ~/backup/ && lzop -d database.sql.lzo && mysql database < database.sql && rm database.sql

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, e.g. customer01.snowflakehosting.ch
  • 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

Postgresql

Databases

  • add a Database
  • title: Database Name
  • type: Database Type: “postgresql”
  • user_password: adds a User with the same Name as the Database with this Password and grant all privileges
{
  "database::databases": {
    "withuser": {
      "type": "postgresql",
      "user_password": "cleartext-password"
    }
  }
}

Backup

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

Elasticsearch

You can setup an Elasticsearch instance as simple as any database.

Settings

The Elasticsearch Database Type accepts the following settings: * memory_ratio - will set the memory available to elasticsearch (see server/configuration for details) * custom_conf - array that will be appended to the elasticsearch config file (see example below) Note: Don’t overwrite path.repo here unless you know what you’re doing. Backup will probably fail.

Access

Elasticsearch only locally accessible through localhost:9200. You can setup a Proxy as follows:

{
  "websites::site": {
    "elasticproxy": {
      "server_name": "elastic.host",
      "type": "proxy",
      "members": [
        "localhost:9200"
      ]
    }
  }
}

Example

{
  "database::databases": {
    "elastic": {
      "type": "elasticsearch",
      "memory_ratio": "1.5",
      "custom_conf": [
        "node.name: my_elastic_node_004",
        "discovery.zen.minimum_master_nodes: 2"
      ]
    }
  }
}

Backup

Elasticsearch is backed up using the Snapshot-Feature: Every night, the server takes a new snapshot backs this snapshot away. This way, you can restore the indexes on a nightly basis. If you need to restore the data of the past night, you can simple do this via the Rest API using the backup snapshot.

Note: Of course you can define other snapshots and backup manually more often or keep them further back. Use custom_conf for configuring a new snapshot-folder (make sure the user elasticsearch can write there) and the rest of the setup is possible via Rest API.