Binary Log recovery using mysqlbinlog tool

Once the full backup and incremental Xtra backup is applied and made ready,

the binary log has to be applied in the sequence.

The last binary log co-ordinates can be found from xtrabackup_binlog_info/ xtrabackup_slave_info in the backup.

mysqlbinlog utility is used to read and apply the binary logs.

Scenario: Recover data upto “2015-05-05 23:30:00”

Binlog co-ordinates:   mysqlbin.002 , 658

Available Binlogs:     mysqlbin.00[2-6]

Full and Incremental backups are applied.

mysqlbinlog  --start-position=658  mysqlbin.002 mysqlbin.003

mysqlbin.004 mysqlbin.005  | mysql –u root –p xxxxx

mysqlbinlog  --stop-datetime=’2015-05-05 23:30:00’ mysqlbin.006 |  mysql –u root –p xxxxx

Note :  To read binary log from remote server --read-from-remote-server can be used in mysqlbinlog utility

• Restore rest of data from binary log.
  If you have more than one binary log to execute on the MySQL server, the safe method is to process them all using a single connection to the server.

  mysqlbinlog mysql_bin.000001 | mysql -u root -ppassword database_name
  mysqlbinlog mysql_bin.000002 | mysql -u root -ppassword database_name

  or:

  mysqlbinlog mysql_bin.000001 mysql_bin.000002 | mysql -u root -ppassword database_name

• Restore data on basis of time

  mysqlbinlog --start-datetime="2005-04-20 10:01:00" \
              --stop-datetime="2005-04-20 9:59:59" mysql_bin.000001 \
              | mysql -u root -ppassword database_name

• Restore data on basis of position

  mysqlbinlog --start-position=368315 \
              --stop-position=368312 mysql_bin.000001 \
              | mysql -u root -ppassword database_name