Operations for intercepting and analyzing specific lines of log files _ MySQL

During operating system and database system management, you may often find a character in the log file or take logs of a certain period of time for analysis. This morning I encountered a problem with the MySQL database. when the mysql database was,

During operating system and database system management, you may often find a character in the log file or take logs of a certain period of time for analysis.
This morning I encountered a problem with the MySQL database. at, the database session connection, tps, cpu and iowait were much larger than usual.
To locate the slow queries and resource consumption that occurred during this time period, you need to query, intercept, and analyze the slow query logs in slow. log.

Several common log truncation methods are summarized and tested during processing:

Summary and test of methods for intercepting log segments
Method 1: Determine the line number of the time period, and use the head and tail commands to interceptSearch Time: 151212 00:00:00 first go to gg, to the top of the file, and then search/Time: 151212, get: 1979016 # Time: 151212 0:00:15

Total number of lines of the file: # wc-l slow-query-169-1212.log 1999422 slow-query-169-1212.log 1999422 limit;


Search for the file/Time: 151212 4 starting after 4:00:05 on G at the end of the file, and get: 1987392 # Time: 151212.

After determining the line number of the time range to be queried, use the head and tail commands to intercept: >>> 1987392-1979016; 8376

The log to be intercepted. The last line number is 1979016. to use the head command, use the tail command to extract the last 8376 lines. the command is: head-n 1979016 slow-query-169-1212.log | tail-n 8376> slow-query-169-1212_00-03.log

Method 2: determine the line number of the time period and use the sed command to interceptCapture slow query logs between 151212 0-3: sed-n '1979016,198 7392p' slow-query-169-1212.log> slow-query-169-1212_00-03.log

>>> 1987392-1979016; 8376 for this part of files, there should be 8376 rows of data; OK;

Method 3: Use the awk command to intercept the line number of a time period.Capture slow query logs between 151212 0-3: awk-F "\ n" '{if (NR> = 1979016 & NR <= 1987392) print $0}' slow-query-169-1212.log> slow-query-169-1212_00-03.log

Method 4: use sed to retrieve the log segment without determining the row number.All slow logs start with # Time: 151212 0:00:15 and use sed to retrieve the logs: sed-n'/^ # Time: 151212 0/,/^ # Time: 151212 4/p' slow-query-169-1212.log> part. log -- this effect is the same as the above, and the data between 0 and 3 points is retrieved.

If you want to retrieve the data of the day 151211, the command is: sed-n '/^ # Time: 151211/,/^ # Time: 151212/p' slow-query-169-1212.log> part. log -- took out the 151211 log of the day

Note: When sed is used, the selection items between the two rows must correspond, otherwise the result is not the expected result: sed-n'/^ # Time: 151212 /, // ^ # Time: 151212 4/p' slow-query-169-1212.log> part. log-different Time points before and after, search for all # Time: 151212

Analyze captured logs using pt toolsThe command to analyze the logs of this time period is as follows:/usr/local/pt/bin/pt-query-digest/data/bbs/slow-query-169-1212_00-03.log>/data/bbs/slow-query-169-1212_00-03-report.log

This is the default analysis and sorting method. you can select other pt report analysis methods as needed.


Note 1 two query methods in vim:In command mode, Press '/' and Enter the character to be searched, Enter .? The difference with/Is that a forward (down) lookup and a backward (up ). In addition, '*' can be used to find the word under the current cursor (exact match), while 'g *' can partially match. Replacing '#' with '*' indicates searching backward (up ).



Note 2 A simple example of several file processing methods:Sed is processed by row and does not load the entire file into the memory. you can use it to cut out all data rows from, (assume that your log file starts with the date format of yyyy-MM-dd.) you only need to: sed-n'/^ 2012-02-09 /, /^ 2012-09-10/p' whole. log> part. log

In any case, you must use a program to read the file and use certain rules to filter it. In Linux, using cat and grep to operate files is the most economical and reasonable. It is certain that a certain amount of system resources are occupied. the specific usage of cat, grep and Linux kernel has a certain relationship, so it may be slightly different. Generally, do not create such a large log file in the system. it is best to set the log size and reasonable log dumping and backup at the beginning.

Display rows 2nd to 6th of the file sed-n'2, 6p' file

The-n parameter needs to be added. head-n 6 file | tail-n 4


Awk can also meet the required awk-F "\ n" '{if (NR> = 2 & NR <= 6) print $0}' file