Hello, dear habryanin and another recipe for this tasty and healthy monitoring!My name is Mikhail Andrianov and I have been working as a Technical Director for 7 years at Emarsys.We often work with client and our databases, so we often need to watch them so that our clients and we can live and work peacefully.And so the idea was born to me to share with you a recipe for deploying a monitoring system that is quite common in the world of MySQL.Here's how roughly this beauty might look:
All Dashboards are already included in the recipe, and you donβt have to sculpt them. In fact, you just need to configure everything correctly and plunge headlong into the world of chic graphs and tables.So, for preparation we need:Mysql> 5.6 (you can do it even younger, but have to fix something in the queries)Grafana> 6.7.1Ubuntu / MS Windows to your taste
Stage one
We set up the log storage base.The basis of the service base I included a convenient and easy-to-prepare my2Collector from the master of Italian MeSql Meo cuisinemysql --user=root -pXXX < my2.sql
* I will use the commands of my beloved Ubuntu 16.You canβt give the user my2 admin rights - it will be seen later that this can get you sideways.When the database is out of the dump, I recommend that you familiarize yourself with the procedures that update the data in it. In particular, the procedure `collect_stats`()
It is advisable to increase the frequency of the request to 5 minutes. So you will have more relevant data on the state of the database.Now that the statistics collection engine is running, you can move on to connecting this source to Grafana.Stage Two
We connect Grafana to the database with logs.
Everything is standard.Choose MySQL Data SourceNext ...URL, user, password** You can see that the user we use to collect logs and restrict only read access performance.schema and write to my2 database, we use the same in Grafana So you can avoid compromising your base if you lose access to Grafana.
Stage Three
Configuring DashboardsI deliberately chose the fastest and most convenient way to configure, so as not to be patient.Again, turn to Meo.His dashboard is called 2MySQL Simple Dashboard.The most convenient way to set dashboards in Grafana is, of course, import the JSON settings object.To complete this straightforward step:- Download JSON itself
- Let's go into the management of dashboards Dashaboards -> Manage
- Select Import and load the object from the downloaded file.
Now we have a ready-made beautiful panel with very efficient data visualizations.
The flexibility of Grafana makes it possible to conveniently modify each panel, and the built-in variables in 2MySQL Simple Dashboard help to immediately adjust the panels to those users and processes that may be most interesting, for example, to track specific database users on separate panels or dashboards.
The next step is to set up alerts for the created dashboard. I will omit this process, leaving it to the discretion of everyone who decides to go along the described path. All alerts are configured at the level of each specific panel.
As promised, the deployment takes place quite quickly and without dancing with a tambourine. I will be glad to additions to my recipe from experienced habrovtsev, especially regarding alerts'ov.