Python2.7 extract relevant information from mysqldumpslow analysis report and save it to excel

Preface

As the business grows , The amount of data is also growing , Then there are the problems that every developer has to face ：MySQL The slow query .

For the better 、 Faster analysis and optimization of online MySQL The slow query , We use mysql Self contained mysqldumpslow Tools , Analyze and extract slow query logs , The following extracts the most slow queries 30 Bar record

namely ：

mysqldumpslow -s c -t 30  /var/lib/mysql/mysqlslow.log > /opt/slowlog/report_slow.log # The log file for slow query is filled in according to the system configuration path , Redirect results here to  /opt/slowlog/

Environmental Science

operating system ：Centos7.3 x64

MySQL database ：MySQL 5.6.49 Enterprise Edition

development environment ：Python2.7 、shell   # Due to site environmental factors , Can only 2.7

Code implementation

First of all, let's take a look mysqldumpslow Analysis report generated by the tool , Here's an example 3 strip

Count: 19847 Time=14.23s (282373s) Lock=0.00s (3s) Rows=0.0 (0), root[root]@[192.168.0.101]
SELECT * FROM T_huwj WHERE name = 'S' AND ( userID IS NOT NULL AND userID != 'S') ORDER BY id ASC LIMIT N,N
Count: 19846 Time=14.91s (295928s) Lock=0.00s (3s) Rows=0.3 (5646), root[root]&