Recover Lost MySQL data with mysqlbinlog point-in-time-recovery example

Backup ... backup... Backup... but of course.. you also need to monitor and test those backups often otherwise they could be worthless.  Having your MySQL binlogs enabled can certainly help you in times of an emergency as well.  The MySQL binlogs are often referenced in regards to MySQL replication, for a good reason, they store all of the queries or events that alter data (row-based is a little different but this an example). The binlogs have a minimal impact on server performance when considering the recovery options they provide.


[anothermysqldba]> show variables like 'log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name                   | Value                                      |
+---------------------------------+--------------------------------------------+
| log_bin                         | ON                                         |
| log_bin_basename                | /var/lib/mysql/binlogs/mysql-binlogs       |
| log_bin_index                   | /var/lib/mysql/binlogs/mysql-binlogs.index |

show variables like 'binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+


So this is just a simple example using mysqlbinlog to recover data from a binlog and apply it back to the database.

First we need something to loose. If something was to happen to our database we need to be able to recover the data or maybe it is just a way to recover from someones mistake.


CREATE TABLE `table_w_rdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `somedata` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `moredata` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
 

We can pretend here and assume that we have developers/DBAs that are not communicating very well and/or saving copies of their code.


delimiter //
CREATE PROCEDURE populate_dummydata( IN rowsofdata INT )
BEGIN

SET @A = 3;
SET @B = 15 - @A;
SET @C = 16;
SET @D = 25 - @C;

  WHILE rowsofdata > 0 DO
    INSERT INTO table_w_rdata
    SELECT NULL, SUBSTR(md5(''),FLOOR( @A + (RAND() * @B ))) as somedata, SUBSTR(md5(''),FLOOR( @C + (RAND() * @D )))  AS moredata  ;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END//
delimiter ;
call populate_dummydata(50);

> SELECT NOW() \G
*************************** 1. row ***************************
NOW(): 2014-11-27 17:32:25
1 row in set (0.00 sec)

> SELECT  * from table_w_rdata  WHERE id > 45;
+----+----------------------------+------------------+
| id | somedata                   | moredata         |
+----+----------------------------+------------------+
| 46 | b204e9800998ecf8427e       | 0998ecf8427e     |
| 47 | d98f00b204e9800998ecf8427e | 8ecf8427e        |
| 48 | b204e9800998ecf8427e       | 800998ecf8427e   |
| 49 | 98f00b204e9800998ecf8427e  | e9800998ecf8427e |
| 50 | 98f00b204e9800998ecf8427e  | 998ecf8427e      |
+----+----------------------------+------------------+


While one procedure is created it is later written over by someone else incorrectly. 


DROP PROCEDURE IF EXISTS populate_dummydata ;
delimiter //
CREATE PROCEDURE populate_dummydata( IN rowsofdata INT )
BEGIN

SET @A = 3;
SET @B = 15 - @A;
SET @C = 16;
SET @D = 25 - @C;

  WHILE rowsofdata > 0 DO
    INSERT INTO table_w_rdata
    SELECT NULL, SUBSTR(md5(''),FLOOR( @C + (RAND() * @A ))) as somedata, SUBSTR(md5(''),FLOOR( @B + (RAND() * @D )))  AS moredata  ;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END//
delimiter ;

call populate_dummydata(50);
> SELECT NOW(); SELECT  * from table_w_rdata  WHERE id > 95;
+---------------------+
| NOW()               |
+---------------------+
| 2014-11-27 17:36:28 |
+---------------------+
1 row in set (0.00 sec)

+-----+-------------------+---------------------+
| id  | somedata          | moredata            |
+-----+-------------------+---------------------+
|  96 | 4e9800998ecf8427e | 00998ecf8427e       |
|  97 | 9800998ecf8427e   | 800998ecf8427e      |
|  98 | e9800998ecf8427e  | 204e9800998ecf8427e |
|  99 | e9800998ecf8427e  | 4e9800998ecf8427e   |
| 100 | 9800998ecf8427e   | 04e9800998ecf8427e  |
+-----+-------------------+---------------------+


The replaced version of the procedure is not generating  random values like the team wanted. The original creator of the procedure just quit from frustration. So what to do? A little time has past since it was created as well. We do know the database name, routine name and the general time frame when the incorrect procedure was created and lucky for us the bin logs are still around, so we can go get it.

We have to take a general look around since we just want a point-in-time-recovery of this procedure.We happen to find the procedure and the position in the binlog before and after it.


NOW(): 2014-11-27 19:46:17
# mysqlbinlog  --start-datetime=20141127173200 --stop-datetime=20141127173628 --database=anothermysqldba mysql-binlogs.000001  | more

 at 253053
 at 253564

# mysql anothermysqldba --login-path=local  -e "DROP PROCEDURE populate_dummydata";
# mysqlbinlog  --start-position=253053 --stop-position=253564 --database=anothermysqldba mysql-binlogs.000001 | mysql --login-path=local  anothermysqldba


> SHOW CREATE PROCEDURE populate_dummydata\G
*************************** 1. row ***************************
           Procedure: populate_dummydata
            sql_mode: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `populate_dummydata`( IN rowsofdata INT )
BEGIN

SET @A = 3;
SET @B = 15 - @A;
SET @C = 16;
SET @D = 25 - @C;

  WHILE rowsofdata > 0 DO
    INSERT INTO table_w_rdata
    SELECT NULL, SUBSTR(md5(''),FLOOR( @A + (RAND() * @B ))) as somedata, SUBSTR(md5(''),FLOOR( @C + (RAND() * @D )))  AS moredata  ;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

NOW(): 2014-11-27 19:51:03
> call populate_dummydata(50);
> SELECT  * from table_w_rdata  WHERE id > 145;
+-----+-----------------------------+------------------+
| id  | somedata                    | moredata         |
+-----+-----------------------------+------------------+
| 146 | 98f00b204e9800998ecf8427e   | 800998ecf8427e   |
| 147 | cd98f00b204e9800998ecf8427e | 800998ecf8427e   |
| 148 | 204e9800998ecf8427e         | 98ecf8427e       |
| 149 | d98f00b204e9800998ecf8427e  | e9800998ecf8427e |
| 150 | 204e9800998ecf8427e         | 9800998ecf8427e  |
+-----+-----------------------------+------------------+


We recovered our procedure from the binary log via point-in-time-recovery.
This is a simple example but it is an example of the tools you can use moving forward.

This is why the binlogs are so valuable.

Helpful URL:

• http://dev.mysql.com/doc/refman/5.6/en/binary-log.html
• http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html