Shulou(Shulou.com)05/31 Report--

How to query log records in mysql? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

1. Enable the configuration

:

Add to the mysql configuration file my.cnf

Log-slow-queries=/var/lib/mysql/slowquery.log (specify the location of the log file, which can be empty, and the system will give a default file host_name-slow.log)

Long_query_time=2 (record time exceeded, default is 10s)

Log-queries-not-using-indexes (query that does not use index under log, you can decide whether to enable it or not according to the situation)

Log-long-format (if set, all queries that do not use indexes will also be recorded)

Windows:

Add the following statement to [mysqld] of my.ini:

Log-slow-queries = E:webmysqllogmysqlslowquery.log

Long_query_time = 2 (other parameters as above)

2, viewing method

Linux:

Use the mysql native command mysqldumpslow to view

Common command

-s ORDER what to sort by (t, at, l, al, r, ar etc), at' is default

-t NUM just show the top n queries

-g PATTERN grep: only consider stmts that include this string

Eg:

S, is the order of order, which means that the writing is not detailed enough. I used it, including reading the code, there are mainly c _ query, lock _ r and ac,at,al,ar, which are sorted according to the number of times, time, time of lock and the number of records returned. The reverse order of an is added in front of it, which means top n, that is, to return how many items of data-g in front, you can write a regular matching pattern after that, which is case-insensitive.

Mysqldumpslow-s c-t 20 host-slow.log

Mysqldumpslow-s r-t 20 host-slow.log

The above command shows the 20 most visited sql statements and the 20 sql that returned the most recordsets.

Mysqldumpslow-t 10-st- g "left join" host-slow.log this returns the first 10 sql statements with left concatenation in time.

The mysql slow log is very useful for tracking problematic queries. You can analyze the resource-consuming sql statements in the current program, so how to open the mysql slow log record?

In fact, it is easy to open the slow log of mysql. You only need to add the following code under [d] in the configuration file of mysql (the system is my.ini,linux and the system is my.cnf):

Log-slow-queries=/var/lib/mysql/slowquery.log

Long_query_time=2

Note:

The log-slow-queries setting writes the log there, and when it is empty, the system will give the slow log a hostname and append slow.log. / var/lib/mysql/slowquery.log is the location of the files stored in the log. Generally, this directory must have the writeable permission of the running account of mysql. Generally, this directory is set to the data storage directory of mysql.

The 2 in long_query_time=2 means that the query takes more than two seconds to record.

If the parameter log-long-format is set, all queries that do not use the index will also be logged. Add the following line to the file my.cnf or my.ini to record these queries

This is a useful journal. It has little impact on performance (assuming that all queries are fast) and emphasizes the queries that need the most attention (missing indexes or indexes not being best applied)

# Time: 070927 8:08:52

#: root [root] @ [192.168.0.20]

# Query_time: 372 Lock_time: 136 Rows_sent: 152 Rows_examined: 263630

Select id, name from manager where id in (66 people 10135)

This is one of the slow query logs. It took 372seconds, locked 136seconds, returned 152lines, checked a total of 263630 lines.

If there is a lot of content in the log, it will be exhausting to read it one by one with your eyes. Mysql has its own analysis tools, which are used as follows:

Under the command line, go to the mysql/bin directory and type mysqldumpslow-help or-- help to see the parameters of the tool.

Usage: mysqldumpslow [OPTS... ] [LOGS... ]

Parse and summarize the MySQL slow query log. Options are

-- verbose verbose

-- debug debug

-- help write this text to standard output

-v verbose

-d debug

-s ORDER what to sort by (t, at, l, al, r, ar etc), at is default

-r reverse the sort order (largest last instead of first)

-t NUM just show the top n queries

-a dont abstract all numbers to N and strings to S

-n NUM abstract numbers with at least n digits within names

-g PATTERN grep: only consider stmts that include this string

-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard)

Default is *, i.e. Match all

-i NAME name of server instance (if using mysql.server startup scrystpt)

-l dont subtract lock time from total time

-s, is the order of order, the description is not detailed enough, I used it, including looking at the code, mainly

CPeragne, ac,at,al,ar, respectively, are sorted by the number of query, the time, the time of lock and the number of records returned, with a flashback added before.

-t, which means top n, that is, how many previous pieces of data are returned.

-g, a regular matching pattern can be written later, which is case-insensitive.

Mysqldumpslow-s c-t 20 host-slow.log

Mysqldumpslow-s r-t 20 host-slow.log

The above command shows the 20 most visited sql statements and the 20 sql that returned the most recordsets.

Mysqldumpslow-t 10-st- g "left join" host-slow.log

This is to return the first 10 sql statements with left concatenation in time.

Windows:

When you first open the slow query of mysql, the record file will be created in the directory you specify. This article is mysqlslowquery.log, and the content of this file is roughly as follows (in the case of opening MYSQL slow query for the first time)

E:webmysqlinmysqld, Version: 5.4.3-beta-community-log (MySQL Community Server (GPL)). Started with:

TCP Port: 3306, Named Pipe: (null)

Time Id Command Argument

You can view the number of records for slow queries with the following command:

Mysql > show global status like'% slow%'

+-+

| | Variable_name | Value |

+-+

| | Slow_launch_threads | 0 | |

| | Slow_queries | 0 | |

+-+

This is the answer to the question about how to query log records in mysql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.