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