Sample Header Ad - 728x90

mysqlbinlog not applying updates for PITR

0 votes
1 answer
21 views
I'm running Percona MySQL 8.0.42-33 on RHEL 9.6. I have a problem when attempting point-in-time-recovery, where I can restore the database from the mysqldump backup, but the application of the binlog isn't updating anything in the database. There are no errors, and verbose output shows the statements being executed, but there are no changes in the database. At first I thought this could be specific to the database I was attempting to restore, but running similar tests in our sandbox instance produced the same result. This is using a replica server that has replication stopped, and I reset the replica and master data before starting this process if that makes any difference. Example Steps 1. create database test03 2. mysqldump --all-databases --log-error BACKUP_LOG --max-allowed-packet=1G --single-transaction --verbose --flush-logs --source-data=2 | gzip > BACKUP_DIR/BACKUP_FILE 3. `CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );` 4. drop database test03 5. mysql < BACKUP_DIR/BACKUP_FILE 6. mysqlbinlog -vv log_bin.000002 --start-position=197 --stop-position=518 | mysql -v 7. use test03 8. show tables - Empty set (0.00 sec) In this case the test03 database is restored from backup, but the Persons table is not added when processing the binlog. I've verified that I'm not read-only or anything like that, and I can see the transaction in the binlog:
#250717  9:32:22 server id 5  end_log_pos 518 CRC32 0x798e87b9  Query   thread_id=125856        exec_time=0     error_code=0    Xid = 112497
use test03/*!*/;
SET TIMESTAMP=1752759142/*!*/;
SET @@session.pseudo_thread_id=125856/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=5/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
)
/*!*/;
along with in the verbose output from mysql: -------------- CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ) -------------- If someone can point me in the right direction it would be greatly appreciated. I'm at a loss for what to check next, and searches have produced no results thus far.
Asked by emaN_yalpsiD (1 rep)
Jul 17, 2025, 02:31 PM
Last activity: Jul 18, 2025, 04:23 PM