Managing Databases in the Cloud

Database on OpenShift PaaS picture

More and more developers are enjoying the convenience of hosting their applications on OpenShift Online--Red Hat's PaaS Platform as a Service.

OpenShift Online applications support MySQL 5.1, PostgreSQL 8.4, MongoDB 2.2 and SQLite.

Although OpenShift is simple to use, there are lots of other awesome functions and features OpenShift provides that you may not be aware of.

That's what I'm going to talk about here. We will find the answers to questions like:

  • I'm a new developer, can I manage my database on OpenShift without knowing SQL?
  • I just wanna remove one single record from my database, can I manage it without complicated commands or an pre-installed client tool?
  • Is it easy to migrate my existing database to OpenShift?
  • Can I backup my database on OpenShift like for a local one?
  • Is there any way to use my database on cloud locally maybe for development or debugging purpose?
  • I have some favorite GUI database client tools, can I use them to manage the database on OpenShift?

Please feel free to ask any other questions you might have. We are here for you :). You can reach us in comments here in the post, in the forums, or on IRC on Freenode in #openshift.

Before we start

We need a few things prepared for the journey:

Get a free OpenShift account

You will be able to create 3 gears(will explain gears later) on OpenShift for free after signed up.

Install the OpenShift Client Tools

OpenShift supplies several different types of client tools for developers.

The OpenShift Web Console is very good for most of the operations. But I still want to recommend the RHC command line tool.

Why?
Because CLI is way more cooler and because it is the most complete in terms of all the functionality provided.

Let's start from 0

Set up your OpenShift account

At this point we're assuming you have an account on OpenShift.

If you haven't created any app with your account before, then you may need to choose a namespace and upload your public ssh key first.

It's easier with CLI, you can finished all these setup works simply by one RHC command:

rhc setup

Told ya it was easy!

Create an application

OK, now for the real work!

First, let's get familiar with the concepts of applications, cartridges and gears:
Cartridges are the containers that house the framework or components that can be used to create an application. One or more cartridges run on each gear or the same cartridge can run on many gears for clustering or scaling. We currently have cartridges that are enabled for PHP, JBoss, Python, Ruby, Perl & Node.JS. We also offer embedded cartridges in support of MySQL, MongoDB, phpmyadmin.

Applications are made up of at least one framework that is contained in a cartridge and runs on one or more gears. Additional cartridges can be added to the application on the same or different gears.

You can find the full list of them via OpenShift Web Console(https://openshift.redhat.com/app/console/application_types) or RHC command:

$ rhc cartridge list
jbossas-7           JBoss Application Server 7.1              web
jbosseap-6.0        JBoss Enterprise Application Platform 6.0 web
jenkins-1.4         Jenkins Server 1.4                        web
nodejs-0.6          Node.js 0.6                               web
perl-5.10           Perl 5.10                                 web
php-5.3             PHP 5.3                                   web
python-2.6          Python 2.6                                web
python-2.7          Python 2.7 Community Cartridge            web
python-3.3          Python 3.3 Community Cartridge            web
ruby-1.8            Ruby 1.8                                  web
ruby-1.9            Ruby 1.9                                  web
jbossews-1.0        Tomcat 6 (JBoss EWS 1.0)                  web
jbossews-2.0        Tomcat 7 (JBoss EWS 2.0)                  web
zend-5.6            Zend Server 5.6                           web
diy-0.1             Do-It-Yourself                            web
10gen-mms-agent-0.1 10gen Mongo Monitoring Service Agent 0.1  addon
cron-1.4            Cron 1.4                                  addon
jenkins-client-1.4  Jenkins Client 1.4                        addon
mongodb-2.2         MongoDB NoSQL Database 2.2                addon
mysql-5.1           MySQL Database 5.1                        addon
metrics-0.1         OpenShift Metrics 0.1                     addon
haproxy-1.4         OpenShift Web Balancer                    addon
phpmyadmin-3.4      phpMyAdmin 3.4                            addon
postgresql-8.4      PostgreSQL Database 8.4                   addon
rockmongo-1.1       RockMongo 1.1                             addon
switchyard-0.6      SwitchYard 0.6                            addon
 
Note: Web cartridges can only be added to new applications.

You'll find that OpenShift supports many languages (Java, Python, PHP, Perl, Ruby, Node.js and boundless DIY). A full blown listing of what you can do with OpenShift is on our technologies page.

Now, let's create a PHP application named myapp. Feel free to pick your favorite programming language and app name.

$ rhc app create myapp php-5.3
 
Application Options
-------------------
  Namespace:  linqing
  Cartridges: php-5.3
  Gear Size:  default
  Scaling:    no
 
Creating application 'myapp' ... done
 
Waiting for your DNS name to be available ... done
 
Downloading the application Git repository ...
Cloning into 'myapp'...
Warning: Permanently added 'myapp-linqing.rhcloud.com,23.20.38.154' (RSA) to the list of known hosts.
 
Your application code is now in 'myapp'
 
myapp @ http://myapp-linqing.rhcloud.com/ (uuid: 511d5125e0b8cdf98d00xxxx)
--------------------------------------------------------------------------
  Created: 9:03 PM
  Gears:   1 (defaults to small)
  Git URL: ssh://511d5125e0b8cdf98d00xxxx@myapp-linqing.rhcloud.com/~/git/myapp.git/
  SSH:     511d5125e0b8cdf98d00xxxx@myapp-linqing.rhcloud.com
 
  php-5.3 (PHP 5.3)
  -----------------
    Gears: 1 small
 
RESULT:
Application myapp was created.

Good! Our application is ready now. Let's move to the database part.

Embed databases into your app

To embed a database cartridge (take mysql as an example), run:

$ rhc cartridge add mysql-5.1 -a myapp
 
Adding 'mysql-5.1' to application 'myapp'
Success
mysql-5.1
=========
  Properties
  ==========
    Username       = admin
    Password       = smWh27V9eYaq
    Database Name  = myapp
    Connection URL = mysql://127.3.110.129:3306/

Now you got mysql database embedded into your app. And it's running already.

Wait, you can even embed multiple databases into the same app!

$ rhc cartridge add mongodb-2.2 -a myapp
 
Adding 'mongodb-2.2' to application 'myapp'
Success
mongodb-2.2
===========
  Properties
  ==========
    Username       = admin
    Password       = wQM4SrQxrNxe
    Database Name  = myapp
    Connection URL = mongodb://127.3.110.129:27017/
 
 
$ rhc cartridge add postgresql -a myapp
 
Adding 'postgresql-8.4' to application 'myapp'
Success
postgresql-8.4
==============
  Properties
  ==========
    Username       = admin
    Password       = HECLd-AbhlWb
    Database Name  = myapp
    Connection URL = postgresql://127.3.110.129:5432/

That's so cool!!! One command gets you a fully configured and ready to use database, no tricks!

Now you have the database connection parameters for your app. Use them in your codes and get your app running!

Database connection parameters

I think this is the most significant part for developers:

Your database connection parameters are stored on your Application's gear as environment variables. You can find out what they are by entering the following command after connecting to your gear with ssh:

env | grep OPENSHIFT

You will notice the following entries for the databases we embedded:

#for MySQL
OPENSHIFT_MYSQL_DB_PORT=3306
OPENSHIFT_MYSQL_DB_HOST=127.3.110.129
OPENSHIFT_MYSQL_DB_PASSWORD=smWh27V9eYaq
OPENSHIFT_MYSQL_DB_USERNAME=admin
OPENSHIFT_MYSQL_DB_URL=mysql://admin:smWh27V9eYaq@127.3.110.129:3306/
 
# for PostgreSQL
OPENSHIFT_POSTGRESQL_DB_PASSWORD=HECLd-AbhlWb
OPENSHIFT_POSTGRESQL_DB_HOST=127.3.110.129
OPENSHIFT_POSTGRESQL_DB_USERNAME=admin
OPENSHIFT_POSTGRESQL_DB_URL=postgresql://admin:HECLd-AbhlWb@127.3.110.129:5432/
OPENSHIFT_POSTGRESQL_DB_PORT=5432
 
#for MongoDB
OPENSHIFT_MONGODB_DB_USERNAME=admin
OPENSHIFT_MONGODB_DB_PASSWORD=wQM4SrQxrNxe
OPENSHIFT_MONGODB_DB_HOST=127.3.110.129
OPENSHIFT_MONGODB_DB_URL=mongodb://admin:wQM4SrQxrNxe@127.3.110.129:27017/
OPENSHIFT_MONGODB_DB_PORT=27017

Environment variables are the best way to configure your application's connection to a database. This is because we may migrate or update your Application/DB from time to time, resulting in potential IP address changes.

Manage your databases

Embedded databases are not bound to the external IP address of your Application's gear. Hence, only your application on that gear can access the database.

If you want to manage your databases manually OpenShift also provides tools to do that.

Embeded Open Source Tools

OpenShift offers some great open source tools for developers to manage their databases on the cloud easily.

phpmyadmin

phpMyAdmin is a free and open source tool written in PHP intended to handle the administration of MySQL using a Web browser. Features provided include:

  • Web interface
  • MySQL database management
  • Import data from CSV and SQL
  • Export data to various formats: CSV, SQL, XML, PDF (via the TCPDF library), ISO/IEC 26300 - OpenDocument Text and Spreadsheet, Word, Excel, LaTeX and others
  • Administering multiple servers
  • Creating PDF graphics of the database layout
  • Creating complex queries using Query-by-example (QBE)
  • Searching globally in a database or a subset of it
  • Transforming stored data into any format using a set of predefined functions, like displaying BLOB-data as image or download-link
  • Active query monitor (Processes)

Embed the phpMyAdmin cartridge to your app:

$ rhc cartridge add phpmyadmin-3.4 -a myapp
 
Adding 'phpmyadmin-3.4' to application 'myapp'
Success
phpmyadmin-3.4
==============
  Properties
  ==========
    Username       = admin
    Password       = smWh27V9eYaq
    Connection URL = https://myapp-linqing.rhcloud.com/phpmyadmin/
RockMongo

RockMongo is a MongoDB management tool, written in PHP 5. It's main features include:

  • easy to install, and open source
  • multiple hosts, and multiple administrators for one host
  • password protection
  • query dbs
  • advanced collection query tool
  • read, insert, update, duplicate and remove single row
  • query, create and drop indexes
  • clear collection
  • remove and change (only work in higher php_mongo version) criteria matched rows
  • view collection statistics

Embed RockMongo to your app by running:

$ rhc cartridge add rockmongo-1.1 -a myapp
 
Adding 'rockmongo-1.1' to application 'myapp'
Success
rockmongo-1.1
=============
  Properties
  ==========
    Username       = admin
    Password       = wQM4SrQxrNxe
    Connection URL = https://myapp-linqing.rhcloud.com/rockmongo/

Please note that both phpMyAdmin and RockMongo are publicly accessible from Internet.
It's quite convenient, but please keep your database credentials safe.

You can embed database or any other cartridges during the creation of your app. For example:

$ rhc app create myapp php-5.3 mysql-5.1 phpmyadmin-3.4

Long story short

Well, OpenShift has a lot more advantages on manipulating databases. But I think I've made my point, and this blog is long enough :)

Thanks for reading! Enjoy OpenShift and see you soon~

What's Next?

What if I want to use the same Database from multiple gears? Is that possible?

Yes, but more like workarounds since the DB port is not externally accessible. There are 2 ways to do so as per: https://www.openshift.com/kb/kb-e1051-how-do-i-connect-multiple-applications-to-one-database

Great article, thanks. I think MariaDB is available now too.

Edit: Doesn't seem visible when doing rhc cartridge list

Perhaps I thought this article:

https://www.openshift.com/blogs/why-mariadb-matters-the-openshift-interview-with-monty

meant MariaDB was now on OpenShift.