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)
Enable the event feature.
set global event_scheduler = 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 ;
show events where Name like '%event_flush_slow_log%'\G; //or select * from information_schema.events where event_name='event_flush_slow_log'\G;
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);
When finished, close event
ALTER EVENT event_flush_slow_log DISABLE;
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.