Puppet and MySQL: A How-To Guide

Posted in: Technical Track

Today’s blog post will include a how-to guide on setting up Puppet Master and Agents for streamlined server management. First off you’ll have to configure each of your puppet agent nodes (i.e. clients.)

Get the latest repo from Puppet Labs for your distro — These will have to be added both to the puppet master and to the puppet agent nodes.

Log in to each node and install/configure the puppet agent:

apt-get install puppet #[or yum install puppet for RHEL derivatives]

Edit your /etc/puppet/puppet.conf and add your puppet master servername under the [main] section:

[main] server= # e.g. dev-laptop in my case

Also, edit your /etc/default/puppet file and set START to yes:

# Start puppet on boot?

Now restart the puppet agent service on all nodes and enable the puppet service:

service puppet restart
puppet resource service puppet ensure=running enable=true

After running the above commands on each of your agent nodes, it’s now time to hop over onto your puppet master server node and configure your puppet master:

apt-get install puppetmaster #[or yum install puppet-server for RHEL]

sudo puppet resource service puppetmaster ensure=running enable=true

By running the following command you should now see the signed certificate requests made by your puppet agents:

sudo puppet cert list
“deb-box-1.lan” (09:00:F5:B9:CF:D0:E7:BF:C5:D3:8B:74:BC:3E:87:E2)
“deb-box-2.lan” (AC:B5:D0:BD:CF:AC:C8:9C:83:21:86:09:40:D3:ED:1E)
“deb-box-3.lan” (67:4E:81:48:18:73:47:16:2F:2C:3D:31:4D:4D:80:F8)

You will have to certify each request in order to enable the agent:

puppet cert sign “deb-box-1.lan”
notice: Signed certificate request for deb-box-1.lan
notice: Removing file Puppet::SSL::CertificateRequest deb-box-1.lan at ‘/var/lib/puppet/ssl/ca/requests/deb-box-1.lan.pem’

puppet cert sign “deb-box-2.lan”
notice: Signed certificate request for deb-box-2.lan
notice: Removing file Puppet::SSL::CertificateRequest deb-box-2.lan at ‘/var/lib/

puppet cert sign “deb-box-3.lan”
notice: Signed certificate request for deb-box-3.lan
notice: Removing file Puppet::SSL::CertificateRequest deb-box-3.lan at ‘/var/lib/puppet/ssl/ca/requests/deb-box-3.lan.pem’

Finally, test the client puppet agent by running:

puppet agent –test

Now you are ready to create/install modules to manage various server components. Activities such as managing your MySQL databases can be performed by installing the puppetlabs/mysql module as follows:

puppet module install puppetlabs/mysql

Your module has been added! From here it is as simple as adding your requirements to your “site.pp” file in “/etc/puppet/manifests” e.g. (1.) to ensure mysql-server is installed on the node <serverbox.lan> with (2.) a root password = “root_pass_string” & (3.) max_connections = “1024” the following configuration will suffice (all options are configurable, just specify the section you would like modified & the variable as in the example ~ https://forge.puppetlabs.com/puppetlabs/mysql for more info):

# vi /etc/puppet/manifests/site.pp

node ‘deb-box-1.lan’ {
class { ‘::mysql::server’:
root_password => ‘root_pass_string’,
override_options => { ‘mysqld’ => { ‘max_connections’ => ‘1024’ } }

node ‘deb-box-2.lan’ {
class { ‘::mysql::server’:
root_password => ‘root_pass_string’,
override_options => { ‘mysqld’ => { ‘max_connections’ => ‘1024’ } }

node ‘deb-box-3.lan’ {
class { ‘::mysql::server’:
root_password => ‘root_pass_string’,
override_options => { ‘mysqld’ => { ‘max_connections’ => ‘1024’ } }

Since site.pp is configured run the following command on the puppet agent node to update the configuration:

puppet agent -t

Voilà! Your MySQL server is now under new management =)

Optionally you can also install puppet dashboard by performing the following steps:

apt-get install puppet-dashboard # RHEL # yum install puppet-dashboard

Edit your database.yml (using standard YAML notation) adding the dashboard database name, username and password – it is recommended to use the same database for production/development/testing environments:

cd /usr/share/puppet-dashboard/config;
vi database.yml # add the database name, username & password you intend to setup in the database (see the following step for details)

Connect to your mysql instance (hopefully installed already) and create the puppet-dashboard database & users:


CREATE USER ‘dashboard’@’localhost’ IDENTIFIED BY ‘my_password’;

GRANT ALL PRIVILEGES ON dashboard.* TO ‘dashboard’@’localhost’;

Now time to populate the database using rake by running:

rake RAILS_ENV=production db:migrate

rake db:migrate db:test:prepare

Your dashboard is now setup – you can run it with the builtin webserver WEBrick:

sudo -u puppet-dashboard ./script/server -e production

Here are some useful Puppet commands that will come in handy:

# Apply changes with noop
puppet apply –noop /etc/puppet/modules/testit/tests/init.pp

# Full apply changes
puppet apply /etc/puppet/modules/testit/tests/init.pp

# Print a puppet agent’s full configuration
puppet –configprint all



Want to talk with an expert? Schedule a call with our team to get the conversation started.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *