Overview:

I use the following script constantly to temporarily turn on/off MySQL query logging. With this enabled, “tail -f” can provide a wealth of live MySQL trace logging for all active connections.

I use this technique constantly when testing one-to-many joins defined in Spring Hibernate entity classes.

Prerequisites:

  • Access to MySQL Workbench or MySQL command-line utility
  • Associated user will need administrative role privileges, as this changes the MySQL Server running configuration.
  • See disclaimer

Procedure:

IMPORTANT: Remember to turn general_log back off when finished, or it will fill up the system. Freeing a dev linux system that is 100% full is a tedious chore.

  • Turn ON logging:     SET global general_log = 1
  • Turn OFF logging:    SET global general_log = 0

I added the comments, so you can paste the entire statement directly into a MySQL Workbench query window. Un-comment the first line, and set as appropriate (see above)

-- SET global general_log = 1;
show variables like '%general_log%';
-- 1) Log into MySQL session with admin priv, execute: show variables like '%general_log%';
-- 2) See status of general_log and general_log_file
-- 3) Temporarily turn on query logging: SET global general_log = 1;
-- 4) At linux console: Tail log file: tail -f your_general_log_file_path
-- 5) When done monitoring, turn off query logging: SET global general_log = 0;
-- 6) If necessary, clear query log at linux console: cat /dev/null > your_general_log_file_path

On my system, I would use the following to show a live trace audit of MySQL activity:
tail -f /var/lib/mysql/localhost.log

 

BitBucket Repo and Project Code

A simple but effective method to capture MySQL activity.
paulsDevBlog.End();