Difference between revisions of "Rackspace API/Cloud Databases"

Revision as of 04:18, 31 December 2015

This article will show various examples and techniques for working with Rackspace's Cloud Databases (DBaaS) RESTful API.

NOTE: Some of these techniques will require you to first enable root on your instance. This must be done via the Rackspace API. However, make sure you note the important caveat that accompanies this process:

HOWTO: enable root

As an example, to enable root on a Cloud Database instance named "foobar" (id: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee; dc: DFW), you would run the following commands:

$ USERNAME=<YOUR_ACCOUNT_USERNAME>
$ API_KEY=<YOUR_API_KEY>
$ TOKEN=`curl -s -XPOST https://identity.api.rackspacecloud.com/v2.0/tokens \
         -d'{"auth":{"RAX-KSKEY:apiKeyCredentials":{"username":"'$USERNAME'","apiKey":"'$API_KEY'"}}}' \
         -H"Content-type:application/json" | \
         python -c 'import sys,json;data=json.loads(sys.stdin.read());print data["access"]["token"]["id"]'`

$ REGION=dfw
$ ACCOUNT=<YOUR_ACCOUNT_NUMBER>
$ ENDPOINT=https://${REGION}.databases.api.rackspacecloud.com/v1.0/${ACCOUNT}
$ DB_UUID=aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee  # "foobar" instance ID
$ curl -XPOST -H "X-Auth-Token: $TOKEN" "$ENDPOINT/instances/${DB_UUID}/root.json" | python -m json.tool

That last command will enable root and return the root password (note: If you ever forget your root password for a given instance, simply run that last command again and it will return your root password).

$ DB_PASSWORD=aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeee  # set this to whatever the last command returns for "password"

I will use this same example "foobar" (DB_UUID=aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee; REGION=dfw)) Cloud Database instance for the remainder of the examples in this article.

HOWTO: enable slow query logging

NOTE: Changes to GLOBAL VARIABLES are _not_ persistent and all changes will revert to the defaults if the instance is rebooted/restarted.

After enabling root on your Cloud Database instance (see above), you can run the following commands to enable slow query logging from within a Cloud Server (it must be in the same region; "DFW" for the examples in this article):

$ DB_HOSTNAME=ffffffffffffffffffffffffffffffffffffffff.rackspaceclouddb.com # use your DB's real hostname
$ mysql -u root -p -h $DB_HOSTNAME mysql  # provide the root password given above (i.e., $DB_PASSWORD)

mysql> SHOW GLOBAL VARIABLES LIKE "%slow%";
+---------------------------+------------------------------------------------------------------+
| Variable_name             | Value                                                            |
+---------------------------+------------------------------------------------------------------+
| log_slow_admin_statements | OFF                                                              |
| log_slow_slave_statements | OFF                                                              |
| slow_launch_time          | 2                                                                |
| slow_query_log            | OFF                                                              |
| slow_query_log_file       | /var/lib/mysql/f40b3b3afc5549de55fdb230252446f3c70f94e3-slow.log |
+---------------------------+------------------------------------------------------------------+

mysql> set global slow_query_log='ON';

mysql> select sleep(30);

mysql> select * from mysql.slow_log\G

    start_time: 2014-09-24 05:10:34
     user_host: root[root] @  [10.x.x.x]
    query_time: 00:00:30
     lock_time: 00:00:00
     rows_sent: 1
 rows_examined: 0
            db: foobar
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: select sleep(30)
     thread_id: 122341

HOWTO: populate timezone tables

Note: In order to populate the timezone tables for a given Cloud Database, you must first enable root on your instance.

After enabling root on your Cloud Database instance (see above), you can run the following commands from within a Cloud Server (it must be in the same region; "DFW" for the examples in this article):

$ DB_HOSTNAME=ffffffffffffffffffffffffffffffffffffffff.rackspaceclouddb.com # use your DB's real hostname
$ mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -u root -p -h $DB_HOSTNAME mysql

You can verify that the tables have been loaded by logging into your MySQL instance like so:

$ mysql -u root -p -h $DB_HOSTNAME mysql  # provide the root password given above (i.e., $DB_PASSWORD)

See also: "Managing the Time Zone with cURL".

HOWTO: change default DBaaS instance configuration parameters

Note: In order to change the default configuration for a given Cloud Database, you must first enable root on your instance.

The configuration management capability allows users of Cloud Databases to override the default database engine configuration settings provided by the Cloud Databases service. I will use "Cloud Database" and "DBaaS instance" interchangeably in this section.

• Authenticate to receive (24-hour valid) token:

$ ACCOUNT=<RAX_ACCOUNT>
$ USERNAME=<RAX_USERNAME>
$ APIKEY=<RAX_API_KEY>
$ TOKEN=`curl -s -XPOST https://identity.api.rackspacecloud.com/v2.0/tokens \
         -d'{"auth":{"RAX-KSKEY:apiKeyCredentials":{"username":"'$USERNAME'","apiKey":"'$APIKEY'"}}}' \
         -H"Content-type:application/json" | \
         python -c 'import sys,json;data=json.loads(sys.stdin.read());print data["access"]["token"]["id"]'`

• Get a list of available datastores (types and versions of databases; e.g., MySQL version 5.6) for a given region:

$ REGION=dfw
$ ENDPOINT=https://${REGION}.databases.api.rackspacecloud.com/v1.0/${ACCOUNT}
$ curl -s -H "Accept: application/json" -H "X-Auth-Token: $TOKEN" "$ENDPOINT/datastores" | python -mjson.tool

• From the above output, we find the following:
  • Type: MySQL = 10000000-0000-0000-0000-000000000001
    • Version: 5.1 = 20000000-0000-0000-0000-000000000002
    • Version: 5.6 = 1379cc8b-4bc5-4c4a-9e9d-7a9ad27c0866
  • Type: MariaDB = d03338b9-7019-4b7d-a629-41668effcdc8
    • Version: 10 = 3011fe42-797d-4b3e-95f0-0f41e54cdd4b
  • Type: Percona = d47dd19e-e5a5-474d-9af1-80d24b565036
    • Version: 5.6 = c9760c5b-5675-4482-b097-dffdf50c22ab

I will use MySQL v5.6 for the remainder of this section:

$ DB_TYPE=10000000-0000-0000-0000-000000000001     # mysql
$ DB_VERSION=1379cc8b-4bc5-4c4a-9e9d-7a9ad27c0866  # v5.6

• Get a list of your DBaaS instances:

$ curl -sH "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" "$ENDPOINT/instances" | python -mjson.tool

• List DBaaS instance status and details (including any attached config groups):

$ INSTANCE_ID=ffffffff-ffff-ffff-ffff-ffffffffffff  # replace with actual DBaaS instance UUID (from above output)
$ curl -sH "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" "$ENDPOINT/instances/${INSTANCE_ID}" | python -mjson.tool

• Get the default configuration parameters/variables for your DBaaS instance:

$ curl -sH "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" "$ENDPOINT/instances/${INSTANCE_ID}/configuration" | python -mjson.tool

• Let's say we want to update/change the "tmp_table_size" variable from its default value of "16777216" (16M) to an updated value of "67108864" (64M). First, check on the allowed range of values ("min_size" and "max_size" in this example):

$ curl -sH "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" \
    "$ENDPOINT/datastores/${DB_TYPE}/versions/${DB_VERSION}/parameters/tmp_table_size"|python -mjson.tool
{
    "datastore_version_id": "1379cc8b-4bc5-4c4a-9e9d-7a9ad27c0866",
    "max_size": "18446744073709551615",
    "min_size": "1024",
    "name": "tmp_table_size",
    "restart_required": false,
    "type": "integer"
}

Good. Our new value of "67108864" is less than the max allowed value of "18446744073709551615".

• Check on the default and current value of "tmp_table_size":

$ curl -sH "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" \
    "$ENDPOINT/instances/${INSTANCE_ID}/configuration"|\
    python -c 'import sys,json;data=json.loads(sys.stdin.read());print data["instance"]["configuration"]["tmp_table_size"]'

Alright, it is 16M. Now, let's change that to 64M.

• Create a configuration group with some values ("tmp_table_size" in this example; note: you can create a configuration group using either the type/version UUID or their names):

$ echo $((64*1024**2))  # => 67108864 (64MB)
$ curl -vH "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" "$ENDPOINT/configurations" \
    -d '{"configuration": {"datastore": {"version": "5.6", "type": "mysql"}, \
         "name": "TestConfig", "description": "my-test-config", \
         "values": { "tmp_table_size": 67108864 }}}' | python -mjson.tool
$ # ~OR~ (by UUID):
$ curl -sH "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" "$ENDPOINT/configurations" \
    -d "{\"configuration\":{\"datastore\":{\"type\":\"$DB_TYPE\",\"version\":\"$DB_VERSION\"},\
         \"name\":\"TestConfig\",\"description\":\"my-test-config\",\
         \"values\":{\"tmp_table_size\":67108864}}}" | python -mjson.tool
$ CONFIGURATION_ID=ffffffff-ffff-ffff-ffff-ffffffffffff  # from above output

• Attach (add) a given configuration group to a given DBaaS instance:

$ curl -iXPUT -H "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" "$ENDPOINT/instances/${INSTANCE_ID}" \
    -d "{\"instance\":{\"configuration\":\"$CONFIGURATION_ID\"}}"

A "202 Accepted" response means the configuration group attachment to the DBaaS instance was successful. Or, you can verify with:

$ curl -sH "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" "$ENDPOINT/instances/${INSTANCE_ID}"|python -mjson.tool

Note that some configuration parameters require the DBaaS instance be restarted. If, for a given parameter, the "restart_required" key has a value of "true", you will need to restart the instance (if "false", no restart is required for your parameter change to take affect).

• Get a list of parameters for a given datastore type and version (and check if changing the parameter requires a restart after attaching it to a DBaaS instance):

$ curl -sH "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" \
    "$ENDPOINT/datastores/${DB_TYPE}/versions/${DB_VERSION}/parameters"|python -mjson.tool

• Restart a DBaaS instance:

$ curl -iXPOST -H "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" "$ENDPOINT/instances/${INSTANCE_ID}/action" -d '{ "restart": {} }'

A "202 Accepted" response means the restart request was accepted. You must wait for instance's status to return to "ACTIVE" before doing anything else with the instance:

$ curl -sH "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" "$ENDPOINT/instances/${INSTANCE_ID}"|python -mjson.tool|grep status

• Verify that the actual MySQL instance reports the updated parameter (or the MySQL "system variable" in this example):

$ INSTANCE_HOSTNAME=`curl -sH"X-Auth-Token:$TOKEN" -H"Content-Type:application/json" "$ENDPOINT/instances/${INSTANCE_ID}"|\
                     python -c 'import sys,json;data=json.loads(sys.stdin.read());print data["instance"]["hostname"]'`

$ mysql -u root -p -h $INSTANCE_HOSTNAME  # run from a Cloud Server in DFW (for this example)

mysql> SHOW VARIABLES LIKE 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 67108864 |
+----------------+----------+

Nice! The "tmp_table_size" parameter/system variable for this DBaaS instance has changed from its default value of "16777216" (16M) to our new value of "67108864" (64M).

Note that querying the API for your instance's configuration will still show the value of "16777216" (16M), as that it the default value. You must actually query MySQL directly (as above) to see the new value:

$ curl -sH "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" "$ENDPOINT/instances/${INSTANCE_ID}/configuration" | python -mjson.tool

Now, here is how to return to the default configuration (i.e., undo everything we have done in this section).

• Detach (remove) a configuration group from DBaaS instance:

$ curl -iXPUT -H "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" "$ENDPOINT/instances/${INSTANCE_ID}" -d '{ "instance": { "configuration" : "" }}'

A "202 Accepted" response means the removal was successful.

After you remove/detach a configuration group from a DBaaS instance, the instance's status will change from "ACTIVE" to "RESTART_REQUIRED". As such, you will need to restart your instance and wait for it to return to "ACTIVE" before doing anything else with the instance.

• Delete a configuration group (note: you must first detach the group from any instance it is attached to before you can delete the group):

$ curl -iXDELETE -H "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" "$ENDPOINT/configurations/${CONFIGURATION_ID}"

A "202 Accepted" response means the deletion was successful. Or, you can verify that the configuration has, indeed, been removed with:

$ curl -sH "X-Auth-Token: $TOKEN" -H "Content-Type: application/json" "$ENDPOINT/configurations"|python -mjson.tool

Done! You are back how things were before you started the process described in this section.

See also

• Rackspace API