Puppet and MySQL: create databases and users

In this blog post, renowned consultant and author John Arundel of Bitfield Consulting explains how to use Puppet to manage MySQL resources. If you'd like to know more, check out John's bestselling Puppet Beginner's Guide!

MySQL module for Puppet

Puppet MySQL management couldn’t be easier. Most applications use some kind of SQL database, and MySQL is a simple, easy to use, drop-in solution. In this article I’ll show you how to manage your MySQL servers, users, databases, and access permissions using Puppet.

Update: this tutorial was written a while ago, and you can find more up-to-date information in the Puppet Beginner’s Guide.

Install MySQL with Puppet

Here’s a Puppet recipe which will install the MySQL server packages (on CentOS; if you’re using another Linux distribution, you may need to change the package names), set the master password, and deploy a my.cnf file to your database server.

class mysql::server {
  
  package { "mysql-server": ensure => installed }
  package { "mysql": ensure => installed }

  service { "mysqld":
    enable => true,
    ensure => running,
    require => Package["mysql-server"],
  }

  file { "/var/lib/mysql/my.cnf":
    owner => "mysql", group => "mysql",
    source => "puppet:///mysql/my.cnf",
    notify => Service["mysqld"],
    require => Package["mysql-server"],
  }
 
  file { "/etc/my.cnf":
    require => File["/var/lib/mysql/my.cnf"],
    ensure => "/var/lib/mysql/my.cnf",
  }

  exec { "set-mysql-password":
    unless => "mysqladmin -uroot -p$mysql_password status",
    path => ["/bin", "/usr/bin"],
    command => "mysqladmin -uroot password $mysql_password",
    require => Service["mysqld"],
  }
}

Set MySQL root password

Puppet needs to know the MySQL root user password, so you will have to set this variable at some point (perhaps in site.pp). There are various tricks to avoid putting passwords directly into Puppet manifests, but we’re not going to worry about that here. The simplest way to set it will be to include a line like this in site.pp:

$mysql_password = "myT0pS3cretPa55worD"

Configure MySQL server with Puppet

The manifest above will look for your my.cnf file in modules/mysql/files, but you need only create an empty file to start with. MySQL uses sensible default settings, so it’s fine to leave the my.cnf file empty for the time being. When you need to make changes to it, Puppet will roll them out automatically.

Create MySQL database and user

Here’s a handy pattern that I use to have Puppet set up the database, user and permissions for my applications.

  define mysqldb( $user, $password ) {
    exec  -db":
      unless => "/usr/bin/mysql -u$ -p$ $",
      command => "/usr/bin/mysql -uroot -p$mysql_password -e \"create database $; grant all on $.* to $@localhost identified by '$password';\"",
      require => Service["mysqld"],
    }
  }

The way this works is that first of all (in the unless clause) Puppet runs a command which does nothing other than try to connect to the database as the named user with the password. If this succeeds, there’s nothing for us to do, so Puppet will skip the rest. Otherwise, it executes a short snippet of SQL which will create the required database and set up access permissions for the user.

Example: you’re deploying a Rails application myapp that requires a database called myapp, and a MySQL user called myappuser with all permissions on the database. Just create this class:

class myapp::db {
    mysqldb { "myapp":
        user => "myappuser",
        password => "5uper5secret",
    }
}

Now all you need to do is include this in the class that sets up your application:

class myapp {
    include myapp::db

    ...
}

Job done! And creating other databases for other apps is just as easy.

Puppet is a powerful tool for automating MySQL database and user management, and this will remove another manual step in setting up new servers, as well as freeing up you or your DBA for some more interesting tasks. Like making the app faster!