Tech Tip - Easy Reading MySql General Query Log.

Document created by DavidM Employee on Jun 30, 2014Last modified by DavidM Employee on Aug 12, 2014
Version 2Show Document
  • View in full screen mode

Reading the MySql general query log can be next to impossible when using a standard text editor.
See attached image.

I've found Notepad++ to be my tool of choice.
http://notepad-plus-plus.org/
See attached image.


However getting a copy of the log is not always possible, sometimes it has to be viewed locally at the system.
Additionally Notepad++ is limited in its search capability when compared to the power of MySql queries.

There is a tool designed for the NetQoS Windows products CAMLTODv3.exe.
ftp://ftp.ca.com/pub/netqos/supporttools/releasedtools/

Run the tool with no parameters and it will create a table containing the log.

camltodv3

Inserting log queries from D:\NetQoS\MySql51\data\<server name>.log into table mysql.<server name>_camltod

Here is a sample output of a simple select * from ... limit 20
---------------------------+--------------------------------------------------------------------------------------------------------------
| querynum | tstamp | query
---------------------------+--------------------------------------------------------------------------------------------------------------
| 1 | 130916 20:57:37 | select max(event_cleared) from event_log
| 2 | 130916 20:57:47 | select max(event_cleared) from event_log
| 3 | 130916 20:57:57 | select max(event_cleared) from event_log
| 4 | 130916 20:58:01 | SET NAMES latin1
| 5 | 130916 20:58:01 | SET character_set_results = NULL
| 6 | 130916 20:58:01 | SET SQL_AUTO_IS_NULL = 0
| 7 | 130916 20:58:01 | set @@sql_select_limit=DEFAULT
| 8 | 130916 20:58:01 | select service_id from services where service_name='Traps'
| 9 | 130916 20:58:01 | select property_set_name from property_sets where property_set_id=14
| 10 | 130916 20:58:01 | select max(event_sequence) from event_log where event_supplier='Traps'
| 11 | 130916 20:58:01 | select value from general where attribute like 'FileLogLevel%'
| 12 | 130916 20:58:01 | select property_value from properties where property_set_id=14 and property_name='TimingEnabled'
| 13 | 130916 20:58:01 | select property_value from properties where property_set_id=14 and property_name='TrapPort'
| 14 | 130916 20:58:01 | select property_value from properties where property_set_id=14 and property_name='TrapSeverity'
| 15 | 130916 20:58:01 | select property_value from properties where property_set_id=14 and property_name='TrapCategory'
| 16 | 130916 20:58:01 | select property_value from properties where property_set_id=14 and property_name='TrapMessage'
| 17 | 130916 20:58:01 | select event_id,event_name,event_descr,event_table,event_severity,event_category,event_message,event_set,even
| 18 | 130916 20:58:01 | select filter_trap,filter_expr from trap_filters where filter_id=1
| 19 | 130916 20:58:01 | select filter_trap,filter_expr from trap_filters where filter_id=2
| 20 | 130916 20:58:01 | select scope_net,scope_mask,scope_includes from trap_scopes where event_id=1
---------------------------+--------------------------------------------------------------------------------------------------------------

oh and in case you are wondering about enabling the general log.
set global general_log = 1;

and disable:
Set global general_log = 0;
or restart MySql.

location: \MySql51\data\<system name>.log.


These images are the same log, the first one is standard Windows Notepad and the second is the same log in Notepad++.

 

 

 

This document was generated from the following discussion: Easy Reading MySql General Query Log.

Outcomes