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

    Copyright 2020. All Rights Reserved