Restoring records from MySQL binlog files.

MySQL is having a good features as binlog files.When ever the transaction happens in the server this binlog files have the transaction details.


Here am going to explain how to restore records from mysqlbinlog file.

For example:

First check  bin log is enabled or not.

login to mysql,check it by,

mysql>show binary logs ;
+------------------+-----------+
| Log_name   | File_size |
+------------------+-----------+
| mysql-bin.000001 |  25071488 |
| mysql-bin.000002 |       150 |
| mysql-bin.000003 |    131262 |
| mysql-bin.000004 |    463122 |
| mysql-bin.000005 |   1040619 |
+------------------+-----------+
5 rows in set (0.00 sec)

So binlog is enabled in this server.If not enabled do the below,


Remove "#" for log-bin=mysql-bin from my.cnf file

Restart mysql.


Now,I have a database name "fruits" inside I have table name is "apple" it contains 15 records as shown below.

mysql>use fruits;
Database changed

 mysql> show tables;
+------------------+
| Tables_in_fruits |
+------------------+
| apple            |
| banana        |
| mango         |
| orange        |
+------------------+

 mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
|  10 | KLM      | 1389335315 |
|  11 | xyz      | 1389335327 |
|  12 | RES      | 1389335329 |
|  13 | xyz      | 1389335331 |
|  14 | RES      | 1389335333 |
|  15 | RES      | 1389335335 |
+-----+----------+------------+
15 rows in set (0.00 sec)

I am taking a backup of this apple table now,

root@knowmysql]# mysqldump -u root -p fruits apple > fruits_apple.sql

After taking backup I am inserting some rows in the apple table like below,

mysql>insert into apple (id,username,timestamp) values('16','ILK','1389335345');

Query OK, 1 row affected (0.00 sec)

mysql> insert into apple (id,username,timestamp) values('17','bcd','1389335355');
Query OK, 1 row affected (0.00 sec)

mysql> insert into apple (id,username,timestamp) values('18','brd','1389335375');
Query OK, 1 row affected (0.00 sec)

mysql> insert into apple (id,username,timestamp) values('19','elf','1389335395');
Query OK, 1 row affected (0.00 sec)

mysql> insert into apple (id,username,timestamp) values('20','OPQ','1389335413');
Query OK, 1 row affected (0.01 sec)


Now totally we have 20 rows in the table.

mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
|  10 | KLM      | 1389335315 |
|  11 | xyz      | 1389335327 |
|  12 | RES      | 1389335329 |
|  13 | xyz      | 1389335331 |
|  14 | RES      | 1389335333 |
|  15 | RES      | 1389335335 |
|  16 | ILK      | 1389335345 |
|  17 | bcd      | 1389335355 |
|  18 | brd      | 1389335375 |
|  19 | elf      | 1389335395 |
|  20 | OPQ      | 1389335413 |
+-----+----------+------------+
20 rows in set (0.00 sec)

Now am deleting last five records from apple table.

mysql>delete from apple where id in('16','17','18','19','20');
Query OK, 3 rows affected (0.00 sec)

Now this table is having 15 records,

mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
|  10 | KLM      | 1389335315 |
|  11 | xyz      | 1389335327 |
|  12 | RES      | 1389335329 |
|  13 | xyz      | 1389335331 |
|  14 | RES      | 1389335333 |
|  15 | RES      | 1389335335 |
| +-----+----------+------------+

15 rows in set (0.00 sec)

Now I am going to restore the deleted records from binlog,

Take a backp of apple table now and drop it from the server.

root@test]# mysqldump -u root -p fruits apple > fruits_apple11.sql

mysql>drop table apple;

mysql> show tables;
+------------------+
| Tables_in_fruits |
+------------------+
| banana        |
| mango         |
| orange        |
+------------------+

apple table dropped.

Import our old backup inside the fruit databases.

root@test]#mysql -u root -p fruits <  fruits_apple.sql

Now the table is having only 15 records.

 mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
|  10 | KLM      | 1389335315 |
|  11 | xyz      | 1389335327 |
|  12 | RES      | 1389335329 |
|  13 | xyz      | 1389335331 |
|  14 | RES      | 1389335333 |
|  15 | RES      | 1389335335 |
+-----+----------+------------+
15 rows in set (0.00 sec)
 

Now check the last binlog file from the data directory.(Binlog default location is mysql data directory)

Here am using row format for binlog in my.cnf.For row format below format is  used to view the binlog file.

 root@knowmysql]#mysqlbinlog -v --base64-output=decode-rows /var/lib/mysqldata/mysql-bin.000005|less

From binlog file search at which position the deleted records were inserted.

I have found the binlog position that insertion happens in the server for id 16,17,18,19,20 in the apple table.

starting position is,  941107 and ending position is 942025.

Now am going to restore from binlog,

root@test]#mysqlbinlog --start-position=941107 --stop-position=942025 /var/lib/mysqldata/mysql-bin.000005 |mysql -u root -p fruits

Records were restored,Now check the records in apple table.

 mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
|   1 | abc      | 1389335150 |
|   2 | abc      | 1389335163 |
|   3 | abc      | 1389335165 |
|   4 | prs      | 1389335167 |
|   5 | abc      | 1389335167 |
|   6 | abc      | 1389335169 |
|   7 | xyz      | 1389335176 |
|   8 | xyz      | 1389335178 |
|   9 | KLM      | 1389335181 |
|  10 | KLM      | 1389335315 |
|  11 | xyz      | 1389335327 |
|  12 | RES      | 1389335329 |
|  13 | xyz      | 1389335331 |
|  14 | RES      | 1389335333 |
|  15 | RES      | 1389335335 |
|  16 | ILK      | 1389335345 |
|  17 | bcd      | 1389335355 |
|  18 | brd      | 1389335375 |
|  19 | elf      | 1389335395 |
|  20 | OPQ      | 1389335413 |
+-----+----------+------------+
20 rows in set (0.00 sec)


Now Apple table is having 20 records and  restored the deleted 17,18,19 records from binlog file.


We can restore data from mysqlbinlog by date and time like below.

mysqlbinlog --start-datetime="2014-01-15  10:00:00" \ --stop-datetime="2014-01-15 10:01:04 \ /var/lib/mysqldata/mysql-bin.000005 > /data/apple.sql

mysqlbinlog --start-position=941327  /var/lib/mysqldata/mysql-bin.000005 |mysql -u root -p tablename

This way is little bit tough but we can restore all the records without missing any record.

I hope it will help you.


Thank you.

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