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 @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