Shulou(Shulou.com)06/02 Report--

This article will explain in detail how to obtain and output information about MYSQL proxysql. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

The first two issues of ProxySQL focus on different ways of MYSQL's high availability, and this issue will delve deeper into some of PROXYSQL's own things.

There is a table in PROXYSQL that stores all the global variables and contains a large number of variable values. In fact, these are key-value pairs. It is divided into two parts with MYSQL as prefix and admin as prefix.

The variables prefixed with mysql are mainly related to MYSQL.

1 mysql-auditlog_filename this variable is mainly to establish an auditlog, give the relevant path and file name, will generate auditlog in the specified location, auditlog is mainly to record the account, address and operation of the database object connected to the middleware proxysql, using the format of json.

Update global_variables set variable_value ='/ proxysql/log/audit.log' where variable_name = 'mysql-auditlog_filename'

It contains the access time, database account number, access source address and other information, basically

2 some statements that record access to MYSQL are filtered by different conditions

Starting from proxysql 2.0.6, you can record the relevant records in the query. By default, this switch is off.

Configuration is relatively simple, for example, if we monitor the statements executed by an application account, we can do the following.

SET mysql-eventslog_filename='queries.log'

SET mysql-eventslog_default_log=1

INSERT INTO mysql_query_rules (rule_id, active,username, log,apply) VALUES

SET mysql-eventslog_format=2

SAVE MYSQL VARIABLES TO DISK

LOAD MYSQL VARIABLES TO RUNTIME

After the operation is completed, all statements operated by the contact account will be recorded in the queries.log file.

3 about the work of proxysql

Although PROXYSQL is more leather and durable, it is important to understand some of the operational status of the internal system.

Pages that jemalloc_active has been assigned to the application

Bytes that jemalloc_allocated has allocated to the application

Memory allocated by jemalloc_metadata Metabase

Memory allocated by Auth_memory authentication module

Memory allocated by SQLlite3_memory_bytes PROXYSQL

As mentioned above in query_digest_memory, the memory allocated to filter the statements executed

ProxySQL itself also makes a count of the statements executed, as shown in the following

Select * from global_variables where variable_name = 'mysql-commands_stats'

Default statistics of the statements accepted by PROXYSQL, and related statistics based on the feedback of the statements

SELECT * FROM stats_mysql_commands_counters

The relevant commands can be counted according to the existing statistical items, which makes up for the trouble of obtaining statistical data in this part of MYSQL.

The above figure shows the status of the current external access MYSQL by accessing stats_mysql_connection_pool. This table shows how many connections are currently made to the specified database server, and the resulting queries, the resulting data sends bytes, receives bytes, and so on.

In MYSQL, you can view the emptying of the session that is currently interacting with MYSQL through show processlist, and stats_mysql_processlist is also provided in PROXYSQL to display the current connection of the system.

MYSQL 5.7 already has the function of tracking history query, proxysql also provides similar functions and statistical information will be more comprehensive and convenient to extract.

Select * from stats_mysql_query_digest

Or count the current database-related account connections.

Mysql itself has many ways to count relevant information, but proxysql does these statistics better and will not conflict with MYSQL itself when extracting them.

In addition, proxysql also has related rich logging functions. There is a monitor database in the database, which stores related monitoring records. Here are a few examples.

1 how to judge the network response between proxysql and database server

Through the following statement

Select * from mysql_server_ping_log where time_start_us in (select time_start_us from mysql_server_ping_log order by time_start_us desc limit 3)

By traversing the latest records, you can determine connectivity and latency between the database and PROXYSQL, or problems that cannot be connected at all.

2 time between PROXYSQL and database connection

Select * from mysql_server_connect_log where time_start_us in (select time_start_us from mysql_server_connect_log order by time_start_us desc limit 3)

(3) to judge who is the master and who is the slave between the current master library and the slave database.

Select hostname,time_start_us,success_time_us,read_only from mysql_server_read_only_log where time_start_us in (select time_start_us from mysql_server_read_only_log order by time_start_us desc limit 5)

What is the relationship among these three? 1 the most basic thing is whether the middleware and the database are connected. If there is a problem here, it indicates whether there is a problem with the network between the middleware and the database or the connected database itself. If there is a problem, you need to give an alarm.

2 at this time, the problem between the connection with the database is 1 to judge, while 2 is to judge the state of the connection between PROXYSQL and the database. The main attention here is the connection time.

The third is to determine where the destination of the data flow is, and whether there is a master database in the overall data replication collection. Through this log, we can judge the flow direction of the current data flow and the status of the master-slave library.

So it is entirely possible to write a set of programs through PROXYSQL to determine the status of the current overall MYSQL replication set.

In addition, the stats_ of PROXYSQL can obtain various states through MYSQL protocol, and some open source monitoring software PMM can also monitor it. PROXYSQL also provides WEB mode for monitoring mode, and the data provided is limited, but it is also a good method when there is no other way of blessing and graphical monitoring.

Let's call it a day, but PROXYSQL itself is powerful, such as read-write separation and control of how read-write separation operates after master-slave delay.

About how MYSQL proxysql information acquisition and information output is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.