MySQL On Air. Monitor SQL queries. Monitoring MySQL with PRTG Switching to PRTG is easy and secure

MySQL is open source database management system, based on Structured Query Language (SQL). In addition to being available for free, the advantage of MySQL is that it works on almost all platforms. MySQL is one of the most widely used database systems in the world and is the foundation for many dynamic websites. Along with the open source version, there is also a commercial version of the database management system. To use MySQL in a commercial product, you must purchase a license.

Benefits of MySQL

MySQL holds the largest market share of any open source database. Hence, there is no shortage of books, articles, and support on MySQL.

Compared to other databases, setting up and administering MySQL is easy. Tools such as MySQL Administrator are available to get you started.

One of the biggest advantages of MySQL: lightning speed! Due to its simple structure, MySQL is extremely resource efficient and uses almost no memory.

Manufacturer-
MySQL-ness: the basis for a number of other services

If your database is very slow, returns errors on some queries, or is not available at all, both the performance and quality of all applications that depend on that database will suffer. To provide constant and fastest availability applications, it is necessary to carry out monitoring MySQL databases. Thus, by optimizing the operation of the database, you create the basis for the availability and performance of all your services at different levels.

What makes PRTG a complete MySQL monitoring tool

If your website, online store or web application uses the MySQL database and the database is broken, the site itself will suffer: it may not display correctly, some parts of it may not be available, or the site may not load at all.

PRTG allows you to constantly monitor availability of your MySQL database. With PRTG, you can reduce database downtime and respond immediately to database outages. The result will be increased customer satisfaction and increased employee productivity.

PAESSLER's comprehensive MySQL monitoring tool monitors not only database availability, but also other MySQL performance data. To do this, PRTG automatically queries the databases and measures time The required to establish the connection, execute the SQL query, process the transaction, and terminate the connection. PRTG dashboard displays clear statistics total query time.

PRTG also allows you to track certain datasets from a MySQL database. You can, for example, get some information from an online store or an order database. To do this, simply define your own SQL queries in the PRTG application and monitor the values they return.

In addition to the return values, PRTG will show the query execution time and the number of rows polled in the data tables. Using customizable thresholds, you can trigger automatic alerts and receive notifications, for example, about the imminent depletion of stock of a product in an online store.

One of the many useful features of PRTG is the creation of custom reports. The contents of the reports can be customized in any way you want, and then you can call the reports on the fly or create them automatically(daily, weekly or monthly) and then sent, for example by e-mail.

PRTG allows you to create reports with all the data that is important to your work. The reporting feature is also an ideal way to provide up-to-date information colleagues and superiors. Simply select relevant information and reports will automatically be sent to the appropriate executives. For example, you can forward the most important website statistics to the marketing team, or automatically send the number of open tickets to the helpdesk.

The MySQL monitoring program PRTG has an alarm function. If the database is not available, or if some queries do not return the required (or return incorrect) values, you can configure PRTG to receive automatic notifications about this situation.

Notification can be in the form of an e-mail message, SMS or push notification to a smartphone. You will be informed in a timely manner, before the problem escalates. With the PRTG API, you can also create custom notifications and automatically execute scripts or HTTP actions.

Enjoy the feeling of complete control with PRTG!

PRTG allows you to constantly monitor
MySQL databases even on the go

PRTG installs in minutes and is compatible with most mobile devices.

PRTG controls these and many other manufacturers and applications for you

Switching to PRTG is easy and safe

There are many tools on the market for monitoring MySQL servers. PRTG has the following benefits.

No hidden
fees

Many administrators use a variety of monitoring tools in special cases. Managing this combination of tools takes time and energy. This translates into countless work hours that could have been spent more efficiently. Moreover, it usually fails to get an overview in this way.

Full version of PRTG for 30 days
After 30 days - free version
For extended version - commercial license

How to monitor MySQL database activity with PRTG

It only takes a few simple steps to start monitoring MySQL with PRTG. The PRTG solution comes with a ready-made MySQL monitoring sensor. In general, you can immediately start using this sensor to track basic database information such as availability and runtime.

For more detailed metrics, add your own SQL queries as a separate file.

Setting up a MySQL sensor is easy and doesn't take much time.

1. Create an SQL query

Self-defined SQL queries allow you to track various KPIs, such as:

the number of website visitors;
sales figures in the online store;
open cases in customer support.

The possibilities are almost endless. The only requirement is that an appropriate SQL statement must be specified.

Here is an example of what it might look like:

SELECT count(idorder), sum(value), avg(value), GROUP_CONCAT(customer SEPARATOR ", ") from test.order

2. Saving the script

Save the SQL query to a separate file on your hard drive. Make sure the file has the .sql extension and the following path is selected:

C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\mysql

3. Creating a sensor in PRTG

Before adding a sensor to a PRTG application, make sure your environment meets the requirements (for more information, see our user guide). Pay special attention to the correct access data entry in the device settings, as this device will represent your database in the PRTG application.

Click "Add sensor" on the device that will receive the sensor, search for MySQL and select MySQL-v2-Sensor. In the subsequent dialog box, enter the name of the sensor and the name of the database to be monitored.

Then select the SQL file containing your SQL query (from step 2) and configure the sensor as needed. For example, decide if you just want to track query execution time or also parse and process values from a data table. If you chose the latter option, specify which values should be read from the table.

The PRTG sensor will be added after the dialog is closed. It will look something like this.

PRTG makes your life easier!

Our monitoring software works for you
and notifies potential problems in a timely manner.
It allows you to focus on more important things.

PRTG saves time

By purchasing PRTG, you get a single centralized tool for monitoring the operation of servers and the entire network. Get the ability to monitor your entire infrastructure with our app and dashboard.

PRTG saves nerves

Installing PRTG is very easy. Getting started with PRTG or switching from another monitoring tool is seamless with auto-discovery and ready-made templates for multiple devices.

PRTG saves money

PRTG is worth the money. 80% of our customers report savings in network management. And the costs of PRTG licenses pay off in just a few weeks on average.

Paessler AG conducted an extensive survey of over 600 IT departments worldwide that use PRTG.
The purpose of this survey was to optimize and fine-tune our network monitoring software
which would better meet the needs of administrators.
In our survey, more than 95% of participants were willing to recommend PRTG or have already done so.

Multiple award winning software

Every day we make every effort to provide a powerful and
simple software for our clients.
Of course, we are proud when we have success and gratitude for our work.

PRTG is a comprehensive monitoring tool
databases

PRTG not only supports MySQL, but is also suitable for monitoring other database software. The program has out-of-the-box sensors for all major database environments, including Microsoft SQL, Oracle SQL, and PostgreSQL. In addition, our ADO SQL sensor can be used with almost any other database management system. This sensor allows you to monitor any data source that is available through OLE DB or ODBC.

PRTG: Revolutionary Network Monitoring Software

Using API, you can tailor PRTG individually and dynamically to your specific needs:

HTTP API: access to network monitoring data and object management using HTTP requests
Individual sensors: you can add your own sensors for individual network monitoring
Individual notices: you can add custom notifications to send alerts to external systems
New sensor REST API: Monitors almost anything that supports XML and JSON

Understanding how this or that CMS works, you have to use various tools that make your work easier.
The most interesting topic is working with data base(s). Naturally, to study queries and query results, you need to use something universal. Something that will work stably with both a well-known engine and a self-written system.
Suppose you have a content management system and you need to see how adding new users or changing passwords is implemented.

Most of the tools that allow you to monitor the work with the database are paid [one, two]. I wanted something lighter and more convenient, so I chose mysql-proxy. Although the capabilities of the utility are much wider than I need, I will describe only the main ones. Works under both Windows and Unix systems.

You can download it here: dev.mysql.com/downloads/mysql-proxy
The first step is to decide on the choice of distribution. At the moment the newest version is "MySQL Proxy 0.8.3 alpha", my OS is Windows 7, so all tests will be on it.

After clicking on the “Download” button, you will be asked to log in or register, but there is a download link below without any extra steps. (The Ubuntu and Debian repositories have prebuilt packages, so: sudo apt-get install mysql-proxy)

I want to note that with a size of 7.9MB, the distribution kit includes Lua with support for basic modules.

After downloading the archive, you need to unpack it into a directory convenient for you, take C:\mysql-proxy as an example
To run the application, you need to decide on the parameters.

In this context, a lot depends on what you have installed. As a LAMP, I use Winginx, you can read about this bundle here: winginx.ru
By default, MySQL runs on port 3306, we will not touch it.
We need to change the port for php to connect to the database. To do this, you need to fix php.ini
Find the string "mysql.default_port" and set the port (4040 by default). For more universal work, change "mysqli.default_port" to 4040 as well

The preparatory part is over, let's get down to business.

Option 1. Request monitoring.

For simple monitoring, you need to use a Lua script. As I said, Lua is bundled, so you don't need to install anything new.
Let's create a simple view.lua script in the C:\mysql-proxy\ directory with the content:

Function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then print(string.sub(packet, 2)) end end

Now you can check the result.
For convenience, let's create a view.bat file in the C:\mysql-proxy directory with the contents:

C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script=C:\mysql-proxy\view.lua --log-file="C:\mysql-proxy\mysql-proxy- log.txt" --proxy-backend-addresses=localhost:3306

Proxy-backend-addresses - address of the MySQL server to which we will proxy the request.

By starting the web server and performing any queries to the database, you can see this:

Requests are displayed, well.

Option 2: Monitor requests and write to a file.

To write requests to a file, we will use the regular features of Lua.
Let's create a view-write.lua file in the C:\mysql-proxy\ directory with the content:

Function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then local file = io.open("C:\\mysql-proxy\\sql-log.txt", "a") file:write( string.sub(packet, 2) .. "\n") file:close() print(string.sub(packet, 2)) end end
and bat file - "view-write.bat"
C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script="C:\mysql-proxy\view-write.lua" --log-file="C:\mysql-proxy\ mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306

Result after executing queries (at "C:\mysql-proxy\sql-log.txt")

In addition to displaying the queries themselves, we may want to display the results of these queries.

Option 3. Requests and result

Using the same scheme, we create the “view-result.lua” script:
function read_query(packet) if packet:byte() == proxy.COM_QUERY then print("Query: " .. string.sub(packet, 2)) local file = io.open("C:\\mysql-proxy\ \sql-log.txt", "a") file:write("Query: " .. string.sub(packet, 2) .. "\n") file:close() proxy.queries:append(2, string.char(proxy.COM_QUERY) .. string.sub(packet, 2), (resultset_is_needed = true)) proxy.queries:append(1, packet, (resultset_is_needed = true)) return proxy.PROXY_SEND_QUERY end end function read_query_result( inj) if inj.id == 1 then for row in inj.resultset.rows do local i = 1 local fields = () while row[i] do if row[i] == row then break end local file = io. open("C:\\mysql-proxy\\sql-log.txt", "a") file:write("Response field: " .. inj.resultset.fields[i].name .. " => " .. row[i] .. "\n") file:close() print("Response field: " .. inj.resultset.fields[i].name .. " => " .. row[i]) i = i + 1 end end return proxy.PROXY_IGNORE_RESULT end end

And view-result.bat
C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script="C:\mysql-proxy\view-result.lua" --log-file="C:\mysql-proxy\ mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306

As a result, we get full logging of requests and responses in a readable form

To monitor server load MySQL there is such a good utility - mytop.
Mytop is an open source MySQL performance monitoring command-line tool modeled after the Linux system monitoring tool called top. Mytop connects to MySQL and runs the show processlist and show global status commands periodically and then summarizes the information in a convenient format. With Mytop, you can monitor MySQL threads, queries, and uptime in real time, find out which user is querying which database, identify slow queries, and much more. All of this information can be used to optimize the performance of the MySQL server.

Installation for different systems:

# apt-get install mytop

# yum install mytop

#pkg install mytop

We connect to our MySQL:

# mysql -u root -p Enter password:

It is advisable to create different accounts for each database or for different programs. Therefore, let's create and describe the rights to the necessary databases for the user mytopuser:

Mysql> create user "mytopuser"@"localhost" identified by "password"; mysql> grant all on *.* to 'mytopuser'@'localhost' identified by 'password';

Create a settings file in your home directory ~/.mytop and add the connection configuration to it:

# mcedit .mytop user=mytopuser pass=dgbple host=localhost db=asterisk port=3306 color=1 delay=3 idle=0

where user and pass are the login and password that we created.

host, port - MySQL server address and port

db - the database to connect to

delay - so that the mytop display is updated every 3 seconds

idle - determines whether waiting threads should be output to mytop. By default, such flows are taken into account. By omitting waiting threads, mytop reverses the default sort order, putting long queries at the top of the list.

Let's change the permissions for this file:

# chmod 700 .mytop

We start the utility:

#mytop

You can display data for a specific database, for example:

# mytop -u mytopuser -p password -d goip

On the first line: the hostname and version of the running server MySQL. Upper right corner - server uptime (uptime) days+hours+minutes+seconds.

In the second line: the total number of queries processed to the database (Queries:), the average number of queries per second (qps:), and the number of slow queries (Slow:).

The third line shows information about the processes: total, active, cached.

In the fourth line: information about the effectiveness of the key buffer - how often MySQL finds the keys in the buffer without going to disk, the average number of bytes sent and received by the server, and the number of bytes currently being sent.

The second part of the screen shows all active threads (including the one using MyTOP). It displays in tabular form the username, database and host name, as well as the current query and status.

In the standard output of mytop, queries are shortened. To view the full post, click F. The program will ask:

Full query for which thread id:

Enter the thread id to view the requested request.

For an explanation of the request, click E, and then the program will provide data about the running query, which will help to find out if the query is being optimized. EXPLAIN is one of the most powerful tools for understanding and optimizing MySQL queries.

To close this mode, press any key; to return standard output, press t.

Another useful view of mytop is available by pressing the C key. It shows running commands.

The Command column shows the type of command or query being run. The Total column indicates the total number of commands of this type since the server was started, and Pct shows this number as a percentage. The Last column shows the number of commands of this type since mytop was last updated. In general, this data gives an idea of the performance of the MySQL server in the short and long term.

Conclusion

This tool allows you to detect and optimize complex SQL queries in time, thereby increasing the overall performance of the server.

In contact with

I want to note that with a size of 7.9MB, the distribution kit includes Lua with support for basic modules.

After downloading the archive, you need to unpack it into a directory convenient for you, take C:\mysql-proxy as an example
To run the application, you need to decide on the parameters.

The preparatory part is over, let's get down to business.

Option 1. Request monitoring.

Function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then print(string.sub(packet, 2)) end end

Now you can check the result.
For convenience, let's create a view.bat file in the C:\mysql-proxy directory with the contents:

C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script=C:\mysql-proxy\view.lua --log-file="C:\mysql-proxy\mysql-proxy- log.txt" --proxy-backend-addresses=localhost:3306

Proxy-backend-addresses - address of the MySQL server to which we will proxy the request.

By starting the web server and performing any queries to the database, you can see this:

Requests are displayed, well.

Option 2: Monitor requests and write to a file.

To write requests to a file, we will use the regular features of Lua.
Let's create a view-write.lua file in the C:\mysql-proxy\ directory with the content:

Result after executing queries (at "C:\mysql-proxy\sql-log.txt")

In addition to displaying the queries themselves, we may want to display the results of these queries.

Option 3. Requests and result

And view-result.bat
C:\mysql-proxy\bin\mysql-proxy.exe --proxy-lua-script="C:\mysql-proxy\view-result.lua" --log-file="C:\mysql-proxy\ mysql-proxy-log.txt" --proxy-backend-addresses=localhost:3306

As a result, we get full logging of requests and responses in a readable form

Mytop is a command line tool that is similar to top command but is designed to control MySQL. MySQL version 3.22 or later is required to work, and the current version of MySQL 5.x is also supported. Mytop works on most Linux systems. In this article, we will use Ubuntu as the basis. Perl, DBI and Term::ReadKey are required to work. With Term::ANSIColor you can output any color you want. Version 0.7 also works on windows.

Installation

Before installation, you must install all the programs specified in the previous paragraph. Mytop is installed like this:

Debian: apt-get install mytop Ubuntu: sudo apt-get install mytop

Or you can download the tar ball and install it:

Tar -zxvf mytop-x.x.x.tar.gz (your version number)
cd mytop-x.x.x. (your version number)
perl Makefile.PL
make
make test
make install

Complete the commands above to extract the data and complete the installation. For CentOS and RedHat users, you will need to download the rpmforge repository for automatic installation, or you can use the manual installation method.

Using mytop

After installation, let's move on to using mytop:

Mytop --prompt (set the password to prompt, then press enter to confirm the password)

This is the standard way, you can also set a password like this:

Mytop -u root -p (necessary to use the mysql password here because the "root" user is specified)

If an error like "root: NO PASS" or something similar occurs, enter the following:

Mytop -u root -p xxxx (replace "xxxx" with our password)

Before using these commands, you need to check if the MySQL server is running.

After checking, just press enter.

MySQL on localhost (5.0.8) up 0+00:32:51 (11:20:10)
Queries: 19.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00
Qps now: 0 Slow ups: 0.0 Threads: 1 (1/ 0) 00/00/00/00
Key Efficiency: 100% Bps in/out: 7.3/907.3 Now in/out: 8.4/1.3k
Id User Host/IP DB Time Cmd Query or State
2 root localhost test 0 Query show full process

The first line displays the server name (localhost) and the version of MySQL running on our system. The line highlighted in nuclear blue indicates the time during which the MySQ server has been operating in working mode, the format is days + hours: minutes: seconds, in addition, the current system time is indicated.

The second line indicates the number of requests that the system has processed since the start of work.

The third line displays the number of threads.

The second part of the image shows the number of threads MySQL is processing. Threads are sorted according to their idle time least idle first.

Some options can be skipped with mytop:

U or user username: Usually used to determine the username during mytop registration. The default is "root".

P or pass or password password: Passwords used to authorize the user. There is no default password, so the password field can be left blank.

H or host hostname[:port]: To specify ports if MySQL does not use the standard port. The default is 3306.

S or -delay seconds: Specifies the time interval for updating. The default is 5.

To view other options for mytop, type the following:

Man my top

man contains enough information about mytop.

Let's look at the configuration file created to access and control our data:

User=root #Set username.
pass= #Set password.
host=localhost #hostname.
db=test #database name.
delay=5 #time interval to update control output.
port=3306 #port to use in mytop.
socket= #if you define any socket, mytop will ignore the port number and hostname, you can remove this line if you don't need it.
batchmode=0 #gives the exact number of queries processed by MySQL.
header=1 #Set if you want to display a header. Displayed with the h key when mytop is running.
color=1 #Set if you want to use different colors (only if you support colors).
idle=1 #to display idle processes in the process list.

The configuration file is created as follows:

Mcedit ~/.mytop file will be created in the base folder, any editor can be used

To view other mytop options, type: ?, this will display the entire list of keyboard shortcuts that are used in mytop.

MySQL On Air. Monitor SQL queries. Monitoring MySQL with PRTG Switching to PRTG is easy and secure

Benefits of MySQL

Manufacturer- MySQL-ness: the basis for a number of other services

What makes PRTG a complete MySQL monitoring tool

PRTG allows you to constantly monitor MySQL databases even on the go

Switching to PRTG is easy and safe

No hidden fees

How to monitor MySQL database activity with PRTG

1. Create an SQL query

2. Saving the script

3. Creating a sensor in PRTG

PRTG makes your life easier!

PRTG saves time

PRTG saves nerves

PRTG saves money

Multiple award winning software

PRTG is a comprehensive monitoring tool databases

PRTG: Revolutionary Network Monitoring Software

Option 1. Request monitoring.

Option 2: Monitor requests and write to a file.

Option 3. Requests and result

Option 1. Request monitoring.

Option 2: Monitor requests and write to a file.

Option 3. Requests and result

Installation

Using mytop

Manufacturer-
MySQL-ness: the basis for a number of other services

PRTG allows you to constantly monitor
MySQL databases even on the go

No hidden
fees

PRTG is a comprehensive monitoring tool
databases