[ad_1]
Grafana is a popular open source observability solution that allows you to visualize metrics in graphical dashboards. Grafana has connectors for a wide selection of data sources, including time series databases, search engines, and individual applications. In this article, you will learn how to use Grafana to set up simple monitoring for a MySQL database server using the official Grafana Cloud integration.
Starting
Grafana is available for self-hosting on your own hardware or as a managed SaaS platform called the Grafana Cloud. We use Grafana Cloud in this tutorial because it supports quick start integrations that connect to your data source and provides pre-built dashboard layouts for the most common metrics.
Login to your Grafana account to get started. You can create a new Grafana Cloud account for free to store up to 10,000 sets of metrics and 50 GB of logs.
Add MySQL integration
You can add the MySQL integration to your account by going to the “Integrations & Connections” page, accessed from the lightning bolt icon on the left sidebar. Enter “mysql” in the search bar at the top of the screen.
You should see two results appear, both labeled “MySQL”. Choose the one marked “Integration”. Integrations include a simplified setup experience and pre-built dashboard layouts for monitoring key server stats. The alternative option, “Data Source”, requires you to manually connect to your server, pull metrics, and assemble dashboards.
The next screen will ask you to select the operating system and processor architecture of the machine hosting your MySQL server. Adjust the dropdown menu options to match your system. Press the blue “Install Integration” button to view the corresponding Grafana Agent installation instructions.
Installing the Grafana Agent
Grafana Agent is a utility that runs as a system service to collect metrics from your machines. It sends data to your Grafana account, where it will appear on your dashboards. The agent is a stripped down version of the Prometheus data collector.
Running the script shown on the MySQL integration screen will download the Grafana Agent, configure it with your Grafana Cloud account, and configure MySQL monitoring. Data will be collected every 60 seconds when using the default settings.
Copy the script shown and run it on the server that hosts your MySQL databases. You will see a few lines of output as the script downloads the binary and retrieves its configuration file.
Verify that the Grafana Agent service has started successfully before continuing:
$ sudo systemctl status grafana-agent.service
● grafana-agent.service - Monitoring system and forwarder
Loaded: loaded (/lib/systemd/system/grafana-agent.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2022-08-13 17:39:03 UTC; 39s ago
Docs: https://grafana.com/docs/agent/latest/
Press the “Test Integration” button in your Grafana Cloud account to verify that everything is ready to use. The agent should send data to Grafana.
You can now hit “View Dashboards” to start interacting with the visualizations included with the integration.
Exploring your boards
The MySQL integration comes with two panels: MySQL Overview and MySQL Logs. The overview dashboard provides charts that cover all aspects of your MySQL server operation, including uptime, queries per second, active connections, queries, ratings, and network activity. You can use these metrics to interrogate MySQL performance and identify optimization opportunities.
The Logs view provides a source of the log files written by your MySQL server instance. The integration automatically collects logs within the /var/log/mysql directory. The dashboard includes graphs of the number of log lines that have been written, broken down by severity level and error code.
Dashboards can be customized by clicking the settings icon in the top right corner and pressing the “Make Editable” button. This will allow you to change the dashboard settings using the other controls on the settings screen. You will also be added to add and edit the graphic panels displayed on the dashboard.
Grafana Agent MySQL Server Authentication
One challenge you might encounter concerns the MySQL user account that the Grafana Agent uses to access its database. The agent is configured to use root default. This will not work if MySQL root login is disabled on your server, or if you have blocked root connections to specific databases.
You can improve security and regain control by creating a dedicated MySQL user for Grafana. Run the following commands in a MySQL shell to add a user and password, then grant it privileges to access your data:
> CREATE USER 'grafana'@'localhost' IDENTIFIED BY '<your-password>'; > GRANT ALL PRIVILEGES ON *.* TO 'grafana'@'localhost'; > FLUSH PRIVILEGES;
The granting of privileges in *.* allows the user to access any table on your server, in all schemas. could you change the GRANT to restrict Grafana privileges or limit interactions to specific schemas and tables. However, the integration works best with elevated privileges that include MySQL system tables. This allows you to collect the most comprehensive selection of metrics.
Once you have created your user, edit your Grafana Agent configuration file to authenticate with your credentials. You will find this in /etc/grafana-agent.yaml. Look for the following section:
the data_source_name The field defines the MySQL server address and user credentials. Modify it to refer to your new account.
data_source_name: grafana:<your-password>@(localhost:3306)/
Restart the Grafana Agent service to apply the change.
$ sudo service grafana-agent restart
Summary
Grafana’s built-in MySQL integration provides a convenient way to monitor the performance of your database server. Included dashboard layouts aggregate your error logs and graph all commonly measured metrics, keeping you on top of resource utilization and query activity.
Regular monitoring of this data can help you spot emerging performance trends and resolve anomalies before they become a problem. Manually recording and retrieving these metrics would be a chore, while Grafana’s dashboards give you everything on one screen, making efficient analysis easy.
[ad_2]
