mysqlbinlog - Binlog has bad magic number error when reading dumped file

Question

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

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
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 */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

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

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.