
MySQL
Prerequisites
- Welcome
- Power of Puppet
- Resources
- Manifests and Classes
- Modules
- NTP
Quest Objectives
- Install and configure a MySQL server.
- Add a MySQL user, add a database, and grant permissions.
Getting Started
In this quest, we'll continue to explore how existing modules from the Puppet forge can simplify otherwise complex configuration tasks. You will use Puppet Labs' MySQL module to install and configure a server, then explore the custom resource types included with the module. To get started, enter the following command.
quest --start mysql
WhySQL?
The Puppet Labs MySQL module is a great example of how a well-written module can build on Puppet's foundation to simplify a complex configuration task without sacrificing robustness and control.
The module lets you install and configure both server and client MySQL instances, and extends Puppet's starndard resource types to let you manage MySQL users, grants, and databases with Puppet's standard resource syntax.
Server Install
Task 1 :
Before getting started configuring your MySQL server installation, fetch the
puppetlabs-mysql
module from the Puppet Forge with the puppet module
tool.
puppet module install puppetlabs-mysql
With this module installed in the Puppet master's module path, all the included classes are available to classify nodes.
Task 2 :
Edit /etc/puppetlabs/puppet/environments/production/manifests/site.pp
to classify the LVM with the
MySQL server class. Using class parameters, specify a root password and set the
server's max connections to '1024.'
class { '::mysql::server':
root_password => 'strongpassword',
override_options => { 'mysqld' => { 'max_connections' => '1024' } },
}
In addition to some standard parameters like the root_password
, the class
takes a hash of override_options
, which you can use to address any
configuration options you would normally set in the /etc/my.cnf
file. Using a
hash lets you set any options you like in the MySQL configuration file without
requiring each to be written into the class as a separate parameter. The
structure of the override_options
hash is analogous to the [section]
,
var_name = value
syntax of a my.cnf
file.
Task 3 :
Use the puppet parser validate
tool to check your syntax, then trigger a
puppet run:
puppet agent -t
If you want to check out your new database, you can connect to the MySQL monitor
with the mysql
command, and exit with the \q
command.
To see the result of the 'max_connections' override option you set, grep the
/etc/my.cnf
file:
cat /etc/my.cnf | grep -B 9 max_connections
And you'll see that Puppet translated the hash into appropriate syntax for the MySQL configuration file:
[mysqld]
...
max_connections = 1024
Scope
It was easy enough to use Puppet to install and manage a MySQL server. The
puppetlabs-mysql
module also includes a bunch of classes that help you manage
other aspects of your MySQL deployment.
These classes are organized within the module directory structure in a way that matches Puppet's scope syntax. Scope helps to organize classes, telling Puppet where to look within the module directory structure to find each class. It also separates namespaces within the module and your Puppet manifests, preventing conflicts between variables or classes with the same name.
Take a look at the directories and manifests in the MySQL module. Use the tree
command with a filter to include only .pp
manifest files:
tree -P *.pp /etc/puppetlabs/puppet/environments/production/modules/mysql/manifests/
You'll see something like the following:
/etc/puppetlabs/puppet/environments/production/modules/mysql/manifests/
├── backup.pp
├── bindings
│ ├── java.pp
│ ├── perl.pp
│ ├── php.pp
│ ├── python.pp
│ └── ruby.pp
├── bindings.pp
├── client
│ └── install.pp
├── client.pp
├── db.pp
├── init.pp
├── params.pp
├── server
│ ├── account_security.pp
│ ├── backup.pp
│ ├── config.pp
│ ├── install.pp
│ ├── monitor.pp
│ ├── mysqltuner.pp
│ ├── providers.pp
│ ├── root_password.pp
│ └── service.pp
└── server.pp
Notice the server.pp
manifest in the top level of the mysql/manifests
directory.
You were able to declare this class as mysql::server
. Based on this scoped
class name, Puppet knows to find the class definition in a manifest called
server.pp
in the manifest directory of the MySQL module.
So mysql::server
corresponds to:
/etc/puppetlabs/puppet/environments/production/modules/mysql/manifests/server.pp
To take an example one level deeper, the mysql::server::account_security
class
corresponds to:
/etc/puppetlabs/modules/mysql/manifests/server/account_security.pp
We won't be calling the mysql
class directly in this quest, but it's worth
reiterating the special case of a module's self-named class. This will always be
found in a manifest called init.pp
in the top level of the module's manifests
directory.
So the mysql
class is found here:
/etc/puppetlabs/modules/mysql/manifests/init.pp
Account Security
For security reasons, you will generally want to remove the default users and
the 'test' database from a new MySQL installation. The account_security
class
mentioned above does just this.
Task 4 :
Go back to your site.pp
manifest and include the
mysql::server::account_security
class. Remember, you don't need to pass any
parameters to this class, so a simple include
statement will work in place of
a parameterized class declaration.
Trigger a Puppet run, and you will see notices indicating that the test database and two users have been removed:
Notice:
/Stage[main]/Mysql::Server::Account_security/Mysql_database[test]/ensure:
removed
Notice:
/Stage[main]/Mysql::Server::Account_security/Mysql_user[@localhost]/ensure:
removed
Notice:
/Stage[main]/Mysql::Server::Account_security/Mysql_user[root@127.0.0.1]/ensure:
removed
Types and Providers
The MySQL module includes some custom types and providers that let you manage some critical bits of MySQL as resources with the Puppet DSL just like you would with a system user or service.
A type defines the interface for a resource: the set of properties you can use to define a desired state for the resource, and the parameters that don't directly map to things on the system, but tell Puppet how to manage the resource. Both properties and parameters appear in the resource declaration syntax as attribute value pairs.
A provider is what does the heavy lifting to bring the system into line with the state defined by a resource declaration. Providers are implemented for a wide variety of supported operating systems. They are a key component of the Resource Abstraction Layer (RAL), translating the universal interface defined by the type into system-specific implementations.
The MySQL module includes custom types and providers that make mysql_user
,
mysql_database
, and mysql_grant
available as resources.
Database, User, Grant:
Task 5 :
These custom resource types make creating a new database with Puppet pretty simple.
Just add the following resource declaration to your node definition in the
site.pp
manifest.
mysql_database { 'lvm':
ensure => 'present',
charset => 'utf8',
}
Similarly, with a user, all you have to do is specify the name and host as the resource title, and set the ensure attribute to 'present'. Enter the following in your node definition as well.
mysql_user { 'lvm_user@localhost':
ensure => 'present',
}
Now that you have a user and database, you can use a grant to define the
privileges for that user. Note that the *
character will match any table,
meaning that the lvm_user
has access to all tables in the lvm
database.
mysql_grant { 'lvm_user@localhost/lvm.*':
ensure => 'present',
options => ['GRANT'],
privileges => ['ALL'],
table => 'lvm.*',
user => 'lvm_user@localhost',
}
Once you've added declarations for these three custom resources, use the puppet
parser validate
command on the site.pp
manifest to check your syntax, and
trigger a puppet run with
puppet agent -t
Review
In this quest, you learned how to install and make configuration changes to a MySQL server. You also got an overview of how classes are organized withing the module structure and how their names within your Puppet manifests reflect this organization.
The MySQL module we used for this quest provides a nice example of how custom types and providers can extend Puppet's available resources to make service or application specific elements easily configurable through Puppet's resource declaration syntax.