Know about MySQL BINLOG FORMATS

What is binlog?

In MySQL the binary log record the events that, when ever the changes happens in the databases. (eg.create,delete,update,insert)

  

What is binlog formats?

The format that has been used to record the changes of data in binlog.

What are the their types?

There are three types of binlog formats.

1) Statement

2) Row

3) Mixed

1) Statement 

It is the default binlog format for MySQL5.6. It records the events in SQL statement in binlog to read easily with mysqlbinlog. The binlog does not grow so fast than row format.

Faster to recover from a backup.  how it works?

· set the binlog format to statement if you use older version of MySQL 5.6 in my.cnf file.

 · restart MySQL 

 · create a database example india.

 · mysql> create database india;

Query OK, 1 row affected (0.00 sec) 

mysql> use india;

Database changed

mysql> create table states(id int not null auto_increment,statename varchar(25),country varchar(25),primary key(id));

Query OK, 0 rows affected (0.18 sec)

  

· insert the details and use update query it must be affect all the rows in the column.

 · Then flush the logs.

 · use the old binlog before the new binlog.

 · Now use mysqlbinlog to view the events recorded in binlog.

 · Ex:shell> mysqlbinlog /path/mysql-bin.000007|less

 · The insert and update query that recorded in mysqlbinlog is. 

 · # at 1457

#130213 18:05:44 server id 1 end_log_pos 1589 Query thread_id=1 exec_time=0 error_code=0

SET TIMESTAMP=1360758944/*!*/; 

insert into states (statename,country)values('Chattisgarh','India')

# at 1934

#130213 18:05:55 server id 1 end_log_pos 1838 Query thread_id=1 exec_time=0 error_code=0

SET TIMESTAMP=1360758955/*!*/; 

insert into states (statename,country)values('Delhi','India')

# at 1934

#130213 18:11:03 server id 1 end_log_pos 2037 Query thread_id=1 exec_time=0 error_code=0

SET TIMESTAMP=1360759263/*!*/;

 update states set country='Great India'

 /*!*/;

 # at 2037

 #130213 18:11:03 server id 1 end_log_pos 2064 Xid = 24

 COMMIT/*!*/;

 # at 2064

 #130213 18:11:56 server id 1 end_log_pos 2107 Rotate to mysql-bin.000011 pos: 4

 DELIMITER ;

 # End of log file

 ROLLBACK /* added by mysqlbinlog */;

 /*!50003 COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 

 In this the events are recorded in SQL statement.So it is very easily to read when point time recovery.

2) Row 

It records each row of modification in binlog. It will record each and every thing that create,delete,update in the datas. The binlog grow faster than statement format.

· set the binlog format to row format in my.cnf file.

 · restart MySQL 

 · create a database example india.

 · mysql> create database india;

   Query OK, 1 row affected (0.00 sec) 

mysql> use india;

Database changed

mysql> create table states(id int not null auto_increment,statename varchar(25),country varchar(25),primary key(id));

Query OK, 0 rows affected (0.18 sec)

 · Insert the details and use update query it must be affect all the rows in the column.

 · Then flush the logs.

 · use the old binlog before the new binlog.

 · Now use mysqlbinlog to view the events recorded in binlog.

 · Ex:shell>mysqlbinlog /path/mysql-bin.000007|less

 · The insert and update query that recorded in mysqlbinlog is. 

 · # at 1514

 # at 1566

#130213 17:20:26 server id 1 end_log_pos 1566 Table_map: `india`.`states` mapped to number 34

#130213 17:20:26 server id 1 end_log_pos 1850 Update_rows: table id 34 flags: STMT_END_F

BINLOG '

An4bURMBAAAANAAAAB4GAAAAACIAAAAAAAEABWluZGlhAAZzdG

0ZXMAAwMPDwQZABkABg== 

An4bURgBAAAAHAEAADoHAAAAACIAAAAAAAEAA///+AEAAAANQW

5kcmgUHJhZGVzaAVJbmRpYfgB 

AAAADUFuZHJhIFByYWRlc2gLR3JlYXQgSW5kaWH4AgAAABFBcn

VuYWNoYWwgUHJhZGVzaAVJbmRp 

YfgCAAAAEUFydW5hY2hhbCBQcmFkZXNoC0dyZWF0IEluZGlh+AM

AAAAFQXNzYW0FSW5kaWH4AwAA 

 AAVBc3NhbQtHcmVhdCBJbmRpYfgEAAAABUJpaGFyBUluZGlh+AQ

AAAAFQmloYXILR3JlYXQgSW5k 

 aWH4BQAAAAxDaGhhdHRpc2dhcmgFSW5kaWH4BQAAAAxDaGhh

dHRpc2dhcmgLR3JlYXQgSW5kaWE= 

 '/*!*/;

 # at 1850

#130213 17:20:26 server id 1 end_log_pos 1877 Xid = 19

COMMIT/*!*/;

· we cant understand when we use mysqlbinlog /path/mysql-bin.000007 for row format. so to view and identify the row format in binlog.There are two ways to read row format in binlog.

 · --verbose,-v: It Reconstruct row events and display them as commented SQL statements. If this option is given twice, the output includes comments to indicate column data types and some metadata. 

 · --base64-output=decode-rows: This option determines when events should be displayed encoded as base-64 strings using BINLOG statements.

 · Eg shell>mysqlbinlog -v /path/mysql-bin.000007|less

or

shell>mysqlbinlog -v --base64-output=decode-rows mysql-bin.000007|less

· The insert query that recorded in mysqlbinlog is. 

· # at 517

 # at 569

#130213 17:15:18 server id 1 end_log_pos 569 Table_map: `india`.`states` mapped to number 34

#130213 17:15:18 server id 1 end_log_pos 623 Write_rows: table id 34 flags: STMT_END_F

BINLOG '

znwbURMBAAAANAAAADkCAAAAACIAAAAAAAEABWluZGlhAAZzdGF0Z

XMAAwMPDwQZABkABg==

znwbURcBAAAANgAAAG8CAAAAACIAAAAAAAEAA//4AQAAAA1BbmRyY

SBQcmFkZXNoBUluZGlh

 '/*!*/;

### INSERT INTO india.states

### SET

### @1=1

### @2='Andra Pradesh'

### @3='India'

# at 623

#130213 17:15:18 server id 1 end_log_pos 650 Xid = 12

COMMIT/*!*/; 

-->

# at 719

# at 771

#130213 17:15:52 server id 1 end_log_pos 771 Table_map: `india`.`states` mapped to number 34

#130213 17:15:52 server id 1 end_log_pos 829 Write_rows: table id 34 flags: STMT_END_F

BINLOG ' 

8HwbURMBAAAANAAAAAMDAAAAACIAAAAAAAEABWluZGlhAAZzdGF

0ZXMAAwMPDwQZABkABg==

8HwbURcBAAAAOgAAAD0DAAAAACIAAAAAAAEAA//4AgAAABFBcnVu

YWNoYWwgUHJhZGVzaAVJbmRp

 YQ==

'/*!*/;

### INSERT INTO india.states

### SET

### @1=2

### @2='Arunachal Pradesh'

### @3='India'

# at 829

#130213 17:15:52 server id 1 end_log_pos 856 Xid = 13

COMMIT/*!*/;

Here the insert statement are recorded in row format.The update query that recorded in mysqlbinlog is. 

 · BINLOG ' 

An4bURMBAAAANAAAAB4GAAAAACIAAAAAAAEABWluZGlhAAZzdGF0

ZXMAAwMPDwQZABkABg==

An4bURgBAAAAHAEAADoHAAAAACIAAAAAAAEAA///+AEAAAANQW5k

cmEgUHJhZGVzaAVJbmRpYfgB 

 AAAADUFuZHJhIFByYWRlc2gLR3JlYXQgSW5kaWH4AgAAABFBcnVuYW

NoYWwgUHJhZGVzaAVJbmRp

 YfgCAAAAEUFydW5hY2hhbCBQcmFkZXNoC0dyZWF0IEluZGlh+AMAAA

AFQXNzYW0FSW5kaWH4AwAA

AAVBc3NhbQtHcmVhdCBJbmRpYfgEAAAABUJpaGFyBUluZGlh+AQAAA

AFQmloYXILR3JlYXQgSW5k

 aWH4BQAAAAxDaGhhdHRpc2dhcmgFSW5kaWH4BQAAAAxDaGhhdHR

pc2dhcmgLR3JlYXQgSW5kaWE=

'/*!*/;

### UPDATE india.states

### WHERE

### @1=1

### @2='Andra Pradesh'

### @3='India'

### SET

### @1=1

### @2='Andra Pradesh'

### @3='Great India'

### UPDATE india.states

### WHERE

### @1=2

### @2='Arunachal Pradesh'

### @3='India'

### SET

### @1=2

### @2='Arunachal Pradesh'

### @3='Great India'

### UPDATE india.states

### WHERE

### @1=3

### @2='Assam'

### @3='India'

### SET

### SET

### @1=3

### @2='Assam'

### @3='Great India'

### UPDATE india.states

### WHERE

### @1=4

### @2='Bihar'

### @3='India'

### SET

### @1=4

### @2='Bihar'

### @3='Great India'

### UPDATE india.states

### WHERE

### @1=5

### @2='Chhattisgarh'

### @3='India'

### SET

### @1=5

### @2='Chhattisgarh'

### @3='Great India'

# at 1850

#130213 17:20:26 server id 1 end_log_pos 1877 Xid = 19

COMMIT/*!*/;

# at 1877

#130213 17:25:01 server id 1 end_log_pos 1896 Stop

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

(END)

Here the update query was recorded by each and every row affected in the table. So that binlog will grow faster than statement format.

3) Mixed 

It combines of both statement and row format. In this statement format is the default one for mixed format. The row format will change automatically when it needs. 

More details : http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html