We removed our free Sandbox April 25th.
You can read more on our blog.

MySQL

DotCloud features MySQL 5.1 and uses the InnoDB storage engine by default.

Basics

If your application requires a MySQL database, all you need to do is to add the following lines to your dotcloud.yml Build Files:

data:
  type: mysql

Note

You can give your database or cache service any name you like instead of “data”. If for some reason your application needs multiple databases, you can either use multiple independant services (by repeating the database section multiple times in dotcloud.yml, each with a different name instead of just “db”), or create multiple users and databases inside one single service. The first option (multiple services) will bring more resources, but keep in mind that when you go to production, this will generally incur higher costs as well.

Your data service will be globally available. This means that you can use it with applications running on other platforms.

If for some reason, you want to use DotCloud just to deploy a standalone data service, create a two-lines “dotcloud.yml” file in an empty directory, and from this directory, just run:

$ dotcloud create mynicedb
$ dotcloud push mynicedb
$ dotcloud info mynicedb.data

In less than one minute, your data service will be up and running, and the “dotcloud info” command will show its connection credentials.

Configure Your Application

There are two ways to configure your app to use your new data service:

  • manually, by hard-coding the host, port, user and password;
  • automatically, by retrieving those parameters from the Environment File.

Manual Configuration

Check your service parameters using “dotcloud info”:

$ dotcloud info ramen.data
cluster: wolverine
config:
    mysql_password: Ikx538noq5
created_at: 1310524922.08429
ports:
-   name: ssh
    url: ssh://mysql@59196556.dotcloud.com:7726
-   name: mysql
    url: mysql://root:Ikx538noq5@59196556.dotcloud.com:7728
state: running
type: mysql

If you plan to use this database service for only one thing in your app, you can opt to directly use the system database called “mysql”. If you plan to store multiple things (e.g. a WordPress blog and a Django-based CMS) and want to configure different access rights for them, or if you don’t feel comfortable with storing your data directly in the “mysql” system database, here is how to setup this.

First, get a shell on the database:

$ dotcloud run ramen.data -- mysql

You will be prompted by MySQL:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 105
Server version: 5.1.41-3ubuntu12.7 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.

mysql>

Let’s create a new database called “wordpress”:

mysql> CREATE DATABASE wordpress;
Query OK, 1 row affected (0.00 sec)

Now create a user “joe” with access rights on this database:

mysql> GRANT ALL ON wordpress.* TO 'joe'@'%' IDENTIFIED BY 'opensesame';
Query OK, 0 rows affected (0.00 sec)

Of course, you should provide a better password than ‘opensesame’!

Using environment.json

When your application is built by the DotCloud platform, a file named environment.json is created in the home directory of each of your services.

This file contains a JSON-formatted dictionary with most of the configuration informations of the services in your stack. You can use it to retrieve automatically the host, port, and password, of your databases, caches, and so on.

You will find snippets of code to retrieve the connection information of your databases in the Environment Guide.

Your ramen.data service will expose the following variables:

  • DOTCLOUD_DATA_MYSQL_HOST
  • DOTCLOUD_DATA_MYSQL_LOGIN
  • DOTCLOUD_DATA_MYSQL_PASSWORD
  • DOTCLOUD_DATA_MYSQL_PORT
  • DOTCLOUD_DATA_MYSQL_URL

Note that DATA will be replaced by the (upper-cased) name of your service.

Master/Slave

The MySQL service can be scaled to Master/Slave for high availability, as explained in the scaling guide:

$ dotcloud scale ramen data:instances=2

Replication and fail-overs will happen automatically. No changes are required to your code: the address and port returned by "dotcloud info" and recorded into environment.json will not change over time (it always point to the current master). However you have to make sure that your application aggressively tries to reconnect to the database because connections will be killed during a fail-over.

You can use "dotcloud info" to check the status of the replication:

$ dotcloud info ramen.data
cluster: wolverine
config:
    mysql_password: TysxP7mhxH
created_at: 1312288848.6290431
ports:
-   name: ssh
    url: ssh://mysql@97f064a1.dotcloud.com:11860
-   name: mysql
    url: mysql://root:TysxP7mhxH@97f064a1.dotcloud.com:11859
replication:
-   status: online
-   master:
        name: mysqltest.db.0
        status: UP
-   slaves:
    -   name: mysqltest.db.1
        replication_lag: 0s
        status: UP
state: running
type: mysql

Notice the replication_lag value, this is the delay between a write on the master, and the same write on the slave. It should usually be very low. It gives a rough indication of the amount of data that would be lost if the master was to fail unexpectedly. I.e., if the replication lag is 10s, it means that losing the master would cause the data written during the last 10 seconds to be lost.

While this replication lag might sound dangerous (since you can lose transactions), one must keep in mind that a fully synchronous replication system would incur a very high performance penalty. All writes would have to go through an extra round-trip transaction, with the master waiting for the slave to complete the write operation before reporting it as completed to the application code.

Under the hood, the replication format used is MIXED. This format is compatible with nondeterministic statements [1].