Add and use a MySQL data source - Application Real-Time Monitoring Service

This topic describes how to add a MySQL data source and create a dashboard in Grafana.

Step 1: Log on to Grafana

Log on to the ARMS console. In the left-side navigation pane, choose Managed Service for Grafana > Workspace Management.
On the Workspace Management page, find the workspace that you want to manage and click the URL in the URL column to go to Grafana.
Note
You can log on to Grafana with the administrator account of Grafana and the password that you configured when you created the workspace. You can also click Sign in with Alibaba Cloud to log on to Grafana with the current Alibaba Cloud account.

Step 2: Add a MySQL data source

Grafana 9.0.x

In the left-side navigation bar, move the pointer over the icon and click Data sources.
On the Data sources tab, click Add data source. On the Add data source page, enter MySQL in the search box and click the result that appears.

On the Settings tab, configure the parameters that are described in the following table.

Parameter	Description
Name	The name of the data source.
Host	The IP address and port number of the MySQL database. Format: `[IP address]:[Port number]`. For more information, see View and manage instance endpoints and ports.
Database	The name of the MySQL database.
User	The username that is used to log on to the MySQL database. Important We recommend that you use a read-only account with fewer permissions. Otherwise, server security may be threatened.
Password	The password that is used to log on to the MySQL database.

Configure other parameters based on your business requirements. For more information, see MySQL data source.

Click Save & test.
If Database Connection OK appears on the page, your MySQL database is added to Grafana as a data source.
Note
If an error is reported after you click Save & test, check whether the IP address, database name, username, and password are valid.
If all information that you specify is valid and your MySQL database is an ApsaraDB RDS for MySQL instance, add the workspace to the whitelist of the ApsaraDB RDS for MySQL instance: For more information, see Configure an IP address whitelist.
If the workspace accesses the database over the Internet, perform the following steps: In the Managed Service for Grafana console, find the workspace that you want to manage on the Workspace Management page and click its ID. On the workspace details page, obtain the public IP address from the Basic Information section. Then, add the public IP address to the whitelist of the ApsaraDB RDS for MySQL instance.
If the workspace accesses the database over a virtual private cloud (VPC), perform the following steps: In the Managed Service for Grafana console, find the workspace that you want to manage on the Workspace Management page and click its ID. In the left-side navigation pane of the workspace details page, click Whitelist and Security Group. On the Private IP Address Whitelist tab, view the VPC associated with the workspace. Add the CIDR block of the VPC, such as 192.168.0.0/16, to the whitelist of the ApsaraDB RDS for MySQL instance. Then, check whether the security group of the VPC allows traffic to and from the port of the MySQL database.

Grafana 10.0.x

On the Grafana homepage, click the icon.
In the left-side navigation pane, choose Management > Data sources.
On the Data sources page, click + Add new data source. On the Add data source page, enter MySQL in the search box and click the result that appears.

On the Settings tab, configure the parameters that are described in the following table.

Parameter	Description
Name	The name of the data source.
Host	The IP address and port number of the MySQL database. Format: `[IP address]:[Port number]`. For more information, see View and manage instance endpoints and ports.
Database	The name of the MySQL database.
User	The username that is used to log on to the MySQL database. Important We recommend that you use a read-only account with fewer permissions. Otherwise, server security may be threatened.
Password	The password that is used to log on to the MySQL database.

Configure other parameters based on your business requirements. For more information, see MySQL data source.

Click Save & test.
If Database Connection OK appears on the page, your MySQL database is added to Grafana as a data source.
Note
- If an error is reported after you click Save & test, check whether the IP address, database name, username, and password are valid.
- If all information that you specify is valid and your MySQL database is an ApsaraDB RDS for MySQL instance, add the workspace to the whitelist of the ApsaraDB RDS for MySQL instance: For more information, see Configure an IP address whitelist.
  If the workspace accesses the database over the Internet, perform the following steps: In the Managed Service for Grafana console, find the workspace that you want to manage on the Workspace Management page and click its ID. On the workspace details page, obtain the public IP address from the Basic Information section. Then, add the public IP address to the whitelist of the ApsaraDB RDS for MySQL instance.
  If the workspace accesses the database over a virtual private cloud (VPC), perform the following steps: In the Managed Service for Grafana console, find the workspace that you want to manage on the Workspace Management page and click its ID. In the left-side navigation pane of the workspace details page, click Whitelist and Security Group. On the Private IP Address Whitelist tab, view the VPC associated with the workspace. Add the CIDR block of the VPC, such as 192.168.0.0/16, to the whitelist of the ApsaraDB RDS for MySQL instance. Then, check whether the security group of the VPC allows traffic to and from the port of the MySQL database.

Step 3: Use the data source to create a dashboard

This section describes how to add tables and time series charts to a dashboard based on the following sample data.

Sample MySQL data:

SELECT * FROM grafana_test.table_5

ID	place	user	operate	operate_time	count
1	Beihai	Zhu	Browse	2024-01-05 19:07:58	26
2	Putian	Su	Browse	2024-02-08 20:58:33	17
3	Pingdingshan	Cao	Browse	2024-01-09 09:28:59	63
4	Shuozhou	Ren	Browse	2024-03-28 03:17:06	19
5	Dazhou	Xu	Browse	2024-03-16 18:50:00	24
6	Fangchenggang	Jiang	Browse	2024-04-14 02:15:21	42
7	Benxi	Luo	Browse	2024-03-27 19:51:12	81
8	Yuncheng	Du	Browse	2024-03-29 15:07:18	6
9	Yangjiang	Shao	Browse	2024-01-14 23:23:23	91
10	Suining	Sheng	Browse	2024-04-09 00:43:47	6

Add a table to a dashboard

Grafana 9.0.x

In the left-side navigation bar of the Grafana console, move the pointer over the icon and click Dashboards.
On the Dashboards page, click New Dashboard.
On the New dashboard page, click Add a new panel.
On the Query tab of the Edit Panel page, select the MySQL data source that you added from the Data source drop-down list.
In the A section, click the icon on the right, enter the following query statement, and then set the Format As parameter to Table.
```
SELECT
  'operate',
  'place',
  'ID',
  'operate_time',
  'count',
  'user'
FROM
  grafana_test.table_5
```
Click Apply.
In the upper-right corner, click the icon. Then, specify the name of the dashboard and the directory in which the dashboard resides.
Click Save.

Grafana 10.0.x

On the Grafana homepage, click the icon.
In the left-side navigation pane, click Dashboards. On the page that appears, select New dashboard from the New drop-down list.
On the New dashboard page, click + Add visualization.
In the Select data source dialog box, select the MySQL data source that you added.
In the A section, set the Format parameter to Table, click Code on the right, and then enter the following query statement.
```
SELECT
  'operate',
  'place',
  'ID',
  'operate_time',
  'count',
  'user'
FROM
  grafana_test.table_5
```
Click Apply.
In the upper-right corner, click the icon. Then, specify the name of the dashboard and the directory in which the dashboard resides.
Click Save.

Add a time series chart to a dashboard

Grafana 9.0.x

In the upper-right corner of the dashboard, click the icon, and then click Add a new panel.
On the Visualizations tab that is on the right side of the Edit Panel page, select Time series as the chart type.
On the Query tab of the Edit Panel page, select the MySQL data source that you added from the Data source drop-down list.

Configure the following query parameters in the A section.

Parameter	Description
FROM	The name of the table in the database.
Time column	The name of the time column in the table. Example: operate_time.
Metric column	The metric column of the table. You can also select none, which indicates that no column of the table is specified as the metric or name of a data point.
SELECT	The data to be filtered.
WHERE	The time filter. Enter `$__timeFilter`.
GROUP BY	The data aggregation mode. Enter `time($__interval,none)`. This value indicates that data is grouped and aggregated based on the time interval that is dynamically calculated.
Format As	The formatting method of the chart. Select Time series.

Optional. In the Graph styles section on the right of the Edit Panel page, set the Style parameter to Bars, click the icon below the Bar alignment parameter, and then modify the value of the Line width parameter. This way, data is displayed as a bar chart.
After you complete the preceding steps, click Apply in the upper-right corner of the Edit Panel page.

Grafana 10.0.x

In the upper-right corner of the dashboard, click Add. Then, select Visualization from the drop-down list.
On the Visualizations tab that is on the right side of the Edit Panel page, select Time series as the chart type.
On the Query tab of the Edit panel page, select the MySQL data source that you added from the Data source drop-down list.

In the A section, set the Format parameter to Table, configure the query parameters that are described in the following table, and then click Run query.

Parameter	Description
Dataset	The name of the database whose data you want to query.
Table	The name of the table in the database.
Column	The column of the table whose data you want to query. An asterisk (*) indicates that data in all columns are queried.

Note

If you want to query data in more than one column but not all columns, click the icon below the Column parameter to add more columns.

Optional. Click Code on the right side of the A section to customize the chart in SQL mode.

SELECT 
  'operate_time' as time, 
  SUM('count') as value, 
  'operate' as metric 
FROM 
  grafana_test.table_5
GROUP BY 
  'operate','operate_time' 
ORDER BY 
  'operate_time' ASC

Optional. In the Graph styles section on the right of the Edit Panel page, set the Style parameter to Bars, click the icon below the Bar alignment parameter, and then modify the value of the Line width parameter. This way, data is displayed as a bar chart.
After you complete the preceding steps, click Apply in the upper-right corner of the Edit Panel page.