mysqlmysql-backupmysqlbinlog

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.

Solution

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

    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]