0

Using binary log file to restore mysql database is very useful, especially in misoperation. Below is my method to do this on Windows. If this question helps you, please mark it as "Useful", thanks.

danny
  • 427
  • 1
  • 15
  • 25

1 Answers1

0

First, we must check if we have turn on Mysql binary log function.

  • Open Mysql Command Line Client.
  • Execute Command: show variables like 'log_bin%'

You will see it marked as 'on' or 'off'. If shows 'off', you need to open config file 'my.ini', and add below configuration at line 120.

# Binary Logging.
# log-bin
# You could change 'C:\Danny\MySql-BackUp\log-bin' to your own directory.
log_bin=C:\Danny\MySql-BackUp\log-bin\mysql-bin

You could find 'my.ini' at 'C:\ProgramData\MySQL\MySQL Server 5.7'.

After that, you will get binary log file while data changed in database.

Export log file to sql file, this could help you to find timestamp and number easier you want to restore. Below is cmd command:

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqlbinlog.exe C:\Danny\MySql-BackUp\log-bin\mysql-bin.000003 > C:\Danny\MySql-BackUp\log-bin\bin-log.sql

You need to find start-positiont and end-position in bin-log.sql, and execute below command to restore your data.

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqlbinlog --no-defaults C:\Danny\M
ySql-BackUp\log-bin\mysql-bin.000003 --start-position="4" --stop-position="912"
| mysql -uroot -p123456 databaseName

Command Format: mysql -u[username] -p[password] [database name]

danny
  • 427
  • 1
  • 15
  • 25
  • PS: You need restart your Mysql service after edit my.ini file. – danny Feb 01 '18 at 02:03
  • If error happens when restart mysql service, you could try to set log-bin directory as this format "C:/Danny/MySql-BackUp/log-bin/mysql-bin"; – danny Feb 01 '18 at 02:23