Installing Munin on Ubuntu 20.04 with MySQL plugin

Munin is a web based tool to monitor system and network statistic. Munin shows this information through Graphs.

Let see how we can setup Munin on Ubuntu 20.04, we will start with updating the package repositories and then installing required package for Munin and Apache web server

apt update
apt install munin libnet-netmask-perl libnet-telnet-perl libxml-parser-perl libxml-simple-perl libcache-cache-perl libdbd-mysql-perl libdbi-perl
apt install apache2 apache2-utils

Access to Munin is by default is restricted locally from server only.
We will change this and allow only authenticated user to access it. This can be done by editing the file /etc/apache2/conf-enabled/munin.conf.

<Directory /var/cache/munin/www>
    Require local
    Options None
</Directory>

Change the above line so they look like

<Directory /var/cache/munin/www>
    AuthType Basic
    AuthName "Login Required"
    AuthUserFile /etc/apache2/htpasswd.munin
    Require valid-user
    Options None
</Directory>

To setup a user/password run the command

htpasswd -c /etc/apache2/htpasswd.munin username

We can now access the Munin Graphs by visiting the url like http://server-ip/munin/

Now lets add Graphs for MySQL monitoring, to do this we first need to add a user to MySQL which can read requires statistics.

CREATE USER 'munin'@'localhost' IDENTIFIED WITH unix_socket;
GRANT PROCESS ON *.* TO 'munin'@'localhost';
FLUSH PRIVILEGES;

We have create a user munin which will be authenticated with unix_socket connection to mysql server.
Next we setup the connection string for mysql, create a new file /etc/munin/plugin-conf.d/mysql and add following line to it

[mysql*]
env.mysqlconnection DBI:mysql:information_schema
env.mysqluser munin

Now to check whether munin user can cannot to MySQL server switch to user munin and run the commands

su - munin --shell=/bin/bash
munin-node-configure --suggest | grep mysql

The output should be similar to

mysql_ | no | yes (+bin_relay_log +binlog_groupcommit +commands +connections +files_tables +innodb_bpool +innodb_bpool_act +innodb_insert_buf +innodb_io +innodb_io_pend +innodb_log +innodb_rows +innodb_semaphores +innodb_tnx +myisam_indexes +network_traffic +qcache +qcache_mem +replication +select_types +slow +sorts +table_locks +tmp_tables)

The yes here means that we can create graphs for listed mysql parametes.
The no here says that they are not currently enabled, so lets enable them
Run the below command to get the command to run to create necessary symlinks

 munin-node-configure  --shell | grep mysql

The output will be like

ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_bin_relay_log'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_binlog_groupcommit'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_commands'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_connections'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_files_tables'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_innodb_bpool'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_innodb_bpool_act'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_innodb_insert_buf'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_innodb_io'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_innodb_io_pend'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_innodb_log'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_innodb_rows'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_innodb_semaphores'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_innodb_tnx'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_myisam_indexes'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_network_traffic'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_qcache'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_qcache_mem'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_replication'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_select_types'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_slow'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_sorts'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_table_locks'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_tmp_tables'

We now need to create these symlinks as root user, you can just copy paste the output from above command to create the symlinks. Finally restart the munin-node service.

service munin-node restart

You can now head back to http://server-ip/munin/ to check the new graphs for mysql