Create MySQL Data Source, Collector and Dashboard
Video Lecture
Description
In this video, I demonstrate how to set up the MySQL Data Source with a collector, and it's related dashboard.
I also demonstrate how to install a MySQL database server.
I install the MySQL database onto a new sever to also demonstrate the types of issues that you may have when connecting Grafana to another server.
Add the MySQL Data source in Grafana. If you try to save it, it will have some issues. We will next set up a MySQL server ready for the Data Source configuration.
After sourcing your new server (you can always use your existing Grafana server if you prefer) we next can install MySQL onto the new server. SSH onto your new MySQL server.
sudo apt update
sudo apt install mysql-server
sudo service mysql status
Note
In the past, I recommended also running mysql_secure_installation. It no longer works since MySQL version 8.0.29. See notes below.
Now that we have a MySQL server, we will install a dedicated collector for it that will periodically gather statistics about the MySQL server and store them into a table containing rows with times and values (my2.status)
The dashboard will be the popular 2MySQL Simple Dashboard (7991) that you can download from https://grafana.com/grafana/dashboards?dataSource=mysql
The collector script that I will use can be downloaded from https://github.com/meob/my2Collector
I have MySQL 8.#.#, so I will download the file called my2_80.sql. If you have MySQL 5, then download my2.sql
SSH onto your new MySQL server and download the script using wget,
wget https://raw.githubusercontent.com/meob/my2Collector/master/my2_80.sql
Open the script that was downloaded
sudo nano my2_80.sql
Find these lines at the end of the script where it creates a specific user,
-- Use a specific user (suggested)
-- create user my2@'%' identified by 'P1e@seCh@ngeMe';
-- grant all on my2.* to my2@'%';
-- grant select on performance_schema.* to my2@'%';
uncomment and change the password to something that you think is better. E.g.,
-- Use a specific user (suggested)
create user my2@'%' identified by 'password';
grant all on my2.* to my2@'%';
grant select on performance_schema.* to my2@'%';
Save the changes and then run the SQL script,
mysql < my2_80.sql
Now open MySQL and do some checks.
mysql
> show databases;
> show variables where variable_name = 'event_scheduler';
> select host, user from mysql.user;
> use my2;
> show tables;
> select * from current;
> select * from status;
> quit
If when running the above lines, it shows that the event_scheduler is not enabled, then we will need to enable it so that the collector runs in the background. You can do this by editing the my.cnf file
sudo nano /etc/mysql/my.cnf
Add lines to the end of the file
[mysqld]
event_scheduler = on
Save and restart MySQL.
sudo service mysql restart
The above test lines should also show a new user in the database named my2.
If everything is ok, then we can continue.
Before we can save the MySQL data source configuration in Grafana, we will need to tell it which database and user it should use. The user should be granted select permissions only since Grafana will not validate and SQL commands sent to it. We will create a specific user that can read the my2 database with select permissions only. We won't use the my2 user we created earlier since it has more permissions than our Grafana server actually needs.
Open the MySQL prompt
mysql
We need to know the IP address of our Grafana server that will initiate the connection to the MySQL server.
> CREATE USER 'grafana'@'###.###.###.###' IDENTIFIED BY 'password';
> GRANT SELECT ON my2.* TO 'grafana'@'###.###.###.###';
> FLUSH PRIVILEGES;
> quit
Note that we have now added 2 extra 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.
If you installed your MySQL onto a different server, then by default it will not allow external connections.
To allow remote connections on the MySQL server.
Open the MySQL configuration file
sudo nano /etc/mysql/my.cnf
Change the bind address to 0.0.0.0 or add this text below to the end of the file if it doesn't already exist.
[mysqld]
bind-address = 0.0.0.0
Save and restart MySQL.
sudo service mysql restart
Save the data source in Grafana and the connection should now be ok.
We can do a quick test using the Explore option on Grafana. Open the Explore tab, select the MySQL data source and use this query below.
SELECT
variable_value+0 as value,
timest as time_sec
FROM my2.status
WHERE variable_name='THREADS_CONNECTED'
ORDER BY timest ASC;
If using Grafana 9 or 10, press the [Code] button at the right first, and then past in the above sample query.
Now that 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 (ID 7991)
After several hours, you should see that dashboard visualizations fill will some data.
Firewall
If your MySQL and Grafana servers are on different servers, then you will need to allow incoming connections on port 3306. If using an unrestricted Ubuntu server as I do, port 3306 will already be allowed. Depending on your cloud provider, you may need to manually create a rule to allow incoming connections on port 3306. You can also restrict the connecting IP address or domain that can connect if you want.
On my MySQL server, I can run these iptables rules to restrict incoming connections to port 3306 only for my Grafana server.
Note
IPTables is a very precise science, to avoid problems setting up the exercises in the course, you can ignore these steps. However, if working in a production environment, ensure your firewalls are managed.
Adapt the commands below as required for your domain name or Grafana servers IP address if you are not using any other firewall solution.
iptables -A INPUT -p tcp -s <your Grafana servers domain name or ip address> --dport 3306 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP
iptables -L
Warning
iptables settings will be lost in case of system reboot. You will need to reapply them manually,
or
install iptables-persistent
sudo apt install iptables-persistent
This will save your settings into two files called,
/etc/iptables/rules.v4
/etc/iptables/rules.v6
Any changes you make to the iptables configuration won't be auto saved to these persistent files, so if you want to update these files with any changes, then use the commands,
iptables-save > /etc/iptables/rules.v4
iptables-save > /etc/iptables/rules.v6
Troubleshooting
ERROR : db query error: query failed
If when connecting to a MySQL data source, you see the error db query error: query failed - please inspect Grafana server log for details.
You can tail the Grafana log using the command
tail -f /var/log/grafana/grafana.log
Your error may be directly visible in the output of the above command, if not, try to connect again using the MySQL data sources Save & Test button, and look for the error as it is logged in real time.
If the error is similar to "Error 1045: Access denied for user 'grafana'@'[IP ADDRESS OF YOUR GRAFANA SERVER]' (using password: YES)", then you haven't added the user to MySQL correctly.
When you add the "read only" user to MySQL, you indicate which IP addresses the user is connecting from and which objects it is allowed to read.
E.g., If your Grafana servers IP Address is 10.20.30.40, and that is the IP address that the MySQL server will get the connection attempt from, then the MySQL commands to create the user and allow read on the tables are,
> CREATE USER 'grafana'@'10.20.30.40' IDENTIFIED BY 'password';
> GRANT SELECT ON my2.* TO 'grafana'@'10.20.30.40';
> FLUSH PRIVILEGES;
If you installed MySQL locally on the same server as your Grafana server, then it would connect using localhost, so your commands would then be
> CREATE USER 'grafana'@'localhost' IDENTIFIED BY 'password';
> GRANT SELECT ON my2.* TO 'grafana'@'localhost';
> FLUSH PRIVILEGES;
ERROR : SET PASSWORD has no significance for user 'root'@'localhost'
mysql_secure_installation stopped working since MySQL version 8.0.29
I no longer recommend using this command since MySQL defaults are now much more secure.
But if you still want to, then before running mysql_secure_installation, you can enter the MySQL prompt
sudo mysql
Alter the localhost root user
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'my-secret-password';
FLUSH PRIVILEGES;
Exit
quit
And now run
mysql_secure_installation
Note that when using the MySQL prompt, you now need to enter the password
I.e., start MySQL prompt using,
mysql -u root -p
instead of just
mysql
ERROR : Duplicate key name 'idx01'
If you run the my2_80.sql script twice, it will error that there is already a duplicate key idx01.
There is no reason to run the script twice, unless you have your reasons, whatever they may be.
You will need to delete the idx01, and any other objects that may have been created on the first run that now prevent the my2_80.sql script from being run twice.
Log onto the MySQL prompt
mysql
and then run these commands
use my2;
drop index idx01 on status;
drop index idx02 on current;
drop user 'my2'@'%';
quit;
And then retry
mysql < my2_80.sql
Grafana 10 and Ubuntu 22.04 Notes
MySQL Secure Installation
Note that in previous versions of this course, I would also run mysql_secure_installation. This is no longer necessary since MySQL version 8.0.29 is already secured by default.
Missing Import Button
Instead of pressing a specific button for [Import] on the dashboards page, you now need to select the [New] button first, and then select the Import option.
Daemons using outdated libraries
Since Ubuntu 22.04, installing new packages may give you a full screen prompt to restart other dependent services. Press Tab to highlight the OK option, and press Enter.
Explore MySQL DataSource
In Grafana 9 & 10, when using the Explore option on a MySQL data source, you now get a query wizard. In order to use the custom query editor instead, press the [Code] button at the right of the screen.
It also no longer provides an editable sample query as shown in the video. Instead, paste and run this example below.
SELECT
variable_value+0 as value,
timest as time_sec
FROM my2.status
WHERE variable_name='THREADS_CONNECTED'
ORDER BY timest ASC;