MySQL Event scheduled task flushing slow logs, mysqlevent

MySQL Event scheduled task flushing slow logs, mysqlevent
Preface

Recently, a log system graylog is used to collect mysql slow query logs for subsequent analysis, monitoring, and alarms. The log has been successfully collected to the graylog during the test. During the test, you need to fl some slow query logs. In order to refresh more logs and not significantly affect the test environment, I thought of using the sleep function of mysql and event to refresh slow logs.

The scheduled tasks of MySQL are completed by event. It is equivalent to a Job of SQL Server. The implementation process is as follows:

Open mysql slow log

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

 

You can also enable the slow log in the configuration file (we recommend that you enable it)

 

Create event

1. Enable event.

set global event_scheduler = 1;

 

2. Create an event and run it once every 1 second.

delimiter $$create or replace EVENT event_flush_slow_logON SCHEDULEEVERY 1 SECONDon completion preserve ENABLEdobegin  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;

 

3. Enable this event

ALTER EVENT event_flush_slow_log ENABLE;

 

You can see that the slow log is being written.

# 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: 0SET 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: 0SET 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: 0SET timestamp=1516940399;SELECT SLEEP(1);

 

4. Close the event

ALTER EVENT event_flush_slow_log DISABLE;

 

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

DROP EVENT IF EXISTS event_flush_slow_log;

 

For detailed usage of sleep functions and events, refer to the relevant documentation.

You can also use the Linux crontab to call the script to regularly fl slow logs. However, you have to log on to MySQL every 1 second to execute a slow query, which consumes a large amount of resources. We do not recommend that you do this.