How To Manage Your AppFog Database from your Desktop on the Other Side of the World

Databases are a wonderful and indispensable thing, but they’re often not much use if you don’t have direct access to them. No matter how powerful any web app might be, it would be pretty boneheaded to pump data into a database that you can’t query and manage in granular detail. That’s why we chose to enable database tunneling on AppFog from the very start.

Our own Jeremy Voorhis wrote about this a while back, but I think it’s more than worthwhile to revisit database tunneling because it’s such a powerful tool and because some AppFog users may not be aware of how powerful database tunneling on AppFog really is.

The basic story: we built our tunneling tool on top of Cloud Foundry’s tunneling support, which came in the shape of the Caldecott Ruby gem. This gem was (quite cleverly) named after the Caldecott Tunnel in the San Francisco Bay Area and was designed to enable seamless data migration across Cloud Foundry (and Cloud Foundry-based) instances. We would have preferred to call the gem Shanghai, but I guess it’s kind of too late.

First step: build yourself a database

In order to use Caldecott on AppFog, you simply need to (a) start an AppFog account and sign in, (b) install Caldecott like you would any other Ruby gem (the tried-and-true sudo gem install caldecott), and then (c) start a database service on AppFog that you can then tunnel into to your heart’s content.

Getting a database service up and running is extremely simple both in the AppFog console and on the command line. If you’re in the console, you simply need to go to the app to which you want to link a database, click on the “Services” tab on the left, and then select the database you want to provision, be it MySQL, MongoDB, or PostgresQL. All you have to do at that point is provide a name for your new service, and you’re all set.


 
If you’re on the command line, you only need a few simple commands. In one line, you need to run the af create-service command, then specify a name, a database type, and a targeted infrastructure (more on recent changes to the AppFog CLI tool here). If you wanted to create, say, a MySQL database called classic-rdbms-ftw on HP’s OpenStack cloud, you would run this command:

af create-service mysql classic-rdbms-ftw --infra hp

To double-check that my service had been properly created, I can simply run the af services command, which will immediately return a list of my currently available services, including the name, the type (mysql, postgresql, or mongodb) and the infrastructure on which the service is deployed.

Second step: put on your spelunking gear, because it’s tunnel time

So now I have a database in place and I want to access it directly because I need to add, say, a users table for an application that I’m building. All I have to do now is run the af tunnel <app-name> command, which in my case looks like this:

af tunnel class-rdbms-ftw

It will then prompt me which client I would like to start, and I can choose either none, mysql, or mysqldump. none doesn’t sound like very much fun and I’m not ready to dump my freshly minted database just yet, so I’ll choose mysql. Once I make that selection, the MySQL prompt springs up like magic and I’m ready to play around with it as if it were on my local machine.

I can create and modify tables, run queries, input data, whatever. It’s really pretty exhilarating. It acts like your local environment but you’re working with a database that could be in Asia, Europe, Texas, Virginia, etc.

One thing to be aware of is that each Caldecott tunnel that you create will require a separate Ruby standalone app requiring 64 MB of RAM. Fortunately, this is only 3.2% of the RAM allotted on the free tier (which provides you 2 GB of RAM for free) and a much smaller percentage for paid plans, so tunneling is surprisingly cheap from a RAM usage perspective.

The REALLY good stuff: using AppFog database tunneling with a desktop client

Not everyone prefers managing databases in the command line, and there’s a good reason why: there are all sorts of powerful desktop clients available that enable you to manage databases with a classic GUI and all the flexibility that comes along with it.

Well, the even-better-than-before news is that database tunneling on AppFog is fully compatible with desktop clients for the databases that we support (MySQL, MongoDB, and PostgresQL). All you have to do is type in the normal af tunnel command and use the connection information provided. Let’s walk through an example using the MySQL database I created above (classic-rdbms-ftw) and manage it using the fabulous MySQL Workbench. Feel free and experiment with the client of your choosing.

We’ll have to begin in the command line, but we’ll be able to switch over to Workbench very soon. Here’s the basic tunnel command from before:

af tunnel classic-rdbms-ftw

But this time, when we’re prompted which client we would like to start, we’ll pick none. This will leave the connection open to whatever client we end up choosing. Notice that up above you’ll see “Service connection info.” This will give you all the information you need to input into Workbench. Don’t close the connection, though! If you hit Ctrl+C in the terminal you will not be able to use Workbench. That connection needs to remain open in the background for the duration of your session.

So now let’s go and open up Workbench. Over on the left-hand side, you’ll see Open Connection to Start Querying. Below that, click on New Connection to get started.


 
This will bring up a new menu where we’ll input the necessary information.

For the Connection Name, you can use whatever you wish.

For Hostname, you need to use your local host (most likely 127.0.0.1), and for the port number you should use what was given to you in the command line (where it says Starting tunnel to <db-name> on port <port-number>). Port 10000 is the default.

The Username will be a big, ugly string of letters and numbers that you’re probably best off cutting and pasting. The Password will be big and ugly as well (for your security, of course!).

The Default Schema will be whatever you want to call it, just like the Connection Name.

Below is what the input looks like for my classic-rdbms-ftw database. I’ve called my connection “Tunnel to classic-rdbms-ftw” and I’ve simply chosen “test” as my default schema:


 
Click on OK, and it will take you back to the main screen, where you can click on your newly formed connection under the Open Connection to Start Querying header. Double-click on the connection you just made, and there you are: using MySQL Workbench to directly manage a database potentially thousands of miles away.

The rest is up to you. Create and destroy tables, play with the data that your app has already accumulated, start writing your own custom SQL files, whatever. Go nuts. Put the “fun” back in “cross-geographical database functionality.”

Oh, and happy tunneling!

Share this post
Facebook Twitter Google
Try AppFog: The new PaaS Hackers love
  • http://twitter.com/garik_piton Igor Petrov

    It’s awesome!

  • DataS2

    I would love to access my appfog db with mysql workbench and I am having trouble implementing this tunneling service. When I enter af tunnel I get an error that says I must first install Caldecott. AF docs say that this will be installed automatically to the cloud app and I am not sure how to do this manually. Do you have any suggestions for this?

    Thanks!

  • richard

    I can’t figure out why all your images links show up as broken? it happens on most pages.

  • capu

    You should install Caldecott on local by gem install caldecott.

    I have same problem with you, and after I install caldecott, It work fine!

  • Gabriel

    Having the same issue. Will someone please fix this quickly?

  • Sourge

    In the second step you wrote but you need the imo. Otherwise a great tutorial, helped me a lot. Thank you!

Powered by Olark