Quickly Deploy MySQL with Puppet Enterprise Supported Module Puppetlabs-MySQL

With the launch of Puppet Enterprise 3.2, Puppet Labs now provides full support for a handful of Forge modules within the Puppet Labs namespace. This list of supported modules will continue to grow, and we encourage all our Puppet Enterprise customers to upgrade to 3.2, to enjoy the benefits of module support (and all the other benefits of the new release, too).

Among the newly supported modules is puppetlabs-mysql, which lets you both configure and manage your MySQL installation, plus manage MySQL resources such as users, grants, and databases.

Managing a MySQL deployment, whether it be the simple installation of the MySQL client or a full-on server deployment, is a pretty common use case for Puppet and Puppet Enterprise. With puppetlabs-mysql, you can take what was once a complex or extensive MySQL deployment and streamline the process into something far simpler and more manageable.

You can expect the puppetlabs-mysql module, and all other supported modules, to function as they should on all supported platforms. You can also expect support in all our usual channels. You can see the (growing) list of supported modules on the Puppet Forge.

In this post, I’ll show you how to easily deploy and configure a MySQL server using our supported module, as well as demonstrate how to quickly deploy a simple MySQL client.

For the following example, we have three separate nodes, all running the most current release of Puppet Enterprise 3.2, and all running CentOS 6.4. The first node is our all-in-one master with the hostname master.puppetlabs.vm. The second node will act as our MySQL server with the hostname db.puppetlabs.vm, and the third node acts as the MySQL client machine with the hostname client.puppetlabs.vm.

To get started, we first need to get the module installed. You can use the Puppet Module Tool, included in Puppet Enterprise, to install the module on our puppet master.

[root@master ~]# puppet module install puppetlabs-mysql
Notice: Preparing to install into /etc/puppetlabs/puppet/modules ...
Notice: Downloading from https://forge.puppetlabs.com ...
Notice: Installing -- do not interrupt ...
/etc/puppetlabs/puppet/modules
└── puppetlabs-mysql (v2.2.3)

Deploying MySQL Client

Deploying the MySQL client is very straightforward: just include the class in (/etc/puppetlabs/puppet/manifests/site.pp):

node 'client.puppetlabs.vm' {
  class { ‘mysql::client’:}
}

That was easy enough! By default, the MySQL client class, ensures the MySQL package is present and determines the package name, depending on the node's OS.

Deploying MySQL Server with Puppetlabs-MySQL

Deploying your MySQL server can be as simple as including the MySQL server class. In most cases, however, this will not suffice, as it uses all the default options. We will want to change some of these options. In the case below, we are going to change the max connections and root password.

class { '::mysql::server':
  root_password    => 'strongpassword',
  override_options => { 'mysqld' => { 'max_connections' => '1024' } }
}

You maybe asking yourself, “what is this override_options paramater?” The override_options parameter allows us to override options that are traditionally set in our my.cnf. As you can see, we used the override_options parameter to set our max connections to 1024. Traditionally, this would appear in configuration file as:

[mysqld]
max_connections = 1024

The override_options parameter uses a hash format and allows us to do things like pass in an array. Something like this:

class { '::mysql::server':
  root_password    => 'strongpassword',
  override_options => { 'Test' => { 'RandomOption' => ['value1', 'value2'] } }
}

Traditionally this would appear in the my.cnf configuration as:

[Test]
RandomOption = value1
RandomOption = value2

The example class above is enough to get a MySQL server deployed. In the next section, I will discuss how you would go about deploying a custom database.

Creating a Database with Puppetlabs-MySQL

Though standing up a MySQL server is important, the server alone means nothing without any databases. In this example, I will create a database called mydb and configure the user, password and host:

mysql::db { 'mydb':

user => 'admin', password => 'secret', host => 'db.puppetlabs.vm', }

At this point, we have a MySQL server deployed with a database mydb, but it doesn't have any data. Let’s create a new database called statedb, so when the database is initially created we use a SQL script to populate the database.

Before we continue, let’s stop using site.pp and move all our work so far into our own module, called blogpost. We do this to avoid making any direct edits to the puppetlabs-mysql module itself once we begin adding any files Puppet needs to serve up, and to simplify our node definition in site.pp.

First, let’s create a directory named blogpost within /etc/puppetlabs/puppet/modules/ and build all the necessary subdirectories for the module to function properly. We will create both the directories /etc/puppetlabs/puppet/modules/blogpost/manifests and /etc/puppetlabs/puppet/modules/blogpost/files.

For the purposes of this example, and in the interest of time, I decided to pull down a SQL script that creates a table of states from statetable. I am going to put the .sql file in the files directory of the module named blogpost (/etc/puppetlabs/puppet/modules/blogpost/files/states.sql), and lay the file down via the file resource.

After the file is laid down, we will be able to use it to populate the data in the database. In the example below we will have Puppet execute this script upon creating the database for the first time.

mysql::db { 'statedb':
  user     => 'admin',
  password => 'secret',

host => 'db.puppetlabs.vm', sql => '/tmp/states.sql', require => File‘/tmp/states.sql’ }

file { "/tmp/states.sql":
  ensure => present,
  source => "puppet:///modules/mysql/states.sql",
}

Now we have MySQL deployed, a database created, and a table of states, but we are not quite done. As you probably guessed, we need to configure users and grants, so we are able to grant permissions to the states table we created. In this example, we will allow the user bob to access the table, but throttle the user’s access.

mysql_user { 'bob@localhost':
  ensure                   => 'present',

max_connections_per_hour => '60', max_queries_per_hour => '120', max_updates_per_hour => '120', max_user_connections => '10', }

Now for the grants:

mysql_grant { 'bob@localhost/statedb.states':

ensure => 'present', options => 'GRANT', privileges => 'ALL', table => 'statedb.states', user => 'bob@localhost', }

Now we officially have a MySQL server deployed, configured, and have put some sample data in a table. Let's take a look at the whole thing in the form of the blogpost module (/etc/puppetlabs/puppet/modules/blogpost/manifests/init.pp):

class blogpost {

  class { '::mysql::server':
    root_password    => 'strongpassword',
    override_options => { 'mysqld' => { 'max_connections' => '1024' } }
  }
         
  mysql::db { 'statedb':
    user     => 'admin',
    password => 'secret',
    host     => 'master.puppetlabs.vm',
    sql        => '/tmp/states.sql',
    require => File['/tmp/states.sql'],
  }
         
  file { "/tmp/states.sql":
    ensure => present,
    source => "puppet:///modules/blogpost/states.sql",
  }
                 
  mysql_user { 'bob@localhost':
    ensure                   => 'present',
    max_connections_per_hour => '60',
    max_queries_per_hour     => '120',
    max_updates_per_hour     => '120',
    max_user_connections     => '10',
  }
         
  mysql_grant { 'bob@localhost/statedb.states':
    ensure     => 'present',
    options    => ['GRANT'],
    privileges => ['ALL'],
    table      => 'statedbl.states',
    user       => 'bob@localhost',
  }
         
}

At this point all we need to do is change our db.puppetlabs.vm node definition in (/etc/puppetlabs/puppet/manifests/site.pp) to include the blogpost module:

node 'db.puppetlabs.vm' {
  include blogpost
}

Additional Documentation

This is a pretty basic introduction and demonstration of how you can use and configure the puppetlabs-mysql module. As you can see, the module is extensive, with many configuration options. For more information and parameters, check out the documentation for the puppetlabs-mysql module.

Jay Wallace is a support engineer at Puppet Labs.

Learn More

Puppet sites use proprietary and third-party cookies. By using our sites, you agree to our cookie policy.