mysqlbinlog : Enable binary logs in mysql and extract from bin file

MySQL Server generate binary log files for every db transaction, provided administrator does not disable. The binary log files are written in binary format. It will be stored in /var/lib/mysql directory. It cannot be read directly, as it is in binary format. So we need to use mysqlbinlog command to read in text file.

Check enabled?

SELECT * from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'LOG_BIN';

 Or

SELECT @@log_bin;

Or

SHOW VARIABLES LIKE 'log_bin';

 

If not enabled:-enable binary logs in mysql

Add this to /etc/my.cnf:

log-bin=mysql-bin

[root@rhel6 ~]# /etc/init.d/mysqld restart --log-bin

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

[root@rhel6 ~]# mysql -u root -p

Enter password:

mysql> show binary logs;

ERROR 1381 (HY000): You are not using binary logging

[root@rhel6 ~]# updatedb

[root@rhel6 ~]# locate mysql-bin

Mysql - To extract from bin file:

mysqlbinlog  is a tool to analyze and view the binlogs  from mysql, which are stored in binary format. This will converts them to plaintext, so that  it can be readable.

--start-datetime and --stop-datetime , both will accept DATETIME or TIMESTAMP entries, and which together set the start/stop of what kind of information we’re interested in.

Ex:-

mysqlbinlog --start-datetime="2012-09-05 00:00:00" --stop-datetime="2012-12-31 14:00:00" mysql-bin.000019 --result-file=05Sept_31Dec.txt

mysqlbinlog --start-datetime="2012-12-31 01:00:00" mysql-bin.000019 --result-file=31dec.txt