Create MySQL database with Capistrano

I was playing about with 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?

  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

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

 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?