Use MySQL general log log to find the most frequently queried SQL sentences

SQL statements with the most queries

Open General Log

Mysql> Show variables like '%general% ';
+------------------+-------------------------------------+
| variable_name | Value |
+------------------+-------------------------------------+
| General_log | Off |
| General_log_file | /usr/local/mysql/data/localhost.log |
+------------------+-------------------------------------+
mysql> Set Global General_log = "on";

analysis-general-log.py Script

#!/usr/bin/python

# Sort and count MySQL general log
# Author:jason
# CREATED:UTC 2015-02-15 17:51:53

Import re
Import Sys
Import OS

If Len (sys.argv) = = 2:
LogPath = sys.argv[1]
If not os.path.exists (LogPath):
Print ("file" + LogPath + "does not exists.")
Sys.exit (1)
Else
Print ("Usage:" + sys.argv[0] + "LogPath")
Sys.exit (1)

LOGFO = open (LogPath)
Match = 0

For line in LOGFO:
Line = Re.sub (r "\ n", "", line)
If match = 0:
# match line begin with numbers
Linematch = Re.match (r "\s+[0-9]+\s+.*", Line,flags=re. I)
If Linematch:
linetmp = Linematch.group (0)
Match = match + 1
Continue

Elif match = 1:
# match line begin with numbers
Linematch = Re.match (r "\s+[0-9]+\s+.*", Line,flags=re. I)
If Linematch:
# Match only Query
Linematchquery = Re.match (r ". *query\s+ (. *)", Linetmp,flags=re. I)
If Linematchquery:
Linetmp = Linematchquery.group (1)
# Remove Extra Space
Linetmp = Re.sub (r "\s+", "", linetmp)
# Replace values (value) to values (x)
Linetmp = Re.sub (r "values\s*\ (. *?\)", "Values (x)", Linetmp,flags=re. I)
# Replace filed = ' value ' to filed = ' x '
Linetmp = Re.sub (=|>|<|>=|<=) \s* (' |\ '). *?\2 ", \\1 ' x '", linetmp)
# Replace filed = value to filed = X
Linetmp = Re.sub (r "(=|>|<|>=|<=) \s*[0-9]+", "\\1 X", linetmp)
# replace like ' value ' to like ' X '
Linetmp = Re.sub (r "like\s+" (' |\ '). *?\1 "," Like ' X ' ", Linetmp,flags=re. I)
# Replace in (value) to In (x)
Linetmp = Re.sub (r "in\s+\ (. *?\)", "in (x)", Linetmp,flags=re. I)
# Replace limit x,y to limit
Linetmp = Re.sub (r "limit.*", "Limit", Linetmp,flags=re. I)

Print (LINETMP)

Match = 1
linetmp = Linematch.group (0)
Else
Linetmp + = line
Match = 1

Logfo.close ()

How to use:

analysis-general-log.py General.log | Sort | uniq-c | Sort-nr

1032 SELECT * from wp_comments WHERE (comment_approved = ' x ' OR comment_approved = ' x ') and comment_post_id = x ORDER BY COMMENT_DATE_GMT DESC
653 SELECT post_id, Meta_key, meta_value from Wp_postmeta WHERE post_id into (x) Order by meta_id ASC
527 SELECT found_rows ()
438 SELECT t.*, tt.* from wp_terms as T INNER JOIN wp_term_taxonomy as TT on t.term_id = tt.term_id WHERE tt.taxonomy = ' x ' and t.term_id = x limit
341 SELECT option_value from wp_options WHERE option_name = ' x ' limit
329 SELECT t.*, tt.*, tr.object_id from wp_terms as T INNER JOIN wp_term_taxonomy as TT on tt.term_id = t.term_id INNER JO In Wp_term_relationships as tr on tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy into (x) and tr.object_id in ( x) Order by T.name ASC
311 SELECT wp_posts.* from wp_posts WHERE 1= x and Wp_posts.id in (x) and Wp_posts.post_type = ' x ' and (Wp_posts.post_sta tus = ' x ') Order by Wp_posts.post_date DESC
219 SELECT wp_posts.* from Wp_posts WHERE ID in (x)
218 SELECT tr.object_id from wp_term_relationships as tr INNER JOIN wp_term_taxonomy as TT on tr.term_taxonomy_id = Tt.ter m_taxonomy_id WHERE tt.taxonomy in (x) and tt.term_id in (x) Order by tr.object_id ASC
217 SELECT wp_posts.* from wp_posts WHERE 1= x and Wp_posts.id in (x) and Wp_posts.post_type = ' x ' and (Wp_posts.post_sta tus = ' x ') Order by Wp_posts.menu_order ASC
SELECT sql_calc_found_rows wp_posts.id from wp_posts WHERE 1= x and wp_posts.post_type = ' x ' and (wp_posts.post_status = ' x ') Order by wp_posts.post_date DESC limit
118 SET NAMES UTF8
The SET session sql_mode= ' x '
Sql_mode SELECT @ @SESSION.
112 SELECT option_name, option_value from wp_options WHERE autoload = ' x '
SELECT user_id, Meta_key, meta_value from Wp_usermeta WHERE user_id into (x) Order by umeta_id ASC
108 SELECT Year (min. post_date_gmt) as Firstdate, year (Max Post_date_gmt) as lastdate WHERE wp_posts = ' X
108 SELECT t.*, tt.* from wp_terms as T INNER JOIN wp_term_taxonomy as TT on t.term_id = tt.term_id WHERE tt.taxonomy in ( x) and Tt.count > x ORDER by Tt.count DESC limit
SELECT t.*, tt.* from wp_terms as T INNER JOIN wp_term_taxonomy as TT on t.term_id = tt.term_id WHERE tt.taxonomy in ( x) and t.term_id in (x) Order by T.name ASC
SELECT * from wp_users WHERE ID = ' x '
SELECT sql_calc_found_rows wp_posts.id from wp_posts WHERE 1= x and wp_posts.post_type = ' x ' and (wp_posts.post_status = ' x ') and post_date > ' x ' Order by wp_posts.post_date DESC limit
SELECT sql_calc_found_rows wp_posts.id from wp_posts WHERE 1= x and wp_posts.post_type = ' x ' and (wp_posts.post_status = ' x ') and post_date > ' x ' Order by RAND () DESC limit
SELECT sql_calc_found_rows wp_posts.id from wp_posts WHERE 1= x and wp_posts.post_type = ' x ' and (wp_posts.post_status = ' x ') and post_date > ' x ' Order by Wp_posts.comment_count DESC limit

MySQL general log log removal tips

MySQL General log log can not be directly deleted, indirect method

Use MySQL;
CREATE TABLE gn2 like General_log;
RENAME TABLE General_log to Oldlogs, gn2 to General_log;