Replication question

MySQL & MariaDB
1

Hello,

This question is not regarding the Percona build but about MySQL in general. Here is the scenario I have:

Site A → Replication → Site B

On Site A, there is a source database running, denoted as SDB. On Site B, there is a Group Replication cluster (GRC) consisting of three servers: M1 (master), S1 (slave), and S2 (slave).

The replication channel is defined on M1. However, when M1 goes down, a new master is chosen. My goal is to relocate the replication channel from the currently down M1 to the new master. I want to ensure that replication from SDB continues, irrespective of the current master of the GRC.

I can identify the new chosen master using ProxySQL, but the channel metadata is not replicated. There are tables under the MySQL database that contain relevant information:

slave_master_info
slave_relay_log_info
slave_worker_info
I believe that the following values need to be preserved to update the replication channel on the new master:

Checkpoint_master_log_name: mysqld-bin.XXX
Checkpoint_master_log_pos: 197
or
Master_log_name: mysqld-bin.XXX
Master_log_pos: 1600
Do you have any thoughts or suggestions on how to accomplish this?

P.S.
I can attempt to create a trigger that copies data from the mentioned tables to my replicated table. This would ensure that relevant data is distributed across all GRC members. I am currently exploring this approach. Nevertheless, it’s difficult to believe that no one has encountered this issue before, and I would prefer not to reinvent the wheel.
Thanks

2

Ensure you are running with the following variables:

master_info_repository=TABLE
relay_log_info_repository=TABLE

Verify that while replication is running, the metadata is being replicated by GR by checking the contents of mysql.slave_master_info table. If you see the same updated data on each GR member, then that’s all you need to restart replication.

3

Thank you. I’m in the final stages of setting up the test. The last time I destroyed the cluster, it was because of this issue. I had believed that starting a replica on the new master would be sufficient, but something went wrong. I’ll provide an update once I have the outcome.

4

According to what I see the table mysql.slave_master_info is not replicated within the group replication.
It get updated only on the master.
So, if master shutdown I would be not able to restart replica properly.

What you think?
Thanks!

5

There is strange issue.
mysql.slave_worker_info this is the table that holds the information about the replication status.
for example : Master_log_name | Master_log_pos | Checkpoint_master_log_name | Checkpoint_master_log_pos
But for some reason it not get replicated between group replication master and slaves.
I am not sure if this is an BUG or I misconfigured the servers.

Do you have any experience with that?

Thank you very much

6

I see I forgot to mention important information.
On site A (the source) there is no GTID enabled.
The replication channel defined with ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS.
Therefore I can’t use SOURCE_AUTO_POSITION (which would be the solution otherwise)

Thanks