How to flush data from mysql.slow_log table in mysql?

Question

Hi i am working on MySQL version 5.5, can somebody please help me to clear/flush data from mysql.slow_log tables in mysql ?

See [this](http://stackoverflow.com/questions/577339/truncate-slow-query-log-in-mysql) — Raptor, Mar 20 '15 at 10:00

jmsb · Answer 1 · 2016-03-08T15:08:56.517

23

You can avoid locking problems without disabling the slow log by using the built-in rotate function:

CALL mysql.rds_rotate_slow_log;
DELETE FROM mysql.slow_log_backup;

This will swap the 'slow_log' and 'slow_log_backup' tables, moving all of the data you want to clear to the non-locked 'slow_log_backup' table, which will happily take the DELETE FROM for data purging purposes.

You can also just invoke rotation twice:

CALL mysql.rds_rotate_slow_log;
CALL mysql.rds_rotate_slow_log;

edited Mar 08 '16 at 15:08

answered Mar 08 '16 at 13:59

jmsb

4,846
5
29
38

Quite straightforward and useful approach, dude. I just didn't figure out why you got no up votes until now. Thanks a lot! – Miere Mar 28 '16 at 21:13
+1 Exactly what I was looking for! would the question be tagged with RDS you would get lot's of upvotes. – Tiago Lopo Jun 20 '16 at 11:53
1

This is beautiful! Thanks for sharing this little gem, I had no idea RDS had their own stored procedures. – Eric L. Jan 28 '17 at 01:32
1

mysql> CALL rds_rotate_slow_log; Then the following error shows: ERROR 1305 (42000): PROCEDURE mysql.rds_rotate_slow_log does not exist; MySQL version 14.14 Distrib 5.7.20 – Happy Nov 19 '17 at 06:12

score 17 · Answer 2 · edited Aug 06 '18 at 15:08

17

TRUNCATE mysql.slow_log

From Mysql Documentation:

TRUNCATE TABLE is a valid operation on a log table. It can be used to expire log entries.

edited Aug 06 '18 at 15:08

The Godfather

4,235
4
39
61

answered Oct 31 '16 at 16:50

Renato Liibke

171
1
3

4

shouldn't this answer be on the top? – rnbguy Mar 21 '18 at 16:50

Danyal Sandeelo · Accepted Answer · 2019-04-21T09:47:13.037

7

If you are on linux

> mysql -uroot -p
> enter your password
> use mysql;
> delete from slow_log;

It will give you an error that you can't lock log tables. Work around is, run the following queries:

SET GLOBAL slow_log= 'OFF';
RENAME TABLE slow_log TO general_log_temp;
DELETE FROM `general_log_temp`;
RENAME TABLE general_log_temp TO slow_log ;
SET GLOBAL slow_log = 'ON';

Taken from "DELETE old rows from Mysql General Log Table"

Update:

You can truncate the table like TRUNCATE mysql.slow_log as mentioned by Renato Liibke

edited Apr 21 '19 at 09:47

answered Mar 20 '15 at 09:48

Danyal Sandeelo

12,196
10
47
78

when deleting records from slow_log getting error "Error Code:1556.You can't use locks with log tables." – Vikrant More Mar 20 '15 at 09:52
got another error DELETE FROM mysql.general_log_temp Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences – Vikrant More Mar 20 '15 at 09:58
unknown system variable "slow_log"? – Mr. Developerdude Feb 16 '17 at 10:27
@LennartRolland that's a table. select mysql database using `use mysql` and there is a table with name, you can verify it by using this query `show tables like '%slow_log%'` after selecting the mysql database – Danyal Sandeelo Feb 16 '17 at 12:04

score 0 · Answer 4 · answered May 21 '18 at 14:04

0

For me this works:

SET GLOBAL slow_query_log= 'OFF';
RENAME TABLE slow_log TO general_log_temp;
DELETE FROM `general_log_temp`;
RENAME TABLE general_log_temp TO slow_log ;
SET GLOBAL slow_query_log = 'ON';

answered May 21 '18 at 14:04

Marcin Mulawa

66
7

How to flush data from mysql.slow_log table in mysql?

4 Answers4