Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Thursday, 8 February 2018

Database Recovery Using binlog file (mysql-bin.xxxxx)

Database Recovery Using binlog file (mysql-bin.xxxxx)

mysqlbinlog Utility for Processing Binary Log Files.

In this  article, I'm discussing about the use of mysqlbinlog Utility. How to read the binary logs , How to use it in case of Disaster recovery , how to export it in other readable format etc. I will start our discussion from basic and then drill in deep step-by-step.


Before starting our discussion, I would like to put a small footprint about MySQL binary log.



The binary log files that the server generates are written in binary format.MySQL records all the transaction in binary log. But we can not able to read this log normally due to it's binary format .To examine these files in text format, use the mysqlbinlog utility. As this file contains all the transaction so we can use this log for any Disaster Recovery / Point-in-Time recovery* .



We can also use mysqlbinlog to read relay log files written by a slave server in a replication* setup. Relay logs have the same format as binary log files.



How to display the content of  binary log file named mysql-bin.xxxxx ?


>> mysqlbinlog  [options] mysql-bin.xxxxx

eg.

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe  mysql-bin.xxxxx 

We can export this file to other format like .sql/.txt etc

eg.
C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe  mysql-bin.xxxxx > D:\GetMySQL.sql


We can also read this log from remote server by using some additional parameters like --read-from-remote-server ,--host, --password, --port, --protocol, --socket, --user etc.


Some of the important parameter's which we use in DB recovery as --database=name, --debug-info , --start-datetime=datetime , --stop-datetime=datetime , --start-position=N , --stop-position=N 


Once again I bring to mind you that Binary log is by-default disabled. So you need to enable* it before starting MySQL services. Binary Log is also use in setup of MySQL Replication*

For example, we have 5 binlog mysql-bin.00001,mysql-bin.00002,mysql-bin.00003,mysql-bin.00004,mysql-bin.00005

We have some limitation in this recovery.

1: We must have to restore any old backup which contains all the database/ table .
2: Need to administrative privileges to perform this activity.
3: Server health must be good in which we will perform this activity.

Now perform below steps in your task...

1: Restore any available old backup .
2: read and export all the binlog file to readable format.
3: Analyze binlog file Start time/Stop Time/Start Position/Stop Position etc.
4: Now we use the below commands for restoring...

For restoring mysql-bin.00001

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe mysql-bin.00001 | mysql -h [host ip] -u root -p ;

If you want to recover by Specifying time stamp..

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --stop-time="2018-02-07 23:59:59" d:\mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --start-time="2018-02-07 00:00:01" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --start-time="2018-02-07 00:00:01" --stop-time="2018-02-07 23:59:59" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

If you want to recover by Specifying particular position..

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --stop-position="255124" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --start-position="107" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --start-position="107" --stop-position="255124" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

If you want to recover by Specifying the operation perform any particular database..

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --database="getmysql" --start-time="2018-02-07 00:00:01" --stop-time="2018-02-07 23:59:59" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

Some more parameter also useful in recover which are already mention earlier .


Note: I'm suggesting to perform this activity on standby server to avoid any miss happening after that propagate the same on production server.


Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...