1#!/usr/local/bin/python3.8
2# -*- python -*-
3
4"""
5=head1 INTRODUCTION
6
7Plugin to monitor MySQL database disk usage per prefix. A prefix can be eg.
8'user' for the databases 'user_testdb' and 'user_db2', as can be seen in
9certain shared hosting setups.
10
11=head1 APPLICABLE SYSTEMS
12
13Local access to the database files is required (which are stored in
14/var/lib/mysql by default).
15
16=head1 INSTALLATION
17
18Place in /etc/munin/plugins/ (or link it there using ln -s)
19
20=head1 CONFIGURATION
21
22Add this to your /etc/munin/plugin-conf.d/munin-node:
23
24=over 2
25
26    [mysql_disk_by_prefix]
27    user mysql
28    env.prefixes_with_underscores foo_bar d_ritchie # prefixes that include an underscore
29    env.db_minsize 1024000 # minimum db size in order to report a specific prefix,
30                           # in bytes (defaults to 50M). "0" for none.
31    env.mysql_db_dir /var/lib/mysql # default value
32
33=back
34
35=head1 AUTHORS
36
37Copyright (C) 2019 pcy <pcy.ulyssis.org>
38
39=head1 MAGIC MARKERS
40
41 #%# family=auto
42 #%# capabilities=autoconf
43
44=cut
45"""
46
47
48import os
49import re
50import sys
51
52
53def weakbool(x):
54    return x.lower().strip() in {'true', 'yes', 'y', 1}
55
56
57LIMIT = os.getenv('db_minsize', str(50000 * 1024))
58try:
59    LIMIT = int(LIMIT)
60except ValueError:
61    LIMIT = 0
62
63exceptions = os.getenv('prefix_with_underscores', '').split(' ')
64if exceptions == ['']:
65    exceptions = []
66
67mysqldir = os.getenv('mysql_db_dir', '/var/lib/mysql')
68
69
70def name_from_path(path):
71    filename = os.path.basename(path)
72    for name in exceptions:
73        if filename.startswith(name):
74            return name
75    name = filename.split('_')[0]
76
77    def decode_byte(m):
78        return bytes.fromhex(m.group(1)).decode('utf-8')
79
80    # Decode MySQL's encoding of non-ascii characters in the table names
81    return re.sub('@00([0-9a-z]{2})', decode_byte, name)
82
83
84def calc_dir_size(directory):
85    total = 0
86
87    for filename in os.listdir(directory):
88        filedir = os.path.join(directory, filename)
89
90        if os.path.islink(filedir):
91            continue
92        if os.path.isfile(filedir):
93            total += os.path.getsize(filedir)
94
95    return total
96
97
98def size_per_subdir(parentdir):
99    for subdir in os.listdir(parentdir):
100        dirpath = os.path.join(parentdir, subdir)
101
102        if os.path.islink(dirpath):
103            continue
104        if os.path.isdir(dirpath):
105            yield calc_dir_size(dirpath), name_from_path(os.path.split(dirpath)[1])
106
107
108def sizes_by_name(limit=None):
109    sizes = {}
110    for size, name in size_per_subdir(mysqldir):
111        sizes[name] = sizes.get(name, 0) + size
112    for name, total_size in sizes.items():
113        if limit <= 0 or limit <= total_size:
114            yield name, total_size
115
116
117def fetch():
118    for name, total_size in sorted(sizes_by_name(limit=LIMIT), key=lambda x: x[0]):
119        print('{}.value {}'.format(name, total_size))
120
121
122def main():
123    if len(sys.argv) == 1:
124        fetch()
125    elif sys.argv[1] == 'config':
126        print('graph_title MySQL disk usage by prefix')
127        print('graph_vlabel bytes')
128        print('graph_category db')
129
130        names = sorted(name for name, _ in sizes_by_name(limit=LIMIT))
131        for name in names:
132            print('{0}.label {0}'.format(name))
133            print('{}.type GAUGE'.format(name))
134            print('{}.draw AREASTACK'.format(name))
135    elif sys.argv[1] == 'autoconf':
136        print('yes' if os.path.isdir(mysqldir)
137              else "no (can't find MySQL's data directory)")
138    else:
139        fetch()
140
141
142if __name__ == "__main__":
143    main()
144