diff options
| -rw-r--r-- | files/munin/mysql_size_all | 179 | ||||
| -rw-r--r-- | lib/puppet/provider/mysql_grant/mysql.rb | 107 | ||||
| -rw-r--r-- | lib/puppet/type/mysql_grant.rb | 10 | ||||
| -rw-r--r-- | manifests/client/perl.pp | 7 | ||||
| -rw-r--r-- | manifests/client/perl/debian.pp | 6 | ||||
| -rw-r--r-- | manifests/server/base.pp | 2 | ||||
| -rw-r--r-- | manifests/server/munin/base.pp | 20 | ||||
| -rw-r--r-- | manifests/server/munin/debian.pp | 17 | ||||
| -rw-r--r-- | manifests/server/nagios.pp | 2 |
9 files changed, 328 insertions, 22 deletions
diff --git a/files/munin/mysql_size_all b/files/munin/mysql_size_all new file mode 100644 index 0000000..f5954ad --- /dev/null +++ b/files/munin/mysql_size_all @@ -0,0 +1,179 @@ +#!/usr/bin/perl +# +# Copyright (C) 2007 - Rodolphe Quiedeville <rodolphe@quiedeville.org> +# Copyright (C) 2003-2004 - Andreas Buer +# +# This program is free software; you can redistribute it and/or +# modify it under the terms of the GNU General Public License +# as published by the Free Software Foundation; version 2 dated June, +# 1991. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. +# +# $Log$ +# Revision 1.1 2007/01/17 10:41:01 rodo +# Change incorrect family +# +# Revision 1.0 2007/01/16 15:57:01 rodo +# Created by Rodolphe Quiedeville +# +# Parameters: +# +# config +# autoconf +# +# Configuration variables +# +# mysqlopts - Options to pass to mysql +# mysqladmin - Override location of mysqladmin +# +#%# family=manual +#%# capabilities=autoconf + +use strict; + +# unless ($0 =~ /mysql_size(?:_([^_]+)|)_(.+)\s*$/) +# { +# die "Could not parse name $0.\n"; +# } +# my $db = $2; + +my $COMMAND; +my $MYSQLADMIN = $ENV{mysqladmin} || "mysql"; + +my %WANTED = ( "Index" => "index", + "Datas" => "datas", + ); + +my $arg = shift(); + +if ($arg eq 'config') { + print_config(); + exit(); +} elsif ($arg eq 'autoconf') { + unless (test_service() ) { + print "yes\n"; + } else { + print "no\n"; + } + exit; +} + +sub getDBList; +foreach my $db (getDBList()) { + + my $datas = 0; + my $indexes = 0; + my (@infos,$info,$i_data,$i_index); + + $COMMAND = "$MYSQLADMIN $ENV{mysqlopts} $db -e 'show table status;' | head -n 1"; + + open(SERVICE, "$COMMAND |") + or die("Coult not execute '$COMMAND': $!"); + + while (<SERVICE>) { + (@infos) = split; + } + close(SERVICE); + + my $i = 0; + foreach $info (@infos) { + $i++; + if ($info eq 'Data_length') { + $i_data = $i; + next; + } + if ($info eq 'Index_length') { + $i_index = $i; + last; + } + } + my $total_size = 0; + if ($i_data>0 && $i_index>0) { + $COMMAND = "$MYSQLADMIN $ENV{mysqlopts} $db -e 'show table status;' | cut -f $i_data,$i_index | grep -v leng"; + + open(SERVICE, "$COMMAND |") + or die("Coult not execute '$COMMAND': $!"); + + while (<SERVICE>) { + (m/(\d+).*?(\d+(?:\.\d+)?)/); + $datas += $1; + $indexes += $2; + } + close(SERVICE); + + $total_size = $datas+$indexes; + } + print("$db.value $total_size\n"); +# print("datas.value $datas\n"); +# print("index.value $indexes\n"); +} + + +sub print_config { + + my $num = 0; + + my @dbs = getDBList; + + print("graph_title MySQL databases size\n"); + print ('graph_args --base 1024 -l 0 +graph_vlabel bytes +graph_category mysql +graph_info Plugin available at <a href="http://rodolphe.quiedeville.org/hack/munin/">http://rodolphe.quiedeville.org/hack/munin/</a> +'); + + for my $db (@dbs) { + my $title = "$db"; + print("$title.label ${title}\n", + "$title.min 0\n", + "$title.type GAUGE\n", + "$title.draw ", ($num) ? "STACK" : "AREA" , "\n", + ); + $num++; + } +} + + +sub test_service { + + my $return = 1; + + system ("$MYSQLADMIN --version >/dev/null 2>/dev/null"); + if ($? == 0) + { + system ("$COMMAND >/dev/null 2>/dev/null"); + if ($? == 0) + { + print "yes\n"; + $return = 0; + } + else + { + print "no (could not connect to mysql)\n"; + } + } + else + { + print "no (mysqladmin not found)\n"; + } + exit $return; +} + +sub getDBList { + my @dbs; + foreach my $f (glob("/var/lib/mysql/*")) { + if (-d $f) { + $f =~ s!.*/!!; + @dbs[$#dbs+1]=$f }; + } + return @dbs; +} + diff --git a/lib/puppet/provider/mysql_grant/mysql.rb b/lib/puppet/provider/mysql_grant/mysql.rb index 6582523..4c41e76 100644 --- a/lib/puppet/provider/mysql_grant/mysql.rb +++ b/lib/puppet/provider/mysql_grant/mysql.rb @@ -11,7 +11,7 @@ MYSQL_USER_PRIVS = [ :select_priv, :insert_priv, :update_priv, :delete_priv, :show_db_priv, :super_priv, :create_tmp_table_priv, :lock_tables_priv, :execute_priv, :repl_slave_priv, :repl_client_priv, :create_view_priv, :show_view_priv, :create_routine_priv, :alter_routine_priv, - :create_user_priv + :create_user_priv, :trigger_priv ] mysql_version = Facter.value(:mysql_version) if mysql_version =~ /^5.1/ && mysql_version.split('.').last.to_i >= 6 @@ -29,6 +29,12 @@ else ] end +MYSQL_TABLE_PRIVS = [ :select, :insert, :update, :delete, :create, :drop, + :references, :index, :alter +] + +MYSQL_COLUMN_PRIVS = [ :select_priv, :insert_priv, :update_priv, :references_priv ] + Puppet::Type.type(:mysql_grant).provide(:mysql) do desc "Uses mysql as database." @@ -42,7 +48,8 @@ Puppet::Type.type(:mysql_grant).provide(:mysql) do # this parses the def split_name(string) - matches = /^([^@]*)@([^\/]*)(\/(.*))?$/.match(string).captures.compact + matches = /^([^@]*)@([^\/]*)(\/([^\/]*))?(\/([^\/]*))?$/.match(string).captures.compact + case matches.length when 2 { @@ -57,6 +64,23 @@ Puppet::Type.type(:mysql_grant).provide(:mysql) do :host => matches[1], :db => matches[3] } + when 6 + { + :type => :tables_priv, + :user => matches[0], + :host => matches[1], + :db => matches[3], + :table_name => matches[5] + } + when 8 + { + :type => :table, + :user => matches[0], + :host => matches[1], + :db => matches[3], + :table => matches[5], + :column => matches[7] + } end end @@ -72,6 +96,10 @@ Puppet::Type.type(:mysql_grant).provide(:mysql) do mysql "mysql", "-e", "INSERT INTO db (host, user, db) VALUES ('%s', '%s', '%s')" % [ name[:host], name[:user], name[:db], ] + when :column + mysql "mysql", "-e", "INSERT INTO columns_priv (host, user, db, table, column_name) VALUES ('%s', '%s', '%s', '%s', '%s')" % [ + name[:host], name[:user], name[:db], name[:table], name[:column], + ] end mysql_flush end @@ -87,6 +115,9 @@ Puppet::Type.type(:mysql_grant).provide(:mysql) do if name[:type] == :db fields << :db end + if name[:type] == :column + fields << :column + end not mysql( "mysql", "-NBe", 'SELECT "1" FROM %s WHERE %s' % [ name[:type], fields.map do |f| "%s = '%s'" % [f, name[f]] end.join(' AND ')]).empty? end @@ -96,6 +127,10 @@ Puppet::Type.type(:mysql_grant).provide(:mysql) do MYSQL_USER_PRIVS when :db MYSQL_DB_PRIVS + when :tables_priv + MYSQL_TABLE_PRIVS + when :column + MYSQL_COLUMN_PRIVS end all_privs = all_privs.collect do |p| p.to_s end.sort.join("|") privs = privileges.collect do |p| p.to_s end.sort.join("|") @@ -112,24 +147,36 @@ Puppet::Type.type(:mysql_grant).provide(:mysql) do privs = mysql "mysql", "-Be", 'select * from user where user="%s" and host="%s"' % [ name[:user], name[:host] ] when :db privs = mysql "mysql", "-Be", 'select * from db where user="%s" and host="%s" and db="%s"' % [ name[:user], name[:host], name[:db] ] + when :tables_priv + privs = mysql "mysql", "-NBe", 'select Table_priv from tables_priv where User="%s" and Host="%s" and Db="%s" and Table_name="%s"' % [ name[:user], name[:host], name[:db], name[:table_name] ] + privs = privs.chomp.downcase + return privs + when :columns + privs = mysql "mysql", "-Be", 'select * from columns_priv where User="%s" and Host="%s" and Db="%s" and Table_name="%s" and Column_name="%s"' % [ name[:user], name[:host], name[:db], name[:table], name[:column] ] end if privs.match(/^$/) privs = [] # no result, no privs else + case name[:type] + when :user, :db # returns a line with field names and a line with values, each tab-separated - privs = privs.split(/\n/).map! do |l| l.chomp.split(/\t/) end - # transpose the lines, so we have key/value pairs - privs = privs[0].zip(privs[1]) - privs = privs.select do |p| p[0].match(/_priv$/) and p[1] == 'Y' end + privs = privs.split(/\n/).map! do |l| l.chomp.split(/\t/) end + # transpose the lines, so we have key/value pairs + privs = privs[0].zip(privs[1]) + privs = privs.select do |p| (/_priv$/) and p[1] == 'Y' end + privs.collect do |p| symbolize(p[0].downcase) end + end end - - privs.collect do |p| symbolize(p[0].downcase) end end def privileges=(privs) - unless row_exists? - create_row + name = split_name(@resource[:name]) + # don't need to create a row for tables_priv and columns_priv + if name[:type] == :user || name[:type] == :db + unless row_exists? + create_row + end end # puts "Setting privs: ", privs.join(", ") @@ -146,19 +193,49 @@ Puppet::Type.type(:mysql_grant).provide(:mysql) do stmt = 'update db set ' where = ' where user="%s" and host="%s"' % [ name[:user], name[:host] ] all_privs = MYSQL_DB_PRIVS + when :tables_priv + currently_set = privileges + currently_set = currently_set.scan(/\w+/) + privs.map! {|i| i.to_s.downcase} + revoke = currently_set - privs + + if !revoke.empty? + #puts "Revoking table privs: ", revoke + mysql "mysql", "-e", "REVOKE %s ON %s.%s FROM '%s'@'%s'" % [ revoke.join(", "), name[:db], name[:table_name], name[:user], name[:host] ] + end + + set = privs - currently_set + stmt = 'GRANT ' + where = ' ON %s.%s TO "%s"@"%s"' % [ name[:db], name[:table_name], name[:user], name[:host] ] + all_privs = MYSQL_TABLE_PRIVS + when :column + stmt = 'update columns_priv set ' + where = ' where user="%s" and host="%s" and Db="%s" and Table_name="%s"' % [ name[:user], name[:host], name[:db], name[:table_name] ] + all_privs = MYSQL_COLUMN_PRIVS end if privs[0] == :all privs = all_privs end - # puts "stmt:", stmt - set = all_privs.collect do |p| "%s = '%s'" % [p, privs.include?(p) ? 'Y' : 'N'] end.join(', ') - # puts "set:", set + #puts "stmt:", stmt + case name[:type] + when :user + set = all_privs.collect do |p| "%s = '%s'" % [p, privs.include?(p) ? 'Y' : 'N'] end.join(', ') + when :db + set = all_privs.collect do |p| "%s = '%s'" % [p, privs.include?(p) ? 'Y' : 'N'] end.join(', ') + when :tables_priv + set = set.join(', ') + end + + #puts "set:", set stmt = stmt << set << where + #puts "stmt:", stmt - mysql "mysql", "-Be", stmt - mysql_flush + if !set.empty? + mysql "mysql", "-Be", stmt + mysql_flush + end end end diff --git a/lib/puppet/type/mysql_grant.rb b/lib/puppet/type/mysql_grant.rb index 6bc7533..c6311e5 100644 --- a/lib/puppet/type/mysql_grant.rb +++ b/lib/puppet/type/mysql_grant.rb @@ -5,6 +5,16 @@ Puppet::Type.newtype(:mysql_grant) do #ensurable autorequire(:service) { 'mysqld' } + autorequire :mysql_table do + reqs = [] + matches = self[:name].match(/^([^@]*)@([^\/]*)\/(.+)\/(.+)$/) + unless matches.nil? + reqs << matches[4] + end + # puts "Autoreq: '%s'" % reqs.join(" ") + reqs + end + autorequire :mysql_db do # puts "Starting db autoreq for %s" % self[:name] reqs = [] diff --git a/manifests/client/perl.pp b/manifests/client/perl.pp new file mode 100644 index 0000000..09a790b --- /dev/null +++ b/manifests/client/perl.pp @@ -0,0 +1,7 @@ +class mysql::client::perl { + + case $operatingsystem { + debian: { include mysql::client::perl::debian } + } + +} diff --git a/manifests/client/perl/debian.pp b/manifests/client/perl/debian.pp new file mode 100644 index 0000000..790eaff --- /dev/null +++ b/manifests/client/perl/debian.pp @@ -0,0 +1,6 @@ +class mysql::client::perl::debian { + + package { 'libdbd-mysql-perl': + ensure => present, + } +} diff --git a/manifests/server/base.pp b/manifests/server/base.pp index eb66b43..75f5725 100644 --- a/manifests/server/base.pp +++ b/manifests/server/base.pp @@ -52,7 +52,7 @@ class mysql::server::base { exec { 'mysql_set_rootpw': command => '/usr/local/sbin/setmysqlpass.sh', - unless => "mysqladmin -uroot status > /dev/null", + unless => '/usr/bin/mysqladmin -uroot status > /dev/null', require => [ File['mysql_setmysqlpass.sh'], Package['mysql-server'] ], refreshonly => true, } diff --git a/manifests/server/munin/base.pp b/manifests/server/munin/base.pp new file mode 100644 index 0000000..ad4bb8a --- /dev/null +++ b/manifests/server/munin/base.pp @@ -0,0 +1,20 @@ +class mysql::server::munin::base { + + file { + "/usr/local/share/munin-plugins/mysql_connections": + source => "puppet:///modules/mysql/munin/mysql_connections", + mode => 0755, owner => root, group => root; + + "/usr/local/share/munin-plugins/mysql_qcache": + source => "puppet:///modules/mysql/munin/mysql_qcache", + mode => 0755, owner => root, group => root; + + "/usr/local/share/munin-plugins/mysql_qcache_mem": + source => "puppet:///modules/mysql/munin/mysql_qcache_mem", + mode => 0755, owner => root, group => root; + + "/usr/local/share/munin-plugins/mysql_size_all": + source => "puppet:///modules/mysql/munin/mysql_size_all", + mode => 0755, owner => root, group => root; + } +} diff --git a/manifests/server/munin/debian.pp b/manifests/server/munin/debian.pp index 2b2acb4..d1636d5 100644 --- a/manifests/server/munin/debian.pp +++ b/manifests/server/munin/debian.pp @@ -1,9 +1,14 @@ # manifests/server/munin/debian.pp -class mysql::server::munin::debian { - munin::plugin { - [mysql_bytes, mysql_queries, mysql_slowqueries, mysql_threads]: - config => "user root\nenv.mysqlopts --defaults-file=/etc/mysql/debian.cnf", - require => Package['mysql'], - } +class mysql::server::munin::debian inherits mysql::server::munin::base { + munin::plugin { + [ mysql_bytes, mysql_queries, mysql_slowqueries, mysql_threads ]: + config => "user root\nenv.mysqlopts --defaults-file=/etc/mysql/debian.cnf", + require => Package['mysql']; + + [ mysql_connections, mysql_qcache, mysql_cache_mem, mysql_size_all ]: + config => "user root\nenv.mysqlopts --defaults-file=/etc/mysql/debian.cnf", + script_path_in => "/usr/local/share/munin-plugins", + require => Package['mysql']; + } } diff --git a/manifests/server/nagios.pp b/manifests/server/nagios.pp index 18eef8a..26644db 100644 --- a/manifests/server/nagios.pp +++ b/manifests/server/nagios.pp @@ -1,3 +1,5 @@ +# manifests/server/nagios.pp + class mysql::server::nagios { # Flip this variable if you need to check MySQL through check_ssh or check_nrpe, # in that case you will have to manually define nagios::service::mysql |