MySQL general_log writing into a table (no server SSH access needed)

Mysql supports a config option called general_log

When enabled (add general_log=1 under [mysqld] into /etc/mysql.cnf or any included file from it), Mysql will print all the executed queries into a log file (see the specific documentation of your version to understand where the file will be written based on settings).

The general log is a useful setting to debug queries coming from the ORM when developing locally. Frameworks normally let you visualise executed queries, but ORM and the majority of queries are executed via prepared statements, and parameters are bound from the MySQL server, preventing the code to know the exact executed query.

Output general log into a table

If you need to debug on a non-local (non-prod) MySQL machine and for particular reasons you prefer to use the MySQL client itself to retrieve the log entries, you can add the setting log_output=TABLE. Mysql will append all the executed queries into mysql.general_log table.

To truncate the log:

truncate mysql.general_log;

To visualise the last 10 executed queries (last executed one first), skipping MySQL internal queries (and this query itself):

SELECT CAST(argument AS CHAR(10000) CHARACTER SET utf8)  as arg
FROM mysql.general_log
WHERE command_type="Query"
AND argument NOT LIKE '%performance_schema%'
AND argument NOT LIKE '%mysql.general_log%'
ORDER BY event_time DESC
LIMIT 10

If you are using MySQL workbench, click on the output and select Copy Field (unquoted) then paste into another tab and format to better visualise or run an EXPLAIN on it

Reference:

Software Engineer @ London

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store