Install MySQL Dashboard and Collector

Video Lecture

Description

Now we have a MySQL Data Source created, we will install a dashboard for it.

The dashboard will be the popular 2MySQL Simple Dashboard you can download from https://grafana.com/grafana/dashboards?dataSource=mysql

Before installing the dashboard, we need to set up a collector on our MySQL server. The collector script is downloaded from https://github.com/meob/my2Collector

I have MySQL 8.0.21, so I will download the file called my2_80.sql If you have MySQL 5, then download my2.sql

I download the script using wget,

wget https://raw.githubusercontent.com/meob/my2Collector/master/my2_80.sql

The event scheduler will also need to be enabled on your MySQL server. You can do this by editing the my.cnf file

sudo nano /etc/mysql/my.cnf

Add lines

...
[mysqld]
event_scheduler = on

Save, and restart,

sudo service mysql restart

Open the script that was downloaded

sudo nano my2_80.sql

Uncomment the last 2 lines. Set the password to something you want, and save

Run the sql script,

mysql < my2_80.sql

Open MySQL and so some checks.

mysql

> show databases;
> show variables where variable_name = 'event_scheduler';
> use my2;
> show tables;
> select * from current;
> select * from status;
> quit

If everything is ok, continue

Import the new dashboard using the Grafana GUI as shown in the video lecture, I have an error after installing the Dashboard, that the SELECT command is denied for user grafana@localhost I need to grant SELECT to the user on the new database my2 which was created when running the my2_80.sql script.

mysql

> GRANT SELECT ON my2.* TO 'grafana'@'localhost';
> FLUSH PRIVILEGES;
> quit

Reopen the dashboard, and you should begin to see data.

Note that there are now 2 database users,

grafana@localhost : Used by the Grafana dashboard, to query the collected data from the MySQL server. This user has been granted the SELECT privilege only.
my2@localhost: Used by the MySQL event scheduler to collect statistics and save them into the DB for use by the Grafana dashboard. This user has been granted ALL privileges.