RDSでは、slow_logをファイルではなくテーブルに保存しますが、このテーブルを掃除したい時のメモです
テーブルをtruncateしてみる
mysql> truncate table mysql.slow_log; ERROR 1044 (42000): Access denied for user 'hoge'@'%' to database 'mysql'
消せないようです
ストアドプロシージャで消す
mysql> SHOW PROCEDURE STATUS LIKE '%slow_log%'\G *************************** 1. row *************************** Db: mysql Name: rds_rotate_slow_log Type: PROCEDURE Definer: rdsadmin@localhost Modified: 2017-02-02 19:54:23 Created: 2017-02-02 19:54:23 Security_type: DEFINER Comment: character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
便利なものが用意されていますね
procedureの中身
mysql> show create procedure rds_rotate_slow_log \G *************************** 1. row *************************** Procedure: rds_rotate_slow_log sql_mode: NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`rdsadmin`@`localhost` PROCEDURE `rds_rotate_slow_log`() READS SQL DATA DETERMINISTIC BEGIN DECLARE sql_logging BOOLEAN; select @@sql_log_bin into sql_logging; set @@sql_log_bin=off; CREATE TABLE IF NOT EXISTS mysql.slow_log2 LIKE mysql.slow_log; DROP TABLE IF EXISTS mysql.slow_log_backup; RENAME TABLE mysql.slow_log TO mysql.slow_log_backup, mysql.slow_log2 TO mysql.slow_log; set @@sql_log_bin=sql_logging; END character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
一回分のバックアップを取ってくれるだけなので注意が必要ですがありがたく使わせてもらいます
mysql> CALL mysql.rds_rotate_slow_log; Query OK, 0 rows affected (0.04 sec)