MySQL Point in Time Recovery using Binary log

Point-in-time recovery refers to recovery of data changes made since a given point in time. Typically, this type of recovery is performed after restoring a full backup that brings the server to its state as of the time the backup was made.Mysql PIT

Sometimes we may face the situation where we have a full MySQL database backup done with mysqldump and then we have to restore and recover just one single table out of our huge mysqldump file.
Further our mysqldump backup was taken hours ago so we want to recover all the changes on that table since our backup was taken up to the end.

The main spread technique to replace those binary logs event is to use the `mysqlbinlog` command. However, depending on your workload, this process can be quick or slow, depending on how much data there is to process. Moreover, `mysqlbinlog` parses and dumps binary logs in a single thread, therefore sequentially.

Point-in-time recovery then brings the server up to date incrementally from the time of the full backup to a more recent time.

Use mysqldump tool backup

First of all, mysqldump resolve the table’s structure. secondly, it will add a CREATE statement to the text file. The record in the table will be transfered to the insert statement for recovering. mysqldump advantages include the convenience and flexibility of viewing or even editing the output before restoring. Mysqldump is able to record the binary logs’ location and the content.

Count of rows at the time of backup

pic1

Take a full backup using mysqldump

$ mysqldump -ubackupuser -pXXXXXX --lock-all-tables --master-data=2 --events --routines --all-databases > /mysqlbackup/database_`date +%F`.sql

–lock-all-tables add the read_only lock for all tables. –master-data=2 mark the binary logs location at present –events Include Event Scheduler events for the dumped databases in the output. –routines Dump stored routines (procedures and functions) from dumped databases –all-databases backup all database.

Verify whether the backup is successful and also note down the master binary file and master position.

[root@ip-172-31-32-96 mysqlbackup]# tail -n10 database_2018-11-26.sql/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2018-11-26 14:53:57

[root@ip-172-31-32-96 mysqlbackup]# head -n24 database_2018-11-26.sql
-- MySQL dump 10.13 Distrib 5.7.24, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.7.24-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin.000002', MASTER_LOG_POS=1603;

pic2

Now we insert around 20 rows

pic3

Now we emulate a data damage .The need for recover is possibly due to a TRUCATE TABLE command or similar operation executed on the wrong system or it is just a time somebody has indicated us to recover to.

pic4

PITR Recovery Strategy

Import the dump file and restore the database.

 mysql -uroot -p < database_2018-11-26.sql

Extract a single table from dump if required

sed -n '/^-- Current Database: `testdata`/,/^-- Current Database: `/p' database_2018-11-26.sql > testdata_extract.sql

 

pic5

What is Binary Log?

  • contains all statements that update data (or possible could update the data)
  • stored in the form of “events” that describe the modifications
  • logs how long each statement took

Point-in-time recovery

  • Recovering first from the backup files to restore the server to its state when the backup was made,
  • Re-executing changes in subsequently written binary log files to redo data modifications up to the desired point in time
  • Because the output of mysqlbinlog includes SET TIMESTAMP statements before each SQL statement recorded, the recovered data and related MySQL logs will reflect the original times at which the transactions were executed

pic6

The mysqlbinlog utility converts the events in the binary log files from binary format to text so that they can be executed or viewed. mysqlbinlog has options for selecting sections of the binary log based on event times or position of events within the log.

The approximate time of deletion has happened at 15:13:09

 # mysqlbinlog -v mysqlbin.000002 | grep -B5 TRUNCATE --color
--
#181126 15:13:09 server id 22 end_log_pos 919664 CRC32 0x14d2cf29 Anonymous_GTID last_committed=2031 sequence_number=2032 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 919664
#181126 15:13:09 server id 22 end_log_pos 919758 CRC32 0xc872a41d Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1543245189/*!*/;
TRUNCATE TABLE testdata

pic7

Dump all the delta changes from binary file to sql file.

mysqlbinlog --start-position=1603 --stop-position=919664 mysqlbin.000002 > /mysqlbackup/binlog_`date +%F_%H`.sql

You can also dump the changes using –start-datetime and –stop-datetime method

DB=testdata
BEG_DT="2018-11-26 14:53:57"
END_DT="2018-11-26 15:13:09"
BINLOGS="mysqlbin.000002 .....mysqlbin.00000n"      # n number of binary logs 
mysqlbinlog -d ${DB} --start-datetime="${BEG_DT}" --stop-datetime="${END_DT}" ${BINLOGS} > /mysqlbackup/database_`date +%F`.sql

Restore the delta changes to mysql instance

mysql -uroot -p   < binlog_2018-11-26_16.sql

Now the table testdba1.testdata is recovered to the wanted point in time

pic8

When writing to a dump file while reading back from a binary log containing GTIDs use the –skip-gtids option with mysqlbinlog :

# mysqlbinlog --skip-gtids binlog.000002 >  /tmp/dump.sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: