- Repository
- Munin (2.0)
- Last change
- 2020-04-13
- Graph Categories
- Family
- manual
- Capabilities
- Language
- Shell
- License
- GPL-2.0-only
- Authors
mysql_innodb
Name
mysql_innodb - Plugin to monitor free space in a pre-allocated innodb tablespace
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.
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';
Configuration
Configuration parameters for @@CONFDIR@@/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
Default Configuration
[mysql_innodb]
env.mysql /usr/bin/mysql
env.mysqlopts --user=munin --password=munin --host=localhost
env.warning 2147483648
env.critical 1073741824
Author
Stig Sandbeck Mathisen ssm@fnord.no
License
GNU General Public License, version 2 or any later version
Magic Markers
#%# family=manual
#%# capabilities=autoconf
#!@@GOODSH@@
: << =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 @@CONFDIR@@/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
mysql_exec() {
# shellcheck disable=SC2086
"$MYSQL" $MYSQLOPTS --batch --skip-column-names --database=information_schema --execute "$1"
}
## 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
mysql_exec "select(1);" | \
while read -r res; do
case $res in
1)
# All is well
;;
*)
echo "no (Could not contact mysql server)"
return 0
;;
esac
done
# shellcheck disable=SC2034
mysql_exec "SHOW VARIABLES LIKE 'innodb_file_per_table';" | \
while read -r 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
mysql_exec "SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB';" | \
while read -r 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() {
mysql_exec "SELECT version();" | awk '{printf "%1.1f", $1}'
}
# InnoDB free bytes for MySQL 5.1 and newer
innodb_free_new() {
t=$(mysql_exec "SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB';")
if [ "$t" -gt "0" ]; then
mysql_exec "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() {
mysql_exec "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)
major_version=$(echo "$ver" | cut -f 1 -d ".")
if [ "$major_version" -ge "5" ]; 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