How to setup MySQL Server using Chef Cookbook

Here in this article we will try to setup a a very basic MySQL Server on Ubuntu 14.04 platform using the steps outlined in the mysql cookbook readme documentation.

Table of Contents

Test Environment

Ubuntu 14.04
Chef Client v13.2.20
MySQL Cookbook v8.4.0

Scenario

Use the documented feature for using Sysvinit instead of Upstart, which is the default, used for Ubuntu 14.04 platform. The below line needs to added into the mysql_service resource for initializing the mysql-default service using Sysvinit.

provider Chef::Provider::MysqlServiceSysvinit

Expected Results

MySQL service with the name ‘mysql-default’ should be created and started up on the Ubuntu 14.04 platform.

Actual Results

NameError
       ---------
       uninitialized constant Chef::Provider::MysqlServiceSysvinit

As per the documentation available for the Chef – mysql cookbook, the latest version as of this writing is 8.4.0 and the versions of mysql_service that are supported on Ubuntu 14.04 are 5.5, 5.6 only. Refer documentation for more information.

Resolution

Here is the environment that I have prepared on my workstation for setting up the mysql service.

Step1: Generate a Cookbook names testdb

Let’s generate a chef cookbook named testdb for this demo.

chef generate cookbook testdb

Step2: Updated the metadata.rb file with MySQL dependency

File: metadata.rb

name 'testdb'
maintainer 'The Authors'
maintainer_email 'you@example.com'
license 'All Rights Reserved'
description 'Installs/Configures testdb'
long_description 'Installs/Configures testdb'
version '0.1.0'
chef_version '>= 12.1' if respond_to?(:chef_version)

depends 'mysql', '~> 8.4.0'

Step3: Updated Test Kitchen definition

Its basically using docker provider and Ubuntu 14.04 image from vagrant. Very basic and minimal.

File: .kitchen.yml

---
driver:
  name: vagrant
  provider: docker


provisioner:
  name: chef_zero
  # You may wish to disable always updating cookbooks in CI or other testing environments.
  # For example:
  #   always_update_cookbooks: <%= !ENV['CI'] %>
  #always_update_cookbooks: true


verifier:
  name: inspec


platforms:
  - name: tknerr/baseimage-ubuntu-14.04


suites:
  - name: default
    run_list:
      - recipe[testdb::default]
    verifier:
      inspec_tests:
        - test/smoke/default
    attributes:

Step4: Generate a template file called mysql

Here we are going to generate a template for mysql service which will create a template file named “mysql.erb”.

chef generate template mysql

Here I am copying the default mysql file that is present in /etc/init.d location by changing the location of the configuration file referenced to /etc/mysql-default/my.cnf which is used to start the service.

Step5: Update Chef recipe

Error

NameError
       ---------
       uninitialized constant Chef::Provider::MysqlServiceSysvinit

File: default.rb

#
# Cookbook:: testdb
# Recipe:: default
#
# Copyright:: 2017, The Authors, All Rights Reserved.


apt_update 'daily' do
        frequency 86_400
        action :periodic
end


### The below mysql_service resource gets configured using the default ‘upstart’ service manager
### This also creates the following config file /etc/mysql-default/my.cnf which will be used for
### starting up the mysql service


mysql_service 'default' do
  port '3306'
  initial_root_password 'test'
  action :create
end

### Here I am copying the template file that was generated in Step4 to /etc/init.d/mysql

template '/etc/init.d/mysql' do
  source 'mysql.erb'
  owner 'mysql'
  group 'mysql'
  mode '0755'
end

### Now I am starting up the mysql service using the Init service provider by Chef

service 'mysql' do
  provider Chef::Provider::Service::Init
  supports status: true
  action [:start]
end

Step6: Test the cookbook

kitchen converge

Uploaded the Output at gist location.

Step7: Validate the mysql service

kitchen exec -c "mysql -h 127.0.0.1 -uroot  -s -e 'show databases;'"

Output:

-----> Execute command on default-tknerr-baseimage-ubuntu-1404.
       Database
       information_schema
       mysql
       performance_schema

kitchen exec -c "sudo service mysql status"

Output:

-----> Execute command on default-tknerr-baseimage-ubuntu-1404.
        * /usr/bin/mysqladmin  Ver 8.42 Distrib 5.5.57, for debian-linux-gnu on x86_64
       Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.


       Oracle is a registered trademark of Oracle Corporation and/or its
       affiliates. Other names may be trademarks of their respective
       owners.


       Server version           5.5.57-0ubuntu0.14.04.1
       Protocol version 10
       Connection               Localhost via UNIX socket
       UNIX socket              /var/run/mysqld/mysqld.sock
       Uptime:                  5 min 0 sec


       Threads: 1  Questions: 119  Slow queries: 0  Opens: 48  Flush tables: 1  Open tables: 41  Queries per second avg: 0.396

Cookbook uploaded at following GitHub location.

Hope you enjoyed reading this article. Thank you..

How to setup MySQL Server using Chef Cookbook