MySQLLogging in MySQL

Logging in MySQL

References : – MySQL Reference Manual

Logging in MySql

Latest version of MysQL allows logging into both files and tables. Though Table logging is more advanced and desirable(we can use queries to select a particular client or timestamps to see what happened via SQL statements) , it has heavy performance requirements when compared to file logging and is still in development stages. So recommended at present is MySQL File logging

There are 2 options for logging

1. General Query Log [Logs all events including select and show statements]
[Performance Decrease > 5%]
2. The Binary Query Log (also called the Mysql BinLog) [Only Logs events that modify/change data like Delete, update] [Performance Decrease – 1%]

The General Query Log

States What MySQL is doing
All client server connections, ALL sql statements received from clients are logged with the timestamps

Imp Note – mysqld writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed.

How to set up

Both points have to be satisfied else logging wont work

1.In “my.ini” set the below system variables

general_log - set it to 1 (default is 0)

general_log_file    = path to the log file name [example -  /mysql/data/mysql.log]

[example Command line : SET GLOBAL general_log = ‘ON’;]

2.At server start up : Mysql server has to be started with Server System variable “–log-output”

Syntax : --log-output=FILE

This logs entries into a file specified by “general_log_file”
More generic syntax is as below
Syntax : –log-output=[FILE/TABLE/NONE] – as u see we can even log into tables
For more information check

Default file name is host_name.log in the data directory.
Default value of logs is 1GB
Use “FLUSH LOGS” to flush out the logs

*****The general query log should be protected because logged statements might contain passwords********

The Binary Query Log

The binary log contains all statements that update data (Even a DELETE Query that matched no rows will be logged) – The binary log is not used for statements such as SELECT or SHOW that do not modify data.

Main purpose of Binlog is for slave server replication. Master Server sends its binarylog to slave servers – slave servers then execute all commands in binlog of master- this keeps master and slave servers inSync

Update log(an old utility) is deprecated and binlog has replaced the update log

The binary log should be protected because logged statements might contain passwords. S

How to Set up

1.In “my.ini” set the below system variables
Uncomment the below

a)log-bin = mysql-bin (logs will be created like mysql-bin.000001,  mysql-bin.000002, mysql-bin.000003 etc)
b)binlog_format = mixed  (note set it to mixed if it is not set)

by default all files created in “data” directory

2.At server startup :Start mysqld with Server System variable “–log-bin[=basename]”

Syntax : --log-bin=dbname

Default value of logs is 1GB
Use “PURGE BINARY LOGS” to flush out the logs

To access the binary logs
Syntax : mysalbinlog [binlog file name] > [optional text file name for easy reading]

example : mysqlbinlog mysql-bin.000001 C:\binarylog.txt

Categories: MySQL


No Comments Yet. Be the first?

Post a comment

Your email address will not be published. Required fields are marked *