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