This section covers the ways that you are able to manage the different aspects of your database instances. These include configuring who has access to your databases, managing the size and flavor of your instances and how to activate and track the logging of your instances. This section follows on from the previous example in ‘creating your database.’ It references the instance that was made in that example.
Warning
In the examples below; Commands that use the “openstack database user or
db parameters are not supported for PostgreSQL instances and will not
work at this time.
Before we continue, when talking about access for your database, it is important
to know the distinction between access to your database and access to your
database instance. In this section we discuss how to create users and
add them to your database so that they are able to perform operations on it.
These users only exist in the context of the database and have no ability to
make changes to, nor interact with the underlying database instance.
If a user was not specified when the instance was created, the only
user account that will exist on the database is the root user. As mentioned
in the previous section, when creating your database instance the password
field will be returned in the output of your create command. This is the
root user password and is necessary to interact with your database as the root
user.
We can confirm our root user exists using:
$ openstack database root show db-instance-1
+-----------------+-------+
| Field | Value |
+-----------------+-------+
| is_root_enabled | True |
+-----------------+-------+
If you ever need to reset the root password then you can run the following command against your database instance:
$ openstack database root enable db-instance-1
+----------+--------------------------------------+
| Field | Value |
+----------+--------------------------------------+
| name | root |
| password | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
+----------+--------------------------------------+
Since we are using a MySQL database, we can check that our root user is working
if we are able to access the database and run the following query.
In order for the following command to reach our database, it must be made from
a location that can ‘see’ our database instance. For this example, we did not
create a database instance accessible from the public and so you will need to
create a jumpbox on your project in order for this command to reach your
database instance:
# From your jumpbox instance:
$ mysql -h 10.0.0.83 -u root -p -e 'SELECT USER()'
Enter password:
+----------------+
| USER() |
+----------------+
| root@10.0.0.83 |
+----------------+
While it is possible to create a database user when launching your database
instance (using the --users <username>:<password> argument) it is more than
likely that further users will need to be added over time.
This can be done using the openstack commandline. Below we can see two example of how we can add a new user to our myDB database. One example creates a user that can access the database from any location. This is the same behavior that is displayed when the user is created as part of the initial database instance creation.
The other example uses the --host argument which creates a user that can
only connect from a specified IP address.
$ openstack database user create db-instance-1 newuser userpass --databases myDB
$ openstack database user list db-instance-1
+---------+-----------+-----------+
| Name | Host | Databases |
+---------+-----------+-----------+
| dbusr | % | myDB |
| newuser | % | myDB |
+---------+-----------+-----------+
$ openstack database user create db-instance-1 newuser2 userpass2 --host 10.0.0.80 --databases myDB
$ openstack database user list db-instance-1
+----------+-----------+-----------+
| Name | Host | Databases |
+----------+-----------+-----------+
| dbusr | % | myDB |
| newuser | % | myDB |
| newuser2 | 10.0.0.80 | myDB |
+----------+-----------+-----------+
Now that we are aware of how to create new users for a database, and we have previously discussed having multiple databases on our database instance we can discuss how to add or revoke user access to different databases.
Going off of the examples we had before, we created a secondary database named myDB2. The following code block is constructed so that it will allow access to the database mydb2 for newuser2
$ openstack database user grant access db-instance-1 newuser2 myDB2
# if we now show the access for our user, we will see it has been given access to myDB2
$ openstack database user show access db-instance-1 newuser2
+--------+
| Name |
+--------+
| myDB |
| myDB2 |
+--------+
If we now try to access myDB2 using newuser2 then we should successfully be able to reach it.
$ mysql -h IP_ADDRESS -u newuser2 -p myDB2
Enter password:
While trying to access this database using newuser will result in the following:
$ mysql -h IP_ADDRESS -u newuser -p myDB2
Enter password:
ERROR 1044 (42000): Access denied for user 'newuser'@'%' to database 'myDB2'
Now that you know how to add access to a user; How do you revoke access from a user? The following code block will remove the access we gave to newuser2 and show you the response we receive when trying to ping the database afterword:
$ openstack database user revoke access db-instance-1 newuser2 myDB2
$ mysql -h IP_ADDRESS -u newuser2 -p myDB2
Enter password:
ERROR 1044 (42000): Access denied for user 'newuser2'@'%' to database 'myDB2'
Before moving on let’s remove our test users for now.
$ openstack database user delete db-instance-1 newuser
$ openstack database user delete db-instance-1 newuser2
After you have created your database instance you may find that you need more storage space or you require a different flavor type. In these cases you do not have to create a new database; you can update your current one to match your sizing requirements. The following section will guide you through how to resize your instances.
Warning
Before making changes to the flavor of your instance, you should stop your instance and restart it after the resizing has been completed.
The following example will resize the flavor of db-instance-1 to c1.c2r4
$ openstack database instance resize flavor db-instance-1 c1.c2r4
This next code block allows you to resize the volume that you have attached to your instance. The command is formed similarly to the above command, you choose your instance and then specify the amount in GB you want your volume to be resized to. Unlike the previous command you do not have to stop your instance and restart it, however there will be a dip in performance until the resize is complete.
$ openstack database instance resize volume db-instance-1 10
Note
When you upgrade the size of an instance, if it has any replicas; there is an option so that they are also upgraded to the same size.
Logging is important for keeping a well maintained database. In the following example we will explain how to publish a slow_query log. These are a performance log that consists of SQL statements that have taken longer than the specified long_query_time to execute.
The first thing we have to do is check whether we have logging enabled on our instance or not.
$ openstack database log list db-instance-1
+------------+------+----------+-----------+---------+-----------+--------+
| Name | Type | Status | Published | Pending | Container | Prefix |
+------------+------+----------+-----------+---------+-----------+--------+
| slow_query | USER | Disabled | 0 | 0 | None | None |
| general | USER | Disabled | 0 | 0 | None | None |
+------------+------+----------+-----------+---------+-----------+--------+
At the moment our database instance does not have logging enabled. The following shows how to enable slow_query specifically.
$ openstack database log set --enable db-instance-1 slow_query
+-----------+----------------------------------------------------------------+
| Field | Value |
+-----------+----------------------------------------------------------------+
| container | None |
| metafile | 6f4e35e6-58fa-4812-a075-xxxxxxxxxxxx/mysql-slow_query_metafile |
| name | slow_query |
| pending | 182 |
| prefix | None |
| published | 0 |
| status | Ready |
| type | USER |
+-----------+----------------------------------------------------------------+
# Check to confirm this action
$ openstack database log list db-instance-1
+------------+------+----------+-----------+---------+-----------+--------+
| Name | Type | Status | Published | Pending | Container | Prefix |
+------------+------+----------+-----------+---------+-----------+--------+
| slow_query | USER | Ready | 0 | 182 | None | None |
| general | USER | Disabled | 0 | 0 | None | None |
+------------+------+----------+-----------+---------+-----------+--------+
Finally we publish the log using:
$ openstack database log set db-instance-1 --publish slow_query
+-----------+----------------------------------------------------------------+
| Field | Value |
+-----------+----------------------------------------------------------------+
| container | database_logs |
| metafile | 6f4e35e6-58fa-4812-a075-xxxxxxxxxxxx/mysql-slow_query_metafile |
| name | slow_query |
| pending | 0 |
| prefix | 6f4e35e6-58fa-4812-a075-xxxxxxxxxxxx/mysql-slow_query |
| published | 404 |
| status | Published |
| type | USER |
+-----------+----------------------------------------------------------------+
$ openstack object list database_logs
+--------------------------------------------------------------------------------------+
| Name |
+--------------------------------------------------------------------------------------+
| 3bc0c29d-b6bc-4729-b6a8-xxxxxxxxxxxx/mysql-slow_query/log-2020-08-05T22:19:09.621839 |
| 3bc0c29d-b6bc-4729-b6a8-xxxxxxxxxxxx/mysql-slow_query_metafile |
+--------------------------------------------------------------------------------------+