Introduction to PingCap/TiDB/ Part-2 Monitoring TiDB MySQL data using Grafana
Published: 2021-04-10 15:50:41

In the last article, we have seen how to install TiDB in window os using Wsl if you haven't install TiDB yet you better go and have a look at the previous [article](https://dev.to/yared123yared/introduction-to-pingcap-tidb-part-1-installation-of-tidb-on-windows-machine-1320).

In this section, we are going to see that how can we set up **Grafana** for TiDB and How it is used to monitor and visualize data from TiDB Mysql Data Source. 

#**Step 1:Start the TiDB playground**

The **Prometheus** and **Grafana** will be installed by default when we run the TiDB playground so the first step will be to start the playground using the following command. 

```
tiup playground
```
Then we will get this information from the terminal. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b5ujlhc87tf7ojjwf4x7.png)
#**Step 2:Start Grafana and change the default password**
from the above information copy, the **Grafana** link and past it to any browser. 

we will get this login page of grafana. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t929vioi5q4wv5khvydm.png)

The default username and password will be **admin** for both. so log in with this information. 

it will give you an optional page that prompt you to change the default password for username **admin**. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/43m8pti7q2m0de1t97ua.png)

change the password and click on the **save**  button. 
 
then after that, you will get the home page of the **Grafana**. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xrtu4dt4r0nk6ody0y05.png)

#**Step 3:Add Data Source**

The next step is to indicate the source of data that will be displayed on Grafana.

To do this from the home page of **Grafana** on the left side there is a list of menus. go to the **Configuration** menu and click on the **Data Source** option. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/slidnr4dxugj9reqbtyx.jpg)

Then you will get the **Data source** configuration page which exactly looks like this. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/48b4suu208wtib2bc6tx.png)
Click on the **Add Data Source** button at the top. and you will get a list of data sources categories. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h7e3dghzmqbgyu4zv4ur.png)

from the categories select the **MySql** category because our database that runs on top of **TiDB playground** is MySQL. after this you will get a **MySql Connection Page**.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/us1h0k61o07x5gmm3ur1.png)

we need to give all the required information in order to make a connection with MySql.

The First is **Host** we should provide the **Host IP address** of our MySql.so let us go back to the MySQL running on our computer and select the Host IP address. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nmezp7lho2s19zgn4j6e.png)

As you can see from the beginning there is an **IP address** with a specific port of **127.0.0.1:4000**. my database name is **FixItDb**. the default User for my case is **root** and the password will be empty. so now I am going to provide all this information to the **MySql Connection Page**.

After that click on the **Save and Test** button at the bottom. and if all your information is valid and the connection made successfully you will get a green confirmation at the bottom.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gr13xnjwbfmlcdj73p6c.png)

At the top of this page, we can also update the **name** for the data source. later when we create a dashboard we will access this data source based on the name we specified now.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3bc1zc2r9528kseh2ndg.png)

we have also an option to set it as a **Default Data Source** you can make it a default data source if you want it to appear by default whenever you create a new dashboard.
 
#**Step 4:Create A DashBoard**

After we successfully added our *data source* the next step will be creating a dashboard to show the data.

to do this from the left menu options click on the **+** create menu and when the list of menus comes select the **dashboard** option. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/phsz8pt6m5qklv14v3pb.png)

then select the **Add Query** option.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ouuxwbyhbmxrrzvhisks.png)

after that, you will get a dashboard in which the data came from the default data source. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2nqu72dsf3cgf450pjau.png)

so change the data source to the data source that we have created before. which is **Service-DS**.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nzxusjds3yt6v2ucgt06.png)

after this, we will get our dashboard with **No Data Points**.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5qlpqe6vp67tnx4z4zns.png)

This is because we haven't configured our dashboard yet. 

#**Step 5:Configuring the Dashboard to get data**


First, let us select the table we want to visualize in my case, it is the **Service** table.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cm23h9ox6tqo40y31mqy.png)
 
This is the whole table information. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2hzqj040fzwwvthofskg.png)

**Because Grafana will do monitoring based on the date-time we should have the Date Time Column in our table** so I have created one called  **Added Time**. which indicates the time in which the particular service is added. so in the **Time Column** option of the dashboard, I am going to select **Added Time**.

At the bottom of the **FROM** option of the dashboard, you can see that there is a **SELECT** nominated row. from this, we can select the Column we want to display on the *Grafana*. but this column should be an integer and because of this Grafana by default will add only the integer data type column on the menu. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zh6um2c8vrpmt1vnh41d.png)

hit the **+** button on that row and add the **Aggregate** option and set it to **max** and give the **Alias** name. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5tdkcvppmitjb1vf07qq.png)
 

at the bottom, we have **WHERE** nominated row. we can remove this if we don't have a condition to select the column from the database. so select on the condition and you will get the **remove** button at the bottom.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4cn5cqbaz0ivo6pngc4v.png)

immediately when removing the where condition the graph will be changed. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1mbbksoixl5fkz6l63oi.png)
 
from this, we can understand that grafana was able to fetch the data from the **Service-DS** MySQL data source. but the graph is a straight line. this is because of the selected time.at the top of the **dashboard** next to the **setting** icon. there is some option which says like **Last 6 hours** click on this. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/os29qiznifjnykif98h1.png)

by default, the **Grafana** is displaying data that have been added 6 hours before now. but from our database, there is only one data that have been added before 6 hours.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2hzqj040fzwwvthofskg.png)

so let me update the beginning time to something like this **2020-05-03 09:37:13** and ending time to **2021-07-03 09:37:13** to include all the data's time from my database table.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2apurjnv9bi0pjmeo288.png)

hit on the **Apply** button then our graph will be changed.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ifuynlwywga4ba2xwlwc.png)
 now if we hover on the graph we can see that the data will be changed from one place to another place. because the visualization includes all rows from the database table based on the **Added Time**

#**Step 6:Query Inspection**

Alternativ to the graph we have also an option to look at the **response** object that comes from the data source. 

from the **dashboard** select the button next to the **Add Query** button called  **Query Selector**. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fecjj5cmji1zpl0gd28f.png)

Then have a look at the **Response** to see the coming data.  

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qdgr9gxbbwjlffn41k6w.png)

#**Step 7:Adding more select option**

If you want to see some other column on this dashboard you can simply do it by adding another column from the **SELECT** row. 

From the **SELECT** option at the end, there is a **+** button. when you click on it you will get an option called **Column** from the listed menu options to click on that.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/94f9tr7f5usdvirarwjl.png)

then provide information about the column you want to display. 

change the **Alias** to the new column name, in order to differentiate the two columns. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x6g5wv5ykbobkmixig76.png)

when you hit enter the graph will be changed. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eflgomriwjtb9gwhhqbt.png)

The new data on the graph looked a straight line because I have constant data for the **Advanced Price** Column which is 10.

That is all about this article. In the next article, we will see the **integration of TiDB with .Net core API.**

Thank You!.