Capture MySQL metrics with Longview
In addition to capturing general system metrics, Longview can also be used to capture metrics for MySQL. The MySQL tab appears in Cloud Manager when Longview detects that you have MySQL installed on your system. It can help you keep track of MySQL's queries, system resource consumption, and other information.
In order to use Longview to capture data for MySQL, you must have the Longview Agent successfully installed on the system you wish to monitor. See Create a Longview Client and Install the Longview Agent.
In this guide:
This guide covers using Longview with MySQL and includes the following topics:
- Configuring Longview for MySQL.
- Interacting with the MySQL data provided by Longview in Cloud Manager.
- Troubleshooting Longview for MySQL.
Configure Longview with MySQL
Automatic configuration
These instructions are compatible only with Debian and Ubuntu and do not work on CentOS.
If MySQL is installed and running when you install the Longview agent, Longview should automatically configure itself for MySQL. If you install MySQL after setting up the Longview agent, you can perform the steps below to configure Longview with any available software integrations (Apache, NGINX, and MySQL). Any existing Longview data is not affected and will remain accessible.
-
SSH into the Compute Instance you are monitoring with Longview.
-
Ensure that MySQL is running.
sudo systemctl status mysql
-
Run the automatic Longview configuration command on your system:
dpkg-reconfigure -phigh linode-longview
On many systems, Longview should be able to configure itself automatically. If this is the case, your output should be similar to the following:
Checking MySQL configuration... Successfully connected to MySQL
Once you see this successful message, the Longview should automatically start collecting MySQL data. Refresh Longview in Cloud Manager to start viewing your MySQL metrics for your Longview Client instance.
Unless you already have a specific Longview database user set up in the
/etc/linode/longview.d/MySQL.conf
file, Longview will locate and use thedebian-sys-maint
database user credentials if it can, located at/etc/mysql/debian.cnf
.If you receive a failure message or the popup shown below, you should visit the Troubleshooting section at the end of this article.
Manual configuration
These instructions work for all supported distributions, including Debian, Ubuntu, and CentOS.
To enable the MySQL Longview integration manually, follow these steps on your system via SSH:
You cannot configure the location of a socket for the Longview client.
-
SSH into the Compute Instance you are monitoring with Longview.
-
Log into MySQL. For example, to log in as the root user:
sudo mysql -u root -p
-
Create a new MySQL user with minimal privileges for Longview. Run the following queries on your database as the root MySQL user to create the new user. Ensure your replace
*****************
with your desired password.CREATE USER 'linode-longview'@'localhost' IDENTIFIED BY '***************'; flush privileges;
-
Exit the MySQL:
exit
-
Edit
/etc/linode/longview.d/MySQL.conf
to include the same username and password you just added. It should look like the following:username linode-longview password example_password
-
Restart Longview:
sudo systemctl restart longview
-
Refresh Longview in Cloud Manager to verify that the MySQL tab is now present and collecting data for your Longview client instance.
You should now be able to see Longview data for MySQL. If that's not the case, proceed to the Troubleshooting section at the end of this article.
View MySQL metrics
-
Log in to Cloud Manager and select the Longview link in the sidebar.
-
Locate the Longview Client you have configured for MySQL and click the corresponding View details link.
-
Select the MySQL tab.
You'll see the current version of MySQL listed on the upper left-hand corner.
Hover over a data point to see the exact numbers for that time. You can also zoom in on data points, or view older time periods with Longview Pro. For details, jump to this section in the main article about navigating the Longview interface. The next sections cover the Longview MySQL App in detail.
Queries
The Queries graph shows the total number of select, update, insert, and delete queries MySQL handled at the selected time.
Throughput
The Throughput graph shows the amount of data that MySQL sent and received at the time selected.
Connections
The Connections graph shows all of the MySQL connections at the selected time.
Slow queries
The Slow Queries graph shows the number of slow MySQL queries at the selected time.
Aborted
The Aborted graph shows the number of aborted MySQL connections and clients at the selected time.
Cpu
The CPU graph shows the percentage of your system's CPU being used by MySQL at the selected time. If you want to see the total CPU use instead, check the Overview tab.
Ram
The RAM graph shows the amount of RAM or memory being used by MySQL at the selected time. If you want to see your system's total memory use instead, check the Overview tab.
Disk io
The Disk IO graph shows the amount of input to and output from the disk caused by MySQL at the selected time. To see the total IO instead, visit the Disks tab.
Process count
The Process Count graph shows the total number of processes on your system spawned by MySQL at the selected time. If you want to see more details, and how this stacks up against the total number of processes on your system, see the Processes tab.
Troubleshooting
If you don't see Longview data for MySQL, you'll instead get an error on the page and instructions on how to fix it. As a general tip, you can check the /var/log/linode/longview.log
file for errors as well.
Unable to automatically configure MySQL popup
If you run the automatic Longview configuration tool, and get the popup message shown below:
This indicates that Longview can't locate any valid MySQL user credentials, so it will create some for itself (in the /etc/linode/longview.d/MySQL.conf
file) and ask you to add them to MySQL. To finish getting Longview set up:
-
Copy the command shown in the popup message. You will need it for the next steps.
-
Log in to your database as the root MySQL user:
sudo mysql -u root -p
-
Run the query that was shown in the popup message to create the Longview user. Ensure you replace
*****************
with the password provided to you by the popup.CREATE USER 'linode-longview'@'localhost' IDENTIFIED BY '***************'; flush privileges;
Refresh Longview in Cloud Manager to verify that the MySQL tab is now present and collecting data for your Longview client instance.
If you've added the credentials to MySQL and it still doesn't work, double-check your MySQL installation, and then do a manual configuration.
Unable to connect to the database, no credentials found
-
You may receive this error:
Unable to connect to the database, no credentials found.
This indicates that you need to add a MySQL user for Longview, and make sure the Longview configuration file has the appropriate credentials. See the manual configuration section for details.
-
You may also encounter this error message:
Unable to connect to the database: Authentication plugin 'sha256_password' cannot be loaded: /usr/lib/x86_64-linux-gnu/mariadb18/plugin/sha256_password.so: cannot open shared object file: No such file or directory.
If this is the case, follow the above instructions for manual configuration.
Unable to connect to the database
This error will state Unable to connect to the database:
and then specify a reason. An incorrect password is one example of something that can generate this type of error. The list of errors that could cause this issue is pretty long, so you may want to reference the MySQL documentation if you need help understanding a specific error message.
Unable to collect MySQL status information
If you receive the error Unable to collect MySQL status information
, this indicates that Longview was able to connect to the MySQL database, but the query it uses to collect statistics has failed. This could occur if the database crashes while the query is being executed. The specific reason that it failed will be listed with the error. If the problem persists, contact Linode support.
MySQL tab is missing
If the Longview MySQL tab is missing entirely, this indicates that MySQL is either not installed, or has stopped. If you restart MySQL, you will be able to see the tab again and view all of your old data.
Updated about 2 months ago