RUN@cloud » Database Guide

Database Guide

Last modified by Cyrille Le Clerc on 2014/02/17 11:27

CloudBees hosted MySQL as a Service

CloudBees provides a fully managed MySQL Database As A Service that you can use for your applications.

Creating a database

To create a MySQL database for your application to use, just login to the console and click the Create DB button (or use the CLI). You can then choose a name for your database (it must be unique), and assign the username/password that applications must use to connect to the database. You can also use the command line tool (or API) to do this (see the bees db command). 

Binding a CloudBees DB to an App

When a CloudBees database is created, you can choose to bind the database to an application (bees app:bind). This makes the database available to the application in a way that is "native" to the container (eg a datasource, or system properties/environment variables) - you don't have to embed credentials into your application (or course you can use credentials in your application if you like - in this case we recommend you use bees config:set to set configuration items). The bees CLI is your best friend for tools like this. 

You can read more about resource binding here. This is the preferred approach.

Databases

Since CloudBees MySQL Databases are platform resources, they can be bound to your applications using the bees app:bind command. This makes the database available to the application in a way that is "native" to the container (system properties/environment variables and, if available in your container, a JNDI datasource).

For Java Web containers, binding a database to an application is the equivalent of adding the jdbc datasource definition into the cloudbees-web.xml, so that the database will be available as a native DataSource object.

Example: Bind the database "mydb" to the "test" Tomcat 7 application of the "acme" account.

bees app:bind -a acme/test -db acme/mydb -as mydb

The following Java System properties will be injected: DATABASE_URL_MYDB, DATABASE_USERNAME_MYDB and DATABASE_PASSWORD_MYDB

And the JNDI datasource jdbc/mydb will be injected with sensible parameters for your environment. You can override these default parameters adding name=value pairs to your binding command (see available parameters on the Tomcat7 ClickStack documentation).

Database binding sample limiting maxActive to 5

bees app:bind -a APP_ID -db DB_ID -as DATASOURCE_NAME maxActive=5

Please note that the way that the database is injected for the application and the parameters it supports is dependent on the ClickStack you are using. Some ClickStacks will use the database binding to setup native DataSources, while other may do nothing more than make the database settings available to you as environment or system property variables.

You can also take a look at the clickstarts - run one of those that has a database and it will use binding (you can take it from there). 

Note: the behavior of binding databases to applications can vary between application stacks.  You should check the documentation of the stack you are using to determine how/if database binds will work.  At this time, database bindings are well supported for the tomcat, jboss and jboss71 stacks.

Checking your Database usage

If you need to determine how much data is being consumed by your database, the following SQL will show you your database usage: (replace YOUR_DB_NAME with your database name)

SELECT table_schema "Database Name",
sum( data_length + index_length ) / 1024 / 1024 "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = 'YOUR_DB_NAME'
GROUP BY table_schema ;

Using a CloudBees database from an application

After creating your database and selecting it in the console, review the Cribsheet to see instructions for using the database from an application.

The basic steps are:

  • Declare a datasource in your application's web.xml file (or similar)
  • Bind the datasource to your database via the CLI or via the cloudbees-web.xml
  • Write the code for using the database

The cribsheet provides code snippets that make this process very straightforward.

Connecting to the database with a MySQL client

Management of your database is typically done using a MySQL client like the 'mysql' command line program distributed with MySQL, or the MySQL Query Browser. To connect to your database with one of these tools, just enter the remote connection settings found on the Overview section of your database in the console.

You can also retrieve your database setting using the CloudBees SDK:

$ bees db:list -a cyrille-leclerc
Databases:
cyrille-leclerc/gf4-demo
cyrille-leclerc/tcat8-1221
$ bees db:info -p cyrille-leclerc/tcat8-1221
Database name: tcat8-1221
Account:       cyrille-leclerc
Created:       Mon Dec 30 12:21:53 CET 2013
Status:        active
Master:        ec2-50-19-213-178.compute-1.amazonaws.com:3306
Port:          3306
Username:      tcat8-1221
Password:      ***

Note: the server info used to connect to your database can change if your database is redeployed for any reason in the RUN@Cloud environment, so you will need to update your database connection if this happens. Your application JDBC URL does not use an explicit server name, so your applications will not break when databases are redeployed in the DB service.

Exporting/Loading using mysql and mysqldump commands

The most popular way to load initial data into a database is to use the mysql and mysqldump command line tools.  These tools are installed as part of mysql when you install it locally.

Exporting data to a dump.sql file

$ mysqldump -u [db_user] -p[db_password] -h [db_hostname] > dump.sql

Importing data from a dump.sql file

$ mysql -h [db_host] -u [db_user] -p[db_password] db2 < dump.sql

Notes:

  • There is no space between -p and [db_password] in these commands
  • You can find the db_hostname parameter on your database configuration page (or via *bees db:info* SDK command)

Database Connection Pools

Using a connection pool is recommended for improving the performance of your application, and to ensure that your application handles excessive concurrency without overloading your Shared Database configurations (which have a limited number of connections available to the application). CloudBees uses the following datasource implementation according to the ClickStack:

Avoiding Database Idle Timeouts

MySQL includes a timeout that will close connections that have been idle for long periods of time. For improved Database performance, If you use a connection that has been idle for too long, your application will likely experience the following error: "The last packet successfully received from the server was XXX seconds ago"

Most connection pools include settings to purge idle connections and/or validate connections. Datasources injected by the CloudBees Database binding are configured to purge old connexions. If you use your own pool, don't forget to setup tuch timeout or test. 

CloudBees MySQL Database features

Enterprise Multi-Tenant MySQL Database

  • Connections: up to 20
  • Database users: 1
  • Emergency backup interval: daily
  • Backup restoration:  dedicated servers only
  • Master/Slave configuration: optional with dedicated servers

Changing Database Password

The database password can be changed using the CloudBees SDK with the "bees db:set -p new_password" command (see http://wiki.cloudbees.com/bin/view/RUN/BeesSDK)

Using External Databases

Verify that you subscribed to the resource service (it's a free service)

bees service:info resource

If it's not the case, subscribe to the "resource" service:

bees service:subscribe resource

Register your clear db database. In this sample, we name it "mydb"

bees resource:create  -t database mydb DATABASE_URL=mysql://db.example.com:3306/mydb   DATABASE_USERNAME=someusername  DATABASE_PASSWORD=somepassword

Note: the DATABASE_URL must begin with "mysql", "postgresql", ...

Create the application

If the database don't already exist, create it

bees app:create -a myapp -t tomcat7

The ClickStack (-t) can be tomcat7, jboss72, jetty9, wildfly8, glassfish4, ...

Bind the database to your application

 

bees app:bind -a myapp -r resource:mydb -as mydb connectionProperties=useSSL=true

Running with Amazon RDS

Some people prefer to use Amazon RDS - and this is fine. In this case you will manually manage your passwords and bindings.

There is a more detailed article here which talks about integration with Amazon services including RDS.

Short version: you need to have your RDS instance open to either the security groups (if you have an older AWS account) or open in general so that traffic from the cloudbees app servers can access it (if you have dedicated servers on CloudBees - you can open up just to your dedicated IP addresses)

The security groups that cloudbees app servers run in: 

Security Groups: appserver, genapp

AWS Account ID: 063356183961

Usage Examples

Spring

To use a database from Spring, you should first start by registering the database as a datasource in your application container.  The process for doing this will vary by for each stack, so you need to check your the documentation for the stack you are using.

For Tomcat6, you can use the Bees SDK to bind the database to your application as a datasource:

$ bees app:bind -a APPID -db DATABASE_ID -as mydb

You can then setup the datasource in your Spring context configuration like this:

<jee:jndi-lookup id="dbDataSource"
   jndi-name="jdbc/mydb"
   expected-type="javax.sql.DataSource" />

or like this:

<bean id="dbDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
   <property name="jndiName" value="java:comp/env/jdbc/mydb"/>
</bean>

See Also

ClearDB

Amazon RDS

Tags:
Created by Spike Washburn on 2011/07/20 04:44