How to view queries executed in MySQL?

Hi all, This is a small post which I would like to share my finding with you. In one of my software project, There was a small peace of software which frequently dealing with database. (I got that clue from MySQL workbench dashboard) I was so curious about that because I wanted to know what does that do and what does it read/write from/to the database. 

Finally I found a solution which helps me to find out that ! The solution was MySQL log. First you will have to enable the log. If you are using a version below 5.1.12, you will have to go to configuration level (my.cnf). But fortunately, If you are using MySQL >= 5.1.12 you can do that on the fly with MySQL global variables and does not need to restart the server as well.

This is how I did (in MySQL >= 5.1.12),

There are two options which can be done.

  1. Writing log to a table
  2. Writing log to a file.

By default, MySQL logging is disabled, because of the performance reasons.  So we have to enable it first.

 

Writing log to a table

Execute following lines in MySQL console

SET GLOBAL log_output = 'TABLE';

SET GLOBAL general_log = 'ON';


Thats it!, Now MySQL will start to log all queries into a table called mysql.general_log table. So you can search the log like below

SELECT * FROM mysql.general_log;


Writing log to a file

Execute following lines in MySQL console

SET GLOBAL log_output = 'FILE';

SET GLOBAL general_log_file = '/specific/path/to/mylog.log';

SET GLOBAL general_log = 'ON';


Now mysql will start to log all queries into the log file you configured.


How to turn off logging.

Execute following line in mysql console

SET GLOBAL general_log = 'OFF';

Then MySQL will immediately stops writing log


Note

If you restarts the MySQL server, the configurations you made above will be discarded and reloads the default values. You can always see current values of global variables with following queries

SHOW GLOBAL VARIABLES LIKE 'general_log';

SHOW GLOBAL VARIABLES LIKE 'log_output';

SHOW GLOBAL VARIABLES LIKE 'general_log_file';

Comments

Post a Comment

Popular posts from this blog

CSRF Defence - Synchronizer Token Pattern Demo

Image
I have wrote some theoretical background of CSRF and CSRF prevention mechanisms in previous posts. If you don't have much idea about what CSRF and how prevention mechanisms works, please go back and read them first. These are the links for them. Cross Site Request Forgery (CSRF) CSRF Defence - Synchronizer Token Pattern From here onword, I assume you have the basic idea of concept of CSRF attack and prevention mechanisms. So, lets get started. This post is fully practical based one. At them end of this, There will be fully functioning web based application to demonstrate how CSRF attack works and how to prevent it using synchronizer token pattern. So, in order to follow this, you will need to setup your development environment first. These are the technologies I used to develop this demonstration application. Technologies Java 8 Spring Boot Framework Spring Security Maven Bootstrap 4 (Optional, Just for enhance the look and feel) Integrated Development Environmen
Read more

Lets read emails in gmail using oAuth 2.0 (Application demo)

Image
Today I will create a oAuth application using java programming language. The application will access google's gmail api and read emails in user's gmail inbox. To access user's gmail account, application will use oAuth access token obtained by gmail authentication server. Don't worry. I will demonstrate each step by clearly. So lets start computing from basics! To use gmail api, we need to obtain app id and app secret from google api console. To do so, you will need a google account. If you are using a gmail account or any other google product (Ex. google drive, youtube), then you already have a google account. If you don't have a google account, you can create it from here. So then you will need to access into the google cloud api console. It has very clear and clean user interface. Google api console will provide various kinds of apis from each of google products. Since we are going to develop for Gmail, lets log into gmail api section. In google itself there are
Read more