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 (
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 <service-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 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!