Preface

Recently, we are trying to use a log system, graylog, to collect the slow query log of mysql for subsequent analysis, monitoring and alarm. The test steps have been completed and the logs have been collected successfully. During the test, you need to brush some slow query logs. In order to brush more logs and not have a greater impact on the test environment, it is proposed to use mysql sleep function combined with event to do slow logging.

MySQL's planned tasks are completed through event. Job equivalent to SQL Server. The following is the implementation process:

Open mysql slow log

set global slow_query_log = 1;
set global long_query_time = 1;

You can also open the slow log in the configuration file (recommended)

Create event

1. Enable the event feature.

set global event_scheduler = 1;

1. Create an event every 1 second.

delimiter $$
create or replace EVENT event_flush_slow_log
ON SCHEDULE
EVERY 1 SECOND
on completion preserve ENABLE
do
begin
  SELECT SLEEP(1);
end; $$
delimiter ;

Query event

show events where Name like '%event_flush_slow_log%'\G;
//or
select * from information_schema.events where event_name='event_flush_slow_log'\G;

1. Open this event

ALTER EVENT event_flush_slow_log ENABLE;

You can see that the slow log has been written all the time.

# Time: 180126 12:19:57
# User@Host: root[root] @ localhost [localhost]
# Thread_id: 1640  Schema: db1  QC_hit: No
# Query_time: 1.000645  Lock_time: 0.000269  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0
SET timestamp=1516940397;
SELECT SLEEP(1);
# Time: 180126 12:19:58
# User@Host: root[root] @ localhost [localhost]
# Thread_id: 1641  Schema: db1  QC_hit: No
# Query_time: 1.000575  Lock_time: 0.000203  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0
SET timestamp=1516940398;
SELECT SLEEP(1);
# Time: 180126 12:19:59
# User@Host: root[root] @ localhost [localhost]
# Thread_id: 1642  Schema: db1  QC_hit: No
# Query_time: 1.000856  Lock_time: 0.000180  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0
SET timestamp=1516940399;
SELECT SLEEP(1);

1. When finished, close event

ALTER EVENT event_flush_slow_log DISABLE;

1. If you do not need this event, you can delete it

DROP EVENT IF EXISTS event_flush_slow_log;

For detailed usage of sleep function and event, please refer to relevant documents, which will not be expanded here.

You can also use the Linux crontab calling script to do timed slow logging, but you need to log in to MySQL every 1 second to perform a slow query, which consumes a lot of resources. It is not recommended to do so.