How to flush data from mysql.slow_log table in mysql?
Clash Royale CLAN TAG#URR8PPP
How to flush data from mysql.slow_log table in mysql?
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 ?
4 Answers
4
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"
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 9: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 9:58
@VikrantMore solved?
– Danyal Sandeelo
Mar 20 '15 at 10:57
yes Danyal Sandeelo
– Vikrant More
Mar 20 '15 at 11:18
unknown system variable "slow_log"?
– Lennart Rolland
Feb 16 '17 at 10:27
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;
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
This is beautiful! Thanks for sharing this little gem, I had no idea RDS had their own stored procedures.
– Eric Lawler
Jan 28 '17 at 1:32
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
– Amos
Nov 19 '17 at 6:12
TRUNCATE mysql.slow_log
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.
shouldn't this answer be on the top?
– rnbguy
Mar 21 at 16:50
So far it's the only right answer...
– Offlein
Jun 20 at 15:21
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';
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
See this
– Raptor
Mar 20 '15 at 10:00