Skip navigation.

Puppet and MySQL: create databases and users

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.

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 { "create-${name}-db":
      unless => "/usr/bin/mysql -u${user} -p${password} ${name}",
      command => "/usr/bin/mysql -uroot -p$mysql_password -e \"create database ${name}; grant all on ${name}.* to ${user}@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!

Come in useful already

Hey John, we were searching for exactly this information this afternoon and found your post. Great timing. One change we made was to split granting db rights from schema creation:

    define db( $user, $password ) {
      exec { "create-${name}-db":
        unless => "/usr/bin/mysql -uroot ${name}",
        command => "/usr/bin/mysql -uroot -e \"create database ${name};\"",
        require => Service["mysqld"],
      }

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

Re: Come in useful already

Glad to be of service!

Idempotency

To maintain Idempotency, remember “IF NOT EXISTS” with your schema creation commands.

Nice one - keep posting more

Nice one - keep posting more such snippets :)

Re: Nice one - keep posting more

I’ll do my best. Remember: hire the Puppet veteran.

There are native types for that

Hi John,

Nice way to do it, but there are already native types to handle databases, users and grants at http://forge.puppetlabs.com/DavidSchmitt/mysql

Best Regards, David Schmitt

Re: There are native types for that

David,

That looks like a handy module, but I’d quibble with the term ‘native’. If it’s not built into Puppet, then as far as I can see it’s not native.

The point about the recipe I outline here is that it’s not dependent on an external module, and it’s easily generalised to any other database (for example PostgreSQL).

Your first example

I think the Service in your top example should require the config File, not the Package.

As I’m sure you know, the conventional trio is:

package <—requires— config file <—requires— service

and the config file —notifies—> service

Thanks for all your great posts about puppet!

Quote the passwords

You’ll want to quote the password when using it in the Exec blocks. Without quotes, it will break on special characters.

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

Reverse script

Thanks for the helpful hint. I have a script to setup student users and then delete them after the learning period is over. If folks are interested here’s a snippet for you to use in deleting users and databases (I’m doing a straight cut and paste, but I’m sure puppet users can figure out how to use this for themselves):

exec { "delete-${title}-db":
  command => "/usr/bin/mysql -uroot -p$mysql_password -e \"DROP DATABASE ${title}; DROP USER '${title}'@'localhost'; FLUSH PRIVILEGES;\"",
  require => User["${title}"]
}

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.
By submitting this form, you accept the Mollom privacy policy.