{"id":75,"date":"2020-04-12T19:05:15","date_gmt":"2020-04-12T11:05:15","guid":{"rendered":"http:\/\/www.sangco.cn\/?p=75"},"modified":"2020-04-12T19:05:15","modified_gmt":"2020-04-12T11:05:15","slug":"mysql-%e5%bc%80%e5%90%af%e6%85%a2%e6%9f%a5%e8%af%a2-%e6%b8%85%e7%a9%baslow_log%e6%97%a5%e5%bf%97%e6%88%96%e8%80%85slow_log%e8%a1%a8","status":"publish","type":"post","link":"https:\/\/www.sangco.cn\/?p=75","title":{"rendered":"mysql \u5f00\u542f\u6162\u67e5\u8be2 \u6e05\u7a7aslow_log\u65e5\u5fd7\u6216\u8005slow_log\u8868"},"content":{"rendered":"\n

show variableslike ‘%log_output%’;– \u9ed8\u8ba4\u662fFILE<\/p>\n\n\n\n

show variableslike ‘%quer%’;<\/p>\n\n\n\n

— log_output \u9ed8\u8ba4\u662fFILE\uff0c\u8868\u793a\u6162\u67e5\u8be2\u65e5\u5fd7\u8f93\u5165\u81f3\u65e5\u5fd7\u6587\u4ef6\uff0c\u53ef\u4ee5\u901a\u8fc7set\u4fee\u6539\u8f93\u51fa\u4e3aTABLE<\/p>\n\n\n\n

— log_queries_not_using_indexes \u9ed8\u8ba4\u662fOFF\uff0c\u8868\u793a\u662f\u5426\u8bb0\u5f55\u6ca1\u6709\u4f7f\u7528\u7d22\u5f15\u7684\u67e5\u8be2<\/p>\n\n\n\n

— slow_query_log \u9ed8\u8ba4\u662fOFF\uff0c\u8868\u793a\u662f\u5426\u6253\u5f00\u6162\u67e5\u8be2<\/p>\n\n\n\n

— long_query_time\u9ed8\u8ba4\u662f 10.000000\uff0c\u8868\u793a\u8bb0\u5f55\u8d85\u8fc7\u65f6\u95f4\u7684\u6162\u67e5\u8be2<\/p>\n\n\n\n

— \u6253\u5f00\u6162\u67e5\u8be2\uff0c\u8bb0\u5f55\u67e5\u8be2\u65f6\u95f4\u8d85\u8fc75\u79d2\u7684\u6162\u67e5\u8be2\uff0c\u8bb2\u6162\u67e5\u8be2\u7ed3\u679c\u8f93\u51fa\u81f3slow_log\u8868\u4e2d<\/p>\n\n\n\n

set global slow_query_log = ON;<\/p>\n\n\n\n

SET GLOBAL long_query_time = 5;– 10.000000<\/p>\n\n\n\n

— SET GLOBAL log_queries_not_using_indexes = ON;– \u662f\u5426\u6253\u5f00\u770b\u4e2a\u4eba\u9700\u8981<\/p>\n\n\n\n

set global log_output=’TABLE’;– FILE<\/p>\n\n\n\n

select * from mysql.slow_log order by start_time desc;<\/p>\n\n\n\n

\u4ee5\u4e0b\u4ee3\u7801\u6062\u590d\u6162\u67e5\u8be2\u7684\u53c2\u6570\u8bbe\u7f6e\u60c5\u51b5 <\/p>\n\n\n\n

SET global slow_query_log = OFF;<\/p>\n\n\n\n

SET GLOBAL long_query_time = 10.000000;<\/p>\n\n\n\n

SET GLOBAL log_queries_not_using_indexes = OFF;<\/p>\n\n\n\n

SET global log_output=’FILE’;– FILE<\/p>\n\n\n\n

— \u6e05\u7a7a\u6162\u67e5\u8be2\u65e5\u5fd7<\/p>\n\n\n\n

show variables like ‘%slow%’;<\/p>\n\n\n\n

+———————+——————————————+<\/p>\n\n\n\n

| Variable_name |Value |<\/p>\n\n\n\n

+———————+——————————————+<\/p>\n\n\n\n

| log_slow_queries| ON |<\/p>\n\n\n\n

| slow_launch_time| 2 |<\/p>\n\n\n\n

| slow_query_log |ON |<\/p>\n\n\n\n

|slow_query_log_file | \/data\/mysql\/slow_queries_3306.log |<\/p>\n\n\n\n

+———————+——————————————+<\/p>\n\n\n\n

4 rows in set(0.00 sec)<\/p>\n\n\n\n

show variables like ‘log_output%’;<\/p>\n\n\n\n

log_output   FILE<\/p>\n\n\n\n

— \u5173\u95ed\u65e5\u5fd7\u8f93\u51fa<\/p>\n\n\n\n

set global slow_query_log=0;<\/p>\n\n\n\n

show variables like ‘%slow%’;<\/p>\n\n\n\n

+———————+——————————————+<\/p>\n\n\n\n

| Variable_name |Value |<\/p>\n\n\n\n

+———————+——————————————+<\/p>\n\n\n\n

| log_slow_queries| OFF |<\/p>\n\n\n\n

| slow_launch_time| 2 |<\/p>\n\n\n\n

| slow_query_log |OFF |<\/p>\n\n\n\n

|slow_query_log_file | \/data\/mysql\/slow_queries_3306.log |<\/p>\n\n\n\n

+———————+——————————————+<\/p>\n\n\n\n

4 rows in set(0.00 sec)<\/p>\n\n\n\n

set global slow_query_log_file=’\/data\/mysql\/new-slow.log’;<\/p>\n\n\n\n

Query OK, 0 rowsaffected (0.03 sec)<\/p>\n\n\n\n

set global slow_query_log=1;<\/p>\n\n\n\n

Query OK, 0 rowsaffected (0.01 sec)<\/p>\n\n\n\n

show variables like ‘%slow%’;<\/p>\n\n\n\n

+———————+———————————————-+<\/p>\n\n\n\n

| Variable_name |Value |<\/p>\n\n\n\n

+———————+———————————————-+<\/p>\n\n\n\n

| log_slow_queries| ON |<\/p>\n\n\n\n

| slow_launch_time| 2 |<\/p>\n\n\n\n

| slow_query_log |ON |<\/p>\n\n\n\n

|slow_query_log_file | \/data\/mysql\/new_slow.log |<\/p>\n\n\n\n

+———————+———————————————-+<\/p>\n\n\n\n

4 rows in set(0.00 sec)<\/p>\n\n\n\n

show variableslike ‘%log_output%’;– \u76ee\u524d\u662fFILE<\/p>\n\n\n\n

mysql> selectsleep(10) as a, 1 as b;<\/p>\n\n\n\n

+—+—+<\/p>\n\n\n\n

| a | b |<\/p>\n\n\n\n

+—+—+<\/p>\n\n\n\n

| 0 | 1 |<\/p>\n\n\n\n

+—+—+<\/p>\n\n\n\n

1 row in set(10.00 sec)<\/p>\n\n\n

[mysql@xxx-xxx ~]<\/p>\n\n\n\n

$more \/data\/mysql\/new-slow.log\n<\/p>\n\n\n\n

6, backup the oldbig slow log file to other directory.<\/p>\n\n\n\n

— \u6e05\u7a7aslow_log\u8868<\/p>\n\n\n\n

select * from mysql.slow_log;<\/p>\n\n\n\n

SET GLOBALslow_query_log = ‘OFF’;<\/p>\n\n\n\n

ALTER TABLE mysql.slow_log RENAME mysql.slow_log_drop;<\/p>\n\n\n\n

CREATE TABLE slow_log<\/code> (<\/p>\n\n\n\n

 start_time<\/code> timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,<\/p>\n\n\n\n

  user_host<\/code>mediumtext NOT NULL,<\/p>\n\n\n\n

 query_time<\/code> time NOT NULL,<\/p>\n\n\n\n

  lock_time<\/code>time NOT NULL,<\/p>\n\n\n\n

  rows_sent<\/code>int(11) NOT NULL,<\/p>\n\n\n\n

 rows_examined<\/code> int(11) NOT NULL,<\/p>\n\n\n\n

  db<\/code>varchar(512) NOT NULL,<\/p>\n\n\n\n

 last_insert_id<\/code> int(11) NOT NULL,<\/p>\n\n\n\n

  insert_id<\/code>int(11) NOT NULL,<\/p>\n\n\n\n

  server_id<\/code>int(10) unsigned NOT NULL,<\/p>\n\n\n\n

  sql_text<\/code>mediumtext NOT NULL<\/p>\n\n\n\n

) ENGINE=CSVDEFAULT CHARSET=utf8 COMMENT=’Slow log’;<\/p>\n\n\n\n

SET GLOBAL slow_query_log = ‘ON’;<\/p>\n\n\n\n

DROP TABLE mysql.slow_log_drop;
\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014
\n\u7248\u6743\u58f0\u660e\uff1a\u672c\u6587\u4e3aCSDN\u535a\u4e3b\u300czhonglijun_05\u300d\u7684\u539f\u521b\u6587\u7ae0\uff0c\u9075\u5faa CC 4.0 BY-SA \u7248\u6743\u534f\u8bae\uff0c\u8f6c\u8f7d\u8bf7\u9644\u4e0a\u539f\u6587\u51fa\u5904\u94fe\u63a5\u53ca\u672c\u58f0\u660e\u3002
\n\u539f\u6587\u94fe\u63a5\uff1ahttps:\/\/blog.csdn.net\/zhonglijun_05\/article\/details\/53333545<\/p>\n","protected":false},"excerpt":{"rendered":"

show variableslike ‘%log_output%’;– \u9ed8\u8ba4\u662fFILE show variableslike ‘%quer%’; — log_outpu…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"topic":[],"class_list":["post-75","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.sangco.cn\/index.php?rest_route=\/wp\/v2\/posts\/75","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sangco.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sangco.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sangco.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sangco.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=75"}],"version-history":[{"count":2,"href":"https:\/\/www.sangco.cn\/index.php?rest_route=\/wp\/v2\/posts\/75\/revisions"}],"predecessor-version":[{"id":77,"href":"https:\/\/www.sangco.cn\/index.php?rest_route=\/wp\/v2\/posts\/75\/revisions\/77"}],"wp:attachment":[{"href":"https:\/\/www.sangco.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=75"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sangco.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=75"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sangco.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=75"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.sangco.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftopic&post=75"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}