관리-도구

편집 파일: mysql_innodb

#!/bin/sh

: << =cut

=head1 NAME

mysql_innodb - Plugin to monitor free space in a pre-allocated innodb
tablespace

=head1 ABOUT

Munin plugin to monitor free space in the InnoDB tablespace of MySQL.
Mostly useful if you use InnoDB on a block device, or if you have
created files for InnoDB storage, and do not want to autoextend the
last file.

If you have set innodb_file_per_table, you do not need to worry about
free tablespace, and you should use the "df" plugin instead.

=head1 USAGE

This plugin should be run as a user with a username and password
stored in ~/.my.cnf, or with --defaults-extra-file pointing to such a
file.

To increase security, the plugin can use its own schema with a simple,
empty table using the InnoDB engine.  To create an empty schema within
the default INNODB tablespace, do the following:

mysql> CREATE SCHEMA munin_innodb;
  mysql> USE munin_innodb
  mysql> CREATE TABLE something (anything int) ENGINE=InnoDB;
  mysql> GRANT SELECT ON munin_innodb.* TO 'munin'@'localhost' IDENTIFIED BY 'munin';

=head1 CONFIGURATION

Configuration parameters for /etc/munin/mysql_innodb,
if you need to override the defaults below:

[mysql_innodb]
  env.mysql     - Path to the mysql binary
  env.mysqlopts - Options to pass to mysql (host, username, password)
  env.warning   - Generate a warning if free space goes below this level
  env.critical  - Generate a critical if free space goes below this level

=head2 DEFAULT CONFIGURATION

[mysql_innodb]
  env.mysql /usr/bin/mysql
  env.mysqlopts --user=munin --password=munin --host=localhost
  env.warning 2147483648
  env.critical 1073741824

=head1 AUTHOR

Stig Sandbeck Mathisen <ssm@fnord.no>

=head1 LICENSE

GNU General Public License, version 2 or any later version

=begin comment

This file is part of Munin.

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; either version 2 of the License, or (at your option) any later
version.

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., 51 Franklin
Street, Fifth Floor, Boston, MA  02110-1301, USA.

=end comment

=head1 MAGIC MARKERS

=begin comment

These magic markers are used by munin-node-configure when installing
munin-node.

=end comment

#%# family=manual
 #%# capabilities=autoconf

=cut

## Tunable parameters with defaults
MYSQL="${mysql:-/usr/bin/mysql}"
MYSQLOPTS="${mysqlopts:---user=munin --password=munin --host=localhost}"

WARNING=${warning:-2147483648}   # 2GB
CRITICAL=${critical:-1073741824} # 1GB

# Convenient variables
MEXEC="$MYSQL $MYSQLOPTS --batch --skip-column-names --database=information_schema --execute"

## No user serviceable parts below
print_config() {
    echo 'graph_title MySQL InnoDB free tablespace'
    echo 'graph_args --base 1024'
    echo 'graph_vlabel Bytes'
    echo 'graph_category mysql'
    echo 'graph_info Free bytes in the InnoDB tablespace'
    echo 'free.label Bytes free'
    echo 'free.type GAUGE'
    echo 'free.min 0'
    echo 'free.warning' $WARNING:
    echo 'free.critical' $CRITICAL:
    exit 0
}

print_data() {
    innodb_free | xargs -r printf "free.value %s\n"
}

check_autoconf() {

# Check client
    if [ ! -x $MYSQL ]; then
	echo "no ($MYSQL not executable)"
	return 0
    fi

# Check server
    $MEXEC "select(1);" | \
	while read res; do
	    case $res in
		1)
		# All is well
		    ;;
		*)
		    echo "no (Could not contact mysql server)"
		    return 0
		    ;;
	    esac
    done

$MEXEC "SHOW VARIABLES LIKE 'innodb_file_per_table';" | \
	while read var res; do
	    case $res in
		OFF)
		# All is well
		    ;;
		ON)
		    echo "no (innodb_file_per_table is ON, should be OFF)"
		    return 0
		    ;;
		*)
		    echo "no (could not read innodb_file_per_table)"
		    return 0
		    ;;
	    esac
    done

$MEXEC "SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB';" | \
	while read res; do
	    if [ "$res" = "0" ]; then
		echo "no (No visible tables use InnoDB)"
		return 0
	    fi
    done

# Default, say "yes" and hope for the best
    echo "yes"
}

# Get major.minor version of the server
mysql_version() {
    $MEXEC "SELECT version();" | awk '{printf "%1.1f", $1}'
}

# InnoDB free bytes for MySQL 5.1 and newer
innodb_free_new() {
    t=$($MEXEC "SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB';")
    if [ "$t" ">" "0" ]; then
	$MEXEC "SELECT data_free FROM tables WHERE ENGINE = 'InnoDB' LIMIT 1;"
    else
	echo >&2 "$0: Error: No visible tables use InnoDB"
	echo U
	return 1
    fi
}

# InnoDB free bytes for MySQL 5.0 and older
innodb_free_old() {
    $MEXEC "SELECT table_comment FROM tables WHERE ENGINE = 'InnoDB' LIMIT 1;" \
	| awk '/^InnoDB free:/ {print $3 * 1024}'
}

# InnoDB free bytes wrapper
innodb_free() {
    ver=$(mysql_version)
    if [ "$ver" ">" "5.0" ]; then
		case $ver in
			5.0.*|5.1.?|5.1.1?|5.1.2[0123])
				innodb_free_old
				;;
			*)
				innodb_free_new
				;;
		esac
    else
	innodb_free_old
    fi
}

# Parse arguments, run correct function
case $1 in
    "autoconf")
	check_autoconf
	;;
    "config")
	print_config
	;;
    *)
	print_data
	;;
esac