I am learning MySQL and performing a test point in time restore using MySQL Server 8.0 on Windows 10. I am having an issue re-playing the binary log as follows.

First, set up the test database

mysql -uroot  -e "DROP DATABASE IF EXISTS StackOverflow;"
mysql -uroot  -e "CREATE DATABASE StackOverflow;"
mysql -uroot  StackOverflow -e "CREATE TABLE Users(FirstName VARCHAR(100),LastName VARCHAR(100));"
mysql -uroot  StackOverflow -e "INSERT INTO Users VALUES ('John','Smith');"

perform a full backup

mysqldump --databases StackOverflow --master-data=2 -uroot > C:\Test\SO.sql

Change more data

mysql -uroot StackOverflow -e "INSERT INTO Users VALUES ('Jimi','Jones');"

perform update in error:

mysql -uroot StackOverflow -e "UPDATE Users SET LastName = 'Walker';"

From here, I want to restore the point after we INSERTed Jimi Jones, but before we performed the UPDATE without the WHERE clause.

My proposed process is:

  • restore the full backup (takes us to the point where John Smith was added)
  • restore the binary logs from the point the full backup was taken, to the point before the UPDATE was run

I carry this out as follows:

dump all the binary logs since the full backup to a text file for analysis:

mysqlbinlog --verbose MyPc-bin.000003 --database StackOverflow > C:\test\binlog.txt

once here I can open C:\test\binlog.txt in a text editor and work out where the log number where I need to stop

Perform the full restore:

mysql -uroot < C:\Test\SO.sql

Replay the Log:

mysqlbinlog C:\test\binlog.txt --start-position=89925 --stop-position=90568 | mysql -uroot

here, I run into an error, I get

ERROR: Binlog has bad magic number; It's not a binary log file that can be used by this version of MySQL

If I try and just display the binlog I dumped to the file, to the screen in it's entirity using mysqlbinlog:

mysqlbinlog C:\test\binlog.txt

I get

ERROR: Binlog has bad magic number;  It's not a binary log file that can be used by this version of MySQL
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
# End of log file

I can read the binlog from the server using mysqlbinlog --verbose MyPC-bin.000003 --database StackOverflow

but can't dump it to a file and read that in.

I am thinking maybe this is an encoding issue with the file that is being dumped?

Please can someone advise how I can read the file mysqlbinlog dumped, using mysqlbinlog


1 Answer 1


What you dump using mysqlbinlog --verbose MyPc-bin.000003 --database StackOverflow > C:\test\binlog.txt, that is, the file C:\test\binlog.txt, is not a binary log, so you cannot use it as the source for the binlog command.

What you should do is this:

mysqlbinlog MyPc-bin.000003 --start-position=89925 --stop-position=90568 | mysql -uroot

That is, you need to still read the original log file.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.