---
title: Create MySQL database with Capistrano
url: https://www.grahambrooks.com/post/2011-01-24-create-mysql-database-with-capistrano/
date: 2011-01-24T00:00:00+00:00
author: Graham Brooks
tags:
- ruby
- Continuous Delivery
---

# Create MySQL database with Capistrano

I was playing about with [Capistrano](https://github.com/capistrano/capistrano) over
the weekend. I wanted to automate the deployment of a Rails
application to my server. The server was (I thought) just about ready
to accept the app but I did not want to go through another manual
deployment. I thought I would take the opportunity to script the
deployment. The first task I set myself was to create a production
MySQL database. Searching for how to do this threw up lots of
interesting information about building and deploying database.yml
files, but not much about configuring MySQL. The first set of tasks I
came up with were:

* Does the database exist?
* Create database if missing
* Grant permissions

## Does the database exist?

```ruby
  def database_exits?
    exists = false

    run "mysql --user=#{db_admin_user} --password=#{db_admin_password} --execute=\"show databases;\"" do |channel, stream, data|
      exists = exists || data.include?(db_name)
    end

    exists
  end
```


Not particularly pretty and would welcome improvements but functional.

## Create database

```ruby
def create_database
    create_sql = <<-SQL
      CREATE DATABASE #{db_name};
    SQL

    run "mysql --user=#{db_admin_user} --password=#{db_admin_password} --execute=\"#{create_sql}\""
  end
```


This snippet could be a task but I have not yet done that
refactoring - the original was parameterised taking in all the
required values as parameters.

## Grant Permissions

```ruby
 def setup_database_permissions
    grant_sql = <<-SQL
      GRANT ALL PRIVILEGES ON #{db_name}.* TO #{db_user}@localhost IDENTIFIED BY '#{db_password}';
    SQL

    run "mysql --user=#{db_admin_user} --password=#{db_admin_password} --execute=\"#{grant_sql}\""
  end
```


Again this step can now be converted into a task.

At the moment the values are coded in the deployment script
deploy.rb - something that I definitely want to change. The code
should be checked into source control but this would release the
usernames and passwords necessary to do deployments. My current
thoughts are to extract the runtime properties to a small yaml file
and read that in, unless anyone has a smarter idea?