This add-on is operated by Navisite, LLC

The high speed database for your MySQL powered applications.

Last updated October 29, 2024

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

Provisioning the shared MySQL 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:create 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.

$ 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!

Provisioning the dedicated MySQL add-on

The ClearDB dedicated service can typically be added to your Heroku application in less than 90 seconds. Run the following command to add a dedicated ClearDB node to your application:

$ heroku addons:create cleardb:standard-25 --name=MyClearDBMaster
-----> Creating cleardb on sharp-mountain-4005...  $54.27/month

Next, sign into the ClearDB portal by clicking on the ClearDB add-on in the Heroku portal for your app.

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

Once in the ClearDB portal, simply click on the Databases tab, then click ‘New Database’. Select a name, character set, sort collation, and finally a username and password for the database, then click ‘Create Database’. Use this information to build your new DATABASE_URL.

Start by retrieving your database host and environment information by issuing the following command:

$ heroku config | grep CLEARDB

Using the example below, simply replace the ‘username’, ‘password’, ‘hostname’, and ‘database_name’ fields with the information that you received in the output of the heroku config command, as well as the information that you supplied when you created the database via the ClearDB portal.

$ heroku config:set DATABASE_URL='mysql://username:password@hostname/database_name?reconnect=true'
Adding config vars:
DATABASE_URL => mysql://adffd...b?reconnect=true
Restarting app... done, v61.

That’s it! Enjoy using ClearDB dedicated instances.

The ClearDB shared MySQL 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 shared MySQL base topology: multiple masters

When using the Ignite, Punch, Drift, or Scream plans, the ClearDB Heroku Add-On provisions your database on two or more “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 nodes 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 the AWS EU-Central-1 regions. So even if there are network problems, EBS failures, or 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 labelled CLEARDB_DATABASE_URL. This URL is your link to the ClearDB Cluster for which your database has been provisioned.

Local setup

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:create 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:create cleardb:ignite --fork=mysql://user:password@myhostname.com/database
-----> Adding cleardb to sharp-mountain-4005... done, v18 (free)

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:create cleardb:ignite --fork=`heroku config:get XEROUND_DATABASE_URL`
-----> Adding cleardb to sharp-mountain-4005... done, v18 (free)

Be sure to choose a plan size that is large enough to accommodate 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 your data, configure your application to use ClearDB instead of your existing database and deploy the changes, if required.

The ClearDB dedicated MySQL 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.

Dedicated ClearDB Overview

When using any of the Standard or Premium single-tenant plans, the ClearDB Heroku Add-On provisions your database on dedicated AWS infrastructure located in the same region as your application. The first ClearDB add-on instance is a master node, which accepts both reads as well as writes. Additional ClearDB add-on instances can also be provisioned as part of the application, and they will be provisioned as read-only slave instances (provided that the environment_id is supplied which is provided as part of the first master instance metadata). All master and slave nodes may be upgraded and downgraded to different Standard and Premium plans independently of each other using standard Heroku add-on upgrade and downgrade commands.

Dedicated ClearDB Security

Dedicated ClearDB instances leverage encryption everywhere: data at rest is encrypted, backups are both compressed as well as encrypted, and network encryption is available using MySQL SSL support. In addition, data is immediately deleted upon the destruction of the add-on instance from your application, so that you can rest assured that not only is data secure when its in use, but also when it is removed. ClearDB also employs strict operational controls on data access to prevent unauthorized personnel from accessing your data without your explicit permission.

Dedicated ClearDB Support

Dedicated ClearDB customers get full access to our support and operations team - we’re here to serve you! All support activities should be processed through the ClearDB support service, available in the ClearDB portal upon accessing it from the ClearDB add-on:

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

ClearDB G5 Dedicated Infrastructure

ClearDB dedicated infrastructure leverages the latest ClearDB Generation 5 (G5) platform, which also provides access to over 30 different types of advanced telemetry, so that you can make critical decisions about what your workload is doing on ClearDB at all times. You can also easily view slow query logs, and connection lists, as well as make informed decisions around proper indexing so that your database workload is always performance positive.

ClearDB Dedicated Infrastructure Plan Information

Each Single-Tenant instance of ClearDB provides dedicated system resources based on the following list of plans. Multiple instances of Standard or Premium plans can be combined together to provide H/A as well as significantly increased read throughput and performance.

Standard Series Plan Details

Standard Series infrastructure is designed for light to general-purpose MySQL database needs, all with dedicated resources for CPU, memory, and storage. All storage uses general-purpose EBS SSD persistent storage in AWS. Click on the desired plan to view pricing and other information.

Standard 25: 1 vCPU, 512MB RAM, 25 GiB Storage

Standard 50: 1 vCPU, 1GB RAM, 50 GiB Storage

Standard 100: 1 vCPU, 2GB RAM, 100 GiB Storage

Standard 200: 2 vCPUs, 4GB RAM, 200 GiB Storage

Standard 400: 2 vCPUs, 8GB RAM, 400 GiB Storage

Standard 800: 4 vCPUs, 16GB RAM, 800 GiB Storage

Standard 1600: 8 vCPUs, 32GB RAM, 1.6 TiB Storage

Premium Series Plan Details

Premium Series infrastructure is designed for more intensive MySQL database workloads and leverages the latest AWS instance types which are designed for high throughput connectivity to EBS SSD persistent storage. Combined with advanced ENA high-speed networking architecture with support for up to 25 gigabit connectivity, ClearDB Premium Series infrastructure can support some of even the toughest workloads. Click on the desired plan to view pricing and other information.

Premium 400: 2 vCPUs, 8GB RAM, 400 GiB Storage

Premium 800: 4 vCPUs, 16GB RAM, 800 GiB Storage

Premium 1600: 8 vCPUs, 32GB RAM, 1.6 TiB Storage

Premium 3200: 16 vCPUs, 64GB RAM, 3.2 TiB Storage

Premium 6400: 48 vCPUs, 192GB RAM, 6.4 TiB Storage

Premium 12800: 96 vCPUs, 384GB RAM, 12.8 TiB Storage

Local setup

The ClearDB dedicated service can typically be added to your Heroku application in less than 90 seconds. Run the following command to add a dedicated ClearDB node to your application:

$ heroku addons:create cleardb:standard-50 --name=MyClearDBMaster
-----> Adding cleardb to sharp-mountain-4005... done, v18 (ignite)

ClearDB provisions MySQL 5.6 by default on our new G5 infrastructure, but we also support MySQL 5.7. You can also provision MySQL 5.5, but this version is long past support and we do not recommend it. We cannot provide you support if you choose to deploy MySQL 5.5. If you wish to use a specific version of the aforementioned MySQL versions, simply specify the version argument to the heroku addons:create command, like this:

$ heroku addons:create cleardb:premium-400 --name=MyClearDBMaster --version=5.7
-----> Adding cleardb to sharp-mountain-4005... done, v18 (premium-400)

Next, sign into the ClearDB portal by clicking on the ClearDB add-on in the Heroku portal for your app.

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

Once in the ClearDB portal, simply click on the Databases tab, then click ‘New Database’. Select a name, character set, sort collation, and finally a username and password for the database, then click ‘Create Database’. Use this information to build your new DATABASE_URL.

Start by retrieving your database host and environment information by issuing the following command:

$ heroku config | grep CLEARDB

Using the example below, simply replace the ‘username’, ‘password’, ‘hostname’, and ‘database_name’ fields with the information that you received in the output of the heroku config command, as well as the information that you supplied when you created the database via the ClearDB portal.

$ heroku config:set DATABASE_URL='mysql://username:password@hostname/database_name?reconnect=true'
Adding config vars:
DATABASE_URL => mysql://adffd...b?reconnect=true
Restarting app... done, v61.

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.

Adding read-only ClearDB MySQL slaves to an existing environment

If you want to horizontally scale your ClearDB dedicated database instance to increase throughput and provide H/A support, simply create additional ClearDB dedicated slave instances in your application.

You can view your primary master’s environment ID by simply running the following Heroku config command:

$ heroku config | grep CLEARDB

Once you have the ClearDB environment_id information, simply issue a new addons:create command similar to this, making sure to use the correct environment ID.

$ heroku addons:create cleardb:standard-25 --environment_id='617dcb67-78ec-11e8-82cc-22000a2309cf' --name=MyClearDBSlave1
-----> Adding cleardb to sharp-mountain-4005... done, v18 (free)

Upgrading your ClearDB dedicated instance

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

$ heroku addons:upgrade cleardb:premium-400 --name=MyClearDBMaster
Upgrading cleardb:premium-400 to myapp... done.

Upgrades are performed in place; no data migration or app reconfiguration is necessary. Please note that upgrades can take a few minutes while computing and storage elements change size.

Migrating from an existing ClearDB shared MySQL database

ClearDB supports native shared to dedicated migration as part of the upgrade path from shared database plans to dedicated datastore plans. This process is fully managed by ClearDB. Please note that you will have to update the DATABASE_URL config variable in your application once the migration has been completed in order to continue using your database. All other elements of your DATABASE_URL config variable will remain the same (such as credentials, database name, etc).

Removing a ClearDB dedicated MySQL instance

To remove a specific instance from your ClearDB environment, issue the typical Heroku add-ons destroy command, but be sure to specify the actual ClearDB instance name to ensure that the correct system is destroyed.

$ heroku addons:destroy cleardb --name=MyClearDBMaster
Removing cleardb:standard-100 from myapp... done.

Rotating Credentials

To rotate the security credentials within ClearDB for your MySQL database, simply sign into the ClearDB portal from Heroku:

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

From there, click the System Information tab, locate your credentials at the bottom-left of the page, and click the reset link to the right of the password field.

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:

$ 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 of 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 merely requires the parsing of the CLEARDB_DATABASE_URL environment variable and passing the extracted connection information to your MySQL library of choice, e.g. MySQLi:

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

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

$conn = new mysqli($server, $username, $password, $db);
?>

You can then issue queries against the database using $conn->query().

Using ClearDB with Python/Django

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

##
## This example works with Python2
##
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.

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:

$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 regard to rebuilding your database.yml file and connecting you to your new ClearDB database.

Using SSL with other Heroku supported languages and frameworks

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

To begin, download your certificates from your ClearDB dashboard. You will need to extract the contents of the files, either by reading them in your code or opening them in a text editor and pasting them into a string variable definition.

Please see the PHP documentation for using certificate files in the mysqli module. In that documentation $key should be set to the contents of the Client Private Key file, $cert to Client Certificate and $ca to ClearDB CA Certificate.

Note that once the mysqli_ssl_set() routine has been called, to connect via SSL you must use mysqli_real_connect().

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:

• The ClearDB Add-On Tutorial