...

Open source softwares - Grafana

Back to Course

Lesson Description


Lession - #735 Create MySQL Data Source, Collector and Dashboard


Create MySQL Data Source, Collector and Dashboard

I exhibit how to set up the MySQL Data Source with a gatherer, and it's connected dashboard.

I likewise exhibit how to introduce a MySQL data set server.

I introduce the MySQL data set onto a new cut off to likewise exhibit the kinds of issues that you might have while interfacing Grafana to another server.

Add the MySQL Data source in Grafana. On the off chance that you attempt to save it, it will have a few issues. We will next set up a MySQL server prepared for the Data Source design.

Subsequent to obtaining your new server (you can constantly utilize your current Grafana server assuming you like>
we next can introduce MySQL onto the new server. SSH onto your new MySQL server

sudo apt update


sudo apt install mysql-server


sudo service mysql status


Since we have a MySQL waiter, we will introduce a committed gatherer for it that will intermittently accumulate measurements about the MySQL waiter and store them into a table containing columns with times and values (my2.status>


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

The authority script that I will utilize can be downloaded from https://github.com/meob/my2Collector

I have MySQL 8.#.#, so I will download the document called my2_80.sql. In the event that you have MySQL 5, download my2.sql

SSH onto your new MySQL server and download the content utilizing 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 while running the above lines, it shows that the event_scheduler isn't empowered, then we should empower it so the gatherer runs behind the scenes. You can do this by altering the my.cnf record

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 ought to likewise show another client in the data set named my2.

In the case of all is great, then we can proceed.

Before we can save the MySQL information source setup in Grafana, we should tell it which data set and client it ought to utilize. The client ought to be conceded select authorizations just since Grafana won't approve and SQL orders shipped off it. We will make a particular client that can peruse the my2 data set with select consents as it were. We won't utilize the my2 client

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 an additional 2 information base clients,

  • grafana@localhost : Used by the Grafana dashboard, to question the gathered information from the MySQL server. This client has been allowed the SELECT honor as it were.
  • my2@localhost : Used by the MySQL occasion scheduler to gather insights and save them into the DB for use by the Grafana dashboard. This client has been conceded ALL honors.
On the off chance that you introduced your MySQL onto an alternate server, of course it won't permit outside associations.

To permit far off associations on the MySQL server.

Open the MySQL setup record

sudo nano/and so forth/mysql/my.cnf

Change the tight spot address to 0.0.0.0 or add this text beneath to the furthest limit of the document in the event that it doesn't as of now exist.

[mysqld]
tie address = 0.0.0.0


Save and restart MySQL.

sudo administration mysql restart


Save the information source in Grafana and the association ought to now be alright.

We can do a fast test utilizing the Explore choice on Grafana. Open the Explore tab, select the MySQL information source and utilize this inquiry beneath.

SELECT
  variable_value+0 as value,
  timest as time_sec
FROM my2.status
WHERE variable_name='THREADS_CONNECTED'
ORDER BY timest ASC;


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 dat

Firewall

In the event that your MySQL and Grafana servers are on various servers, you should permit approaching associations on port 3306. In the event that utilizing an unlimited Ubuntu server as I do, port 3306 will as of now be permitted. Contingent upon your cloud supplier, you might have to physically make a standard to permit approaching associations on port 3306. You can likewise limit the interfacing IP address or space that can associate assuming you need.

On my MySQL waiter, I can run these iptables rules to limit approaching associations with port 3306 just for my Grafana waiter. Adjust as expected for your space name or Grafana servers IP address in the event that you are not utilizing some other firewall arrangement.

iptables - An INPUT - p tcp - s  - - dport 3306 - j ACCEPT
iptables - An INPUT - p tcp - - dport 3306 - j DROP
iptables - L