mysql
Version information
This version is compatible with:
- Puppet Enterprise 2023.5.x, 2023.4.x, 2023.3.x, 2023.2.x, 2023.1.x, 2023.0.x, 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
Add this module to your Puppetfile:
mod 'eyp-mysql', '0.4.14'
Learn more about managing modules with a PuppetfileDocumentation
mysql
Table of Contents
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
- Fork it
- Create your feature branch (
git checkout -b my-new-feature
) - Commit your changes (
git commit -am 'Added some feature'
) - Push to the branch (
git push origin my-new-feature
) - Create new Pull Request
Types in this module release
CHANGELOG
0.4.14
- bugfix: possible dependency cycle
0.4.13
- added sql_mode and lower_case_table_names to mysqld section
- fixed unless and onlyif for mysql_sql
- moved backup scripts to files folder
- minor bugfix mysqldump script
- added masterdata and file_per_db options to mysql::backup::mysqldump
0.4.12
- bugfix backup_xtrabackup script (no cleanup was performed when MAILTO was undefined)
0.4.11
- added mysql::tools::perconatoolkit
0.4.10
- added Ubuntu14 (sys-v) compatibility for mysqlcommunity and xtradbcluster
0.4.9
- bugfix: xtradbcluster binlogdir
0.4.8
- changed /var/mysql and instancedir mode from 0750 to 0755
- moved socket from datadir to instancedir
- bugfix: binlog filename
0.4.7
- added client connect info using a group in the global my.cnf for mysqlcommunity
0.4.6
- added client connect info using a group in the global my.cnf for xtradbcluster
0.4.5
- added options to changed uid and gid for mysql user and group
0.4.4
- bugfix: fixed xtrabackup and mysqldump scripts to use the proper my.cnf file
0.4.3
- bugfix: Added a dependency for mysql_install_db for mysql::community::instance
- added gmcast_listen_addr variable for single node XtraDB clusters (sic)
- added percona repo support for CentOS
0.4.2
- added mysql user and group under puppet management
- added percona repo support
- INCOMPATIBLE CHANGE: xtrabackup is now installed using perconarepo
- added support for xtrabackup in Ubuntu 16.04
- added Percona XtraDB cluster support
- INCOMPATIBLE CHANGE: added mysql::pid_location to define where to store instance's pid file
- bugfix: default pid_location for CentOS 7 is datadir (instead of run) due to this bug: Bug 1428110 - RFE: RuntimeDirectory= should resolve %i and other specifiers
0.4.1
INCOMPATIBLE WITH PREVIOUS VERSIONS: major rewrite, not intended to be compatible with eyp-mysql 0.3 or lower in any way
- mysql community: multi instance
- dropped CentOS 5 support
0.3
- INCOMPATIBLE CHANGE: backup scripts moved to namespace mysql::backup:
- mysql::backupmysqldump moved to mysql::backup::mysqldump
- added backup wrapper for xtrabackup (versions 2.0.8 and 2.4.4)
Dependencies
- puppetlabs/stdlib (>= 1.0.0 < 9.9.9)
- puppetlabs/concat (>= 1.2.3 < 9.9.9)
- eyp/eyplib (>= 0.1.0 < 0.2.0)
- eyp/systemd (>= 0.1.25 < 0.2.0)
- eyp/initscript (>= 0.1.2 < 0.2.0)