Module of the Week: puppetlabs/mysql – MySQL Management

Purpose Manage MySQL clients, servers, and databases
Module puppetlabs/mysql
Puppet Version 2.6+
Platforms RedHat, CentOS, Ubuntu, Debian, FreeBSD

One of the most common first big tasks users are interested in using Puppet for is a MySQL deployment. The puppetlabs/mysql module provides users with the tools needed to quickly manage everything from a MySQL client to a full MySQL server deployment complete with backups and tuning. In this post, we’ll show you how to easily deploy and configure a MySQL server and ensure databases are present.

Resource Overview

mysql class

Files None
Directories None
Packages mysql_client
Services None
Description This class installs the MySQL client

mysql::server class

Files /root/.my.cnf, /etc/mysql/mysql.conf
Directories /etc/mysql, /etc/mysql/conf.d
Packages mysql-server
Services mysqld
Description This class installs the mysql server and ensures the mysql server service is running.

Installing the module

Complexity Easy
Installation Time 2 minutes

Installing Puppet Labs’ MySQL module is as simple as using the Puppet module tool; available in Puppet 2.7.10+ and Puppet Enterprise 2.5+); also available as a rubygem:

$ puppet module install puppetlabs/mysql
Preparing to install into /etc/puppetlabs/puppet/modules ...
Downloading from http://forge.puppetlabs.com ...
Installing -- do not interrupt ...
/etc/puppetlabs/puppet/modules
└── puppetlabs-mysql (v0.3.0)

Alternatively, you can install the mysql module manually:

$ cd /etc/puppet/modules
$ wget http://forge.puppetlabs.com/system/releases/p/puppetlabs/puppetlabs-mysql-0.3.0.tar.gz
$ tar -xvf puppetlabs-mysql-0.3.0.tar.gz
$ mv puppetlabs-mysql-0.3.0 mysql
$ rm puppetlabs-mysql-0.3.0.tar.gz

You should now have a MySQL directory in your module path.

Configuring the module

Complexity Medium
Installation Time 15 minutes

Configuring a mysql client

To assign a mysql client role to a node, simply assign the mysql class to the node. The mysql class exposes two parameters with sane defaults.

Parameters Default value Source
$package_name varies per OS class parameter
$package_ensure present class parameter

Example usage

If you want to ensure a mysql client is available on a node, use the following convention.

$ cat /etc/puppetlabs/puppet/manifests/site.pp
…
node 'webapp1.puppetlabs.com' {
 class { ‘mysql’: }
}
…

Then on webapp1 we issue a Puppet run:

$ puppet agent -t
…

Now we have a mysql client installed on webapp1.

Adding Language Bindings

Many languages have bindings available to interface with the mysql client. The puppetlabs/mysql module includes many classes to install language bindings. The following languages are supported simply by declaring their respective class on the node.

Language Class
Java mysql::java
Python mysql::python
Ruby mysql::ruby

Configuring a mysql server

To manage a mysql server on a node, you can assign the mysql::server class to a node intended to be a database server. The mysql::server class provides the following parameters with sane defaults.

Parameter Default Source
$package_name varies per OS class parameter
$package_ensure present class parameter
$service_name varies per OS class parameter
$service_provider varies per OS class parameter
$config_hash empty hash class parameter

You will rarely have to use most of the parameters in the above chart, however the $config_hash parameter is special. The $config_hash parameter allows you to configure the mysql service. Its value must be a Hash. Currently the following keys for the hash are supported:

Parameter Default Description
$root_password The root password for the mysql service. The password gets cached in /root/.my.cnf. If $etc_root_password is set, then the root password will be cached in /etc/my.cnf as well.

In most cases, this is the only key that’s required.

$old_root_password Only necessary if it’s you’re first time managing an existing mysql service installation and you’re changing the root password to the value of $root_password

$etc_root_password False Whether to cache the mysql root password in /etc/my.cnf
$bind_address 127.0.0.1 The IP address to listen for database connections on
$port 3306 The port to listen for database connections on
$config_file Varies per OS The location of the mysql configuration file
$socket Varies per OS The location of the mysql socket to use
$datadir Varies per OS The mysql service datadir setting
$ssl false Whether to use SSL for incoming connections
$ssl_ca The location of the CA to use. Only used if $ssl is true
$ssl_cert The SSL certificate to use. Only used if $ssl is true
$ssl_key The SSL key to use. Only used if $ssl is true
$log_error Varies per OS The file path of the mysql error log.
$default_engine The mysql default-storage-engine setting
$root_group Varies per OS The ‘root’ system group. Usually this is root or wheel.

Example Usage

$ cat /etc/puppetlabs/puppet/manifests/site.pp
…
node 'db1.puppetlabs.com' {
  class { ‘mysql::server’:
    config_hash => {
      ‘root_password’ => ‘my_root_password_in_plain_text’,
    },
  }
}
…

Configuring a database

The puppetlabs/mysql module allows you to manage mysql databases in addition to the mysql service itself. The module provides custom puppet types; database, database_grant, and database_user. In addition, the module provides custom providers, mysql, for each type. To make using these custom types and providers easier, the module provides a defined type, mysql::db that makes the common use of these types and providers easier. The following parameters are accepted with the defined type:

Parameter Default Description
$user The user to ensure exists in the mysql system and has grant privileges associated with the database to be managed
$password The password of $user
$host localhost The host to allow the user to connect to the database from
$charset UTF8 The character set to use for the mysql database to be managed
$grant [all] Accepts an array of MySQL grant privileges.
$sql The full path to a sql script file. It is only run when the database is created, unless $enforce_sql is true
$enforce_sql false Whether to run the sql script specified by $sql every time puppet runs.

Example Usage

…
mysql::db { ‘mydatabase’:
  user     => ‘myapp1’,
  password => ‘supersecret’,
  host     => ‘webapp1.puppetlabs.com’,
}
…

Declare this resource on your database host. After running puppet on your database host, the host will contain a MySQL database with the myapp1 user allowed to access it from the webapp.puppetlabs.com host.

Conclusion

The Puppet Labs’ MySQL module is a powerful tool that allows for quick management of MySQL clients and reliable management of MySQL services and databases. By using simple class declarations and a few mysql::db resource declarations, you can fully automate your MySQL deployments. There are even more classes provided by the module we haven’t gone into in this post. You can further tune, secure, and monitor your MySQL deployments using the puppetlabs/mysql module.

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