What is the Nectar database service?

The Nectar database service is a Database-as-a-Service (DBaaS) system powered by the Trove OpenStack project. It provides a simple interface to manage databases whilst hiding the underlying infrastructural requirements of configuring and running them. The Nectar database service offers the benefit of API or web based access for tasks like creating and restoring backups, adding and removing users and more.


The Nectar database service offers:

  • MySQL and PostgreSQL datastore support only (MongoDB support is planned).
  • All user and database related operations: For instance, it allows you to add users and databases to your instance through the Trove API and dashboard.
  • Database backups, stored in Nectar object storage. Both incremental and non-incremental backups are supported.


Glossary

  • Datastore – the type of database provided. For example, MySQL.
  • Datastore Version – The version of the datastore. For example, the datastore version of MySQL is currently 5.7.
  • Instance – A database host running the chosen datastore that Trove has provisioned.
  • Configuration Group – a set of configuration options for an instance. Administrators can alter the behaviour of the instance by making changes to the configuration groups. 
  • Database – can have one or more in on a Trove instance. Managed through the API or dashboard.


Available datastores

The datastores currently available are:

  • MySQL, version 5.7
  • Postgres, version  9.6

MongoDB is expected to be supported in the future.


Available flavors

The flavors currently available are:

  • db.small (2 virtual CPU cores, 4GB RAM)


Database service quota

Access to the Nectar database service is governed by its own type of quota, which you must apply for, through an allocation request form.


The allocation form allows you to request quota for:

  • Database servers
  • Database storage


You can apply for specific quota for either servers or storage, or both, depending on your requirements. If you don't specify storage, we'll allocate 10GB per server you request. If you request database storage, but don't specifically request any servers, we'll allocate you two.


Launching an instance: dashboard

To launch a Nectar database instance from the dashboard, select the Instances option underneath the Databases menu item from the left panel, and click the Launch Instance button from the top right of the screen.


You will be presented with a Launch Instance wizard to guide you through the options. In the Details tab, start by entering a name for your new database instance.


The other options are:

  • Flavor: The flavor to use for the new instance. This will govern the amount of CPU and RAM that is allocated to your new database instance. See the Available flavors list above for details.
  • Volume Size: The disk size required for your database. This cannot be extended, so choose a little more than you expect to require.
  • Availability Zone: The Nectar availability zone to launch the instance in. If unsure, choose the institution you have an existing allocation with.
  • Datastore: The datastore type and version. See the Available datastores list above for details.


The Networking tab allows you to select a network for your instance. If you're not sure, choose Classic Provider for a publicly available IP address to be allocated to your new database instance.


The Initialize Databases tab allows you to create an initial database with a username, password and optional access control. For security reasons, it is highly recommended to use a strong password and to limit access to your new database by providing an IP address you will be connecting from, to prevent any unauthorised access.

Lastly, the Advanced tab allows you to choose to create your database from an initial backup file you may have created previously.

Once you're satisfied with your configuration, click the Launch button to start the instance creation process.


The instance should enter the Build status for a few minutes, until becoming Active


Once Active, you can click the name of the instance to see the details. Under the Connection Info heading, you'll find the details you need to connect to your new database instance.


Creating backups: dashboard

To create a backup, simply click on the "Create Backup" button.





Launching an instance: command line

To launch a Nectar database instance from the command line, you'll need to ensure you have the python-troveclient package installed on your system. See your operating system documentation for more information about how to install this. You will also need an understanding of the Openstack API, including the authentication configuration steps provided there.


At any time, you can use the Trove command line help for more information about the options available:

$ trove -h


The following example will create a small MySQL 5.7 database instance, with a 1GB volume in the melbourne-np availability zone:

$ trove create my-database-instance db.small --datastore MySQL --datastore_version 5.7 --size 1 --availability_zone melbourne-np
+-------------------+--------------------------------------+
| Property          | Value                                |
+-------------------+--------------------------------------+
| created           | 2017-08-31T00:15:19                  |
| datastore         | mysql                                |
| datastore_version | 5.7                                  |
| flavor            | 325c919d-b523-4960-968c-f2baffafff94 |
| id                | 45e97eb5-3668-491b-82f6-fa5b6fef65cc |
| ip                | 115.146.92.125                       |
| name              | my-database-instance                 |
| region            | Melbourne                            |
| status            | BUILD                                |
| updated           | 2017-08-31T00:15:27                  |
| volume            | 1                                    |
| volume_used       | 0.12                                 |
+-------------------+--------------------------------------+


You can then use the trove list command to see the status of all your Nectar database instances, or trove show <id> to see the full details of a single Nectar database instance:

$ trove show 45e97eb5-3668-491b-82f6-fa5b6fef65cc
+-------------------+--------------------------------------+
| Property          | Value                                |
+-------------------+--------------------------------------+
| created           | 2017-08-31T00:15:19                  |
| datastore         | mysql                                |
| datastore_version | 5.7                                  |
| flavor            | 325c919d-b523-4960-968c-f2baffafff94 |
| id                | 45e97eb5-3668-491b-82f6-fa5b6fef65cc |
| ip                | 115.146.92.125                       |
| name              | my-database-instance                 |
| region            | Melbourne                            |
| status            | ACTIVE                               |
| updated           | 2017-08-31T00:15:27                  |
| volume            | 1                                    |
| volume_used       | 0.12                                 |
+-------------------+--------------------------------------+


In this example, the status has now become ACTIVE and is ready for use.


Checking your Nectar database service quota

When you are granted a Nectar dashboard allocation, limits are placed on the number of resources that you can consume. To see how your project is constrained:


$ trove limit-list
+---------------+----------+
| Property      | Value    |
+---------------+----------+
| max_backups   | 5        |
| max_instances | 5        |
| max_volumes   | 100      |
| verb          | ABSOLUTE |
+---------------+----------+
+-------+--------+-----------+--------+
| Value | Verb   | Remaining | Unit   |
+-------+--------+-----------+--------+
|   200 | DELETE |       199 | MINUTE |
|   200 | GET    |       199 | MINUTE |
|   200 | POST   |       199 | MINUTE |
|   200 | POST   |       200 | MINUTE |
|   200 | PUT    |       199 | MINUTE |
+-------+--------+-----------+--------+


Where max_volumes is the maximum size of the disk that you can use in GB.


Creating backups: command line

$ trove backup-create 45e97eb5-3668-491b-82f6-fa5b6fef65cc my-database-backup
+-------------+-------------------------------------------------------------------------------------------------+
| Property    | Value                                                                                           |
+-------------+-------------------------------------------------------------------------------------------------+
| created     | 2017-07-05T01:50:47                                                                             |
| datastore   | {u'version': u'5.7', u'type': u'MySQL', u'version_id': u'358f74f2-add4-46a6-9774-9ee7ebe65204'} |
| description | None                                                                                            |
| id          | b427e9c0-6c5d-4b29-b393-df415fac2ab2                                                            |
| instance_id | 45e97eb5-3668-491b-82f6-fa5b6fef65cc                                                            |
| locationRef | None                                                                                            |
| name        | my-database-backup                                                                              |
| parent_id   | None                                                                                            |
| size        | None                                                                                            |
| status      | NEW                                                                                             |
| updated     | 2017-07-05T01:50:47                                                                             |
+-------------+-------------------------------------------------------------------------------------------------+

$ trove backup-show b427e9c0-6c5d-4b29-b393-df415fac2ab2
+-------------+-------------------------------------------------------------------------------------------------+
| Property    | Value                                                                                           |
+-------------+-------------------------------------------------------------------------------------------------+
| created     | 2017-07-05T01:50:47                                                                             |
| datastore   | {u'version': u'5.7', u'type': u'MySQL', u'version_id': u'358f74f2-add4-46a6-9774-9ee7ebe65204'} |
| description | None                                                                                            |
| id          | b427e9c0-6c5d-4b29-b393-df415fac2ab2                                                            |
| instance_id | 45e97eb5-3668-491b-82f6-fa5b6fef65cc                                                            |
| locationRef | <location url>                                                                                  |
| name        | my-database-backup                                                                              |
| parent_id   | None                                                                                            |
| size        | 0.11                                                                                            |
| status      | COMPLETED                                                                                       |
| updated     | 2017-07-05T01:50:51                                                                             |
+-------------+-------------------------------------------------------------------------------------------------+


Incremental Backups

Create first incremental backup:

$ trove backup-create 45e97eb5-3668-491b-82f6-fa5b6fef65cc my-incremental-database-backup --incremental
+-------------+-------------------------------------------------------------------------------------------------+
| Property    | Value                                                                                           |
+-------------+-------------------------------------------------------------------------------------------------+
| created     | 2017-07-05T04:16:50                                                                             |
| datastore   | {u'version': u'5.6', u'type': u'MySQL', u'version_id': u'358f74f2-add4-46a6-9774-9ee7ebe65204'} |
| description | None                                                                                            |
| id          | 435f2993-e843-4e72-8aa5-6e5c1c619e74                                                            |
| instance_id | 45e97eb5-3668-491b-82f6-fa5b6fef65cc                                                            |
| locationRef | None                                                                                            |
| name        | my-incremental-database-backup                                                                  |
| parent_id   | None                                                                                            |
| size        | None                                                                                            |
| status      | NEW                                                                                             |
| updated     | 2017-07-05T04:16:50                                                                             |
+-------------+-------------------------------------------------------------------------------------------------+

$ trove backup-show 435f2993-e843-4e72-8aa5-6e5c1c619e74 my-incremental-database-backup --incremental
+-------------+-------------------------------------------------------------------------------------------------+
| Property    | Value                                                                                           |
+-------------+-------------------------------------------------------------------------------------------------+
| created     | 2017-07-05T04:16:50                                                                             |
| datastore   | {u'version': u'5.6', u'type': u'MySQL', u'version_id': u'358f74f2-add4-46a6-9774-9ee7ebe65204'} |
| description | None                                                                                            |
| id          | 435f2993-e843-4e72-8aa5-6e5c1c619e74                                                            |
| instance_id | 45e97eb5-3668-491b-82f6-fa5b6fef65cc                                                            |
| locationRef | https://swift.test.rc.nectar.org.au:8888/.......                                                |
| name        | trove-test-backup-mysql-incremental                                                             |
| parent_id   | None                                                                                            |
| size        | 0.11                                                                                            |
| status      | COMPLETED                                                                                       |
| updated     | 2017-07-05T04:16:50                                                                             |
+-------------+-------------------------------------------------------------------------------------------------+


Create second incremental backup:

$ trove backup-create 45e97eb5-3668-491b-82f6-fa5b6fef65cc my-incremental-database-backup --parent 435f2993-e843-4e72-8aa5-6e5c1c619e74
+-------------+-------------------------------------------------------------------------------------------------+
| Property    | Value                                                                                           |
+-------------+-------------------------------------------------------------------------------------------------+
| created     | 2017-07-05T04:18:30                                                                             |
| datastore   | {u'version': u'5.7', u'type': u'MySQL', u'version_id': u'358f74f2-add4-46a6-9774-9ee7ebe65204'} |
| description | None                                                                                            |
| id          | eefbf435-7d8c-4642-917d-c5a24c310671                                                            |
| instance_id | 45e97eb5-3668-491b-82f6-fa5b6fef65cc                                                            |
| locationRef | None                                                                                            |
| name        | trove-test-backup-mysql-from-incremental                                                        |
| parent_id   | 435f2993-e843-4e72-8aa5-6e5c1c619e74                                                            |
| size        | None                                                                                            |
| status      | NEW                                                                                             |
| updated     | 2017-07-05T04:18:30                                                                             |
+-------------+-------------------------------------------------------------------------------------------------+

$ trove backup-show eefbf435-7d8c-4642-917d-c5a24c310671 
+-------------+-------------------------------------------------------------------------------------------------+
| Property    | Value                                                                                           |
+-------------+-------------------------------------------------------------------------------------------------+
| created     | 2017-07-05T04:18:30                                                                             |
| datastore   | {u'version': u'5.7', u'type': u'MySQL', u'version_id': u'358f74f2-add4-46a6-9774-9ee7ebe65204'} |
| description | None                                                                                            |
| id          | eefbf435-7d8c-4642-917d-c5a24c310671                                                            |
| instance_id | 45e97eb5-3668-491b-82f6-fa5b6fef65cc                                                            |
| locationRef | https://swift.test.rc.nectar.org.au:8888/v1/.......                                             |
| name        | my-incremental-database-backup                                                                  |
| parent_id   | 435f2993-e843-4e72-8aa5-6e5c1c619e74                                                            |
| size        | 0.11                                                                                            |
| status      | COMPLETED                                                                                       |
| updated     | 2017-07-05T04:18:34                                                                             |
+-------------+-------------------------------------------------------------------------------------------------+


Creating Databases: command line

Let us create a database on our MySQL instance

$ trove help database-create 
usage: trove database-create <instance> <name>
                             [--character_set <character_set>]
                             [--collate <collate>]

Creates a database on an instance.

Positional arguments:
  <instance>                      ID or name of the instance.
  <name>                          Name of the database.

Optional arguments:
  --character_set <character_set>
                                  Optional character set for database.
  --collate <collate>             Optional collation type for database.

$ trove database-create 821a534b-f68f-404b-9a16-5b6842f4bb2c my-new-database
$ trove database-list 821a534b-f68f-404b-9a16-5b6842f4bb2c
+-----------------+
| Name            |
+-----------------+
| my-new-database |
+-----------------+


Creating Users: command line

Users are managed on a per instance basis, meaning that they are instance specific. 

Within an instance, you are free to change users names and passwords, the host that they can connect from, and to even delete users. You can also grant and revoke user access to particular databases on the instance.

By default users added to an instance database will be granted full permissions. 

Usernames and passwords are restricted by the underlying datastore: so on the NeCTAR MySQL instances the following limits are in place:

  • Usernames cannot be more than 16 characters in length.

  • Passwords can be of any length.

  • The following characters are not allowed: 

    • single quotation marks (‘)

    • double quotation marks (“)

    • backticks (`)

    • semicolons

    • commas

    • forward or backwards slashes

    • spaces at the start or the end.

  • The following characters are allowed:

    • uppercase and lowercase letters

    • numbers

    • spaces (but not at the start or end)

    • the symbols @, _, ? and #.

A user can be limited to access a the databases on a datastore instance from only a given host.

If limiting users to access the database from a given host, the only accepted option is the IPv4 address of the host. ie: 127.0.01 is accepted, but app.test.com is not. If the host parameter is set to the symbol % (the default) it allows the user to connect to the database from any host.


Let us create a user for our instance:

$ trove help user-create
usage: trove user-create <instance> <name> <password>
                         [--host <host>]
                         [--databases <databases> [<databases> ...]]

Creates a user on an instance.

Positional arguments:
  <instance>                      ID or name of the instance.
  <name>                          Name of user.
  <password>                      Password of user.

Optional arguments:
  --host <host>                   Optional host of user.
  --databases <databases> [<databases> ...]
                                  Optional list of databases.


$ trove user-create 821a534b-f68f-404b-9a16-5b6842f4bb2c new-db-user my-db-password
$ trove user-list 821a534b-f68f-404b-9a16-5b6842f4bb2c
+-------------+------+-----------+
| Name        | Host | Databases |
+-------------+------+-----------+
| new-db-user | %    |           |
+-------------+------+-----------+


If you don’t specify the database when creating a user then the user will have no access to any of the databases on the instance.

Now that we have a created a user, we need to grant this user access to the my-new-database to perform any operations. This step could have been combined by specifying the database name in the --databases parameter in the user-create command. Or, when creating the instance using --users command.


$ trove help user-grant-access
usage: trove user-grant-access <instance> <name> <databases> [<databases> ...]
                               [--host <host>]

Grants access to a database(s) for a user.

Positional arguments:
  <instance>     ID or name of the instance.
  <name>         Name of user.
  <databases>    List of databases.

Optional arguments:
  --host <host>  Optional host of user.


$ trove user-grant-access 821a534b-f68f-404b-9a16-5b6842f4bb2c myUser mysqlDatabase
$ trove user-list 821a534b-f68f-404b-9a16-5b6842f4bb2c
+-------------+------+-----------------+
| Name        | Host | Databases       |
+-------------+------+-----------------+
| new-db-user | %    | my-new-database |
+-------------+------+-----------------+

The new-db-user can now access the my-new-database.


Accessing the database

$ trove show 45e97eb5-3668-491b-82f6-fa5b6fef65cc
+-------------------+--------------------------------------+
| Property          | Value                                |
+-------------------+--------------------------------------+
| created           | 2017-08-31T00:15:19                  |
| datastore         | mysql                                |
| datastore_version | 5.7                                  |
| flavor            | 325c919d-b523-4960-968c-f2baffafff94 |
| id                | 45e97eb5-3668-491b-82f6-fa5b6fef65cc |
| ip                | 115.146.92.125                       |
| name              | my-database-instance                 |
| region            | Melbourne                            |
| status            | ACTIVE                               |
| updated           | 2017-08-31T00:15:27                  |
| volume            | 1                                    |
| volume_used       | 0.12                                 |
+-------------------+--------------------------------------+


Using the MySQL client:

$ mysql -u new-db-user -p -h 115.146.92.125
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 5.7 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>


Let us see if we can see or do anything with the database:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my-new-database    |
+--------------------+
2 rows in set (0.01 sec)

mysql>


Now that we're satisfied it works, let's try following the MySQL Example:

mysql> USE my-new-database
Database changed
mysql> CREATE TABLE cats
    -> (
    ->   id              INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record
    ->   name            VARCHAR(150) NOT NULL,                # Name of the cat
    ->   owner           VARCHAR(150) NOT NULL,                # Owner of the cat
    ->   birth           DATE NOT NULL,                        # Birthday of the cat
    ->   PRIMARY KEY     (id)                                  # Make the id the primary key
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql>  SHOW TABLES;
+---------------------------+
| Tables_in_my-new-database |
+---------------------------+
| cats                      |
+---------------------------+
1 row in set (0.01 sec)

mysql> DESCRIBE cats;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(150)     | NO   |     | NULL    |                |
| owner | varchar(150)     | NO   |     | NULL    |                |
| birth | date             | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql>