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

Introduction to docker!

Image
Well, Today I thought to bring you something different. Docker is the most popular platform in today to run applications easily. Docker helps us to deploy our applications more easily with less involvements of configurations. Main concept behind the docker is virtualization . Anyway, this is some kind of large topic and like you, I'm still learning these stuff. However I would like to share my experience on this technology with you. I hope this will help someone who willing to take the first step of the docker technology. Without further, let's dive into the docker sea !. So as I say, Lets start docker from basic. 😉 Why we need docker? The requirement of docker is started with the introduction of micro service architecture. According to the microservice architecture , we should split/divide our application into different individual services. So that, unlike 3 tier (monolithic) architecture, we have to deploy and run several services. Example system with microservic...
Read more