Forge Home

mysql

multi instance mysql community and Percona XtraDB cluster (galera)

8,322 downloads

6,430 latest version

5.0 quality score

We run a couple of automated
scans to help you access a
module's quality. Each module is
given a score based on how well
the author has formatted their
code and documentation and
modules are also checked for
malware using VirusTotal.

Please note, the information below
is for guidance only and neither of
these methods should be considered
an endorsement by Puppet.

Version information

  • 0.4.14 (latest)
  • 0.4.13
  • 0.4.11
released Dec 21st 2017
This version is compatible with:
  • Puppet Enterprise 2021.7.x, 2021.6.x, 2021.5.x, 2021.4.x, 2021.3.x, 2021.2.x, 2021.1.x, 2021.0.x, 2019.8.x, 2019.7.x, 2019.5.x, 2019.4.x, 2019.3.x, 2019.2.x, 2019.1.x, 2019.0.x, 2018.1.x, 2017.3.x, 2017.2.x, 2017.1.x, 2016.5.x, 2016.4.x
  • Puppet >= 3.4.0
  • , , , , ,

Start using this module

  • r10k or Code Manager
  • Bolt
  • Manual installation
  • Direct download

Add this module to your Puppetfile:

mod 'eyp-mysql', '0.4.14'
Learn more about managing modules with a Puppetfile

Add this module to your Bolt project:

bolt module add eyp-mysql
Learn more about using this module with an existing project

Manually install this module globally with Puppet module tool:

puppet module install eyp-mysql --version 0.4.14

Direct download is not typically how you would use a Puppet module to manage your infrastructure, but you may want to download the module in order to inspect the code.

Download

Documentation

eyp/mysql — version 0.4.14 Dec 21st 2017

mysql

Table of Contents

  1. Overview
  2. Module Description
  3. Setup
  4. Usage
  5. Reference
  6. Limitations
  7. Development

Overview

multi instance / multi flavor MySQL management

Module Description

multi instance / multi flavor MySQL management:

  • MySQL Community
  • XtraDB Cluster (percona's Galera)

backup strategies:

  • mysqldump: grants + 1 file per DB
  • xtrabackup: full mode only

Setup

What mysql affects

  • installs percona repo (if needed)
  • installs MySQL packages
  • manages configuration files (/etc/mysql) - it's not possible to have a unmanaged MySQL instance

Setup Requirements

This module requires pluginsync enabled

Beginning with mysql

mysql::community::instance { 'test':
  port              => '3306',
  password          => 'password',
  add_default_mycnf => true,
}

mysql::community::instance { 'test2':
  port              => '3307',
  password          => 'password',
  add_default_mycnf => true,
}

Usage

single node XtraDB demo cluster

mysql::xtradbcluster::instance { 'cluster1':
  cluster_name       => 'galera',
  port                         => '3306',
  bootstrap          => true,
  password           => 'password',
  serverid           => '1',
  default_instance   => true,
  cluster_list       => [ '192.168.56.103:4568' ],
  sst_username       => 'sstuser',
  sst_password       => 'sstpassw0rd',
  node_address       => '192.168.56.103',
  gmcast_listen_addr => 'tcp://0.0.0.0:4567'
}

->

mysql::xtradbcluster::instance { 'cluster2':
  cluster_name       => 'galera',
  port                         => '3307',
  bootstrap          => false,
  password           => 'password',
  serverid           => '2',
  default_instance   => false,
  cluster_list       => [ '192.168.56.103:4567' ],
  sst_username       => 'sstuser',
  sst_password       => 'sstpassw0rd',
  node_address       => '192.168.56.103',
  gmcast_listen_addr => 'tcp://0.0.0.0:4568'
}

Note: Once cluster is bootstraped we need to change bootstrap to false in the primary node

backup scripts

install backup script using xtrabackup tool for cluster1 instance:

mysql::backup::xtrabackup { 'cluster1':
  destination => '/backup',
}

install backup script for galera instance using hiera:

xtrabackup:
  'galera':
    hour: '3'
    minute: '0'
    destination: '/var/mysql/backup'
    idhost: 'EXAMPLE001'
    mailto: 'backups@backups.systemadmin.es'
    retention: '5'

misc

run SQL query

mysql_sql { 'caca':
  command => 'select version()',
  instance_name => 'test',
}

create database

mysql::database { 'provaprova': }

Reference

classes

mysql::perconarepo

percona repo installation

  • srcdir: = '/usr/local/src',
  • package_ensure: = 'installed',

mysql::tools::perconatoolkit

percona toolkit installation

  • package_ensure: = 'installed',

mysql::tools::innochecksum

ibdata inspector

  • binpath: place to install innochecksum tool (default: /usr/local/bin/innochecksum)

defines

mysql::xtradbcluster::instance

mysql::community::instance

mysql::backup::xtrabackup

  • general options:
    • destination:
    • retention: = undef,
    • logdir: = undef,
    • mailto: = undef,
    • idhost: = undef,
    • backupscript: backup script path (default: /usr/local/bin/backup_xtrabackup)
    • backupid: = 'MySQL',
    • xtrabackup_version: xtrabackup version to install (default: 2.4.4)
  • full backup related variables:
    • fullbackup_monthday: day of month to perform full backups, space padded (default: undef) - INCOMPATIBLE with fullbackup_weekday
    • fullbackup_weekday: day of week (1..7) to perform full backups; 1 is Monday (default: undef) - INCOMPATIBLE with fullbackup_monthday
  • cronjob related variables:
    • hour: = '2',
    • minute: = '0',
    • month: = undef,
    • monthday: = undef,
    • weekday: = undef,
    • setcron: = true,

mysql::backup::mysqldump

  • destination: ,
  • instance: = $name,
  • retention: = undef,
  • logdir: = undef,
  • compress: = true,
  • mailto: = undef,
  • idhost: = undef,
  • backupscript: = '/usr/local/bin/backupmysqldump',
  • hour: = '2',
  • minute: = '0',
  • month: = undef,
  • monthday: = undef,
  • weekday: = undef,
  • setcron: = true,

mysql::mycnf::client

  • instance_name: = $name,
  • client_name: = $name,
  • default: = false,
  • password: = undef,
  • socket: = undef,

mysql::mycnf::mysqld

  • instance_name: = $name,
  • skip_external_locking: = $mysql::params::skip_external_locking_default,
  • tmpdir: = $mysql::params::tmpdir_default,
  • port: = '3306',
  • pidfile: = undef,
  • datadir: = "/var/mysql/${name}",
  • relaylogdir: = "/var/mysql/${name}/relaylogs",
  • binlogdir: = "/var/mysql/${name}/binlogs",
  • default_storage_engine: = 'InnoDB',
  • ignoreclientcharset: = true,
  • charset: = 'utf8',
  • readonly: = false,
  • key_buffer_size: = $mysql::params::key_buffer_size_default,
  • sysdate_is_now: = true,
  • max_allowed_packet: = '16M',
  • max_connect_errors: = '1000000',
  • nameresolve: = false,
  • innodb: = 'FORCE',
  • expirelogsdays: = '5',
  • binlog_format: = 'MIXED',
  • sync_binlog: = true,
  • serverid: = '1',
  • max_binlog_size: = '1073741824',
  • log_bin_trust_function_creators: = false,
  • slave: = false,
  • max_relay_log_size: = '0',
  • replicate_ignore_db: = [],
  • max_heap_table_size: = '32M',
  • tmp_table_size: = '32M',
  • query_cache_type: = '0',
  • query_cache_size: = '0',
  • query_cache_limit: = '1048576',
  • max_connections: = '500',
  • max_user_connections: = '0',
  • thread_cache_size: = '50',
  • open_files_limit: = '65535',
  • table_definition_cache: = '4096',
  • table_open_cache: = '100',
  • sort_buffer_size: = '262144',
  • join_buffer_size: = '131072',
  • innodb_flush_method: = 'O_DIRECT',
  • innodb_log_files_in_group: = '2',
  • innodb_log_file_size: = '50331648',
  • innodb_flush_log_at_trx_commit: = '2',
  • innodb_file_per_table: = true,
  • innodb_buffer_pool_size: = ceiling(sprintf('%f', $::memorysize_mb)*838860),
  • innodb_autoinc_lock_mode: = undef,
  • log_queries_not_using_indexes: = false,
  • slow_query_log: = true,
  • log_error: = "/var/log/mysql/${name}/mysql-error.log",
  • slow_query_log_file: = "/var/log/mysql/${name}/mysql-slow.log",

mysql::mycnf::galera

  • wsrep_node_address: = $::ipaddress,
  • wsrep_cluster_address: = [],
  • instance_name: = $name,
  • wsrep_provider: = '/usr/lib/libgalera_smm.so',
  • wsrep_sst_method: = 'xtrabackup-v2',
  • wsrep_cluster_name: = 'my_wsrep_cluster',
  • wsrep_sst_auth_username: = 'dmlzY2EK',
  • wsrep_sst_auth_password: = 'Y2F0YWx1bnlhCg',
  • wsrep_dirty_reads: = false,
  • wsrep_desync: = false,
  • wsrep_reject_queries: = 'NONE',
  • wsrep_sst_donor: = undef,
  • srep_sst_donor_rejects_queries: = false,
  • gmcast_listen_addr: = 'tcp://0.0.0.0:4567',

Limitations

Tested on:

  • Ubuntu 16.04
  • Ubuntu 14.04
  • CentOS 6
  • CentOS 7

Development

We are pushing to have acceptance testing in place, so any new feature should have some test to check both presence and absence of any feature

TODO

  • xtrabackup: incremental mode
  • On Ubuntu fails to install because packages are starting the service before being configured. Should be installed using RUNLEVEL=1 (puppet package provider does not support environment variables) or to use a similar approach

Contributing

  1. Fork it
  2. Create your feature branch (git checkout -b my-new-feature)
  3. Commit your changes (git commit -am 'Added some feature')
  4. Push to the branch (git push origin my-new-feature)
  5. Create new Pull Request