DocsICP FilingConsole
官方文档
首页

Add and use a MySQL data source

更新时间：
复制 MD 格式
产品详情
我的收藏

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

Step 1: Log on to Grafana

  1. Log on to the Managed Service for Grafana console. In the left-side navigation pane, click Workspace Management.

  2. 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

  1. In the left-side navigation pane, choose image > Data sources.

  2. On the Data sources tab, click Add data source. In the search box, search for and select MySQL.

  3. On the Settings tab, configure the following parameters.

    Parameter

    Description

    Name

    The name of the data source.

    Host

    The address of the MySQL database in the format [address]:[port]. For more information about how to obtain the address, see View and manage instance connection addresses and ports.

    Database

    The name of the MySQL database.

    User

    The MySQL database username.

    Important

    For security, use a read-only account with limited permissions. Using a high-privilege account can expose your server to significant security risks.

    Password

    The MySQL database password.

    Configure other parameters as needed. For more information, see the official Grafana documentation.

  4. Click Save & test.

    If the Database Connection OK message is displayed, the MySQL data source has been added successfully.

    Note

    • If an error occurs after you click Save & test, check whether the MySQL host address, database name, username, and password are correct.

    • If all the information is correct and your MySQL database is an ApsaraDB RDS for MySQL instance, you must configure a whitelist. For more information, see Set an IP whitelist.

      • If you connect using a public IP address, go to the Workspace Information page in the Managed Service for Grafana console. In the Basic Information section, obtain the Public Endpoint and add it to the whitelist of the ApsaraDB RDS for MySQL instance.

      • If you connect using a private IP address, go to the Whitelists and Security Groups page in the Managed Service for Grafana console. On the Private network whitelist settings tab, find the VPC and add its CIDR block (for example, 192.168.0.0/16) to the whitelist of the ApsaraDB RDS for MySQL instance. Also, ensure that the security group for the data channel allows traffic on the MySQL port.

Grafana 10.0.x

  1. On the Grafana homepage, click the image icon.

  2. In the left-side navigation pane, choose Management > Data sources.

  3. On the Data Source tab, click + Add new data source. In the search box, search for and select MySQL.

  4. On the Settings tab, configure the following parameters.

    Parameter

    Description

    Name

    The name of the data source.

    Host

    The address of the MySQL database in the format [address]:[port]. For more information about how to obtain the address, see View and manage instance connection addresses and ports.

    Database

    The name of the MySQL database.

    User

    The MySQL database username.

    Important

    For security, use a read-only account with limited permissions. Using a high-privilege account can expose your server to significant security risks.

    Password

    The MySQL database password.

    Configure other parameters as needed. For more information, see the official Grafana documentation.

  5. Click Save & test.

    If the Database Connection OK message is displayed, the MySQL data source has been added successfully.

    Note

    • If an error occurs after you click Save & test, check whether the MySQL host address, database name, username, and password are correct.

    • If all the information is correct and your MySQL database is an ApsaraDB RDS for MySQL instance, you must configure a whitelist. For more information, see Set an IP whitelist.

      • If you connect using a public IP address, go to the Workspace Information page in the Managed Service for Grafana console. In the Basic Information section, obtain the Public Endpoint and add it to the whitelist of the ApsaraDB RDS for MySQL instance.

      • If you connect using a private IP address, go to the Whitelists and Security Groups page in the Managed Service for Grafana console. On the Private network whitelist settings tab, find the VPC and add its CIDR block (for example, 192.168.0.0/16) to the whitelist of the ApsaraDB RDS for MySQL instance. Also, ensure that the security group for the data channel allows traffic on the MySQL port.

Step 3: Create a dashboard

This section uses sample MySQL data to demonstrate how to create table and time series panels for a dashboard.

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

Create a table panel

Grafana 9.0.x

  1. In the left-side navigation pane, choose image > Dashboards.

  2. On the Dashboards page, click New Dashboard.

  3. On the New Dashboard page, click Add a new panel.

  4. On the Query tab of the Edit Panel page, select your MySQL data source from the Data source drop-down list.

  5. Click the image icon on the right side of the A section, enter the following query, and then set Format As to Table.

    SELECT
  `operate`,
  `place`,
  `ID`,
  `operate_time`,
  `count`,
  `user`
FROM
  grafana_test.table_5

  6. Click Apply.

  7. In the upper-right corner, click the Save dashboard icon. Then, specify the name of the dashboard and the directory in which the dashboard resides.

  8. Click Save.

Grafana 10.0.x

  1. On the Grafana homepage, click the image icon.

  2. In the left-side navigation pane, click Dashboards. On the page that appears, select New dashboard from the New drop-down list.

  3. On the New dashboard page, click + Add visualization.

  4. In the Select data source panel, select your MySQL data source.

  5. In the A section, set Format to Table. Then, click Code on the right and enter the following query.

    SELECT
  `operate`,
  `place`,
  `ID`,
  `operate_time`,
  `count`,
  `user`
FROM
  grafana_test.table_5

  6. Click Apply.

  7. In the upper-right corner, click the Save dashboard icon. Then, specify the name of the dashboard and the directory in which the dashboard resides.

  8. Click Save.

Create a time series chart

Grafana 9.0.x

  1. In an existing dashboard, click the image icon in the upper-right corner, and then click Add a new panel.

  2. On the right side of the Edit Panel page, in the Visualization area, set the chart type to Time series.

  3. On the Edit Panel tab, in the Query section, select your MySQL data source from the Data source drop-down list.

  4. In the A section, configure the query parameters.

    Parameter

    Description

    FROM

    Select the name of the table in your database.

    Time column

    Select the time column from the table, such as operate_time.

    Metric column

    Select the metric column from the table. You can also select none, which indicates that no specific column is used as the metric (or name) for data points.

    SELECT

    Specify the data you want to display.

    WHERE

    Select $__timeFilter as the time filter.

    GROUP BY

    Select time($__interval,none). This groups and aggregates data by a dynamically calculated time interval.

    Format As

    Select Time series as the format.

  5. (Optional) In the Graph styles section on the right, you can set Style to Bars, set Bar alignment to image, and modify the value of Line width to display the data as a bar chart.

    For example, set Line width to 3 and Fill opacity to 100.

  6. After you complete the settings, click Apply in the upper-right corner.

Grafana 10.0.x

  1. In an existing dashboard, click Add in the upper-right corner and select Visualization from the drop-down list.

  2. On the right side of the Edit Panel page, in the Visualization area, set the chart type to Time series.

  3. On the Edit Panel tab, in the Query section, select your MySQL data source.

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

    Parameter

    Description

    Dataset

    Select the name of your database.

    Table

    Select the name of the table.

    Column

    Select the columns that you want to query. You can also select an asterisk (*) to query all columns.

    In the visualization type drop-down list in the upper-right corner, select Time series. In the query section, select MySQL from the Data source drop-down list.

    Note

    If you want to query multiple specific columns but not all of them, click the image icon next to the Column field to add more columns.

  5. (Optional) On the right side of the A section, click Code to customize the chart by using an SQL query.

    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

    Add LIMIT 50 to the end of the SQL query to limit the number of rows returned, and set Format to Time series.

  6. (Optional) In the Graph styles section on the right, you can set Style to Bars, set Bar alignment to image, and modify the value of Line width to display the data as a bar chart.

    For example, set Bar alignment to center, set both Line width and Fill opacity to 4, and set Gradient mode to None.

  7. After you complete the settings, click Apply in the upper-right corner.

Previous：Add and use a Managed Service for Prometheus data sourceNext：Add and use a Zabbix data source