DocsICP FilingConsole
Documentation
Home Page Managed Service for Grafana User Guide Connection description Add a data source Add and use a MySQL data source

Add and use a MySQL data source

Updated at:
Copy as MD
Product
My Favorites

This topic describes 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 of Grafana, choose image > Data sources.

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

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

    Parameter

    Description

    Name

    The name of the data source.

    Host

    The MySQL address. The format is [Address]:[Port]. For more information, see View and manage instance connection addresses and ports.

    Database

    The name of the MySQL database.

    User

    The username for your MySQL database.

    Important

    We recommend that you use a low-privilege, read-only account. Otherwise, server security may be at risk.

    Password

    The password for the MySQL database.

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

  4. Click Save & test.

    When the page displays Database Connection OK, it indicates that the MySQL data source has been successfully added to Grafana.

    Note

    • If an error occurs after you click Save & test, verify that the host, database name, username, and password are correct.

    • If your MySQL database is an Alibaba Cloud RDS instance and the connection still fails with the correct settings, you must configure a whitelist. For more information, see Configure an IP address whitelist.

      • If you are connecting using a public IP address, go to the Workspace Information page in the Managed Service for Grafana console. In the Basic Information section, find the Public Endpoint and add it to the RDS whitelist.

      • If you are connecting 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 RDS whitelist. Also, ensure that the security group 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, enter MySQL and click the MySQL result.

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

    Parameter

    Description

    Name

    The name of the data source.

    Host

    The MySQL address. The format is [Address]:[Port]. For more information, see View and manage instance connection addresses and ports.

    Database

    The name of the MySQL database.

    User

    The username for your MySQL database.

    Important

    We recommend that you use a low-privilege, read-only account. Otherwise, server security may be at risk.

    Password

    The password for the MySQL database.

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

  5. Click Save & test.

    When the page displays Database Connection OK, it indicates that the MySQL data source has been successfully added to Grafana.

    Note

    • If an error occurs after you click Save & test, verify that the host, database name, username, and password are correct.

    • If your MySQL database is an Alibaba Cloud RDS instance and the connection still fails with the correct settings, you must configure a whitelist. For more information, see Configure an IP address whitelist.

      • If you are connecting using a public IP address, go to the Workspace Information page in the Managed Service for Grafana console. In the Basic Information section, find the Public Endpoint and add it to the RDS whitelist.

      • If you are connecting 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 RDS whitelist. Also, ensure that the security group allows traffic on the MySQL port.

Step 3: Use the data source to create a dashboard

This section shows how to create dashboard panels with table and time series visualizations by using the following sample MySQL data.

SELECT * FROM grafana_test.table_5

ID

Place

User

Action

Operation 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 Edit Panel page, on the Query tab, select your MySQL data source from the Data source drop-down list.

  5. In the query editor for panel A, click the image icon to switch to code mode. Enter the following SQL 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 that appears, choose your MySQL data source.

  5. In the query editor for panel A, set Format to Table. Click Code on the right and enter the following SQL 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 page, on the Query tab, select your MySQL data source from the Data source drop-down list.

  4. In the query builder for panel A, configure the parameters.

    Parameter

    Description

    FROM

    Select the table from your database.

    Time column

    Select the column that contains time values, such as operate_time.

    Metric column

    Select none if you do not want to specify a metric name column.

    SELECT

    Select the data that you want to query.

    WHERE

    Set the time filter to $__timeFilter.

    GROUP BY

    Select time($__interval,none) to group and aggregate data based on a dynamically calculated time interval.

    Format as

    Set the format to Time series.

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

    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 page, on the Query tab, select the MySQL data source.

  4. In the query builder for panel A, set Format to Time series. Configure the parameters as described in the following table, and then click Run query.

    Parameter

    Description

    Dataset

    Select the database that you want to query.

    Table

    Select the table.

    Column

    Select the columns that you want to query. An asterisk (*) selects all columns.

    Note

    To query multiple specific columns, click the image icon in the Column section to add more columns.

  5. (Optional) To customize the query, click Code on the right side of the query editor and enter 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 statement to limit the number of returned rows, and set Format to Time series.

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

    For example, set Bar alignment to center, Line width and Fill opacity to 4, and 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