How to know MySQL activities: MysqlBinLog

Mysql has this new (maybe it's old) logging system called binary log (also called logbin). This consists of files containing events that describe  modifications to database contents. This file is saved in binary format by the server and the only way to read this binary file in human form is through mysqlbinlog utility. (By the way, this binary log is used by slave to replicated master's schema, so this is really a power file).

To execute mysqlbinlog in your terminal:

mysqbinlog [options] logfile

like:

mysqlbinlog /var/lib/mysql/localhost-bin.000005 

This command will then display the content of the binary file localhost-bin.000005. The content of this file is:

# at 430

#111110 21:55:34 server id 1  end_log_pos 537 Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1320933334/*!*/;

update Company set name='hello' where id = 1

This 1st line which is "# at 430" means that the starting position of the event in the binary log file is 430. On the second line, the number's with colon is the timestamp of the event (this is when the event happen). The "server id 1" state that the event happen on server id number 1. "end_log_pos 537" indicates that the next event starts at 537. "thread_id=2" says that the thread that execute the event is id 2. "exec_time=0" means the time spent to execute the event. Lastly, the last line is the event executed by the server. As you can is it is an update.

For your server to log event as a binary file, you must first enable it. To enable, you must edit the my.cnf and append log-bin=path.

The normal formal of binary file logs is host_name-bin.###### and store in /var/bin/mysql directory.

Mysqbinlog I commonly use:

mysqbinlog /var/lib/mysql/localhost-bin.000005 > ~/mysql5.sql

This will put all the content of the logbin to the file mysql5.sql.

mysqbinlog --start-datetime="2011-11-11 11:11:11"  /var/lib/mysql/localhost-bin.000005

This will only display event that is log on and after 2011-11-11 11:11:11.

mysqbinlog --stop-datetime="2011-11-12 11:11:11" /var/lib/mysql/localhost-bin.000005

This will only display event that is log before 2011-11-12 11:11:11.

mysqbinlog --database=liims2 /var/lib/mysql/localhost-bin.000005

This will only display event that is invoke on database liims2.

Hope this will help you on using mysqlbinlog utility.

Reference:

http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html

http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html