User Tools

Site Tools


cloud:recipe:sql_service

Creating a Managed DB Instance

SETUP LOCAL ENVIRONMENT FIRST

For Cloud access, see the Intro Guide.

To bootstrap your local environment, see the Configuration and Usage sections of the CLI document.

In addition to the openstack client, you must also install the Trove client in order to interact with the database service.

python3 -m pip install --user python-troveclient

Example Usage

Test your client by listing the available datastores. Only mysql and postgresql are available at this time.

 openstack datastore list 

Obtain UUIDs

You need two UUID numbers before you create a database. The UUID of an existing flavor and the UUID of an existing network.

The flavor will decide the size of the underlying system that hosts your database. Use the flavor m1.medium.

The network will determine if and how your database can be reached. For an Internet accessible database service, use the network named campus37. Your database instance will be exposed directly to the Internet by default

List flavors and network UUIDs and note the m1.medium and campus37 UUIDs in the output.

# openstack database flavor list
+--------------------------------------+-----------------------+-------+-------+------+-----------+
| ID                                   | Name                  |   RAM | vCPUs | Disk | Ephemeral |
+--------------------------------------+-----------------------+-------+-------+------+-----------+
| 0ff9f4c1-7b57-4d5e-89fe-25511963c389 | m1.xlarge             | 16384 |     8 |  128 |         0 |
| 74a0f626-dfa8-43bb-9648-29bafeef48c1 | m1.small              |  2048 |     1 |   32 |         0 |
| 8c70c6f6-0608-415e-8674-ed948d8a3387 | m1.medium             |  4096 |     1 |   64 |         0 |
| 94ab1283-8ccb-4449-a442-576824c08289 | m1.tiny               |  1024 |     1 |    8 |         0 |
| f2bec4b9-6a4f-4c62-8706-9e40bce9fd1d | m1.large              |  8192 |     2 |  128 |         0 |
+--------------------------------------+-----------------------+-------+-------+------+-----------+
# openstack network list
+--------------------------------------+--------------------+--------------------------------------+
| ID                                   | Name     | Subnets                              |
+--------------------------------------+----------+--------------------------------------+
| 3f510b67-d623-44fe-8e35-e6e2beb9dfb5 | cloud    | 9d2c428a-14b7-4820-aeca-57fe3faaae0b |
| b5d53de5-9ebe-4166-950e-957d4f2507de | campus37 | e4c5c059-616b-4321-b8fd-72510f4b7c5e |
+--------------------------------------+----------+--------------------------------------+

MySQL Usage Example

These examples may include sample output or IDs that can change. Beware if you copy and paste it.

Determine what datastore versions are available (what we have successfully tested)

openstack datastore version list mysql
+--------------------------------------+-----------+---------+
| ID                                   | Name      | Version |
+--------------------------------------+-----------+---------+
| ce40c975-0c62-4cee-aeac-a202150d9c71 | 5.7.29    |         |
| 011bf990-34c5-41dc-9d94-cd29ad86dbd9 | 5.7.33    |         |
+--------------------------------------+-----------+---------+

Launch Instance

This command will request a MySQL 5.7.29, 10GB database named mytestdb with a specific user and password.

openstack database instance create mydb \
  --flavor 8c70c6f6-0608-415e-8674-ed948d8a3387 \
  --nic net-id=b5d53de5-9ebe-4166-950e-957d4f2507de \
  --size 10 \
  --databases mytestdb \
  --users chudler:NotVewySecure \
  --datastore mysql \
  --datastore-version 5.7.29 \
  --allowed-cidr 128.135.164.0/24 \
  --allowed-cidr 10.135.164.0/24 

After a few moments, check the status of your database instance.

openstack database instance show mydb
+--------------------------+-----------------------------------------+
| Field                    | Value                                   |
+--------------------------+-----------------------------------------+
| allowed_cidrs            | ['128.135.164.0/24', '10.135.164.0/24'] |
| created                  | 2021-04-15T22:18:07                     |
| datastore                | mysql                                   |
| datastore_version        | 5.7.29                                  |
| datastore_version_number | None                                    |
| flavor                   | 8c70c6f6-0608-415e-8674-ed948d8a3387    |
| id                       | 1d53f400-b3ec-4b08-90e0-6b7b48c8e7c5    |
| name                     | mydb                                    |
| public                   | False                                   |
| region                   | RegionOne                               |
| service_status_updated   | 2021-04-15T22:18:07                     |
| status                   | BUILD                                   |
| updated                  | 2021-04-15T22:18:24                     |
| volume                   | 10                                      |
+--------------------------+-----------------------------------------+

Note that your instance status is set to BUILD until the database is ready. Your database is backed by an on-demand virtual instance.

When the database instance status chances to HEALTHY, you can connect to your database using the IP address that is shown in the ouput:

mysql -u chudler -h 128.135.37.9 --password=NotVewySecure

Customizing DB Configuration

You can change the configuration of the database while it is running, and apply configurations across groups of systems. See Upstream Docs

PostgreSQL Usage Example

Follow the preceding MySQL example, but ask for a different datastore and version in the instance request. For example

openstack database instance create myPGdb \
  --flavor 8c70c6f6-0608-415e-8674-ed948d8a3387 \
  --nic net-id=b5d53de5-9ebe-4166-950e-957d4f2507de \
  --size 10 \
  --databases mytestdb \
  --users chudler:NotVewySecure \
  --datastore postgresql \
  --datastore-version 12.6 \
  --allowed-cidr 128.135.164.0/24 \
  --allowed-cidr 10.135.164.0/24 

We do not yet support PG13.

External Users Docs

The Official Docs have a lot of information that is not covered here.

  • Backup/Snapshot
  • Managing Users
  • Managing DBs
  • Upgrading
  • Configuration
  • Replication
  • Clustering
/var/lib/dokuwiki/data/pages/cloud/recipe/sql_service.txt · Last modified: 2021/04/15 17:22 by chudler