Introducing hiera-mysql MySQL Backend for Hiera

Introduction

Some time ago I started looking at Hiera, a configuration datastore with pluggable back ends that also plugs seamlessly into Puppet for managing variables. When I wrote hiera-gpg a few months ago I realised how easy extending Hiera was and the potential for really useful backends that can consolidate all your configuration options from a variety of systems and locations into one streamlined process that systems like Puppet and other tools can hook into. This, fuelled by a desire to learn more Ruby, lead to hiera-mysql, a MySQL Backend for Hiera.

Installing

hiera-mysql is available as a ruby gem and can be installed with: 

# gem install hiera-mysql

Note: this depends on the Ruby mysql gem, so you’ll need gcc, ruby-devel and mysql-devel packages installed. Alternativley the source can be Downloaded here

MySQL database

To demonstrate hiera-mysql, here’s a simple MySQL database some sample data; 

mysql> use mytest;
Database changed
mysql> SHOW CREATE TABLE configuration \G
*************************** 1. row ***************************
       Table: configuration
Create Table: CREATE TABLE `configuration` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `var` varchar(255) DEFAULT NULL,
  `val` varchar(255) DEFAULT NULL,
  `env` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
mysql> SELECT * FROM configuration;
+----+-------+--------+------+
| id | var   | val    | env  |
+----+-------+--------+------+
|  1 | color | green  | live |
+----+-------+--------+------+
1 rows in set (0.00 sec)

Configuring Hiera

In this example we’re going to pass the variable “env” in the scope. hiera-mysql will interpret any scope variables defined in the query option, and also has a special case for %{key}. Example: 

# /etc/hiera.yaml
---
:backends: - mysql

:logger: console

:mysql:
    :host: localhost
    :user: root
    :pass: examplepassword
    :database: mytest
    :query: SELECT val FROM configuration WHERE var='%{key}' AND env='%{env}'

Running Hiera

With the above example, I want to find the value of the variable colour in the scope of live

[root@dev1 ~]# hiera color env=live
green

If I add more rows to the database that match the criteria, and use Hiera’s array search function by passing -a I can make Hiera return all the rows 

 
mysql> INSERT INTO configuration VALUES (2,'color','purple','live');
Query OK, 1 row affected (0.00 sec)
 
 
[root@dev1 ~]# hiera color env=live -a 
["green", "purple"]

Hiera’s pluggable nature means that you can use this back end alongside other back ends such as YAML or JSON and configure your search order accordingly.

Limitations

Currently hiera-mysql will only return the first element of a row, or an array of first elements, so you can’t do things like SELECT foo,bar FROM table. I intend to introduce this feature by implementing Hiera’s hash search in a future release. Also, the module could do with slightly better exception handling around the mysql stuff. Please let me know if theres anything else that would improve it.

Puppet

And of course, because Hiera is completely transparent, accessing these variables from Puppet couldn’t be easier! 

class foo { 
    $env="live"
    $color=hiera("color")
}

References

  • Github homepage for hiera-mysql
  • Official Hiera Project Homepage
  • Hiera – A pluggable hierarchical data store
    Share