嗨, 大家好: 网站在新年后正式开通,欢迎大家加入我们,共同维护一个良好氛围的中国技术社区。 欢迎举报不良帖子,欢迎提供建议。
我们的QQ群:136102234,欢迎进群找答案,里边会有很多资深的大师们。 point overflow

执行清除mysql.slow_log时报错。

如下:

[SQL]delete from slow_log where start_time<'2016-02-18';

[Err] 1556 - You can't use locks with log tables.

asked 2016-02-18 10:38:12

jkant's gravatar image

jkant ♦♦
11134138139
accept rate: 3%


经过测试,方式如下。
原因为slow_log 等日志表为csv存储引擎,不能直接删除。

-- 1.建表保存还需要的数据
create table mysql.slow_log_copy like slow_log;
-- 2.写入数据
insert into mysql.slow_log_copy
select * from mysql.slow_log where start_time>'2015-02-18';
-- 3.slow_log删除
set GLOBAL slow_query_log='off'
-- 4.slow_log重命名
drop table mysql.slow_log;
-- 5.数据恢复为重命名的表slow_log
rename table mysql.slow_log_copy to mysql.slow_log;
-- 6启动慢日志
set GLOBAL slow_query_log='on'

link

answered 2016-02-18 10:58:49

jkant's gravatar image

jkant ♦♦
11134138139
accept rate: 3%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×72
×2
×2
×1

Asked: 2016-02-18 10:38:12

Seen: 1,335 times

Last updated: 2016-02-18 10:58:49