Rackspace API/Cloud Databases

This article will show various techniques for working with Rackspace's Cloud Databases.

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:

"Changes you make as a root user may cause detrimental effects to the database instance and unpredictable behavior for API operations. When you enable the root user, you accept the possibility that we will not be able to support your database instance. While enabling root does not prevent us from a 'best effort' approach to helping you if something goes wrong with your instance, we cannot ensure that we will be able to assist you if you change core database settings. These changes can be (but are not limited to) turning off binlogs, removing users that we use to access your instance, and so forth."

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
*************************** 1. row ***************************
    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".

See also

• Rackspace API