mysqlbinlog not applying updates for PITR

Question

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.

Answer 1

For anyone having a similar issue, I was directed by someone on the Percona forum to look at the GTID of the database and of the backups/binarylog. If the GTID matches, you will not receive an error message, it will silently ignore the transaction.

 ID:oracle DB:(none)> SHOW GLOBAL VARIABLES LIKE '%GTID%';
+----------------------------------+------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | b3ee40ef-055c-11ee-aad1-005056a3aeb5:1-3 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+------------------------------------------+
8 rows in set (0.01 sec)
$less BACKUP_DIR/BACKUP_FILE
...
-- GTID state at the beginning of the backup
-- (origin: @@global.gtid_executed)
--
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'b3ee40ef-055c-11ee-aad1-005056a3aeb5:1';
$less backup/binlog.sql
#250718 10:13:21 server id 5 end_log_pos 276 CRC32 0x6b866490 GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1752848001193733 immediate_commit_timestamp=1752848001193733 transaction_length=301
# original_commit_timestamp=1752848001193733 (2025年07月18日 10:13:21.193733 EDT)
# immediate_commit_timestamp=1752848001193733 (2025年07月18日 10:13:21.193733 EDT)
/*!80001 SET @@session.original_commit_timestamp=1752848001193733*//*!*/;
/*!80014 SET @@session.original_server_version=80042*//*!*/;
/*!80014 SET @@session.immediate_server_version=80042*//*!*/;
SET @@SESSION.GTID_NEXT= 'b3ee40ef-055c-11ee-aad1-005056a3aeb5:2'/*!*/;
# at 276
#250718 10:13:21 server id 5 end_log_pos 498 CRC32 0x2b664a27 Query thread_id=131872 exec_time=0 error_code=0 Xid = 122402
use `test03`/*!*/;
SET TIMESTAMP=1752848001/*!*/;
SET @@session.pseudo_thread_id=131872/*!*/;
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) )
/*!*/;
# at 498

I ran RESET MASTER to clear out gtid_executed and gtid_purged prior to reloading the backup. Then the resulting mysqlbinlog update loaded without issue.

ID:oracle DB:(none)> reset master;
Query OK, 0 rows affected (0.01 sec)
ID:oracle DB:(none)> SHOW GLOBAL VARIABLES LIKE '%GTID%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-------+
8 rows in set (0.00 sec)
$mysql < BACKUP_DIR/BACKUP_FILE
ID:oracle DB:(none)> use test03
Database changed
ID:oracle DB:test03> show tables;
Empty set (0.00 sec)
$cat backup/binlog.sql | mysql
ID:oracle DB:(none)> use test03
Database changed
ID:oracle DB:test03> show tables;
+------------------+
| Tables_in_test03 |
+------------------+
| Persons |
+------------------+
1 row in set (0.00 sec)