Module of the Week: puppetlabs/mysql – MySQL Management

by Carl Caum|10 June 2012

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