This post explains how to connect to CenturyLink Cloud's Relational Database Service (RDB) offering through AppFog using SSL. Readers who have some knowledge of the Cloud Foundry platform (that AppFog is built on) may find it a little easier to follow along.
For my example, I'll create a simple Ruby/Sinatra app that binds to an existing RDB MySQL instance over SSL and validates that the connection is operational. Before I build the Ruby/Sinatra app I will create a MySQL instance through the CloudFoundry command line.
Before you start, there are a few things you will need to set up on your machine:
The first step is to log into AppFog through the AppFog Control Panel UI using the credentials created for you by the Cloud Foundry CLI. Once you're logged in, use the marketplace command to ensure that you have access to the RDB MySQL service:
Brians-MacBook-Pro:~ bbutton$ cf marketplace Getting services from marketplace in org T3OS / space Dev as admin... OK service plans description ctl_mysql free, micro, small, medium, large CenturyLink's MySQL RDB. For development use only; not subject to SLAs. orchestrate free Orchestrate DBaaS TIP: Use 'cf marketplace -s SERVICE' to view descriptions of individual plans of a given service.
If you follow the tip above, you can see the plans available for ctl_mysql. These plans define the quotas and costs for using a service:
Brians-MacBook-Pro:~ bbutton$ cf marketplace -s ctl_mysql Getting service plan information for service ctl_mysql as admin... OK service plan description free or paid free UnManaged MySQL-compatible Database as a Service - 1vCPU/1GB, up to 100MB storage, 100 concurrent connections, daily backups held locally for 7 days. SSL Encryption option. This service is a 0.1 release and not subject to any SLAs. Please see CenturyLink Cloud Supplemental Terms for terms of service. free micro UnManaged MySQL-compatible Database as a Service - 1vCPU/1GB, up to 100MB storage, 100 concurrent connections, daily backups held for 7 days. SSL Encryption option. This service is a 0.1 release and not subject to any SLAs. Please see CenturyLink Cloud Supplemental Terms for terms of service. free small UnManaged MySQL-compatible Database as a Service - 1vCPU/2GB, up to 1GB storage, 1200 concurrent connections, daily backups held for 7 days. SSL Encryption option. This service is a 0.1 release and not subject to any SLAs. Please see CenturyLink Cloud Supplemental Terms for terms of service. free medium UnManaged MySQL-compatible Database as a Service - 2vCPU/6GB, up to 64GB storage, 3600 concurrent connections, daily backups held for 7 days. SSL Encryption option. This service is a 0.1 release and not subject to any SLAs. Please see CenturyLink Cloud Supplemental Terms for terms of service. free large UnManaged MySQL-compatible Database as a Service - 4vCPU/16GB, up to 256MB storage, 9600 concurrent connections, daily backups held for 7 days. SSL Encryption option. This service is a 0.1 release and not subject to any SLAs. Please see CenturyLink Cloud Supplemental Terms for terms of service. free
Once you know the service name, create an instance of that service. The service instance is a long-lived, provisioned MySQL database, created with unique credentials and SSL information that live as long as the instance exists:
Brians-MacBook-Pro:~ bbutton$ cf create-service ctl_mysql free example-dbaas-instance Creating service instance example-dbaas-instance in org T3OS / space Dev as brianbuttonxp... OK
You can then see your service ready for you to use:
Brians-MacBook-Pro:~ bbutton$ cf services Getting services in org T3OS / space Dev as brianbuttonxp... OK name service plan bound apps last operation my_orchestrate orchestrate free create succeeded example-dbaas-instance ctl_mysql free create succeeded
At this point, you have all the infrastructure created to be able to connect to MySQL with the code shown here.
Accessing the Database from Ruby
You will use a simple Sinatra app to access AppFog and the MySQL instance. This gives you an easy way to create an endpoint you can hit from a browser, which creates the database connection. I'll only discuss the relevant parts of the application here, but the entire code base is available on Github.
This is the entry point of our Sinatra app. The interesting bits of this file begin with the
ENV["VCAP_SERVICES] and continue beyond. The _VCAPSERVICES environment variable is set by CloudFoundry as your application is staged and deployed. It holds the information needed to consume any services that bind to your application. For your MySQL instance, the VCAP_SERVICES variable will hold something like this:
This block of JSON includes information about the database name, username, password, the IP address of your service and the information that you'll need to connect to your service over SSL.
Next, we'll look at how to parse that data, but for now let's look at this code taken from main.rb:
vcap_services = ENV["VCAP_SERVICES"] pem_file_path = './client_cert.pem' connection_string_model = DatabaseConnectionModel.FromCloudFoundryJson(vcap_services, pem_file_path) ssl_certificate_file = SslCertificate.FromCloudFoundryJSON(vcap_services) ssl_certificate_file.create_pem(pem_file_path) set :connection_info, connection_string_model
Most of the code is fairly simple Ruby. The important parts are the last four lines. These lines take the contents of the VCAP_SERVICES JSON and parse it into the connection string model using the
DatabaseConnectionModel class. Then, they take the data from the client certificate passed through the JSON and store it in a file.
This particular database library requires the certificate to be in a separate file, rather than having the data passed into the hash for the connection string so we have to create the certificate file using the
SslCertificateFile class. This presents a potential issue when running on AppFog because developers aren't supposed to use the file system when using CloudFoundry. When running on a Platform-as-a-Service (PaaS) like CloudFoundry the file system is only temporary and is recreated from scratch each time an application is staged or moved by the PaaS to another server. Since your application recreates the file from the VCAP_SERVICES variable each time it starts, this should not be a problem.
The other interesting class in your application is routes.rb, which is the class used by Sinatra to decide how to route requests coming into the endpoint. Again, the code is pretty simple:
get '/' do connection_string_model = settings.connection_info connection = Mysql2::Client.new( host: connection_string_model.host_name, port: connection_string_model.port, username: connection_string_model.user_id, password: connection_string_model.password, database: connection_string_model.database, sslca: connection_string_model.cert_file) results = connection.query("show status like 'Ssl_cipher'") results.count.to_s end
This code fills out the Mysql2 connection string with information parsed out of VCAP_SERVICES by the
DatabaseConnectionModel and then makes a simple request to the server to validate that everything worked correctly.
Note: The last field of the connection string has a few talking points. First of all, be careful when implementing your own code - the appropriate field to fill in is
sslca, not other choices such as
sslkey. What is sent to you when binding to the instance is a key that basically allows you to trust the self-signed certificate sent by the RDB infrastructure, which is why you need to set the
The Rest of the Code
From here it is simple. The code either pulls data out of JSON and creates and fills the PEM file with the appropriate information or provides the boilerplate code needed by Sinatra. All of the code is available in the Github repository.
Running the Application in AppFog
Pushing an application to AppFog is really easy. Most of the important data needed is in the manifest.yml file:
--- applications: - name: bab-dbaas-test memory: 128M instances: 1 services: - example-dbaas-instance
The information in this file allows AppFog to stage and run your application with no further manual interaction. The name represents the unique label associated with your program. This name does have to be unique across the entire AppFog universe so you should change it to something specific and meaningful to you. The name of the service, (
example-DBaaS-instance in this case) has to match the name of the instance you created in the Preparing AppFog section from above. Other than that, it should be fine the way it is.
After you've made those changes, you can push the application by typing
cf push from the same directory as the application on your file system. You'll see a lot of lines fly by showing the different stages of launching the application. At the end of it, though, you should see a line that looks something like this:
App started OK App bab-dbaas-test was started using this command `bundle exec rackup config.ru -p $PORT` Showing health and status for app bab-dbaas-test in org T3OS / space Dev as brianbuttonxp... OK requested state: started instances: 1/1 usage: 128M x 1 instances urls: bab-dbaas-test.useast.appfog.ctl.io last uploaded: Thu Sep 17 16:11:57 UTC 2015 stack: cflinuxfs2 buildpack: Ruby state since cpu memory disk details #0 running 2015-09-17 11:12:26 AM 0.0% 63.3M of 128M 99.9M of 1G
As long as the state above is
running, you should be able to go to the URL listed in the
urls line and see the output. If you get any sort of error, like a 500 Internal Server error, you can investigate it using standard CloudFoundry tools, such as
cf logs bab-dbaas-test.
It really is this easy to provision and consume a MySQL instance using Database-as-a-Service. It only requires a small amount of CloudFoundry command line knowledge and basic knowledge of Ruby.
If anything is unclear or if you have a question, feel free to add an issue to the repo or contact me directly through this blog.