Mysql SQL Execution and slow query monitoring

[Preface] mysql can record the SQL statements executed by users: it can record the files and tables. mysql can define the execution time or longer. The SQL statements are slow queries, record related information to files and tables [background description] the company wants to monitor and record what SQL statements are executed every day, which SQL statements are slow queries, and then optimize SQL statements [technical description] As long

[Preface] mysql can record the SQL statements executed by users: it can record the files and tables. mysql can define the execution time or longer. The SQL statements are slow queries, record related information to files and tables [background description] the company wants to monitor and record what SQL statements are executed every day, which SQL statements are slow queries, and then optimize SQL statements [technical description] As long

[Preface]

Mysql records the SQL statements executed by users: records to files and tables

Mysql can define how much time the SQL statement is executed. It is a slow query and records related information to files and tables according to the configuration.

[Background description]

The company wants to monitor which sqls are executed every day and which sqls are slow queries, and then optimizes the sqls.

[Technical description]

As long as you understand how mysql records the execution of SQL statements

How to record slow queries

The next step is to write code to organize the report. Here I use python

[The final result is as follows]

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/12150411V-0.jpg "title =" 1.png" alt = "wKiom1UdRT6BXY31AAM9m0s2MRM071.jpg"/>

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215041260-1.jpg "style =" float: none; "title =" 2.png" alt = "wKioL1UdRDXwm-mkAAMsYmucxzw431.jpg"/>

[Technical Details]

1. modify my. cnf

# The overall effect is that both the table and log file are enabled globally, and the general_log, slow_query_log, and table and log files are recorded.

General_log = 1 # enable the log slow_query_log = 1 # enable the log of mysql slow SQL

# Setting will affect general_log and slow_query_log,

Log_output = table, File # The log output will write tables and log files. To facilitate program statistics, it is best to write tables.

# General_log_file is not configured here, So general_log will only write the table

# In mysql5.1.29 or later, set the following to enable mysql to record the executed SQL statements in the file

General_log_file =/log/general. log

#5.1.29 previously:

# Log =/var/lib/mysql/SQL _row.log

Long_query_time = 1 # Set the slow query of mysql to slow_query_log_file =/log/slow. log for more than 1 s

2. modify the format of the mysql Log table (in the mysql database)

# By default, general_log is in csv format. modifying it to MyISAM is much more efficient.

Set global general_log = off; alter table general_log engine = MyISAM; # the innodb format set global general_log = on cannot be used;

# By default, general_log is in csv format. modifying it to MyISAM is much more efficient.

Set global slow_query_log = off; equal to 0. alter table slow_log engine = MyISAM; # set global slow_query_log = on in innodb format; equal to 1

3. Because mysql Log tables general_log and slow_query_log cannot be modified, you need to create a new table to facilitate deletion and modification (this log table is too large and needs to regularly clean data obtained n days ago)

3.1 create a slow_log_dba table

CREATE TABLE `slow_log_dba` (  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `user_host` mediumtext NOT NULL,  `query_time` time NOT NULL,  `lock_time` time NOT NULL,  `rows_sent` int(11) NOT NULL,  `rows_examined` int(11) NOT NULL,  `db` varchar(512) NOT NULL,  `last_insert_id` int(11) NOT NULL,  `insert_id` int(11) NOT NULL,  `server_id` int(10) unsigned NOT NULL,  `sql_text` mediumtext NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log for dba';

3.2 Create a general_log_dba table

CREATE TABLE `general_log_dba` (  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `user_host` mediumtext NOT NULL,  `thread_id` int(11) NOT NULL,  `server_id` int(10) unsigned NOT NULL,  `command_type` varchar(64) NOT NULL,  `argument` mediumtext NOT NULL,  KEY `user_host` (`user_host`(200)),  KEY `event_time` (`event_time`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='general log for dba op';

4. Because the program uses the general_log_dba and slow_log_dba tables, you need to copy the data of general_log and slow_query_log to general_log_dba and slow_log_dba at regular intervals.

Because the report is generated once a day, you only need to perform this operation once a day.

# The script stores the data obtained for 10 days. The data of general_log and slow_query_log are copied to general_log_dba and slow_log_dba every day.

# Execute mysqllogtable. sh once a daily scheduled task #! /Bin/shNDaysAgo = $ (date-d '-10 days' "+ % F % H: % M: % S ") /usr/local/mysql/bin/mysql-uXXXX-p 'xxxxxx'-D 'mysql'-e "insert general_log_dba select * from general_log; truncate general_log; delete from general_log_dba where event_time <\ "$ NDaysAgo \"; insert slow_log_dba select * from slow_log; truncate slow_log; delete from slow_log_dba where start_time <\ "$ NDaysAgo \""

5. python script write statistics on daily SQL operations and daily mysql slow queries (some of the scripts can be abstracted and processed as appropriate)

5.1 count the scripts of mysql daily execution records

#-*-Coding: UTF-8-*-_ author _ = 'river' import MySQLdb as mysqlimport refrom datetime import datetime, timedeltaimport smtplibfrom email. mime. text import MIMETextdef sendHtmlMail (mailcontent, myip): try: yestoday = (datetime. now ()-timedelta (days = 1 )). strftime ("% Y-% m-% d ") sender = 'xxx @ xxx.com 'guest ER = ['xxx @ xxx.com'] subject = myip + 'mysql operation report' + yestoday smtpserver = 'smtp. exmail. xx. Com 'username = 'xxx @ xxx.com 'password = 'xxxxx' msg = MIMEText (mailcontent, 'html', 'utf-8') # '', 'text ', 'utf-8' msg ['subobject'] = Subject msg ['from'] = sender msg ['to'] = 'xxx @ xxxxxx.com 'smtp = smtplib. SMTP () smtp. connect (smtpserver) smtp. login (username, password) smtp. sendmail (sender, receiver, msg. as_string () smtp. quit () Does T Exception, e: print e, 'Send mail error' if _ name __= = '_ main _': r Esult = None htmlfile='mysqlLogMon.html 'myiplist = ['2017. 168.10.10 ', '2017. 168.10.19 '] yestoday = (datetime. now ()-timedelta (days = 1 )). strftime ("% Y-% m-% d 00:00:00") today = datetime. now (). strftime ("% Y-% m-% d 00:00:00") for myip in myiplist: SQL = "select user_host, argument from general_log_dba where event_time> = '% s' and event_time <=' % S' "% (yestoday, today) try: dbcon = mysql. connect (host = myip, user = 'xxxxx', pa Sswd = 'xxxxx', db = 'mysql', port = 3306, charset = 'utf8') cur = dbcon. cursor () print "step 1," + myip + ',' + datetime. now (). strftime ("% Y-% m-% d % H: % M: % S") cur.exe cute (SQL) result = cur. fetchall () cur. close () dbcon. close () failed t Exception, e: print e, 'conn mysql error' user_host_set = set () print "step 2," + myip + ',' + datetime. now (). strftime ("% Y-% m-% d % H: % M: % S") allhash = {} if result: for user_host, argument in resu Lt: argument_delcom = re. compile (R' (\/\ * (\ s | .)*? \*\/)'). Sub ("", argument ). strip (). replace (u "\ x00 ",''). lower () if re. compile (R' ^ access. *'). match (argument_delcom) or re. compile (R' ^. *@. * on. *'). match (argument_delcom) or re. compile (R' ^ grant. *'). match (argument_delcom): tmpargument = argument_delcom.strip () else: tmpargument = argument_delcom.split ('') [0]. strip () if len (tmpargument)> 30: # Some SQL statements are u'select \ n \ t \ tcount (m. enquirymainid) ', you can use print repr (tmpargument) tmpargument = argument_delcom.split (' \ n') [0]. strip () # if all comments exist, this entry is not counted if not tmpargument or tmpargument. strip () = ''or tmpargument. strip () = '': continue if allhash. has_key (user_host): allhash [user_host] [tmpargument] = allhash [user_host]. get (tmpargument, 0) + 1 else: allhash [user_host] = {tmpargument: 1} print "step 3," + myip + ',' + datetime. now (). strftime ("% Y-% m-% d % H: % M: % S") headhtml = '''
                         
 
 
    '''Print "step 4," + myip + ',' + datetime. now (). strftime ("% Y-% m-% d % H: % M: % S") with open (htmlfile, 'w') as htmlfileobj: htmlfileobj. write (headhtml) htmlfileobj. flush () print "step 5," + myip + ',' + datetime. now (). strftime ("% Y-% m-% d % H: % M: % S") with open (htmlfile, 'A') as htmlfileobj: for hostkey in allhash. keys (): listtmp = sorted (allhash [hostkey]. iteritems (), key = lambda labkey: labkey [1], reverse = True) rowspan = len (allhash [hostkey]) # htmlfileobj. write () tmpline =' 
   '% (Rowspan, hostkey. encode ('utf-8') htmlfileobj. write (tmpline) countn = 0 for runsql, count in listtmp: if countn = 0: tmpline =' 
    '% (Runsql. encode ('utf-8'), count) else: tmpline =' 
   '% (Runsql. encode ('utf-8'), count) countn + = 1 htmlfileobj. write (tmpline) tmpline = ''' 
  
                        
  
  
 
   
 
    
User
 
    
Execute SQL
 
    
Number of executions
 
   
 
  
  
  
 
    
% S
% S
 
    
% S
 
   
 
    
% S
 
    
% S
 
   
 
 
'''Htmlfileobj. write (tmpline) with open (htmlfile, 'R') as htmlfileobj: mailcontent = htmlfileobj. read () sendHtmlMail (mailcontent, myip) else: print 'SQL result is None, exit 'print "step 6," + myip +', '+ datetime. now (). strftime ("% Y-% m-% d % H: % M: % S ")

5.2 count mysql daily slow SQL scripts

#-*-Coding: UTF-8-*-_ author _ = 'river' import MySQLdb as mysqlimport refrom datetime import datetime, timedeltaimport smtplibfrom email. mime. text import MIMETextdef sendHtmlMail (mailcontent, myip): try: yestoday = (datetime. now ()-timedelta (days = 1 )). strftime ("% Y-% m-% d ") sender = 'xxx @ xxx.com 'receiver = ['xxx @ xxx.com'] subject = myip + 'mysql operation report' + yestoday smtpserver = 'smtp .exmail.xx.com 'username = 'xxx @ xxx.com' password = 'xxxxx' msg = MIMEText (mailcontent, 'html', 'utf-8') # '', 'text ', 'utf-8' msg ['subobject'] = Subject msg ['from'] = sender msg ['to'] = 'xxx @ xxxxxx.com 'smtp = smtplib. SMTP () smtp. connect (smtpserver) smtp. login (username, password) smtp. sendmail (sender, receiver, msg. as_string () smtp. quit () Does T Exception, e: print e, 'Send mail error' if _ name __= = '_ main __': result = None htmlfile+'mysqlslowmon.html 'myiplist = ['2017. 168.10.10 ', '2017. 168.10.19 '] yestoday = (datetime. now ()-timedelta (days = 1 )). strftime ("% Y-% m-% d 00:00:00") today = datetime. now (). strftime ("% Y-% m-% d 00:00:00") for myip in myiplist: SQL = "select start_time, user_host, query_time, lock_time, rows_sent, SQL _text from slow_log_dba where start_time> = '% s' and start_time <=' % s' order by query_time desc limit 500 "% (yestoday, today) try: dbcon = mysql. connect (host = myip, user = 'xxx', passwd = 'xxxxxx', db = 'mysql', port = 3306, charset = 'utf8') cur = dbcon. cursor () print "step 1," + myip + ',' + datetime. now (). strftime ("% Y-% m-% d % H: % M: % S") cur.exe cute (SQL) result = cur. fetchall () cur. close () dbcon. close () failed t Exception, e: print e, 'conn mysql error' print "step 2," + myip + ',' + datetime. now (). strftime ("% Y-% m-% d % H: % M: % S") if result: headhtml = '''
                         
 
 
    '''With open (htmlfile, 'w') as htmlfileobj: htmlfileobj. write (headhtml) htmlfileobj. flush () for start_time, user_host, query_time, lock_time, rows_sent, SQL _text in result: SQL = re. compile (R' (\/\ * (\ s | .) *? \*\/)'). Sub ("", SQL _text) [0: 150]. replace (u "\ x00 ",''). strip () if not SQL or SQL. strip () = ''or SQL. strip () = '': continue with open (htmlfile, 'A') as htmlfileobj: tmpstring =' 
   'Htmlfileobj. write (tmpstring) with open (htmlfile, 'A') as htmlfileobj: tmpline = ''' 
  
                        
  
  
 
   
 
    
Execution time
 
    
User
 
    
Query duration/s
 
    
Lock duration/s
 
    
Number of sent rows/line
 
    
Execute SQL
 
   
 
  
  
  
 
    
'+ Str (start_time) +'
 
    
'+ User_host +'
 
    
'+ Str (query_time) +'
 
    
'+ Str (lock_time) +'
 
    
'+ Str (rows_sent) +'
 
    
'+ SQL +'
 
   
 
 
'''Htmlfileobj. write (tmpline) with open (htmlfile, 'R') as htmlfileobj: mailcontent = htmlfileobj. read () print "step 3," + myip + ',' + datetime. now (). strftime ("% Y-% m-% d % H: % M: % S") sendHtmlMail (mailcontent, myip) else: print 'SQL result is None, exit ing 'print "step 4," + myip + ',' + datetime. now (). strftime ("% Y-% m-% d % H: % M: % S ")

[Summary]

In fact, this feature is not technically difficult, but requires a certain understanding of the mysql Logging set python.