Adoptable Cookbooks List

Looking for a cookbook to adopt? You can now see a list of cookbooks available for adoption!
List of Adoptable Cookbooks

Supermarket Belongs to the Community

Supermarket belongs to the community. While Chef has the responsibility to keep it running and be stewards of its functionality, what it does and how it works is driven by the community. The chef/supermarket repository will continue to be where development of the Supermarket application takes place. Come be part of shaping the direction of Supermarket by opening issues and pull requests or by joining us on the Chef Mailing List.

Select Badges

Select Supported Platforms

Select Status


mysql_tuning (10) Versions 0.7.0

Tries to create MySQL configuraiton better suited for your system.

cookbook 'mysql_tuning', '= 0.7.0', :supermarket
cookbook 'mysql_tuning', '= 0.7.0'
knife supermarket install mysql_tuning
knife supermarket download mysql_tuning
Quality 71%

MySQL Tuning Cookbook

Cookbook Version

Dependency Status
Code Climate
Build Status

This Chef cookbook creates a generic MySQL server configuration, presumably more optimized for your current machine than the default configuration.

Of course, depending on your application your requirements may change and MySQL is a really complex application. So, in some cases, this cookbook will not help you much. But hopefully may serve as a point of departure.

Warning: This cookbook will not configure MySQL for you. Use it with care. But if you have ideas to improve it, you are more than welcome \(^o^)/.


Supported Platforms

This cookbook has been tested on the following platforms:

  • Amazon
  • CentOS
  • Debian
  • Fedora
  • FreeBSD
  • Oracle Linux
  • Red Hat
  • Scientific Linux
  • Ubuntu

Please, let us know if you use it successfully on any other platform.

Required Cookbooks

To use it with older mysql or ohai cookbook versions look at the following table:

ohai \ mysql 5 6 7
any 0.2
2 0.3 0.4
3 0.5 0.6
4 0.7

For example, if you need to use the mysql cookbook version 5 and ohai cookbook version 2, try using the cookbook version 0.3:

# metadata.rb

depends 'mysql', '~> 5.0'
depends 'ohai', '~> 2.0'
depends 'mysql_tuning', '~> 0.3.0' # old unmaintained cookbook version

Required Applications

  • Chef 12 or higher.
  • Ruby 2.2 or higher.
  • MySQL 5.0 or higher.


Using with MySQL Cookbook

This cookbook has been created to be used mainly with the Chef's official MySQL cookbook. The MySQL cookbook must be included before calling this cookbook recipes or using the resources:

service_name = 'default'

mysql_service service_name do
  action [:create, :start]

mysql_tuning service_name

Other MySQL Cookbooks

It could also work with other cookbooks. The only requirement is that the used MySQL cookbook creates an included directory in the MySQL configuration file. For example:

# my.cnf
!includedir /etc/mysql/conf.d

Then, make sure that this directory is correctly set in the node['mysql_tuning']['include_dir'] attribute. You may also need to set the node['mysql_tuning']['recipe'] and the node['mysql']['service_name'] attribute (or the mysql_tuning#service_name parameter).

The official MySQL cookbook takes care of adding the includedir itself and should work out of the box.

Configured Variables

This cookbook will try to set some variable values depending mainly on the system memory.

The following variables will be configured by default inside tuning.cnf:

  • mysqld
    • key_buffer_size
    • max_allowed_packet
    • table_open_cache
    • sort_buffer_size
    • read_buffer_size
    • read_rnd_buffer_size
    • join_buffer_size
    • net_buffer_length
    • myisam_sort_buffer_size
    • bulk_insert_buffer_size
    • myisam_max_sort_file_size
    • thread_stack
    • query_cache_size
    • query_cache_limit
    • binlog_cache_size
    • max_allowed_packet
    • thread_cache_size
    • innodb_buffer_pool_size
    • innodb_additional_mem_pool_size
    • innodb_log_buffer_size
    • innodb_log_files_in_group
    • innodb_lock_wait_timeout
    • innodb_write_io_threads
    • innodb_read_io_threads
    • innodb_max_dirty_pages_pct
    • max_connections
    • max_connect_errors
    • max_heap_table_size
    • tmp_table_size
  • mysqldump
    • quick
    • max_allowed_packet
  • mysql
    • no-auto-rehash
  • myisamchk
    • key_buffer_size
    • sort_buffer_size
    • read_buffer
    • write_buffer
  • mysqlhotcopy
    • interactive-timeout
  • mysqld_safe
    • open-files-limit

The following variables will be configured by default inside logging.cnf:

  • mysqld
    • expire_logs_days
    • slow_query_log
    • slow_query_log_file

Creating Your Own Configuration Files

This cookbook creates the following configuration files by default:

  • tuning.cnf: This configuration file will be calculated from samples in node['mysql_tuning']['configuration_samples'].
  • logging.cnf: This configuration file will set some log options, read from node['mysql_tuning']['logging.cnf'].

You can create your own configuration files using the following attribute format: node['mysql_tuning']["#{filename}.cnf"].

But you can also change the tuning.cnf (or logging.cnf) variables by setting them in the corresponding attribute:

node.default['mysql_tuning']['tuning.cnf']['mysqld']['query_cache_size'] = 0

If you want to enable a boolean variable (those with <span>skip-</span> prefix), you can set it to true (or false to disable it):

node.default['mysql_tuning']['tuning.cnf']['mysqld']['skip-innodb'] = true
node.default['mysql_tuning']['tuning.cnf']['mysqld']['skip-name-resolve'] = true

For those variables that have different names in different versions of MySQL, the node['mysql_tuning']['old_names'] attribute will try to help you. This last attribute comes with a recommended default value.

Configuration Variables Interpolation

MySQL variable values can be interpolated from configuration samples. The default samples are in node['mysql_tuning']['configuration_samples'] and are based on MySQL 5.5.38 example configuration files. These samples will be used to generate the tuning.cnf configuration file.

You can avoid the interpolation of some variables by setting them directly in the node['mysql_tuning']['tuning.cnf'] attribute:

node.default['mysql_tuning']['tuning.cnf']['mysqld']['table_open_cache'] = 200

This cookbook will use 'proximal' interpolation by default. You can change the algorithm used with the node['mysql_tuning']['interpolation'] attribute. Be careful when using this feature because it should be considered experimental.

Currently, the following algorithms are supported:

query_cache_size Interpolation Chart

There are some charts for variables generated from configuration samples here.

You can use different interpolation algorithms for some variables by setting them in the node['mysql_tuning']['interpolation_by_variable'] attribute or the mysql_tuning#interpolation_by_variable resource parameter. This attribute has the following structure: interpolation_by_variable[variable_name]. For example:

  ['interpolation_by_variable']['key_buffer_size'] = 'catmull'
  ['interpolation_by_variable']['thread_stack'] = 'proximal'
include_recipe 'mysql_tuning::default'

Using the resource, it would be as follows:

mysql_tuning 'default' do
  interpolation 'linear'
    key_buffer_size: 'catmull',
    thread_stack: 'proximal'

Dynamic Configuration

When there are configuration changes, this cookbook can try to set the configuration values without restarting the MySQL server. The cookbook will go for each variable and try to set it dynamically. If any of the variables cannot be changed, the MySQL server will be restarted.

If your MySQL password is not in the node['mysql']['server_root_password'] attribute, you must use the mysql_tuning resource and set the MySQL user and password to the correct values instead of calling the mysql_tuning::default recipe. For example:

mysql_tuning 'default' do
  mysql_user 'root'
  mysql_password 'PWMzIv4ACtwhbNx9VF8wumsuVIAVVMTzE8$N#,t0'

This code will do the interpolations and generate all the configuration files like the mysql_tuning::default recipe. The user must have SUPER privileges in the MySQL server.

This feature is disabled by default because it is considered a bit experimental. You must set node['mysql_tuning']['dynamic_configuration'] attribute to true to enable it.

Ohai Plugin

The mysql_tuning::ohai_plugin recipe installs an Ohai plugin for MySQL. This recipe will install and enable the plugin automatically.

It will set the following attributes:

  • node['mysql']['installed_version']: Installed MySQL version.

This is an output example:

"mysql": {
  "installed_version": "5.5.38"

Keep in mind that this plugin will not be enabled by the mysql_tuning::default recipe. You need to use the mysql_tuning::ohai_plugin if you want to enable it.


Attribute Default Description
node['mysql_tuning']['system_percentage'] 100 System percentage used for MySQL. Use 100 for MySQL dedicated servers.
node['mysql_tuning']['dynamic_configuration'] false Tries to change the MySQL configuration without restarting the server, setting variable values dynamically See above.
node['mysql_tuning']['interpolation'] 'proximal' Interpolation algorithm to use. Possible values: 'proximal', 'linear', 'cubic', 'bicubic', 'catmull' See above.
node['mysql_tuning']['interpolation_by_variable'] {} Use different interpolation algorithms for some variables See above.
node['mysql_tuning']['recipe'] nil MySQL recipe name, required if not included beforehand.
node['mysql_tuning']['include_dir'] calculated MySQL configuration include directory.
node['mysql_tuning']['mysqld_bin'] calculated MySQL daemon binary path.
node['mysql_tuning']['logging.cnf'] calculated MySQL logging.cnf configuration.
node['mysql_tuning']['tuning.cnf'] calculated MySQL tuning.cnf configuration.
node['mysql_tuning']['configuration_samples'] calculated MySQL configuration samples.
node['mysql_tuning']['old_names'] calculated MySQL configuration variable old names hash. (internal)
node['mysql_tuning']['non_interpolated_keys'] calculated MySQL keys that should not be interpolated. (internal)
node['mysql_tuning']['variables_block_size'] calculated MySQL variables block size. (internal)



Creates MySQL configuration files. Uses the mysql_tuning resource.


Enables MySQL ohai plugin (optional).



Creates MySQL configuration files:

  • tuning.cnf: This configuration file will be calculated from samples.
  • logging.cnf: This configuration file will set some log options, read from node['mysql_tuning']['logging.cnf'].
  • <span>*.cnf</span>: You can create your own configuration files setting them in node['mysql_tuning']["#{filename}.cnf"].

See above for more information.

mysql_tuning Actions

  • create: Creates configuration files.
  • delete: Deletes configuration files.

mysql_tuning Parameters

Parameter Default Description
service_name name parameter MySQL service name, recommended to notify the restarts. See below.
include_dir node['mysql_tuning']['include_dir'] MySQL configuration directory.
interpolation node['mysql_tuning']['interpolation'] MySQL interpolation type used.
interpolation_by_variable {} Use different interpolation algorithms for some variables See above.
configuration_samples node['mysql_tuning']['configuration_samples'] MySQL tuning configuration samples.
mysql_user 'root' MySQL login user.MySQL login user.
mysql_password node['mysql']['server_root_password'] MySQL login password. Required mainly if you enable dynamic configuration and change the default password.
mysql_port node['mysql']['port'] MySQL port.

mysql_tuning Name Parameter

The mysql_tuning resource name is the MySQL Chef service_name, like for example "default" or "mysql_service[default]". In most cases this will be"default"`.

Service type ("mysql_service") is added if not specified, assuming that the official MySQL cookbook is used. The MySQL official cookbook uses "default" as service name by default.

For example, using the official MySQL cookbook:

service_name = 'default'

mysql_service service_name
mysql_tuning service_name


Creates a MySQL configuration file.

Restarts the server only when required. Tries to set the configuration without restarting if dynamic enabled.

mysql_tuning_cnf Actions

  • create: Creates the configuration file.
  • delete: Deletes the configuration file.

mysql_tuning_cnf Parameters

Parameter Default Description
filename name parameter Configuration file name.
service_name nil MySQL service name, recommended to notify the restarts.
include_dir node['mysql_tuning']['include_dir'] MySQL configuration directory.
dynamic node['mysql_tuning']['dynamic_configuration'] Whether to enable dynamic configuration. This tries to set the configuration without restarting the server.
values node['mysql_tuning'][filename] Configuration values as Hash.
persist true Whether to create the configuration file on disk.
mysql_user 'root' MySQL login user.
mysql_password node['mysql']['server_root_password'] MySQL login password. Required mainly if you enabled dynamic configuration and changed the default password.
mysql_port node['mysql']['port'] MySQL port.


Including in a Cookbook Recipe

You can simply include it in a recipe, after installing MySQL:

# in your recipe
node.default['mysql_tuning']['tuning.cnf']['mysqld']['table_open_cache'] = 520

mysql_service 'default'
include_recipe 'mysql_tuning::default'

Don't forget to include the mysql_tuning cookbook as a dependency in the metadata:

# metadata.rb
depends 'mysql'
depends 'mysql_tuning'

Including in the Run List

Another alternative is to include it in your Run List:

  "name": "",
  "[...]": "[...]",
  "normal": {
    "mysql_tuning": {
      "tuning.cnf": {
        "mysql": {
          "table_open_cache": 520
  "run_list": [

Using the mysql_service Resource

In case you want to use the official MySQL cookbook's mysql_service example:

mysql_root_password = 'r00t_p4ssw0rd'

# Set MySQL service resource name
service_name = 'default'

mysql_service service_name do
  initial_root_password mysql_root_password
  action [:create, :start]

# Pass the credentials to the mysql_tuning resource
mysql_tuning service_name do
  mysql_user 'root'
  mysql_password mysql_root_password

Generating and Using Encrypted MySQL Passwords

We need to use the mysql_service and mysql_tuning resources if we want to save the MySQL passwords encrypted.

In this example we are using the openssl and the encrypted_attributes cookbooks to generate and encrypt the MySQL credentials:

# Include the #secure_password method from the openssl cookbook
Chef::Recipe.send(:include, Opscode::OpenSSL::Password)

# Install Encrypted Attributes gem
include_recipe 'encrypted_attributes'

# Include the Encrypted Attributes cookbook helpers
Chef::Recipe.send(:include, Chef::EncryptedAttributesHelpers)

# We can use an attribute to enable or disable encryption
# (recommended for tests)
# self.encrypted_attributes_enabled = node['myapp']['encrypt_attributes']

# Encrypted Attributes will be generated randomly and saved in in the
# node['myapp']['mysql'] attribute encrypted.
def generate_mysql_password(user)
  key = "server_#{user}_password"
  encrypted_attribute_write(['myapp', 'mysql', key]) { secure_password }

# Generate the encrypted passwords
mysql_root_password = generate_mysql_password('root')

# Set MySQL service resource name
service_name = 'default'

mysql_service service_name do
  mysql_user 'root'
  mysql_password mysql_root_password
  action [:create, :start]

# Pass the root credentials to the mysql_tuning resource
mysql_tuning service_name do
  mysql_user 'root'
  mysql_password mysql_root_password

Reading Encrypted MySQL Passwords from Chef-Vault

Another secure solution is to read the passwords from a previously generated Chef-Vault bag item. The following example uses the chef-vault cookbook:

# Install chef-vault gem
include_recipe 'chef-vault'

# Read the secret from "dbsecrets" chef-vault
def read_mysql_password(user)
  chef_vault_item('dbsecrets', user)

mysql_root_password = read_mysql_password('root')

# Set MySQL service resource name
service_name = 'default'

# Read the encrypted passwords
mysql_service service_name do
  mysql_user 'root'
  mysql_password mysql_root_password
  action [:create, :start]

# Pass the root credentials to the mysql_tuning resource
mysql_tuning service_name do
  mysql_user 'root'
  mysql_password mysql_root_password

See the Chef-Vault documentation to learn how to create Chef Vault bags.

mysql_tuning::ohai_plugin Recipe Usage Example

In a recipe:

mysql_service 'default'
include_recipe 'mysql_tuning::ohai_plugin'



ChefSpec Matchers


Helper method for locating a mysql_tuning resource in the collection.

resource = chef_run.mysql_tuning('default')
expect(resource).to notify('service[apache2]').to(:restart)


Assert that the Chef run creates mysql_tuning.

expect(chef_run).to create_mysql_tuning('default')


Assert that the Chef run deletes mysql_tuning.

expect(chef_run).to delete_mysql_tuning('default')


Helper method for locating a mysql_tuning_cnf resource in the collection.

resource = chef_run.mysql_tuning_cnf('tuning.cnf')
expect(resource).to notify('service[apache2]').to(:restart)


Assert that the Chef run creates mysql_tuning_cnf.

expect(chef_run).to create_mysql_tuning_cnf('tuning.cnf')


Assert that the Chef run deletes mysql_tuning_cnf.

expect(chef_run).to delete_mysql_tuning_cnf('tuning.cnf')


Please do not hesitate to open an issue with any questions or problems.




License and Author

Author: Xabier de Zuazo (
Copyright: Copyright (c) 2015, Xabier de Zuazo
Copyright: Copyright (c) 2014-2015, Onddo Labs, SL.
License: Apache License, Version 2.0
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
See the License for the specific language governing permissions and
limitations under the License.

CHANGELOG for mysql_tuning

T his file is used to list changes made in each version of the mysql_tuning cookbook.

v0.7.0 (2016-10-15)

  • Update ohai cookbook to version 4.

v0.6.0 (2016-10-15)

  • Update mysql cookbook to version 7.

v0.5.0 (2016-10-14)

Breaking Changes on v0.5.0

  • Drop Chef 11 support.
  • Require Ruby 2.2 or higher.
  • Update ohai cookbook to version 3 (closes #3, #4, thanks David Brown for opening the issues).

Improvements on v0.5.0

  • Fix Ohai plugin warnings and improve its tests.
  • Add use_inline_resources to providers (fixes FC059).
  • Update RuboCop to 0.39: New offenses fixed.

Documentation Changes on v0.5.0

  • Update TESTING file.
  • README: Add GitHub and License badges.

Changes on Tests on v0.5.0

  • Travis CI: Update ChefDK to 0.18.30, gems and add Ruby 2.3.
  • Update foodcritic to version 6.
  • Update the Rakefile.
  • Update the Vagrantfile.
  • Disable integration tests on Debian.

v0.4.0 (2015-10-20)

Upgrading from a 0.3.x Cookbook Release

If you need to use the mysql cookbook version 5, try using the cookbook version 0.3.0:

# metadata.rb

# Use the mysql cookbook version 5
depends 'mysql', '~> 5.0'
depends 'mysql_tuning', '~> 0.3.0' # old unmaintained cookbook version

Breaking Changes on v0.4.0

  • Drop Ruby 1.9 support: Ruby 2 required.
  • Require mysql cookbook version 6.
    • Drop mysql cookbook version 5 support.

Fixes on v0.4.0

  • Fix include_dir attribute value with the mysql cookbook version 6.
  • Fix MySQL binary path on Fedora.
  • Fix myisamchk read buffer configuration for 4G.
  • Fix some features deprecated on Chef 13.

New Features on v0.4.0

  • Add Oracle Linux and Scientific Linux support.

Improvements on v0.4.0

  • Improve platform support using platform_family instead of platform.
  • Replace mysql_chef_gem dependency with mysql2_chef_gem (issue #1, thanks Dieter Blomme for reporting).
    • Update the libraries to use the mysql2 gem.
  • Update foodcritic to version 5.
  • Update RuboCop to version 0.34.
  • RuboCop: Fix offenses on ohai plugin templates.

Documentation Changes on v0.4.0

  • metadata: Add source_url and issues_url links.
  • Update chef links to use domain.
  • Update contact information and links after migration.
    • Improve title and description.
    • Some improvements.
    • Fix RuboCop offenses in examples.
    • Fix json examples.

Changes on Tests on v0.4.0

  • Run tests against Chef 11 and Chef 12.
  • Move ChefSpec tests from spec/ to test/unit/.
  • Integration tests: Add /usr/sbin/mysqld path.
  • Update .kitchen.yml file platforms.
  • Update Berkshelf to version 4.
  • Travis CI: Run tests on Ruby 2.2.
  • Integrate tests with should_not gem.
  • Integrate tests with coveralls.
  • Update Gemfile and Rakefile files.
  • Add .kitchen.docker.yml file to run integration tests on Docker.
  • Replace bats integration tests with Serverspec tests.
  • Run test-kitchen with Travis CI native Docker support.

v0.3.0 (2014-12-18)

  • Fix Chef 12 support.
  • Fix MySQL cookbook 6 support.
  • metadata:
    • mysql-chef_gem cookbook < 2.
    • ohai cookbook ~> 2.0.
  • Update RuboCop to 0.28.0.
  • Gemfile:
    • Use fixed foodcritic and RuboCop versions.
    • Update vagrant-wrapper version 2.
  • README: s/name attribute/name parameter/.
  • Update to use Digital Ocean Access Token.

v0.2.1 (2014-10-19)

  • Add .rubocop.yml.
  • ChefSpec :define_matcher check fix.

v0.2.0 (2014-10-14)

  • mysql_tuning[name] renamed to mysql_tuning[service_name] (breaking change).
  • Fix mysql.cnf.erb file not found error: set cookbook property to the template.
  • Fix mysql_tuning LWRP to pass the MySQL credentials correctly, added the mysql_tuning_test cookbook.
  • Use "default" for mysql_tuning resource name by default.
  • MysqlHelpers::Cnf avoid rounding the variable value if the key should be ignored.
  • Fix CentOS 7 support.
  • Fix FreeBSD integration tests.
  • ::ohai_plugin: avoid setting the group, fix FreeBSD support.
  • Improve MySQL version parsing, fixes CentOS 5.
  • Fix ohai plugin to work with mysql cookbook version 5.5.
  • Fix LWRP resource notifications.
  • Fix RuboCop offenses.
  • FC024: Consider adding platform equivalents.
  • ChefSpec matchers: added helper methods to locate LWRP resources.
  • Add ChefSpec tests for ohai_plugin recipe.
  • ChefSpec updated to 4.1.
  • Add Vagrantfile.
  • Gemfile:
    • Missing utf-8 encoding comment.
    • Updated and refactored to use style, unit and integration groups.
    • Replace vagrant gem by vagrant-wrapper.
    • Berkshelf updated to 3.1.
  • Berkfile: use a generic Berksfile template.
  • Add Guardfile.
  • Rakefile:
    • Only include kitchen if required.
    • Add documentation link.
  • travis.yml: exclude some groups from bundle.
  • spec_helper: set default platform and version.
  • Add license header file to all ruby files.
    • Add example using the mysql_service resource, encrypted attributes and chef-vault.
    • Change tables to use Markdown format.
  • CONTRIBUTING: tests before changes.
  • TODO: use checkboxes.
  • Some small documentation fixes.
  • TESTING: add interpolator gem requirement.

v0.1.1 (2014-07-27)

    • Use hex enttities for "" to avoid replacing it by sup tag.
    • Use code tags for recipe names.
    • README, CONTRIBUTING: some fixes.

v0.1.0 (2014-07-21)

  • Initial release of mysql_tuning.

Collaborator Number Metric

0.7.0 failed this metric

Failure: Cookbook has 1 collaborators. A cookbook must have at least 2 collaborators to pass this metric.

Contributing File Metric

0.7.0 passed this metric

Foodcritic Metric

0.7.0 failed this metric

FC066: Ensure chef_version is set in metadata: mysql_tuning/metadata.rb:1
FC069: Ensure standardized license defined in metadata: mysql_tuning/metadata.rb:1
FC072: Metadata should not contain "attribute" keyword: mysql_tuning/metadata.rb:1
FC074: LWRP should use DSL to define resource's default action: mysql_tuning/resources/cnf.rb:1
FC074: LWRP should use DSL to define resource's default action: mysql_tuning/resources/default.rb:1
Run with Foodcritic Version 10.3.1 with tags metadata,correctness ~FC031 ~FC045 and failure tags any

License Metric

0.7.0 passed this metric

No Binaries Metric

0.7.0 passed this metric

Testing File Metric

0.7.0 passed this metric

Version Tag Metric

0.7.0 passed this metric