MySQL Point-in-Time (Incremental) Recovery Using the Binary Log

Point-in-time recovery refers to recovery of data changes made since a given point in time. Point-in-time principles:

  • the server must be started with the "--log-bin" option to enable binary logging
  • To restore data from the binary log, you must know the name and location of the current binary log files.
  • To see a listing of all binary log files, use this statement:
    mysql> SHOW BINARY LOGS;
    +-------------------+------------+
    | Log_name          | File_size  |
    +-------------------+------------+
    | bih001-bin.000001 |     471330 |
    | bih001-bin.000002 | 1074180683 |
    | bih001-bin.000003 | 1073851408 |
    | bih001-bin.000004 | 1074179088 |
    | bih001-bin.000005 | 1074520589 |
    | bih001-bin.000006 | 1074506906 |
    | bih001-bin.000007 | 1074612015 |
    | bih001-bin.000008 | 1074354544 |
    | bih001-bin.000009 |  670753331 |
    | bih001-bin.000010 |        598 |
    +-------------------+------------+
    10 rows in set (0.00 sec)
    
    #Find the binaty log in use:
    
    mysql> SHOW MASTER STATUS;
    +-------------------+----------+--------------+------------------+
    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +-------------------+----------+--------------+------------------+
    | bih001-bin.000010 |      598 |              |                  |
    +-------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    
    mysql>
  • Find where the lob-bins are located on your host. I do this by looking into your my.cnf file(located in /etc/my.cnf normally).
  • bash>cat /etc/my.cnf | grep log-bin
    log-bin=mysql-bin
    log-bin=/home/mysql_dados/bih001_bin_logs/bih001-bin
  • To get the content of the log you need to use the mysqlbinlog tool that comes with mysql installation.
  • bash> mysqlbinlog bih001-bin.000010 >  /tmp/stmt.sql
    You can alter the output and use it as you wish to rollback or restore