Replication to be 3 hours behind on this server

MySQL & MariaDB Percona XtraDB Cluster 8.x
,
1

Hi,
I have a mysql xtradb cluster environment with 5 nodes in master master structure. 3 of these nodes are located on dc1 and the other 2 are located on dc2. In addition to my current system, I want to position another server on DC2. I want the replication to be 3 hours behind on this server, and only reading will be done through this newly added node. Is it possible to implement this new architecture that I want to do? If possible, I would like some guidance on how to do it.

2

Hello @bthnklc,
Percona XtraDB Cluster is exactly that, “a cluster”, which means data is the same on every node. If you want to intentionally lag replication, you must use native async replication. You can set up a 3rd MySQL server (non-PXC) in DC2. Configure it to replicate async from node4.dc2 and set SOURCE_DELAY=10800 The blue circles below are your 5-node PXC, and the orange circle is your async replica, 3 hrs delayed.

Screenshot 2023-12-11 at 10.24.05 AM
Screenshot 2023-12-11 at 10.24.05 AM844×498 23.3 KB

3

thanks @matthewb Is it possible to share the configuration example or document for the node on the server that is included in the cluster on DC2 and the Mysql server that will not be included in the cluster?

4

There isn’t really a config to share. The async node is just a regular Percona Server MySQL set up to be a replica.

1 Like
5

Hi matthewb,
we just upgraded 3 node percona xtradb cluster 5.7 to 8.0 (GTID=ON). We have extra host with percona server 8.0 as delayed replica. Everything was working fine until master upgrade. Now the delay replica is failing over and over with:
2024-02-20T20:49:06.058500Z 25 [ERROR] [MY-010584] [Repl] Replica SQL for channel ‘’: Worker 1 failed executing transaction ‘200be2d2-10da-11e4-b7d3-3bbbce82e394:10788071’ at source logbinlog.000002, end_log_pos 16805332; Could not execute Update_rows event on table live.events; Can’t find record in ‘events’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s source log binlog.000002, end_log_pos 16805332, Error_code: MY-001032

This transaction is an update query for some field in table live.events.

After analysis we found out that the transaction with insert query (should happen before update transaction) was just skipped, so the transaction above can’t update field in table.

If we don’t use delay on replication it works fine.

Any idea what’s going on?

6

@sven.star Please open a new post.