All we need is an easy explanation of the problem, so here it is.
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
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
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:
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @[email protected]@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 [email protected]_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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂