ClearDB MySQL Database

This add-on is operated by SuccessBricks, Inc.

The high speed, 100% uptime database for your MySQL powered applications.

ClearDB MySQL Database

Last Updated: 10 April 2014

addons database

Table of Contents

ClearDB is a powerful, fault tolerant database-as-a-service in the cloud for your MySQL powered applications.

Provisioning the add-on

The ClearDB add-on can be installed and up and running in your app in no time! Run the following command to add ClearDB to your application:

$ heroku addons:add cleardb:ignite
-----> Adding cleardb to sharp-mountain-4005... done, v18 (free)

Retrieve your database URL by issuing the following command:

$ heroku config | grep CLEARDB_DATABASE_URL
CLEARDB_DATABASE_URL => mysql://adffdadf2341:adf4234@us-cdbr-east.cleardb.com/heroku_db?reconnect=true

Copy the value of the CLEARDB_DATABASE_URL config variable.

If you’re using Ruby on Rails and the mysql2 gem, you will need to change the mysql:// scheme in the CLEARDB_DATABASE_URL to mysql2://

$ heroku config:set DATABASE_URL='mysql://adffdadf2341:adf4234@us-cdbr-east.cleardb.com/heroku_db?reconnect=true'
Adding config vars:
DATABASE_URL => mysql2://adffd...b?reconnect=true
Restarting app... done, v61.

That’s it!

The complete tutorial

Prerequisites

This tutorial assumes that you or someone on your team has adequate knowledge of MySQL’s SQL database software as well as how to configure and use it in a supported language or framework of your choice in the Heroku cloud. It also presumes that you are familiar with basic Heroku command line functions, such as provisioning add-ons and using basic environment variables from within your application’s programming language and Heroku to configure your applications to use MySQL.

Getting started

During your development process, you will most likely be using a local MySQL instance to power your application. ClearDB uses native MySQL to power your MySQL applications, so you don’t have to worry about any special data handling or transformation when working on your app. Using the ClearDB Heroku Add-On, ClearDB becomes part of your application stack once your application has been pushed into Heroku. The goal here is to ensure that moving into Heroku and ClearDB is a seamless transition from working in your local environment.

ClearDB base topology: dual masters

The ClearDB Heroku Add-On provisions your database on two “multi-master” database servers in two different Amazon EC2 regions to ensure absolute data availability. However, when you provision your ClearDB add-on, you will only receive a single database URL, which connects to our high availability router network.

The primary master nodes are located right next to Heroku, in Amazon’s EC2’s US-East-1 region. The secondary master nods are located in Amazon’s EC2’s US-West-1 region. For our European customers, the primary master nodes are located in Amazon EC2’s EU-West-1 region, with secondary masters located in Windows Azure’s West or North Europe subregions. So even if there are network problems, EBS failures, even natural disasters, your data will still be online and available.

Using the ClearDB database URL

Upon provisioning the ClearDB Heroku Add-On in your Heroku environment, you will receive a database URL labeled CLEARDB_DATABASE_URL. This URL is your link to the ClearDB Cluster for which your database has been provisioned.

Local setup

If you’re using Ruby, you’ll need to install the mysql2 gem in order to connect to ClearDB’s MySQL database service.

Preparing to use ClearDB once your application has been deployed to Heroku is as easy as simply setting up your application to use MySQL. To reference how to connect to MySQL from your framework, please consult your framework’s documentation.

Creating your ClearDB database

To create your ClearDB database, simply type the following Heroku command:

$ heroku addons:add cleardb:ignite
-----> Adding cleardb to sharp-mountain-4005... done, v18 (free)

This will automatically provision your new ClearDB database for you and will return the database URL to access it. You can also browse the Add-On catalog for ClearDB and simply click “Add” on the ClearDB Heroku Add-On to auto-provision your database for you.

Upgrading your ClearDB database

Upgrading your ClearDB database to a larger plan is as easy as running the following command:

$ heroku addons:upgrade cleardb:punch
Upgrading cleardb:punch to myapp... done.

Upgrades are performed in place; no data migration or app reconfiguration is necessary.

Migrating an existing database to ClearDB

If you have an existing MySQL database that you wish to migrate to ClearDB, you can do so using the --fork parameter along with a full publicly accessible URL where your database can be accessed. For example:

$ heroku addons:add cleardb:ignite --fork=mysql://user:password@myhostname.com/database
-----> Adding cleardb to sharp-mountain-4005... done, v18 (free)

Before migrating an active database, it is recommended that maintenance mode is enabled and that all running dynos are scaled down.

For apps that have an existing database attached, you can user the heroku config:get command to supply the correct value to --fork:

$ heroku addons:add cleardb:ignite --fork=`heroku config:get XEROUND_DATABASE_URL`
-----> Adding cleardb to sharp-mountain-4005... done, v18 (free)

Be sure to chose a plan size that is large enough to accomodate your data set.

You can view the progress of your data migration on the ClearDB dashboard:

$ heroku addons:open cleardb
-----> Opening cleardb:ignite for sharp-mountain-4005... done

When the migration is complete you can connect to your database using the standard mysql tools. Once you’ve confirmed the state of of your data, configure your application to use ClearDB instead of your existing database and deploy the changes, if required.

Configuring your Ruby application to use ClearDB

To configure your Rails application to use ClearDB, simply set your DATABASE_URL environment variable to use the ClearDB URL that you have been issued. Heroku should do the rest by auto-generating a new database.yml file for your application which will connect your application directly to your ClearDB database.

You can retrieve your new ClearDB database URL by issuing the following command:

$ heroku config | grep CLEARDB_DATABASE_URL
CLEARDB_DATABASE_URL => mysql://adffdadf2341:adf4234@us-cdbr-east.cleardb.com/heroku_db?reconnect=true

To set your DATABASE_URL to the value of CLEARDB_DATABASE_URL, simply issue the following heroku command. For example:

If you’re using the mysql2 gem, you will need to change the mysql:// scheme in the CLEARDB_DATABASE_URL to mysql2://

$ heroku config:set DATABASE_URL='mysql://adffdadf2341:adf4234@us-cdbr-east.cleardb.com/heroku_db?reconnect=true'
Adding config vars:
DATABASE_URL => mysql2://adffd...b?reconnect=true
Restarting app... done, v61.

Using ClearDB in a Play! Framework app

ClearDB uses the standard MySQL approaches to connecting via Java and the Play! framework. Below are a couple of examples on how to do so.

Using the DATABASE_URL in plain JDBC

To instantiate a JDBC connection in your code, you can use a method like this:

private static Connection getConnection() throws URISyntaxException, SQLException {
    URI dbUri = new URI(System.getenv("CLEARDB_DATABASE_URL"));

    String username = dbUri.getUserInfo().split(":")[0];
    String password = dbUri.getUserInfo().split(":")[1];
    String dbUrl = "jdbc:mysql://" + dbUri.getHost() + dbUri.getPath();

    return DriverManager.getConnection(dbUrl, username, password);
}

Using the DATABASE_URL in Spring with XML configuration

You can use XML for configuring Spring to use the CLEARDB_DATABASE_URL:

<bean class="java.net.URI" id="dbUrl">
    <constructor-arg value="#{systemEnvironment['CLEARDB_DATABASE_URL']}"/>
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="url" value="#{ 'jdbc:mysql://' + @dbUrl.getHost() + @dbUrl.getPath() }"/>
    <property name="username" value="#{ @dbUrl.getUserInfo().split(':')[0] }"/>
    <property name="password" value="#{ @dbUrl.getUserInfo().split(':')[1] }"/>
</bean>

Using the DATABASE_URL in Spring with Java configuration

You can use Java for configuration of the BasicDataSource to connect to ClearDB in Spring:

@Configuration
public class MainConfig {

    @Bean
    public BasicDataSource dataSource() throws URISyntaxException {
        URI dbUri = new URI(System.getenv("CLEARDB_DATABASE_URL"));

        String username = dbUri.getUserInfo().split(":")[0];
        String password = dbUri.getUserInfo().split(":")[1];
        String dbUrl = "jdbc:mysql://" + dbUri.getHost() + dbUri.getPath();

        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(dbUrl);
        basicDataSource.setUsername(username);
        basicDataSource.setPassword(password);

        return basicDataSource;
    }
}

Using ClearDB with PHP

Connecting to ClearDB from PHP is super easy, and merely requires the parsing of the CLEARDB_DATABASE_URL to get connected, like this:

<?php
    $url=parse_url(getenv("CLEARDB_DATABASE_URL"));

    $server = $url["host"];
    $username = $url["user"];
    $password = $url["pass"];
    $db = substr($url["path"],1);

    mysql_connect($server, $username, $password);


    mysql_select_db($db);
?>

Using ClearDB with Python/Django

Python/Django users will need to strip the “?reconnect=true” query string parameter from the DATABASE_URL config setting, as this is for Rails users.

When you deploy a Django application, the compile process appends the following code to your settings.py to use the DATABASE_URL environment variable:

import os
import sys
import urlparse

# Register database schemes in URLs.
urlparse.uses_netloc.append('mysql')

try:

    # Check to make sure DATABASES is set in settings.py file.
    # If not default to {}

    if 'DATABASES' not in locals():
        DATABASES = {}

    if 'DATABASE_URL' in os.environ:
        url = urlparse.urlparse(os.environ['DATABASE_URL'])

        # Ensure default database exists.
        DATABASES['default'] = DATABASES.get('default', {})

        # Update with environment configuration.
        DATABASES['default'].update({
            'NAME': url.path[1:],
            'USER': url.username,
            'PASSWORD': url.password,
            'HOST': url.hostname,
            'PORT': url.port,
        })


        if url.scheme == 'mysql':
            DATABASES['default']['ENGINE'] = 'django.db.backends.mysql'
except Exception:
    print 'Unexpected error:', sys.exc_info()

You don’t need to do anything special to configure this, the above information is purely for reference.

Using SSL with ClearDB and Rails

In order to provide a secure operating environment for your applications, we strongly recommend that you use SSL encryption using the certificates available from your ClearDB dashboard. This will ensure that the communications link between your Heroku applications and ClearDB is secure.

While we automatically generate SSL certificates for encryption purposes, the private key file must be modified in order to work with Heroku’s MySQL libraries.

To begin, download your certificates from your ClearDB dashboard, then run the following command on your private key file (the file with the name “key” in it):

$ openssl rsa -in cleardb_id-key.pem -out cleardb_id-key-no-password.pem

Delete the original cleardb_id-key.pem and rename cleardb_id-key-no-password.pem to cleardb_id-key.pem (this is not required, but it makes more sense with the next step). Pass the path to the SSL certificates that you downloaded from your ClearDB dashboard as query-string parameters in the database URL, like this:

In this example, the certificates are located in your application’s root directory. To specify a different directory, simply prepend the path to the certificate.

$heroku config:set DATABASE_URL='mysql2://abc1223:dfk243@us-cdbr-east.cleardb.com/my_heroku_db?sslca=cleardb-ca-cert.pem&sslcert=cleardb_id-cert.pem&sslkey=cleardb_id-key.pem&reconnect=true'
Adding config vars:
DATABASE_URL => mysql2://abc12...b?reconnect=true
Restarting app... done, v61.

At this point Heroku should do the rest with regards to rebuilding your database.yml file and connecting you to your new ClearDB database.

Using SSL with other Heroku supported languages and frameworks

While we automatically generate SSL certificates for encryption purposes, the private key file must be modified in order to work with Heroku’s MySQL libraries.

To begin, download your certificates from your ClearDB dashboard, then run the following command on your private key file (the file with the name “key” in it):

$ openssl rsa -in cleardb_id-key.pem -out cleardb_id-key-no-password.pem

PHP

Unfortunately, Heroku currently does not support the use of the MySQLi PHP extension, which is required for SSL support with ClearDB. This is due to the fact that ClearDB requires both the SSL CA certificate as well as the client certificate and key pair. We will update this article upon Heroku’s MySQLi support announcement.

Java

The process of setting up SSL on MySQL via the MySQL Java Connector is available via the MySQL Java Connector SSL Documentation.

Python/Django

You can connect via SSL to ClearDB’s MySQL service by first downloading your SSL certificates from your ClearDB dashboard and then by adding the following configuration to your app:

DATABASES['default'] = {
    'ENGINE': 'django.db.backends.mysql',
    'HOST': 'my-host-goes-here',
    'USER': 'my-user-goes-here',
    'NAME': 'my-db-name-goes-here',
    'PASSWORD': 'my-db-pass-goes-here',
    'OPTIONS': {'ssl': {'ca':'/path/to/ca-cert.pem', 'cert':'/path/to/cert.pem', 'key':'/path/to/key.pem'},},
}

Further reading: