IMPORTANT

Requires hiera-puppet 1.0.0 or above

Installation

gem install hiera-mysql

or

puppet module install mjpa/hiera_mysql

Introduction

Hiera is a configuration data store with pluggable back ends, hiera-mysql is a back end that fetches configuration valus from a MySQL database. It can be use instead of or along side other back ends.

Configuration

hiera-mysql configuration is fairly simple. The query specified in mysqlquery is parsed by Hiera to interpret any %{var} values specifed in the scope. It also has the ability to interpret %{key} (the name of the value you're searching for) directly into the SQL string.

Here is a sample hiera.yaml file that will work with mysql

---
:backends: 
    - mysql

:mysql:
    :host: localhost
    :user: root
    :pass: examplepassword
    :database: config

    :query: SELECT val FROM configdata WHERE var='%{key}' AND environment='%{env}'


:logger: console

:query: can be either a string or an array - if it's an array then each query is executed in order (similar to the :hierarchy: configuration parameter for the YAML backend. So the above could be configured as

    :query:
      - SELECT val FROM configdata WHERE var='%{key}' AND environment='%{env}'
      - SELECT val FROM configdata WHERE var='%{key}' AND location='%{location}'
      - SELECT val FROM configdata WHERE var='%{key}' AND environment='common'

:query: can also be a hash - when it's a hash, the lookup key must be of the format queryname:key. The queryname should match the key of the hash. With the below configuration, a user's email can be found with a query for useremail:root and only the useremail query will be executed.

    :query:
      :useremail: SELECT email FROM users WHERE name = '%{key}'
      :content: SELECT * FROM content WHERE author = '%{key}'

Results and data types

When looking up a single column (eg: SELECT foo FROM bar):

• hiera() will run iterate through each query and give back the first row returned.

• hiera_array() will iterate through each query and return an array of the every row returned from all the queries

When looking up multiple columns (eg: SELECT foo,bar FROM baz):

• hiera() will iterate through each query and return a hash of the first row as {column => value} eg:

DEBUG: Wed Oct 31 03:35:41 +0000 2012: Executing SQL Query: SELECT val,id FROM configuration WHERE var='color' AND env='common' OR env='qa'
DEBUG: Wed Oct 31 03:35:41 +0000 2012: Mysql Query returned 4 rows
{"id"=>"5", "val"=>"pink"}

• hiera_array() will iterate through each query and return an array of hashes for every row returned from all queries, eg:

DEBUG: Wed Oct 31 03:35:49 +0000 2012: Executing SQL Query: SELECT val,id FROM configuration WHERE var='color' AND env='common' OR env='qa'
DEBUG: Wed Oct 31 03:35:49 +0000 2012: Mysql Query returned 4 rows
[{"val"=>"pink", "id"=>"5"}, {"val"=>"red", "id"=>"6"}, {"val"=>"rose", "id"=>"7"}, {"val"=>"plain white", "id"=>"10"}]

Release Notes

0.2.0:

• Added array support
• Added multi-column hashes
• First Puppet Forge release

Todo

• Better MySQL error/exception handling

Contact

• Author: Craig Dunn
• Email: [email protected]
• Twitter: @crayfishX
• IRC (Freenode): crayfishx
• Web: http://www.craigdunn.org