Forge Home

mysql

multi instance mysql community and Percona XtraDB cluster (galera)

jordi prats

eyp

7,435 downloads

5,583 latest version

5.0 quality score

Version information

  • 0.4.14 (latest)
  • 0.4.13
  • 0.4.11
released Dec 21st 2017
This version is compatible with:
  • Puppet Enterprise 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
  • RedHat
    ,
    CentOS
    ,
    Scientific
    ,
    OEL
    ,
    OracleLinux
    ,
    Ubuntu

Start using this module

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