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.
- 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>"
- 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>"
- 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
Every database is backed up daily into the users backup directory:
Choose between 2 options.
- “rollback” with the MySQL binlog (point in time recovery)
- restore the nightly backup
Import the binlog.
- start-datetime: time of the last nightly dump
- stop-datetime: required restore point
mysqlbinlog --start-datetime="2015-02-09 22:07:00" --stop-datetime="2015-02-10 17:15:00" /var/log/mysql/mysql-bin.* | mysql database
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.
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
- Password: see DB_PASSWORD in
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
simply access your database over the shell:
- 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"
Every database is dumped daily into the
You can setup an Elasticsearch instance as simple as any database.
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:
path.repo here unless you know what you’re doing.
Backup will probably fail.
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
database::databases: "elastic": "type": "elasticsearch" "memory_ratio": "1.5" "custom_conf": - "node.name: my_elastic_node_004" - "discovery.zen.minimum_master_nodes: 2"
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
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.