Product docs and API reference are now on Akamai TechDocs.
Search product docs.
Search for “” in product docs.
Search API reference.
Search for “” in API reference.
Search Results
 results matching 
 results
No Results
Filters
Manage MySQL Database with Puppet Hiera on Ubuntu 16.04
Traducciones al EspañolEstamos traduciendo nuestros guías y tutoriales al Español. Es posible que usted esté viendo una traducción generada automáticamente. Estamos trabajando con traductores profesionales para verificar las traducciones de nuestro sitio web. Este proyecto es un trabajo en curso.
DeprecatedThis guide has been deprecated and is no longer being maintained. Please refer to the updated version of this guide.
Puppet is a configuration management system that helps simplify the use and deployment of different types of software, making system administration more reliable and replicable. In this guide, we use Puppet to manage an installation of MySQL , a popular relational database used for applications such as WordPress, Ruby on Rails, and others. Hiera is a method of defining configuration values that Puppet will use to simplify MySQL configuration.
In this guide, you’ll use Puppet to deploy modules on your server. At the end, you will have MySQL installed, configured, and ready to use for a variety of applications that require a database backend.
sudo
. If you’re not familiar with the sudo
command, see the Users and Groups
guide.Before You Begin
If you have not already done so, create a Linode account and Compute Instance. See our Getting Started with Linode and Creating a Compute Instance guides.
Follow our Setting Up and Securing a Compute Instance guide to update your system. You may also wish to set the timezone, configure your hostname, create a limited user account, and harden SSH access.
Install and Configure Puppet
Follow these steps to set up Puppet for single-host, local-only deployment. If you need to configure more than one server or to deploy a Puppet master, follow our multi-server Puppet guide .
Install the Puppet Package
Install the
puppetlabs-release
repository to add the Puppet packages:wget https://apt.puppet.com/puppetlabs-release-pc1-xenial.deb sudo dpkg -i puppetlabs-release-pc1-xenial.deb
Update the apt package index to make the Puppet Labs repository packages available, then install Puppet. This will install the
puppet-agent
package, which provides thepuppet
executable within in a compatible Ruby environment:sudo apt update && sudo apt install puppet-agent=1.10.4-1xenial
Confirm the version of Puppet installed:
puppet --version
Note that the
puppet-agent
package includes a different version of Puppet, which is expected:4.10.4
Install the Puppet MySQL Module
Puppet Forge is a collection of modules that aid in the installation of different types of software. The MySQL module handles the installation and configuration of MySQL without you needing to manage various configuration files and services by hand.
Install the MySQL module:
sudo -i puppet module install puppetlabs-mysql --version 3.11
This will install the
mysql
module into the default path/etc/puppetlabs/code/environments/production/modules/
.
Puppet MySQL Manifest
This guide uses a Puppet manifest to provide Puppet with installation and configuration instructions. Alternatively, you can configure a Puppet master .
While the entirety of a Puppet manifest can contain the desired configuration for a host, values for Puppet classes or types can also be defined in a Hiera configuration file to simplify writing Puppet manifests in most cases. In this example, the mysql::server
class parameters will be defined in Hiera, but the class must first be applied to the host.
To apply the mysql::server
class to all hosts by default, create the following Puppet manifest:
- File: /etc/puppetlabs/code/environments/production/manifests/site.pp
1
include ::mysql::server
Note that site.pp
is the default manifest file. Without a qualifying node { .. }
line, this applies the class to any host applying the manifest. Puppet now knows to apply the mysql::server
class, but still needs values for resources like databases, users, and other settings. Configure Hiera to provide these values in the next section.
Install and Configure Puppet Hiera
To understand how Hiera works, consider this excerpt from the default hiera.yaml
file:
- File: /etc/puppetlabs/puppet/hiera.yaml
1 2 3 4 5 6
--- :backends: - yaml :hierarchy: - "nodes/%{::trusted.certname}" - common
This Hiera configuration instructs Puppet to accept variable values from nodes/%{::trusted.certname}.yaml
. If your Linode’s hostname is examplehostname
, define a file called nodes/examplehostname.yaml
). Any variables found in YAML files higher in the hierarchy are preferred, while any variable names that do not exist in those files will fall-through to files lower in the hierarchy (in this example, common.yaml
).
The following configuration will define Puppet variables in common.yaml
to inject variables into the mysql::server
class.
Initial Hiera Configuration
Hiera configuration files are formatted as yaml, with keys defining the Puppet parameters to inject their associated values. To get started, set the MySQL root password. The following example of a Puppet manifest is one way to control this password:
- File: example.pp
1 2 3
class { '::mysql::server': root_password => 'examplepassword', }
We can also define the root password with the following Hiera configuration file. Create the following YAML file and note how the root_password
parameter is defined as Hiera yaml:
- File: /etc/puppetlabs/code/environments/production/hieradata/common.yaml
1
mysql::server::root_password: examplepassword
Replace examplepassword
with the secure password of your choice. Run Puppet to set up MySQL with default settings and the chosen root password:
sudo -i puppet apply /etc/puppetlabs/code/environments/production/manifests/site.pp
Puppet will output its progress before completing. To confirm MySQL has been configured properly, run a command:
mysql -u root -p -e 'select version();'
Enter the password and MySQL returns its version:
+-------------------------+
| version() |
+-------------------------+
| 5.7.19-0ubuntu0.16.04.1 |
+-------------------------+
Define MySQL Resources
Using Hiera, we can define the rest of the MySQL configuration entirely in yaml. The following steps will create a database and user for use in a WordPress installation.
Create a pre-hashed MySQL password. Replace the password
wordpresspassword
in this example, and when prompted for a the root MySQL password, use the first root password chosen in the previous section to authenticate. Note the string starting with a*
that the command returns for Step 2:mysql -u root -p -NBe 'select password("wordpresspassword")' *E62D3F829F44A91CC231C76347712772B3B9DABC
With the MySQL password hash ready, we can define Hiera values. The following YAML defines parameters to create a database called
wordpress
and a user namedwpuser
that has permission to connect fromlocalhost
. The YAML also defines aGRANT
allowingwpuser
to operate on thewordpress
database withALL
permissions:- File: /etc/puppetlabs/code/environments/production/hieradata/common.yaml
1 2 3 4 5 6 7 8 9 10 11 12 13 14
mysql::server::root_password: examplepassword mysql::server::databases: wordpress: ensure: present mysql::server::users: wpuser@localhost: ensure: present password_hash: '*E62D3F829F44A91CC231C76347712772B3B9DABC' mysql::server::grants: wpuser@localhost/wordpress.*: ensure: present privileges: ALL table: wordpress.* user: wpuser@localhost
Re-run Puppet:
sudo -i puppet apply /etc/puppetlabs/code/environments/production/manifests/site.pp
The
wpuser
should now be able to connect to thewordpress
database. To verify, connect to the MySQL daemon as the userwpuser
to thewordpress
database:mysql -u wpuser -p wordpress
After you enter the password for
wpuser
, exit the MySQL prompt:exit
Add Hierarchies for Specific Environments
Additional configurations can be added that will only be applied to specific environments. For example, backup jobs may only be applied for hosts in a certain region, or specific databases can be created in a particular deployment.
In the following example, Puppet will configure the MySQL server with one additional database, but only if that server’s distribution is Debian-based.
Modify
hiera.yaml
to contain the following:- File: /etc/puppetlabs/puppet/hiera.yaml
1 2 3 4 5 6
--- :backends: - yaml :hierarchy: - "%{facts.os.family}" - common
This change instructs Hiera to look for Puppet parameters first in
"%{facts.os.family}.yaml"
and then incommon.yaml
. The first, fact-based element of the hierarchy is dynamic, and dependent upon the host that Puppet and Hiera control. In this Ubuntu-based example, Hiera will look forDebian.yaml
, while on a distribution such as CentOS, the fileRedHat.yaml
will automatically be referenced instead.Create the following YAML file:
- File: /etc/puppetlabs/code/environments/production/hieradata/Debian.yaml
1 2 3 4 5 6 7
lookup_options: mysql::server::databases: merge: deep mysql::server::databases: ubuntu-backup: ensure: present
Though similar to the
common.yaml
file defined in previous steps, this file will add theubuntu-backup
database only on Debian-based hosts (like Ubuntu). In addition, thelookup_options
setting ensures that themysql::server:databases
parameter is merged betweenDebian.yaml
andcommon.yaml
so that all databases are managed. Withoutlookup_options
set to deeply merge these hashes, only the most specific hierarchy file will be applied to the host, in this case,Debian.yaml
.Alternatively, because our Puppet manifest is short, we can test the same command using the
-e
flag to apply an inline manifest:sudo -i puppet apply -e 'include ::mysql::server'
Run Puppet and observe the changes:
sudo -i puppet apply /etc/puppetlabs/code/environments/production/manifests/site.pp
Verify that the new database exists:
mysql -u root -p -e 'show databases;'
This includes the new
ubuntu-backup
database:+---------------------+ | Database | +---------------------+ | information_schema | | mysql | | performance_schema | | sys | | ubuntu-backup | | wordpress | +---------------------+
Congratulations! You can now control your Puppet configuration via highly configurable Hiera definitions.
More Information
You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.
This page was originally published on