CODEWITHSUNDEEP
mysqlmysql-backupmysqlbinlog
danny
danny

Reputation: 457

Restore mysql database using binary log file (log-bin) on Windows

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.

Upvotes: 0

Views: 4944

Answers (1)

danny
danny

Reputation: 457

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]

Upvotes: 0

Related Questions